CODEWITHSUNDEEP
javamysqljdbcinsert-update
ovunccetin
ovunccetin

Reputation: 8663

MySQL "INSERT ... ON DUPLICATE KEY UPDATE" on Java: How to Differ Inserted/Updated/NoChange states

I'm confused about the return value of MySQL's INSERT ... ON DUPLICATE KEY UPDATE statement. When I try it on a MySQL client (mysql terminal, phpmyadmin or MySQL Workbench), the execution results with one of the followings:

These results make sense. However, when I executed the same query in Java (with mysql-connector 5.1.34), JDBC's executeUpdate method returns 1 in case of both insertion and no change, and 2 in case of an successful update. It doesn't return 0 when update has no effect.

Is this a bug of MySQL JDBC driver? If so, is there a release which is free of this bug? If this is not a bug, how can I get the same result that is returned by a MySQL client?

Upvotes: 4

Views: 1949

Answers (1)

Sridhar DD
Sridhar DD

Reputation: 1980

Seems you need to set useAffectedRows in driver properties manually.

I got the solution after some googling. You can refer this MySql Bug for this INSERT ON DUPLICATE KEY UPDATE return 0 if no change in JDBC ExecuteUpdate

Also in the discussion it is stated they released it in 5.1.7

EDIT For Your Second Question:

useAffectedRows:

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.

Default: false

Since version: 5.1.7

Resference useAffectedRows in JDBC Connector-j

Upvotes: 5

Related Questions