Getting auto-generated key from row insertion in spring 3 / PostgreSQL 8.4.9

Question

I would like to retrieve the auto-generated id from a row insertion, but I get a NullPointerException

Here is the code :

long result = 0;
        final String SQL = "INSERT INTO compte (prenom, nom, datenaissance, numtelephone) "
                            + " VALUES(?,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int row= this.jdbcTemplate.update(new PreparedStatementCreator(){
            public PreparedStatement createPreparedStatement(Connection connection)
                throws SQLException {
                PreparedStatement ps =connection.prepareStatement(SQL);
                ps.setString(1, a.getSurname());
                ps.setString(2, a.getName());
                ps.setDate(3, a.getDob());
                ps.setString(4, a.getPhone());
                return ps;
            }
        },keyHolder);

        if (row > 0)
            result = keyHolder.getKey().longValue(); //line 72

And this is the PostgreSQL table :

CREATE TABLE compte
(
  idcompte serial NOT NULL,
  prenom character varying(25) NOT NULL,
  nom character varying(25) NOT NULL,
  datenaissance date NOT NULL,
  numtelephone character varying(15) NOT NULL,
  CONSTRAINT pk_compte PRIMARY KEY (idcompte )
);

PostgreSQL supports auto-generated keys, but I get this exception :

java.lang.NullPointerException
    at com.tante.db.JDBCUserAccountDAO.insertAccount(JDBCUserAccountDAO.java:72)

EDIT : I tried this to get the auto generated key :

result = jdbcTemplate.queryForLong("select currval('compte_idcompte_seq')");

but I get a PSQLException :

the current value (currval) of the sequence compte_idcompte_seq is not defined in this session, although I thought that compte_idcompte_seq.NEXTVAL should have been called when inserting the row

EDIT :

The auto-increment value is properly created when a row is inserted

Any idea ?

sorry, the line 72 refers to `keyHolder.getKey().longValue()` — Jerec TheSith, May 15 '12 at 09:13
but `keyHolder.getKeys().get("idcompte")` is null too, so is there any way to retrieve the auto-generated key ? — Jerec TheSith, May 15 '12 at 09:17

score 64 · Accepted Answer · edited Nov 27 '17 at 17:05

64

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);

edited Nov 27 '17 at 17:05

Dan Oak

704
1
7
26

answered Apr 17 '13 at 13:29

Artur

656
6
2

I am coding it in another way. Trying to learn from your method but keep hit error. Kindly help: http://stackoverflow.com/questions/24567633/get-back-the-primary-key-value-after-insertion-to-db2-table-by-keyholder – Panadol Chong Jul 04 '14 at 09:26
1

I was missing the Statement.RETURN_GENERATED_KEYS in my query. Adding this made it work. – Rory Hunter Sep 29 '14 at 14:50
This doesn't seem to work with HSQL (it returns `This function is not supported`) – maja May 13 '15 at 08:47
Is there a way to do this without a PreparedStatementCreator? I have some strange issues with PreparedStatement. – arunwithasmile Mar 09 '18 at 12:21
doesnt work for mssql database https://stackoverflow.com/questions/61236730/for-sql-server-database-getting-com-microsoft-sqlserver-jdbc-sqlserverexception – nikhil kekan Apr 15 '20 at 21:33

score 22 · Answer 2 · answered May 15 '12 at 16:42

22

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.

answered May 15 '12 at 16:42

Paul

19,704
14
78
96

score 14 · Answer 3 · answered Nov 27 '13 at 05:32

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

score 9 · Answer 4 · edited Mar 30 '19 at 05:16

A solution using NamedParameterJdbcTemplate with Sequence.nextval :

        MapSqlParameterSource parameters = new MapSqlParameterSource();
        parameters.addValue("prenom", prenom);
        parameters.addValue("nom", nom);
        parameters.addValue("datenaissance", datenaissance);
        parameters.addValue("numtelephone", numtelephone);

    final String SQL = "INSERT INTO compte (idcompte,  prenom, nom, datenaissance, numtelephone) "
                        + " VALUES(compte_idcompte_seq.NEXTVAL, :prenom, :nom, :datenaissance, :numtelephone)";

        KeyHolder keyHolder = new GeneratedKeyHolder();
        NamedParameterJdbcTemplate namedJdbcTemplate = new NamedParameterJdbcTemplate(txManager.getDataSource());
        int nb = namedJdbcTemplate.update(SQL, parameters, keyHolder, new String[]{"idcompte"});
        Long generatedId = keyHolder.getKey().longValue();

I like this solution because with NamedParameterJdbcTemplate because the parameters are passed by name and the code is more readable and less prone to errors, especially when there are big queries.

I dont see a way to get Primary keys in bulk for the batch update. We might need to loop through and get the details — PAA, Feb 22 '22 at 16:20

score 1 · Answer 5 · edited May 23 '17 at 10:31

1

There seems to be some known issues with Keyholder and PostgreSQL. Have a look at the workaround at this link Spring JDBC - Last inserted id

Also do check the database table directly to see if the record is inserted properly(i.e with PK). This will help to narrow down the problem.

edited May 23 '17 at 10:31

Community

1
1

answered May 15 '12 at 09:33

Kshitij

8,474
2
26
34

score 1 · Answer 6 · edited May 23 '17 at 10:31

1

Take a look at this post, especially the accepted answer of using the INSERT...RETURNING syntax:

How to get a value from the last inserted row?

This the best way to get this value in PostgreSQL as you only make one network round trip and it's done as a single atomic operation on the server.

edited May 23 '17 at 10:31

Community

1
1

answered May 15 '12 at 16:23

Matthew Wood

16,017
5
46
35

score 1 · Answer 7 · answered Mar 22 '17 at 20:48

1

I had the same problem and it turned out that my table ID was not auto incrementing.

answered Mar 22 '17 at 20:48

Eidan Spiegel

307
1
2
10

score 1 · Answer 8 · answered Apr 25 '17 at 18:56

I faced the similar problem. I do not know why exactly I faced this problem but good thing is I got to resolve this by using below code:

final KeyHolder holder = new GeneratedKeyHolder();
int status = myTemplate.update(yourInsertSQL, namedParameters, holder, new String[]{"PrimaryKeyColumnName"});

Hope that helps someone.

score 0 · Answer 9 · answered Nov 26 '18 at 04:27

0

setGeneratedKeysColumnNames(new String[]{"column_name"});

Don't forget to set the names for the auto-generated columns.

answered Nov 26 '18 at 04:27

N. Osil

494
7
13

score 0 · Answer 10 · answered Aug 23 '22 at 15:33

Why go through all that pain when you can just use native RETURNING:

INSERT INTO compte (prenom, nom, datenaissance, numtelephone)
VALUES (?, ?, ?, ?)
RETURNING id;

Or with JDBC:

try (PreparedStatement s = c.prepareStatement("""
    INSERT INTO compte (prenom, nom, datenaissance, numtelephone)
    VALUES (?, ?, ?, ?)
    RETURNING id;
    """
)) {
    s.setString(1, a.getSurname());
    s.setString(2, a.getName());
    s.setDate(3, a.getDob());
    s.setString(4, a.getPhone());

    try (ResultSet rs = s.executeQuery()) {
        while (rs.next())
            System.out.println("ID = " + rs.getLong(1));
    }
}

This approach is obviously PostgreSQL specific (though it also works on Firebird, MariaDB). If you're using another SQL dialect, then this blog post showing the different native syntaxes or JDBC approaches might help.

Getting auto-generated key from row insertion in spring 3 / PostgreSQL 8.4.9

10 Answers10

Linked