带有JDBC参数标记的PostgreSQL更新 [英] Postgresql upsert with JDBC parameter markers

问题描述

当使用参数标记从JDBC调用JDBC时，我有一条今天可以工作的INSERT语句：

I've got an INSERT statement that works today when called from JDBC using parameters markers:

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

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

In my Java code, I bind the parameters:

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时需要如何处理这些参数，这很麻烦：

I'm struggling with how I'd need to handle the parameters if I convert this to an 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）

It's sneaky subtle, but in the INSERT the parameter order is (a,b,c) but in the update, the paramter binding needs to be (c,a,b)

推荐答案

您不需要冲突部分。只需使用 set item = exclude.item 。而且您也不需要 where 子句来进行更新：

You don't need parameters in the on conflict part at all. Just use set item = excluded.item. And you also don't need a where clause for the update:

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

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

You can leave your Java code as it is and Postgres will take care of matching the correct rows.

这篇关于带有JDBC参数标记的PostgreSQL更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！