Great question! Let's tackle this step by step since you're looking to optimize those update operations with Spring's JdbcTemplate.batchUpdate.

First, let's clarify how to use batchUpdate depending on whether your updates share the same SQL template or not:

Case 1: Same SQL Template, Different Parameters #

If you were updating the same table with multiple sets of parameters (e.g., updating multiple users in the USER table), this is the most efficient scenario because the database can reuse the prepared statement:

String sql = "UPDATE USER SET name = ? WHERE user_id = ?";
// Prepare a list of parameter arrays
List<Object[]> batchParams = Arrays.asList(
    new Object[]{name, id},
    new Object[]{name, anotherId},
    new Object[]{name, thirdId}
);
// Execute batch update
int[] updateResults = getJdbcTemplate().batchUpdate(sql, batchParams);

Each entry in the batchParams list corresponds to a set of values for the ? placeholders in your SQL. The returned int[] tells you how many rows were updated for each batch entry.

Case 2: Different SQL Templates (Different Tables) #

Your original code uses three different tables (USER, USER2, USER3), so each update has a unique SQL string. Spring's JdbcTemplate doesn't have a direct overload for batch processing multiple distinct prepared statements, but you can handle this by manually working with the JDBC Connection to create and execute batches:

getJdbcTemplate().execute((Connection connection) -> {
    // Create prepared statements for each table
    try (PreparedStatement psUser = connection.prepareStatement("UPDATE USER SET name = ? WHERE user_id = ?");
         PreparedStatement psUser2 = connection.prepareStatement("UPDATE USER2 SET name = ? WHERE user_id = ?");
         PreparedStatement psUser3 = connection.prepareStatement("UPDATE USER3 SET name = ? WHERE user_id = ?")) {
        
        // Set parameters and add to batch
        psUser.setString(1, name);
        psUser.setLong(2, id);
        psUser.addBatch();
        
        psUser2.setString(1, name);
        psUser2.setLong(2, id2);
        psUser2.addBatch();
        
        psUser3.setString(1, name);
        psUser3.setLong(2, id3);
        psUser3.addBatch();
        
        // Execute each batch
        psUser.executeBatch();
        psUser2.executeBatch();
        psUser3.executeBatch();
        
        return null;
    } catch (SQLException e) {
        throw new RuntimeException("Batch update failed", e);
    }
});

If you have more tables to handle, you can loop through a list of table names and IDs to avoid repeating code.

It's important to note that JDBC prepared statements send the SQL template and parameters separately to the database (for security and performance), so there's no built-in way to get the exact "filled-in" SQL string that the database executes. However, if you need this for debugging purposes, here are two approaches:

1. Manual String Replacement (For Simple Cases) #

You can write a helper method to replace ? placeholders with your parameter values. Just be careful to escape special characters (like single quotes in strings) to avoid SQL injection risks (this is only for debugging, not production code):

private String generateDebugSql(String preparedSql, Object[] params) {
    StringBuilder sqlBuilder = new StringBuilder(preparedSql);
    int paramIndex = 0;
    int placeholderIndex;
    while ((placeholderIndex = sqlBuilder.indexOf("?")) != -1 && paramIndex < params.length) {
        Object param = params[paramIndex++];
        String paramValue;
        if (param instanceof String) {
            // Escape single quotes in strings
            paramValue = "'" + ((String) param).replace("'", "''") + "'";
        } else if (param instanceof Number) {
            paramValue = param.toString();
        } else {
            // Handle other types (e.g., dates) as needed
            paramValue = "'" + param.toString() + "'";
        }
        sqlBuilder.replace(placeholderIndex, placeholderIndex + 1, paramValue);
    }
    return sqlBuilder.toString();
}

// Usage example
String preparedSql = "UPDATE USER SET name = ? WHERE user_id = ?";
Object[] params = {name, id};
String debugSql = generateDebugSql(preparedSql, params);

2. Use a JDBC Proxy Library (Better for Debugging) #

For a more robust solution, use libraries like P6Spy which intercepts JDBC calls and logs the actual executed SQL (with parameters filled in). This avoids the risk of manual replacement errors and works for all JDBC operations, not just your updates.

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