从 spring 3/PostgreSQL 8.4.9 中的行插入获取自动生成的键 [英] Getting auto-generated key from row insertion in spring 3 / PostgreSQL 8.4.9

问题描述

我想从行插入中检索自动生成的 ID，但我得到一个 NullPointerException

I would like to retrieve the auto-generated id from a row insertion, but I get a 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 表:

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 支持自动生成的键，但我得到这个异常:

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

但我得到一个 PSQLException :

序列compte_idcompte_seq的当前值(currval)在这个session中没有定义，虽然我以为compte_idcompte_seq.NEXTVAL应该在插入行时调用

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

插入行时正确创建自增值

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

有什么想法吗?

推荐答案

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 中，您需要使用

Note that in newer versions of Postgres you need to use

connection.prepareStatement(sql.toString(), 
    new String[] { "idcompte" /* name of your id column */ })

代替

connection.prepareStatement(sql.toString(), 
    Statement.RETURN_GENERATED_KEYS);

这篇关于从 spring 3/PostgreSQL 8.4.9 中的行插入获取自动生成的键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！