jdbc的一个特性是将多个查询分组在一个单元中,并在一次网络访问中将其传递给数据库。
如本代码示例所示:
String[] EMPLOYEES = new String[]{"Zuck","Mike","Larry","Musk","Steve"};
String[] DESIGNATIONS = new String[]{"CFO","CSO","CTO","CEO","CMO"};
String insertEmployeeSQL = "INSERT INTO EMPLOYEE(ID, NAME, DESIGNATION) VALUES (?,?,?)";
PreparedStatement employeeStmt = connection.prepareStatement(insertEmployeeSQL);
for(int i = 0; i < EMPLOYEES.length; i++){
String employeeId = UUID.randomUUID().toString();
employeeStmt.setString(1,employeeId);
employeeStmt.setString(2,EMPLOYEES[i]);
employeeStmt.setString(3,DESIGNATIONS[i]);
employeeStmt.addBatch();
}
employeeStmt.executeBatch();
但是,在sql中,我们可以使用具有许多值组的插入,就像:
String[] EMPLOYEES = new String[] {"Zuck","Mike","Larry","Musk","Steve"};
String[] DESIGNATIONS = new String[] {"CFO","CSO","CTO","CEO","CMO"};
StringBuilder insertEmployeeSQL = "INSERT INTO EMPLOYEE(ID, NAME, DESIGNATION) VALUES (?,?,?)"
+ ", (?, ?, ?)".repeat(EMPLOYEES.length - 1);
PreparedStatement employeeStmt = connection.prepareStatement(insertEmployeeSQL.toString());
int columnIndex = 0;
for(int i = 0; i < EMPLOYEES.length; i++){
String employeeId = UUID.randomUUID().toString();
employeeStmt.setString(++columnIndex, employeeId);
employeeStmt.setString(++columnIndex, EMPLOYEES[i]);
employeeStmt.setString(++columnIndex, DESIGNATIONS[i]);
}
employeeStmt.execute();
我需要知道的是,这两种方法有什么区别?
组织分解结构:
第一个例子来自这里https://www.baeldung.com/jdbc-batch-processing 第二本由我编辑。英语不是我的母语,这是我的第一篇帖子,所以很抱歉。
1条答案
按热度按时间sczxawaw1#
INSERT
具有多个VALUES
块,即多个(val, ...)
块不是有效的sql语法。当然,有些数据库可能支持这种扩展语法,但不是所有的数据库都支持。即使您的数据库这样做了,使用该语法也会大大增加语句参数的数量,并且所有数据库都对单个请求中允许的参数数量有限制。实际限制因数据库而异。除了存储所有值的内存量之外,批处理没有批大小限制。