使用JDBC参数标记的Postgresql upsert

Question

我有一个INSERT语句，今天使用参数标记从JDBC调用时有效：

INSERT INTO errorqueue as eq (jobname, sourceid, item) VALUES(?,?,?)

在我的Java代码中，我绑定了参数：

Connection connection=null;
PreparedStatement stmt=null;
try {

    connection = getConnection();
    stmt = connection.prepareStatement(sqlInsert);

    stmt.setString(1, this.jobName);
    stmt.setString(2, errorItem.getId());
    stmt.setString(3, item.getBody());
    stmt.executeUpdate();
} catch () {
...
}

我正在思考如果将此转换为UPSERT，需要如何处理参数：

INSERT INTO errorqueue as eq (jobname, sourceid, item) VALUES(?,?,?) ON CONFLICT (jobname,sourceid) UPDATE eq SET item=? Where jobname=? and sourceid=?;

这个问题有点隐晦，但在 INSERT 语句中参数顺序是 (a,b,c)，但在更新语句中，参数绑定需要是 (c,a,b)。

- Bobby White

---

好的，现在你有6个参数。只需按正确顺序绑定它们即可。 - JB Nizet

从概念上讲不清楚的是，我是否需要以不同的顺序再次重新绑定那三个参数：key1、key2、payload？ - Bobby White

是的，你必须这样做。 - JB Nizet

有没有什么简单的方法可以避免重复绑定，还是我必须创建一个存储过程才能实现？ @JBNizet - Bobby White

一些库支持命名参数，例如Spring-JDBC。您可以通过名称绑定参数，并在查询中两次使用此命名参数。 - JB Nizet

感谢@JBNizet - 我正在编写一个“最小公倍数”JDBC，所以我认为最好的做法是传递6个参数，但重新排列INSERT使其与UPDATE匹配并且更易于维护，例如：INSERT values(item,key1,key2) UPDATE item Where key1,key2。 - Bobby White

Answer 1

您不需要在on conflict部分中使用参数。只需使用set item = excluded.item即可。并且您也不需要为更新添加where子句：

INSERT INTO errorqueue as eq 
   (jobname, sourceid, item) 
VALUES(?,?,?) 
ON CONFLICT (jobname,sourceid) 
  UPDATE SET item=exluded.item;

您可以保留Java代码不变，Postgres将负责匹配正确的行。

- user330315

---

非常好，谢谢@a_horse_with_no_name - 我在测试中犯了一个愚蠢的错误。 - Bobby White

Answer 2

答案#1是错误的。

在上面的答案中，"eq"不应该出现。 column1和column2属于某个约束条件，即使没有明确列出列1&2，上面的插入语句将更新列1、列2和值。

INSERT INTO tablename（column1，column2，value）VALUES（？，？，？）ON CONFLICT（column1，column2）DO UPDATE SET value = excluded.value";

"eq"不属于此处的任何内容。

- James Baker