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 ?

Answer 1

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