Apr 24, 2008

Last Insert Id Without Extra SELECT in Spring

If you have a table with auto_increment for an id, you often need to get this id immediately after a new instance has been created.

The easy way to do this is to perform a SELECT LAST_INSERT_ID() query. However, most of the time you want as less database requests as possible. There is a way to avoid LAST_INSERT_ID() code using Spring's JDBC support.

final String DEF_INSERT_USER_QUERY = "insert into users (username, password) values (?,?)";
final String firstname = createdUser.getFirstname();
final String lastname = createdUser.getLastname();

KeyHolder keyHolder = new GeneratedKeyHolder();
getJdbcTemlpate().update(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection)
throws SQLException {
PreparedStatement ps = connection.prepareStatement(DEF_INSERT_USER_QUERY,
new String[] {"firstname", "lastname"});
ps.setString(1, firstname);
ps.setString(2, lastname);
return ps;
}, keyHolder);



Diego Silva said...

it's great! it's just that I needed!

Neeraj Verma said...

As of Spring 2.5 you can use SimpleJdbcInsert

Manual page:

sp said...

Thanks for bringing that up, Neeraj.