Getting auto-generated key from row insertion in spring 3 / PostgreSQL 8.4.9
The easiest way to get a key back from an INSERT with Spring JDBC is to use the SimpleJdbcInsert
class. You can see an example in the Spring Reference Guide, in the section titled Retrieving auto-generated keys using SimpleJdbcInsert.
KeyHolder holder = new GeneratedKeyHolder();
getJdbcTemplate().update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection connection)
throws SQLException {
PreparedStatement ps = connection.prepareStatement(sql.toString(),
Statement.RETURN_GENERATED_KEYS);
ps.setString(1, person.getUsername());
ps.setString(2, person.getPassword());
ps.setString(3, person.getEmail());
ps.setLong(4, person.getRole().getId());
return ps;
}
}, holder);
Long newPersonId = holder.getKey().longValue();
Note that in newer versions of Postgres you need to use
connection.prepareStatement(sql.toString(),
new String[] { "idcompte" /* name of your id column */ })
instead of
connection.prepareStatement(sql.toString(),
Statement.RETURN_GENERATED_KEYS);
I'm using Spring3.1 + PostgreSQL9.1, and when I use this
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection)
throws SQLException {
PreparedStatement ps =
connection.prepareStatement(youSQL,
Statement.RETURN_GENERATED_KEYS);
ps.setString(1, post.name_author);
...
return ps;
}
}, keyHolder);
long id = keyHolder.getKey().longValue();
I got this exception:
org.springframework.dao.InvalidDataAccessApiUsageException:
The getKey method should only be used when a single key is returned.
The current key entry contains multiple keys: ...
So I changed to :
PreparedStatement ps =
connection.prepareStatement(youSQL, new String[]{"id"});
where "id" is
id serial not null primary key
And the problem is solved. So I supposed that using
prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
is not right here. The official guide is here, Chapter 13.2.8 :Retrieving auto-generated keys