使用JDBC Batch Update时PostgreSQL NOT IN逻辑异常排查

Let's break down why your code is deleting the very option IDs you want to keep, and how to fix it.

What's Going Wrong? #

Your current approach uses batchUpdate to run the same DELETE statement multiple times—once for each option ID in your ids (the list of IDs you want to keep). Let's walk through a concrete example where ids = [1,2,3] and poll_id = 5:

• First batch iteration: Runs DELETE FROM vote_votes WHERE poll_id=5 AND option_id <> 1 → this deletes every record in poll 5 except those with option_id=1 (so options 2 and 3 get wiped out here)
• Second batch iteration: Runs DELETE FROM vote_votes WHERE poll_id=5 AND option_id <> 2 → now the only remaining records are option_id=1, so this statement deletes all records where option_id isn't 2 (which is all remaining records, including option_id=1)
• Third batch iteration: Runs DELETE FROM vote_votes WHERE poll_id=5 AND option_id <>3 → the table is already empty, so no action here

By the end of the batch, all records for the poll are gone—including the ones you meant to preserve. The batch execution works against your intent here; each subsequent DELETE wipes out the remaining records from the previous step.

Correct Approach: Use a Single DELETE with NOT IN (or NOT EXISTS) #

Instead of looping and running multiple DELETEs, craft a single SQL statement that targets all records except your keep list in one go. Here are two clean ways to do this:

Option 1: Plain JdbcTemplate with Dynamic Placeholders

public void clearDeletedOptions(int pollId, List<Integer> keepOptionIds) {
    // Handle edge case: delete all records if there are no options to keep
    if (keepOptionIds.isEmpty()) {
        jdbcTemplate.update("DELETE FROM vote_votes WHERE poll_id=?", pollId);
        return;
    }
    
    // Build dynamic placeholders for the IN clause (e.g., "?, ?, ?" for 3 IDs)
    String placeholders = String.join(",", Collections.nCopies(keepOptionIds.size(), "?"));
    String sql = "DELETE FROM vote_votes WHERE poll_id=? AND option_id NOT IN (" + placeholders + ")";
    
    // Combine poll ID with keep list arguments
    List<Object> args = new ArrayList<>();
    args.add(pollId);
    args.addAll(keepOptionIds);
    
    // Define argument types (all integers)
    int[] argTypes = new int[keepOptionIds.size() + 1];
    argTypes[0] = Types.INTEGER;
    Arrays.fill(argTypes, 1, argTypes.length, Types.INTEGER);
    
    jdbcTemplate.update(sql, args.toArray(), argTypes);
}

Option 2: NamedParameterJdbcTemplate (Cleaner for Lists)

If you use NamedParameterJdbcTemplate, it handles list parameters automatically, avoiding manual placeholder building:

private final String SQL_CLEAR_DELETED_OPTIONS = "DELETE FROM vote_votes WHERE poll_id=:pollId AND option_id NOT IN (:keepOptionIds)";
private final NamedParameterJdbcTemplate namedJdbcTemplate;

// Inject via constructor
public YourRepository(NamedParameterJdbcTemplate namedJdbcTemplate) {
    this.namedJdbcTemplate = namedJdbcTemplate;
}

public void clearDeletedOptions(int pollId, List<Integer> keepOptionIds) {
    if (keepOptionIds.isEmpty()) {
        namedJdbcTemplate.update("DELETE FROM vote_votes WHERE poll_id=:pollId", 
            Collections.singletonMap("pollId", pollId));
        return;
    }
    
    Map<String, Object> params = new HashMap<>();
    params.put("pollId", pollId);
    params.put("keepOptionIds", keepOptionIds);
    
    namedJdbcTemplate.update(SQL_CLEAR_DELETED_OPTIONS, params);
}

Key Takeaway #

Batch updates are designed for running the same statement with different parameter sets (like inserting multiple rows), not for building a negation list. Your original approach inverted the logic by running multiple exclusionary deletes, which wiped out all records instead of keeping the intended ones.

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