jdbc is returning 1 for no update to the record in

This question already has an answer here:

MySQL “INSERT … ON DUPLICATE KEY UPDATE” on Java: How to Differ Inserted/Updated/NoChange states 1 answer

I have a mysql query like this

INSERT INTO table (col1, col2, col3) VALUES ('val1','val2','val3') ON DUPLICATE KEY UPDATE col1=VALUES(col1),STATUS=1

I am using executeBatch() in JDBC to insert multiple records, if record already exists updating status column and some other values. Table has last_updated column also.

For new record insertion jdbc returning 1 and 2 for updating, but it is returning 1 for no update to the record also. I am getting 0 rows affected if I manually executed that query with same values.

String usersToConvquery = "INSERT INTO ne_ms_conversation_users (conversationId, userId, role) VALUES (?,?,?) ON DUPLICATE KEY UPDATE role=VALUES(role),STATUS=1";
        stm = connection.prepareStatement(usersToConvquery);
        for (long userId : users) {
            stm.setString(1, conversationId);
            stm.setLong(2, userId);
            stm.setInt(3, 3);
            stm.addBatch();
        }

        int[] insertCount = stm.executeBatch();
        System.out.println(Arrays.toString(insertCount));

The MySQL Connector/J drivers by default returns the number of 'found' rows that have been inserted or updated, even if none of the values changed, see useAffectedRows (which defaults to false) connection property documentation:

Don't set the CLIENT_FOUND_ROWS flag when connecting to the server (not JDBC-compliant, will break most applications that rely on "found" rows vs. "affected rows" for DML statements), but does cause "correct" update counts from "INSERT ... ON DUPLICATE KEY UPDATE" statements to be returned by the server.

Interestingly, the JDBC specification is silent on the exact meaning of the return count (although it may be checked by the TCK), however it is true that for some database systems 'affected rows' == 'found rows', because they will update irrespective of the old values (if only to get the right transactional behavior), and it is also true that a lot of applications rely on updateCount > 0 meaning the row already exists.

The MySQL documentation on INSERT ... ON DUPLICATE KEY UPDATE says:

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag to the mysql_real_connect() C API function when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

You might also want to look at connection property compensateOnDuplicateKeyUpdateCounts, which will simply make all executions of such a statement return update count 1.