Java PreparedStatement和ON DUPLICATE KEY UPDATE：我如何知道行是插入还是更新？ [英] Java PreparedStatement and ON DUPLICATE KEY UPDATE: how do I know whether row was inserted or updated?

问题描述

有以下代码，我如何知道execute（）方法是否导致插入或更新？：

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();

提前感谢。

推荐答案

请考虑以下MySQL测试表：

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 ;

现有示例数据如下：

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

使用默认连接设置 compensOnDuplicateKeyUpdateCounts = false （描述此处）以下Java代码

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, "[e-mail]");
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));
}

产生以下控制台输出

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, "[e-mail]");

，控制台输出为

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

这表明 .executeUpdate 真的可以返回2，如果唯一索引导致现有行更新。如果您需要有关实际测试代码的进一步帮助，请修改您的问题以包含它。

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.

进一步测试显示 .executeUpdate 将返回1如果

Further testing reveals that .executeUpdate will return 1 if

1. 尝试的INSERT中止，因为它会导致重复的UNIQUE键值和


2. 指定的ON DUPLICATE KEY UPDATE更改实际修改现有行中的任何值
。

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.

这可以通过连续两次运行上面的测试代码来确认具有完全相同的参数值。请注意， UPDATE ... id = LAST_INSERT_ID（id）trick确保正确的 id

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.

这可能解释了OP的测试结果，如果插入的唯一值是UNIQUE键值。

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

这篇关于Java PreparedStatement和ON DUPLICATE KEY UPDATE：我如何知道行是插入还是更新？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！