PostgreSQL JDBC getGeneratedKeys返回所有列 [英] PostgreSQL JDBC getGeneratedKeys returns all columns

问题描述

我最近在项目的后端从MySQL切换到PostgreSQL，发现我的一些数据库代理方法需要检查.为了插入链接的对象，我使用事务来确保所有内容都已存储.我使用jdbc方法(例如setAutoCommit(false)和commit())来执行此操作.我编写了一个实用程序方法，该方法将记录插入表中并返回生成的键.基本上，我遵循如下所述的技术2:

http://www.selikoff .net/2008/09/03/database-key-generation-in-java-applications/

自项目开始以来就一直有效，但是从MySQL迁移到PostgreSQL后，getGeneratedKeys返回新插入记录的所有列(请参见下面的控制台输出).

代码:

final ResultSet keys = ps.getGeneratedKeys();
final ResultSetMetaData metaData = keys.getMetaData();
for (int j = 0; j < metaData.getColumnCount(); j++) {
    System.out.println("Col name: "+metaData.getColumnName(j+1));
}

输出:

Col name: pathstart
Col name: fk_id_c
Col name: xpathid
Col name: firstnodeisroot

表的数据库签名(从pgAdmin III自动生成的SQL):

CREATE TABLE configuration.configuration_xpath
(
  pathstart integer NOT NULL,
  fk_id_c integer NOT NULL,
  xpathid integer NOT NULL DEFAULT nextval('configuration.configuration_xpath_id_seq'::regclass),
  firstnodeisroot boolean NOT NULL DEFAULT false,
  CONSTRAINT configuration_xpath_pkey PRIMARY KEY (xpathid),
  CONSTRAINT configuration_fk FOREIGN KEY (fk_id_c)
      REFERENCES configuration.configuration (id_c) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)

PK后面序列的数据库签名:

CREATE SEQUENCE configuration.configuration_xpath_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 242
  CACHE 1
  OWNED BY configuration.configuration_xpath.xpathid;

所以问题是，为什么getGeneratedKeys返回所有列而不是仅返回生成的键?我在这里搜索并找到了其他有类似问题的人:

http://www. postgresql.org/message-id/004801cb7518$cbc632e0$635298a0$@[e-mail]

但是他们的问题尚未得到解答，仅提供了建议的解决方法.

解决方案

大多数驱动程序通过在查询结束时使用自动生成的列附加RETURNING子句来支持getGeneratedKeys(). PostgreSQL返回所有字段，因为它具有RETURNING *，它仅返回所有列.这意味着要返回生成的密钥，不必查询系统表即可确定要返回的列，从而节省了网络往返次数(和查询时间).

这是JDBC规范隐式允许的，因为它 >

另一个原因可能是很难确定哪些列是自动生成的，哪些不是(我不确定这是否适用于PostgreSQL).例如，在Jaybird(我维护的Firebird的JDBC驱动程序)中，我们还返回了所有列，因为在Firebird中，无法确定哪些列是自动生成的(但是我们确实需要在系统表中查询列名称，因为Firebird确实需要没有RETURNING *).

因此，始终建议通过列名而不是位置来显式查询生成的键ResultSet.

其他解决方案是使用接受http://www.selikoff.net/2008/09/03/database-key-generation-in-java-applications/

This has worked since the start of the project, but after migrating from MySQL to PostgreSQL getGeneratedKeys returns all the columns of the newly inserted record (see console output below).

Code:

final ResultSet keys = ps.getGeneratedKeys();
final ResultSetMetaData metaData = keys.getMetaData();
for (int j = 0; j < metaData.getColumnCount(); j++) {
    System.out.println("Col name: "+metaData.getColumnName(j+1));
}

Output:

Col name: pathstart
Col name: fk_id_c
Col name: xpathid
Col name: firstnodeisroot

Database signature for the table (auto generated SQL from pgAdmin III):

CREATE TABLE configuration.configuration_xpath
(
  pathstart integer NOT NULL,
  fk_id_c integer NOT NULL,
  xpathid integer NOT NULL DEFAULT nextval('configuration.configuration_xpath_id_seq'::regclass),
  firstnodeisroot boolean NOT NULL DEFAULT false,
  CONSTRAINT configuration_xpath_pkey PRIMARY KEY (xpathid),
  CONSTRAINT configuration_fk FOREIGN KEY (fk_id_c)
      REFERENCES configuration.configuration (id_c) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)

Database signature for the sequence behind the PK:

CREATE SEQUENCE configuration.configuration_xpath_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 242
  CACHE 1
  OWNED BY configuration.configuration_xpath.xpathid;

So the question is, why is getGeneratedKeys returning all the columns instead of just the generated key? I've searched and found someone else with a similar problem here:

http://www.postgresql.org/message-id/004801cb7518$cbc632e0$635298a0$@[e-mail]

But their question has not been answered, only a suggested workaround is offered.

解决方案

Most drivers support getGeneratedKeys() by tacking on a RETURNING-clause at the end of the query with the columns that are auto-generated. PostgreSQL returns all fields because it has RETURNING * which simply returns all columns. That means that to return the generated key it doesn't have to query the system table to determine which column(s) to return, and this saves network roundtrips (and query time).

This is implicitly allowed by the JDBC specification, because it says:

Note:If the columns which represent the auto-generated keys were not specified, the JDBC driver implementation will determine the columns which best represent the auto-generated keys.

Reading between the lines you can say that this allows for saying 'I don't know, or it is too much work, so all columns best represent the auto-generated keys'.

An additional reason might be that it is very hard to determine which columns are auto-generated and which aren't (I am not sure if that is true for PostgreSQL). For example in Jaybird (the JDBC driver for Firebird that I maintain) we also return all columns because in Firebird it is impossible to determine which columns are auto-generated (but we do need to query the system tables for the column names because Firebird does not have RETURNING *).

Therefor it is always advisable to explicitly query the generated keys ResultSet by column name and not by position.

Other solutions are explicitly specifying the column names or the column positions you want returned using the alternate methods accepting a String[] or int[] (although I am not 100% sure how the PostgreSQL driver handles that).

BTW: Oracle is (was?) even worse: by default it returns the ROW_ID of the row, and you need to use a separate query to get the (generated) values from that row.

这篇关于PostgreSQL JDBC getGeneratedKeys返回所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！