-1

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

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();
        }...

`

user3181885
  • 49
  • 2
  • 9
  • 1
    I believe that `deleteCdr.getInt(2)` is the cause of the problem. Are you sure that this cached result set actually has two columns available? – Tim Biegeleisen Aug 02 '16 at 12:46
  • Not directly related to your issue, but you need to close `Statement` and `Connection`. Consider [try-with-resources](https://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html). – bradimus Aug 02 '16 at 12:50
  • ` 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 = ?); ` Is this query Ok? My first test was positive. – user3181885 Aug 08 '16 at 10:03

1 Answers1

0

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

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

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

while (1) {
   INSERT ...
   if (insert went ok) break;
   UPDATE ...
   if (update changed one row) break;
}
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263