How to get generated ID after I inserted into a ne

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);
}

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();