identity from sql insert via jdbctemplate
Solution 1
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
Solution 2
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 interfaceSimpleJdbcInsertOperations
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(
SqlParameterSource
parameterSource)
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 interfaceSimpleJdbcInsertOperations
Parameters:
parameterSource - SqlParameterSource containing values to use for insert
Returns:
the generated key value.
Solution 3
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();
Solution 4
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
Hi, I'm Shaun! I enjoying creating software. I started my career in enterprise integration (think: camel, activemq, java). Nowadays, I typically work on back end data systems (databases, datastores, publishing, infrastructure). I work on the data engineering team, at edmunds.com (beep beep). In my formative years I was primarily interested java, Eclipse and maven, and I also use to dink around in Python, Perl, Bash Scripts, Windows Scripts, HTML, javascript, PHP, CSS, C, ASP, VB, C#, OSGi, PDE, RCP...and the list goes on. Since 2017, I've gone back end to front end and and back again, working on service engineering teams (tomcat, spring, java) to front end teams (react, redux, bootstrap) and finally back again to where I am now on data engineering (spark, spark-sql, databricks, scala). I use to blog here and here is my gitlab account here if you want to see some of the open source stuff I've done.
Updated on August 17, 2020Comments
-
javamonkey79 almost 4 years
Is it possible to get the @@identity from the SQL insert on a Spring jdbc template call? If so, how?