identity from sql insert via jdbctemplate

Question

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

score 102 · Accepted Answer · answered Nov 03 '09 at 16:14

102

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

answered Nov 03 '09 at 16:14

Jason Gritman

5,251
4
30
38

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 Nov 03 '09 at 16:21
2

It getting a real one liner in Java 8 with lambdas \m/ – fabwu Dec 17 '15 at 14:33
1

What if the generated key is not a number, but a string? – DCameronMauch Jan 10 '19 at 20:36
in case of using NEWID, which is a hash (string) how would you return it? – Tiago Medici Feb 21 '20 at 10:27
KeyHolder has not only getKey(), but also getKeys() and getKeyList() methods, returning key values as Objects. – yuriy.weiss Mar 04 '20 at 15:01
Does the table (my_test) has to have IDENTITY property? without it, I am getting null exception. – Jonathan Hagen Sep 24 '20 at 15:40

score 52 · Answer 2 · edited Jul 08 '18 at 14:40

How about SimpleJdbcInsert.executeAndReturnKey? It takes two forms, depending on the input:

(1) The input is a `Map`

public java.lang.Number executeAndReturnKey(java.util.Map<java.lang.String,?> args)

Description copied from interface: SimpleJdbcInsertOperations

Execute the insert using the values passed in and return the generated key. This requires that the name of the columns with auto generated keys have been specified. This method will always return a KeyHolder but the caller must verify that it actually contains the generated keys.

Specified by:

executeAndReturnKey in interface SimpleJdbcInsertOperations

Parameters:

args - Map containing column names and corresponding value

Returns:

the generated key value

(2) The input is a `SqlParameterSource`

public java.lang.Number executeAndReturnKey(SqlParameterSourceparameterSource)

Description copied from interface: SimpleJdbcInsertOperations

Execute the insert using the values passed in and return the generated key. This requires that the name of the columns with auto generated keys have been specified. This method will always return a KeyHolder but the caller must verify that it actually contains the generated keys.

Specified by:

executeAndReturnKey in interface SimpleJdbcInsertOperations

Parameters:

parameterSource - SqlParameterSource containing values to use for insert

Returns:

the generated key value.

Wow, I didn't really know about that class - kinda neat. Thanks. +1 — javamonkey79, Nov 12 '09 at 01:15
I like this version more than version with PreparedStatementCreator. — ruslanys, Aug 13 '15 at 14:07

score 28 · Answer 3 · edited Aug 16 '18 at 10:55

28

Adding detailed notes/sample code to todd.pierzina answer

jdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
        jdbcInsert.withTableName("TABLE_NAME").usingGeneratedKeyColumns(
                "Primary_key");
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("Column_NAME1", bean.getval1());
        parameters.put("Column_NAME2", bean.getval2());
        // execute insert
        Number key = jdbcInsert.executeAndReturnKey(new MapSqlParameterSource(
                parameters));
           // convert Number to Int using ((Number) key).intValue()
            return ((Number) key).intValue();

edited Aug 16 '18 at 10:55

Peter Szanto

7,568
2
51
53

answered Feb 07 '14 at 09:31

Sheetal Mohan Sharma

2,908
1
23
24

1

I'm facing this exception : org.springframework.dao.InvalidDataAccessResourceUsageException: The getGeneratedKeys feature is not supported by this database – Az.MaYo Apr 01 '16 at 05:05
@Az.MaYo - Your issue could be related to JDBC driver version. – Sheetal Mohan Sharma Apr 18 '16 at 13:05

score 3 · Answer 4 · answered Nov 03 '09 at 08:15

3

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.

answered Nov 03 '09 at 08:15

javamonkey79

17,443
36
114
172

3

Page not found with the link. – Ruslan May 09 '17 at 12:02
1

For SQL Server, @@identity returns the last id generated on any table in the database in the current session. So for example if your insert causes a trigger to fire that also generates a new identity in some table, you'll get that value. In a majority of cases you never want to use @@identity. – Rick Oct 01 '18 at 15:49

identity from sql insert via jdbctemplate

4 Answers4

(1) The input is a `Map`

(2) The input is a `SqlParameterSource`

Linked

identity from sql insert via jdbctemplate

4 Answers4

(1) The input is a Map

(2) The input is a SqlParameterSource

Linked

(1) The input is a `Map`

(2) The input is a `SqlParameterSource`