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