从PostgreSQL中批量插入的行中检索序列号 [英] Retrieving serial id from batch inserted rows in postgresql

问题描述

这是有效的代码:

        Connection c = ds.getConnection();
        c.setAutoCommit(false);
        PreparedStatement stmt = c.prepareStatement("INSERT INTO items (name, description) VALUES(?, ?)");
        while (!(items = bus.take()).isEmpty()) {
          for (Item item : items) {
            stmt.setString(1, item.name);
            stmt.setString(2, item.description);
            stmt.addBatch();
          }
          stmt.executeBatch();
          c.commit();
        }

但是现在我需要填充另一个表，其中id是外键. 如果我将INSERT与RETURNING id一起使用，则executeBatch会失败，并显示在没有预期结果时返回结果"错误.

But now I need to populate another table where id is a foreign key. If I use INSERT with RETURNING id then executeBatch fails with "A result was returned when none was expected" error.

我看到几种解决方法

• 执行单个插入，而不是批量插入.
• 用客户端生成的guid替换序列号.
• 使用某种存储过程来执行批量插入并返回ID列表.

在我看到的最后三个方法中，似乎同时保留了批处理插入和返回id的效率，但这对我来说也是最复杂的，因为我从未编写过存储过程.

Of the three methods that I see the last one seems to preserve both the efficiency of batch insert and return the ids, but it is also the most complex for me as I have never written stored procedures.

是否有更好的方法来批量插入并获取ID?我使用Postgresql特定的API而不是jdbc没问题.

Is there a better way to batch insert and get the IDs? I have no problem using postgresql specific API rather than jdbc.

如果没有，那么任何人都可以绘制这样的存储过程吗?

If not, could any one sketch such a stored procedure?

这是表模式:

CREATE UNLOGGED TABLE items
(
  id serial,
  name character varying(1000),
  description character varying(10000)
)
WITH (
  OIDS=FALSE
);

推荐答案

类似的方法应该起作用:

Something like this should work:

// tell the driver you want the generated keys
stmt =  c.prepareStatement("INSERT ... ", Statement.RETURN_GENERATED_KEYS);

stmt.executeBatch();

// now retrieve the generated keys
ResultSet rs = stmt.getGeneratedKeys();
while (rs.next()) {
 int id = rs.getInt(1);
 .. save the id somewhere or update the items list 
}

我认为(我不确定不确定！)密钥是按生成顺序返回的.因此，ResultSet中的第一行应映射到您正在处理的列表中的第一个项目".但是请确认！

I think (I am not sure!) that the keys are returned in the order they were generated. So the first row from the ResultSet should map to the first "item" from the list you are processing. But do verify that!

修改

如果这不起作用，请尝试指定为其生成值的实际列:

If that doesn't work, try specifying the actual columns for which the values are generated:

stmt =  c.prepareStatement("INSERT ... ", new String[] {"id"});

这篇关于从PostgreSQL中批量插入的行中检索序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！