Java PreparedStatement and ON DUPLICATE KEY UPDATE: how do I know whether row was inserted or updated?

Question

Having following code, how do I know if the execute() method resulted in insert or in update?:

Connection c = DriverManager.getConnection(connectionString);

PreparedStatement st = c.prepareStatement("INSERT INTO `table`(`field1`) VALUES (?) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);");

st.setString(1,"some value");
st.execute();

Thanks in advance.

Answer 1

Consider the following MySQL test table:

CREATE TABLE `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

with existing sample data as follows:

id  name            email
--  --------------  ----------------
 1  Loblaw, Bob     [email protected]
 2  Thompson, Gord  [email protected]

With the default connection setting compensateOnDuplicateKeyUpdateCounts=false (described here) the following Java code

PreparedStatement ps = dbConnection.prepareStatement(
        "INSERT INTO customers (name, email) " +
        "VALUES (?, ?) " +
        "ON DUPLICATE KEY UPDATE " +
            "name = VALUES(name), " +
            "id = LAST_INSERT_ID(id)");
ps.setString(1, "McMack, Mike");
ps.setString(2, "[email protected]");
int euReturnValue = ps.executeUpdate();
System.out.printf("executeUpdate returned %d%n", euReturnValue);
Statement s = dbConnection.createStatement();
ResultSet rs = s.executeQuery("SELECT LAST_INSERT_ID() AS n");
rs.next();
int affectedId = rs.getInt(1);
if (euReturnValue == 1) {
    System.out.printf("    => A new row was inserted: id=%d%n", affectedId);
}
else {
    System.out.printf("    => An existing row was updated: id=%d%n", affectedId);
}

produces the following console output

executeUpdate returned 1
    => A new row was inserted: id=3

Now run the same code again with the parameter values

ps.setString(1, "Loblaw, Robert");
ps.setString(2, "[email protected]");

and the console output is

executeUpdate returned 2
    => An existing row was updated: id=1

This demonstrates that .executeUpdate really can return 2 if the unique index causes an existing row to be updated. If you require further assistance with your actual test code then you should edit your question to include it.

Edit

Further testing reveals that .executeUpdate will return 1 if

1. the attempted INSERT is aborted because it would result in a duplicate UNIQUE key value, and
2. the specified ON DUPLICATE KEY UPDATE changes do not actually modify any values in the existing row.

This can be confirmed by running the above test code twice in a row with the exact same parameter values. Note that the UPDATE ... id = LAST_INSERT_ID(id) "trick" does ensure that the correct id value is returned.

That probably explains OP's test results if the only value being inserted was the UNIQUE key value.

Answer 2

Use executeUpdate instead as it returns an int row count.

UPDATE 1: According to the MySQL INSERT ... ON DUPLICATE KEY UPDATE documentation:

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, and 2 if an existing row is updated.

UPDATE 2: INSERT IGNORE may also be an option:

INSERT IGNORE INTO `table`(`field1`) VALUES (?)

executeUpdate should return 1 when a new row is inserted and 0 when there is a duplicate.