views:

548

answers:

3

Is it possible to get the @@identity from the SQL insert on a Spring jdbc template call? If so, how?

TIA

+2  A: 

I don't know if there is a "one-liner" but this seems to do the trick (for MSSQL at least):

// -- call this after the insert query...
this._jdbcTemplate.queryForInt( "select @@identity" );

Decent article here.

javamonkey79
+3  A: 

The JDBCTemplate.update method is overloaded to take an object called a GeneratedKeyHolder which you can use to retrieve the autogenerated key. For example (code taken from here):

final String INSERT_SQL = "insert into my_test (name) values(?)";
final String name = "Rob";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
    new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps =
                connection.prepareStatement(INSERT_SQL, new String[] {"id"});
            ps.setString(1, name);
            return ps;
        }
    },
    keyHolder);
// keyHolder.getKey() now contains the generated key
Jason Gritman
That would be the "one liner" I am looking for here. Nice. Sad thing is I saw the link but glossed past it due to this: "part of the JDBC 3.0 standard". (I don't think we use JDBC 3.0, but I also don't think this is relevant).
javamonkey79
+2  A: 

How about SimpleJdbcInsert.executeAndReturnKey?

tpierzina
Wow, I didn't really know about that class - kinda neat. Thanks. +1
javamonkey79