我想从行插入中检索自动生成的ID,但得到一个 NullPointerException
NullPointerException
这是代码:
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
这是PostgreSQL表:
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支持自动生成的密钥,但是我得到了这个异常:
java.lang.NullPointerException at com.tante.db.JDBCUserAccountDAO.insertAccount(JDBCUserAccountDAO.java:72)
编辑:我试图这样做以获得自动生成的密钥:
result = jdbcTemplate.queryForLong("select currval('compte_idcompte_seq')");
但我得到了PSQLException:
PSQLException
the current value (currval) of the sequence compte_idcompte_seq is not defined in this session,尽管我认为compte_idcompte_seq.NEXTVAL 应该在插入行时调用
the current value (currval) of the sequence compte_idcompte_seq is not defined in this session
compte_idcompte_seq.NEXTVAL
编辑:
插入行时正确创建了自动增量值
任何的想法 ?
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();
请注意,在较新版本的Postgres中,你需要使用
connection.prepareStatement(sql.toString(), new String[] { "idcompte" /* name of your id column */ })
代替
connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);