mysql getjdbctemplate 获取插入数据主键,在使用Spring JDBCTemplate在数据库中插入新的数据记录后，如何获取生成的ID？...

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

}

解决方案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();