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