12

I'm using Jdbctemplate and I need the inserted id of a query. I read that I have to build a particular PreparedStatement and use GeneratedKeyHolder object.

The problem is that in my application all inserts method uses this JdbcTemplate update method:

getJdbcTemplate().update(SQL_INSERT,param1,param2,param3,...);

Is there another way to get the inserted id without refactoring all daos?

Tobia
  • 9,165
  • 28
  • 114
  • 219
  • 1
    If you're using NamedParameterJdbcTemplate, then it's very easy. Check https://alvinalexander.com/blog/post/jdbc/spring-jdbc-insert-auto-generated-key. – Eagle_Eye Jan 27 '18 at 13:45

2 Answers2

25

Looking at the documentation for NamedParameterJdbcTemplate and JdbcTemplate You have two choices:

use NamedParameterJdbcTemplate's update method.

use JdbcTemplate's update method.

There are also some other methods available which will populate the keys to the given GeneratedKeyHolder, it's up to you which one suits your needs.

EDIT

For e.g. using JdbcTemplate:

GeneratedKeyHolder holder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
    @Override
    public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
        PreparedStatement statement = con.prepareStatement("INSERT INTO SOME_TABLE(NAME, VALUE) VALUES (?, ?) ", Statement.RETURN_GENERATED_KEYS);
        statement.setString(1, "SomeName");
        statement.setString(2, "SomeValue");
        return statement;
    }
}, holder);

long primaryKey = holder.getKey().longValue();
Paulius Matulionis
  • 23,085
  • 22
  • 103
  • 143
  • Ok but how to wrap existing code? (param1,param2,param3...args) – Tobia Jan 29 '16 at 16:31
  • 1
    If you'd choose NamedParameterJdbcTemplate you'll have to create SqlParameterSource, otherwise with JdbcTemplate you'd have to use PreparedStatementCreator. Give me a sec, I'll update my answer giving an example. – Paulius Matulionis Jan 29 '16 at 16:32
  • MMh... so I have to refactor all daos... now other way? – Tobia Jan 29 '16 at 16:38
  • 1
    What spring documentation states - yes, there is no other way. – Paulius Matulionis Jan 29 '16 at 16:39
  • Is the statement with this example code recreated for each call? For example if I have many inserts should I use a different storing the PreparedStatement? – Tobia Feb 05 '16 at 14:53
  • If you'd have many inserts you have to use [batchUpdate](http://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html#batchUpdate-java.lang.String-org.springframework.jdbc.core.BatchPreparedStatementSetter-). You can have a look at examples [here](http://www.mkyong.com/spring/spring-jdbctemplate-batchupdate-example/) – Paulius Matulionis Feb 05 '16 at 19:06
  • in case you use NEWID(), sql server , how would you handle ? – Tiago Medici Feb 21 '20 at 12:45
  • How can we get the same value in Batch Update? – PAA Feb 14 '22 at 12:17
0

This does not work for PostgreSQL, the holder returns all the attributes. Here is a link to the solution in PostgreSQL Spring: How to use KeyHolder with PostgreSQL

alext
  • 678
  • 1
  • 11
  • 25