Java PreparedStatement and ON DUPLICATE KEY UPDATE:如何知道是否插入或更新了row ?

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

有了以下代码，我如何知道execute()方法是否导致插入或更新?

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.

提前谢谢。

2 个解决方案

#1

Consider the following MySQL test table:

考虑下面的MySQL测试表:

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     [e-mail]
 2  Thompson, Gord  [e-mail]

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

使用默认的连接设置补偿复制的keyupdatec= false(这里描述的)下面的Java代码。

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

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

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.

这说明，如果惟一索引导致更新现有行，. executeupdate实际上可以返回2。如果您需要对实际的测试代码提供进一步的帮助，那么您应该编辑您的问题以包含它。

Edit

Further testing reveals that .executeUpdate will return 1 if

进一步的测试显示.executeUpdate将返回1 if

1. the attempted INSERT is aborted because it would result in a duplicate UNIQUE key value, and
2. 尝试的插入被中止，因为它将导致重复的唯一键值，并且
3. the specified ON DUPLICATE KEY UPDATE changes do not actually modify any values in the existing row.
4. 重复键更新所指定的修改实际上不会修改现有行中的任何值。

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.

可以通过在具有相同参数值的行中运行上述测试代码两次来确认这一点。注意更新…id = LAST_INSERT_ID(id)“trick”确保返回正确的id值。

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

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

#2

Use executeUpdate instead as it returns an int row count.

使用executeUpdate，因为它返回int行计数。

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

更新1:根据MySQL插入…复制关键更新文档:

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.

对于重复键更新，如果将行插入为新行，则每一行的影响行值为1，如果更新了现有行，则为2。

UPDATE 2: INSERT IGNORE may also be an option:

更新2:插入忽略也可以是一个选项:

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

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

当插入新行时，executeUpdate将返回1，重复行时返回0。