How to get generated ID after I inserted into a new data record in database using Spring JDBCTemplate?

Question

I got a very common question when I was using Spring JDBCTemplate, I want to get the ID value after I inserted a new data record into database, this ID value will be referred to another related table. I tried the following way to insert it, but I always return 1 rather than its real unique ID. (I use MySQL as the database)

public int insert(BasicModel entity) {
    String insertIntoSql = QueryUtil.getInsertIntoSqlStatement(entity);

    log.info("SQL Statement for inserting into: " + insertIntoSql);

    return this.jdbcTemplate.update(insertIntoSql);
}

Margaretmargareta

Answer 1

JdbcTemplate.update() returns:

the number of rows affected

Which is always 1 for INSERT statement. Different databases support generated key extraction in different ways, but most JDBC drivers abstract this and JdbcTemplate supports this. Quoting 12.2.8 Retrieving auto-generated keys

An update() convenience method supports the retrieval of primary keys generated by the database. This support is part of the JDBC 3.0 standard; see Chapter 13.6 of the specification for details.

Basically you need this much more verbose statement:

final String insertIntoSql = QueryUtil.getInsertIntoSqlStatement(entity);
KeyHolder keyHolder = new GeneratedKeyHolder();

jdbcTemplate.update(
  new PreparedStatementCreator() {
    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
      return connection.prepareStatement(insertIntoSql, new String[] {"id"});
    }
  }, keyHolder);

return keyHolder.getKey().intValue();

Laryngoscope

Hi Tomasz, I try your method, but didnt work in my place. I work on it on long time but cant get the solution. Kindly advise #24568133 – Petrous

How can we do the same in Batch update? – Aerostatic

Answer 2

You can use @Tomasz Nurkiewicz answer and it works for sure, but you don't need to : Spring offers SimpleJdbcInsert which "provides meta-data processing to simplify the code needed to construct a basic insert statement".

@Repository
public class UserDao {

    private JdbcTemplate jdbcTemplate;
    private SimpleJdbcInsert insertIntoUser;

    @Autowired
    public UserDao(DataSource datasource) {
        jdbcTemplate = new JdbcTemplate(datasource);
        insertIntoUser = new SimpleJdbcInsert(jdbcTemplate).withTableName("user").usingGeneratedKeyColumns("id_user");
    }

    public Number insertUser(User u) {
        final Map<String, Object> parameters = new HashMap<>();
        parameters.put("name", u.getName());

        return insertIntoUser.executeAndReturnKey(parameters);
    }
}

The "Number" returned by insertUser is the id_user generated for the user insertion.

Waspish

Thanks, I liked this approach and it worked. – Philbrick

Documentation: Simplifying JDBC Operations with the SimpleJdbc Classes – Defence

Answer 3

@panadol-chong, a small modification was necessary for @tomasz-nurkiewicz's code to work here.

final String SQL = "INSERT INTO ... RETUNING id";

KeyHolder keyHolder = new GeneratedKeyHolder();

jdbcTemplate.update(connection -> {
    PreparedStatement ps = connection.prepareStatement(SQL, 
                           Statement.RETURN_GENERATED_KEYS);

    return ps;
}, keyHolder);

return keyHolder.getKey().intValue();

The main difference is the Statement.RETURN_GENERATED_KEYS.

Covariance