执行批处理未更新SQL表-中的数据

Question

我正在尝试使用execute batch更新表。我没有犯任何错误。程序正在成功执行，但是表没有用数据更新。我不确定是什么停止了SQL表中的更新。请帮忙。。

PreparedStatement pstmtAssignmentTable = null;
try {
Connection connection = DriverManager.getConnection(url);
try {
connection.setAutoCommit(false);
try {
String sql_SelectSpecialSPOCInfo = "Select CustomerNumber, U.UserId, U.TeamId, SPOC.EmployeeId, WorkProfileId from admn.SpecialSPOCAssignmentLookUp SPOC join admn.Users U on SPOC.EmployeeID = U.EmployeeID join admn.UserPhone UP on UP.UserID = U.UserID join admn.SpecialCustomerLookUp LLU on LLU.CustomerCode = SPOC.CustomerCode";
String sql_updateAssignmentTable = "Update asgn.Assignment Set UserId = ?, TeamId = ? , Source = 'Manual Assignment', WorkProfileId = ? , AssignedDate = cast(getdate() as date)  where CustomerNumber = ?";
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(sql_SelectSpecialSPOCInfo);
ManualAssignmentModel employeeInfo = new ManualAssignmentModel();
int recordNo = 0;
int batchSize = 100;
while (rs.next()) {
employeeInfo.setCustomerNumber(rs.getString("CustomerNumber"));
employeeInfo.setUserId(rs.getString("UserId"));
employeeInfo.setTeamId(rs.getString("TeamId"));
employeeInfo.setWorkProfileId(rs.getString("WorkProfileId"));
recordNo++;
pstmtAssignmentTable = connection.prepareStatement(sql_updateAssignmentTable);
{
pstmtAssignmentTable.setString(1, employeeInfo.getUserId());
pstmtAssignmentTable.setString(2, employeeInfo.getTeamId());
pstmtAssignmentTable.setString(3, employeeInfo.getWorkProfileId());
pstmtAssignmentTable.setString(4, employeeInfo.getCustomerNumber());
System.out.println("Adding to Batch ");
pstmtAssignmentTable.addBatch();
}
if (recordNo == batchSize) {
System.out.println(
"Batch Limit Reached. Updating the Database with the items added in the current Batch");    
pstmtAssignmentTable.executeBatch();
System.out.println("Batch execute cpmpleted..");
pstmtAssignmentTable.clearBatch();
recordNo = 0;
}
}
System.out.println("Record count is less than the Batch Limit. Updating the
Database with available records...");
pstmtAssignmentTable.executeBatch();
System.out.println(" Job Completed");
} finally {
if (pstmtAssignmentTable != null) {
pstmtAssignmentTable.close();
}
}
connection.commit();
} catch (Exception e) {
connection.rollback();
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

Answer 1

这里有很多可能的原因：

1. 也许select查询返回的行数少于100(int batch＝100(，所以您永远不会输入if语句。。。即使我们假设有100多行，您的代码也不会为最后100个元素之后的元素执行Batch(363行->300行将被更新，最后63行代码不会输入if语句(：

   if(recordNo==batchSize({//代码}

2. 在while语句中，您再次分配pstmtAssignmentTable，您需要将其移动到while之外：

   pstmtAssignmentTable=connection.prepareStatement(sql_updateAssignmentTable(；while(rs.next((({//代码}

FIX：

以下是经过上述修改的代码：

PreparedStatement pstmtAssignmentTable = null;
try {
Connection connection = DriverManager.getConnection(url);
try {
connection.setAutoCommit(false);
try {
String sql_SelectSpecialSPOCInfo = "Select CustomerNumber, U.UserId, U.TeamId, SPOC.EmployeeId, WorkProfileId from admn.SpecialSPOCAssignmentLookUp SPOC join admn.Users U on SPOC.EmployeeID = U.EmployeeID join admn.UserPhone UP on UP.UserID = U.UserID join admn.SpecialCustomerLookUp LLU on LLU.CustomerCode = SPOC.CustomerCode";
String sql_updateAssignmentTable = "Update asgn.Assignment Set UserId = ?, TeamId = ? , Source = 'Manual Assignment', WorkProfileId = ? , AssignedDate = cast(getdate() as date)  where CustomerNumber = ?";
//
String sql_CountSpecialSPOCInfo = "Select COUNT(*) from admn.SpecialSPOCAssignmentLookUp SPOC join admn.Users U on SPOC.EmployeeID = U.EmployeeID join admn.UserPhone UP on UP.UserID = U.UserID join admn.SpecialCustomerLookUp LLU on LLU.CustomerCode = SPOC.CustomerCode";
ResultSet rsCount = connection.createStatement().executeQuery(sql_CountSpecialSPOCInfo);
//BE CAREFUL: In Case this throws an exception, maybe we need to use  rsCount.next(); to move to the first row
int size = rsCount.getInt(1);
//If the size is less than the batchSize, then we use the size to
int batchSize = size > 100 ? 100 : size;

Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(sql_SelectSpecialSPOCInfo);
ManualAssignmentModel employeeInfo = new ManualAssignmentModel();
int recordNo = 0;

pstmtAssignmentTable = connection.prepareStatement(sql_updateAssignmentTable);
while (rs.next()) {
employeeInfo.setCustomerNumber(rs.getString("CustomerNumber"));
employeeInfo.setUserId(rs.getString("UserId"));
employeeInfo.setTeamId(rs.getString("TeamId"));
employeeInfo.setWorkProfileId(rs.getString("WorkProfileId"));
recordNo++;
pstmtAssignmentTable.setString(1, employeeInfo.getUserId());
pstmtAssignmentTable.setString(2, employeeInfo.getTeamId());
pstmtAssignmentTable.setString(3, employeeInfo.getWorkProfileId());
pstmtAssignmentTable.setString(4, employeeInfo.getCustomerNumber());
System.out.println("Adding to Batch ");
pstmtAssignmentTable.addBatch();
if (recordNo == batchSize) {
System.out.println("Batch Limit Reached. Updating the Database with the items added in the current Batch");
pstmtAssignmentTable.executeBatch();
System.out.println("Batch execute cpmpleted..");
pstmtAssignmentTable.clearBatch();
recordNo = 0;
}
}
// We will still need to excuteBatch in case recordNo<batchSize
if(recordNo>0){
System.out.println("Updating the Database with the items added in the current Batch");
pstmtAssignmentTable.executeBatch();
System.out.println("Batch execute cpmpleted..");
pstmtAssignmentTable.clearBatch();
}
System.out.println("Record count is less than the Batch Limit. Updating the Database with available records...");
pstmtAssignmentTable.executeBatch();
System.out.println(" Job Completed");
} finally {
if (pstmtAssignmentTable != null) {
pstmtAssignmentTable.close();
}
}
connection.commit();
} catch (Exception e) {
connection.rollback();
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

请分享结果，这样我们就可以更新答案，使其更通用。