如何仅对新行(目标表中不存在ID列值)使用jdbc高效地对Sql Server表执行批处理插入?
实际上,我正在使用PreparedStatement对象创建批次,但是没有“仅在新行时插入”逻辑:
String sqlInsertStub = "INSERT INTO mytable (id, col2, col3) values (?, ?, ?)";
try (PreparedStatement preparedStatement = connection.prepareStatement(sqlInsertStub)) {
connection.setAutoCommit(false);
int processed = 0;
while (resultSet.next()) {
// [...]
preparedStatement.setInt(1, id);
preparedStatement.setString(2, col2);
preparedStatement.setString(3, col3);
preparedStatement.addBatch();
if (++processed % BATCH_SIZE == 0) {
preparedStatement.executeBatch();
connection.commit();
}
}
preparedStatement.executeBatch();
connection.commit();
catch (Exception e) {
connection.rollBack();
e.printStackTrace();
System.exit(1);
}
最佳答案
一种选择是在表中确定唯一性的三列上添加唯一性约束。然后,尝试插入重复的记录将导致Java异常。但是,这可能会导致整个批次失败。如果需要其他选项,可以将查询更改为以下内容:
INSERT INTO mytable
SELECT ?, ?, ?
WHERE NOT EXISTS (SELECT 1 FROM mytable
WHERE id = ? AND col2 = ? AND col3 = ?);
对于此查询,将值绑定两次,如下所示:
preparedStatement.setInt(1, id);
preparedStatement.setString(2, col2);
preparedStatement.setString(3, col3);
preparedStatement.setInt(4, id);
preparedStatement.setString(5, col2);
preparedStatement.setString(6, col3);