Java Preparedstatement and on duplicate key update: how do I know if a row has been inserted or updated?

With the following code, how do I know whether the execute () method causes insertion or 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();

Thank you in advance

Solution

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 ;

The existing sample data are as follows:

id  name            email
--  --------------  ----------------
 1  Loblaw,Bob     bob@example.com
 2  Thompson,Gord  gord@example.com

Use 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,"mike@example.com");
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);
}

Generate 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,"bob@example.com");

And console output is

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

This indicates that if a unique index causes an existing row to be updated Executeupdate can indeed return 2 If you need further help with the actual test code, you should edit your question to include it

edit

Further tests show that Executeupdate will return 1 if

>The attempted insert is aborted because it results in duplicate unique key values, and the on duplicate key update change specified by > does not actually modify any values in the existing row

This can be confirmed by running the above test code twice in succession with exactly the same parameter values Note that update... Id = last_ INSERT_ ID (ID) "trick" ensures that the correct ID value is returned

If the only value inserted is the unique key value, this may explain the test results of Op