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…

Accepted Answer

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

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

Solution

Problem

Related problems