列索引超出范围:1，列数:0。

i updated the UPSERT_DELETE_JOB_COUNTERS. Now i think the "upsert" works with the new Query =) Or what do you think?

我更新了UPSERT_DELETE_JOB_COUNTERS。现在，我认为“upsert”与新的查询=)或您认为如何?

regards, Peter

问候,彼得

`private static final String UPSERT_DELETE_JOB_COUNTERS =
        "UPDATE rating.delete_job_counters SET delete_count = delete_count + ? WHERE fee_group_id = ? AND delete_job_id = ?; "+
        "INSERT INTO  rating.delete_job_counters(delete_job_id, fee_group_id, delete_count ) "+
        "SELECT ?, ? , ? " +
        "WHERE NOT EXISTS (SELECT 1 FROM rating.delete_job_counters  WHERE fee_group_id = ? AND delete_job_id = ?); ";

@Override
public Boolean insertIntoDeleteStatistic(final CachedRowSet deletedEntries,
                                      Long deleteJobId,
                                      Transaction transaction) throws SQLException {
    boolean finish = true;
    if (deletedEntries.size() == 0) {
        return finish;
    }
    final Connection connection = transaction.getConnection();
    final Timer executeTimer = Timer.start("Insert took ");
    try {
        PreparedStatement pstmt = connection.prepareStatement(UPSERT_DELETE_JOB_COUNTERS);
        while (deletedEntries.next()) {
            pstmt.setInt(1, deletedEntries.getInt(2));
            pstmt.setInt(2, deletedEntries.getInt(1));
            pstmt.setLong(3, deleteJobId);
            pstmt.setLong(4, deleteJobId);
            pstmt.setInt(5, deletedEntries.getInt(1));
            pstmt.setInt(6, deletedEntries.getInt(2));
            pstmt.setInt(7, deletedEntries.getInt(1));
            pstmt.setInt(8, deletedEntries.getInt(2));
            pstmt.execute();
        }...

”

1 个解决方案

#1

-1

You cannot use a parameterized java.sql.PreparedStatement with a DO statement.

不能使用参数化的java.sql。用DO语句准备语句。

Why don't you use INSERT ... ON CONFLICT DO UPDATE (available since PostgreSQL 9.5)?

为什么不使用INSERT…关于冲突的更新(从PostgreSQL 9.5开始可用)?

For older versions of PostgreSQL, you'll probably have to code an “endless loop” like this pseudocode sample:

对于老版本的PostgreSQL，您可能需要编写一个类似于伪代码示例的“无限循环”:

while (1) {
   INSERT ...
   if (insert went ok) break;
   UPDATE ...
   if (update changed one row) break;
}

#1

-1