有下面的代码,我怎么知道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();
提前致谢.
解决方法
考虑以下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 ;
现有样本数据如下：
id name email -- -------------- ---------------- 1 Loblaw,Bob bob@example.com 2 Thompson,Gord gord@example.com
使用默认连接设置compensateOnDuplicateKeyUpdateCounts = false(描述为here)以下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,"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);
}
executeUpdate returned 1
=> A new row was inserted: id=3
现在使用参数值再次运行相同的代码
ps.setString(1,"Loblaw,Robert"); ps.setString(2,"bob@example.com");
和控制台输出是
executeUpdate returned 2
=> An existing row was updated: id=1
这表明如果唯一索引导致更新现有行,.executeUpdate确实可以返回2.如果您需要有关实际测试代码的进一步帮助,那么您应该编辑您的问题以包含它.
编辑
进一步测试显示.executeUpdate将返回1 if
>尝试的INSERT被中止,因为它会导致重复的UNIQUE键值,和
>指定的ON DUPLICATE KEY UPDATE更改实际上不会修改现有行中的任何值.
这可以通过使用完全相同的参数值连续两次运行上述测试代码来确认.请注意,UPDATE … id = LAST_INSERT_ID(id)“trick”确保返回正确的id值.
如果插入的唯一值是UNIQUE键值,这可能解释了OP的测试结果.
