JDBC ON DUPLICATE KEY UPDATE doesn't return 0

Question

I have a little java code that uses a SQL query to INSERT, or UPDATE if it already exists.

SQL Statement:

PreparedStatement cl = c.prepareStatement("INSERT INTO client VALUES (?, ?, ?, ?, ?, ?) " +
"ON DUPLICATE KEY UPDATE sex = VALUES(sex), zip = " +
"VALUES(zip), gem = VALUES(gem), agb = VALUES(agb), dob = VALUES(dob)");

Java code:

private void clientInsert(PreparedStatement cl, String bsn, String sex, String zip, String dob, String gem, String agb) throws SQLException {
    cl.setString(1, bsn);
    cl.setString(2, sex);
    cl.setString(3, zip);
    cl.setString(4, dob);
    cl.setString(5, gem);
    cl.setString(6, agb);

    int result = cl.executeUpdate();
    System.out.println("return: " + result);

    if (result == 1) System.out.println("New client inserted: " + bsn);
    else if (result == 2) System.out.println("Existing client updated: " + bsn);
    else if (result == 0) System.out.println("Existing client, not updated (duplicate values)");
    else System.out.println("query failed");
}

It inserts fine(returning 1) and when running it again with the same "bsn"(id) but a different zip for example, it updates fine(returning 2). But when I run it again using exact same data, without changes, it returns 1 again, so I can't tell in my console wether the client already exists without changes or not.

How do I check that?

Related, possibly duplicate: https://stackoverflow.com/questions/27793830/mysql-insert-on-duplicate-key-update-on-java-how-to-differ-inserted-updat — Mark Rotteveel, Feb 27 '19 at 10:53
@GordThompson this is actually what I uses as example, but it doens't say anywhere why it doesn't return 0. — Samoht, Feb 27 '19 at 14:27
@MarkRotteveel can't find anywhere how to enable useAffectedRows in my database — Samoht, Feb 27 '19 at 14:27
@ThomasFranken You don't enable it in the database, it is a connection property that you need to add to your JDBC url or datasource configuration. — Mark Rotteveel, Feb 27 '19 at 14:38
@MarkRotteveel Yes, I added ?useAffectedRows=true to my URL and it works now, thanks! — Samoht, Feb 27 '19 at 14:49

JDBC ON DUPLICATE KEY UPDATE doesn't return 0

0 Answers0