java preparedstatement insert,Java PreparedStatement和ON DUPLICATE KEY UPDATE：我如何知道行是插入还是更新？...

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.

解决方案

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 bob@example.com

2 Thompson, Gord gord@example.com

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

int euReturnValue = ps.executeUpdate();

System.out.println(String.format("executeUpdate returned %d", 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.println(String.format(" => A new row was inserted: id=%d", affectedId));

}

else {

System.out.println(String.format(" => An existing row was updated: id=%d", 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, "bob@example.com");

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

the attempted INSERT is aborted because it would result in a duplicate UNIQUE key value, and

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.