使用Execute Batch更新SQL表无报错但数据未更新求助

Let's break down why your batch update isn't modifying the table even though your code runs without errors—there are a few key issues in your implementation that stand out:

1. Critical Bug: Reinitializing PreparedStatement in Every Loop Iteration #

Looking at your code, you're creating a new PreparedStatement instance inside the while (rs.next()) loop:

while (rs.next()) {
    // ...
    pstmtAssignmentTable = connection.prepareStatement(sql_updateAssignmentTable);
    {
        pstmtAssignmentTable.setString(1, employeeInfo.getUserId());
        // ...
        pstmtAssignmentTable.addBatch();
    }
    // ...
}

This means every time you loop through a result set row, you discard the previous PreparedStatement (and all the batch entries you added to it) and create a new one. Only the last row's batch entry will be present when you call executeBatch()—all prior entries are lost entirely.

Fix:
Initialize the PreparedStatement once before the loop starts, not inside it:

// Move this line outside the while loop
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 completed..");
        pstmtAssignmentTable.clearBatch();
        recordNo = 0;
    }
}

2. Verify Your Select Query Returns Data #

If your sql_SelectSpecialSPOCInfo query doesn't return any rows, the loop won't run, no batch entries will be added, and executeBatch() will run without doing anything (no errors, no updates).

Quick Check:
Add a validation after executing the query to confirm the result set has data:

ResultSet rs = statement.executeQuery(sql_SelectSpecialSPOCInfo);
if (!rs.isBeforeFirst()) {
    System.out.println("No records found from the select query—nothing to update.");
    return; // exit early or handle accordingly
}

3. Ensure Your Update WHERE Clause Matches Rows #

Even if your select query returns CustomerNumber values, if those values don't exist in the asgn.Assignment table, the UPDATE statement will run but modify 0 rows (SQL doesn't treat this as an error).

Validate Manually:
Run the update query with one of the CustomerNumber values from your select result to test if it affects any rows:

Update asgn.Assignment 
Set UserId = 'sample-user-id', TeamId = 'sample-team-id' , Source = 'Manual Assignment', WorkProfileId = 'sample-profile-id' , AssignedDate = cast(getdate() as date) 
where CustomerNumber = 'your-customer-number-from-select';

If this returns 0 rows affected, you need to check data consistency between admn.SpecialSPOCAssignmentLookUp and asgn.Assignment.

4. Add Debugging for Batch Execution Results #

When you call executeBatch(), it returns an int array where each element represents the number of rows affected by each batch entry. Logging this will help you confirm if updates are actually happening:

Add This to Your Batch Execution:

int[] updateCounts = pstmtAssignmentTable.executeBatch();
int totalUpdated = 0;
for (int count : updateCounts) {
    totalUpdated += count;
}
System.out.println("Updated " + totalUpdated + " rows in this batch.");

If all counts are 0, your WHERE clause isn't matching any rows in the target table.

5. Fix Edge Case: Empty Final Batch #

If the number of records is exactly a multiple of batchSize, recordNo will be reset to 0, and the final executeBatch() after the loop will run an empty batch. Worse, if no records were processed at all, pstmtAssignmentTable will be null and calling executeBatch() will throw a NullPointerException. Add a check to avoid this:

Fix:

if (pstmtAssignmentTable != null && 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");
}

内容的提问来源于stack exchange，提问作者Bharath_melomaniac