postgresql Not In clause using batch update

what i need is a query to delete all ids except those i have specified. therefore i have such a query in spring:

private final String SQL_Clear_Deleted_Options = "DELETE FROM vote_votes WHERE poll_id=? AND option_id <> ?";

i'm using jdbcTemplate and batchUpdate to do so. i also used <> operator to indicate NOT IN clause. this is my code:

public void clearDeletedOptions(int id) {
        int[] argTypes = { Types.INTEGER, Types.INTEGER };

        List<Object[]> batchArgs = new ArrayList<>();
        for (int i:ids) {
            batchArgs.add(new Object[]{id, i});
        }
        jdbcTemplate.batchUpdate(SQL_Clear_Deleted_Options, batchArgs, argTypes);
}

in above code ids is a list of integers that indicates option_id s in the query. i wonder why it works opposite and deletes all ids given! every thing looks fine and batchArges contains pairs (poll_id,option_id) indicating a particular poll_id and option_ids that should not be removed.

what is the problem?

This is because, each not equals will delete rest of the records. One of the solution is to use jdbcTemplate.execute by dynamically creating sql.

import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;

@SpringBootApplication
public class Application implements CommandLineRunner {

    private static final Logger log = LoggerFactory.getLogger(Application.class);

    public static void main(final String args[]) {
        SpringApplication.run(Application.class, args);
    }

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public void run(final String... strings) throws Exception {

        log.info("Creating tables");

        jdbcTemplate.execute("DROP TABLE votes IF EXISTS");
        jdbcTemplate.execute("CREATE TABLE votes(id SERIAL, poll_id integer, option_id integer)");

        final List<Object[]> splitUpValues = Arrays.asList("1 0", "1 1", "2 2", "1 3", "1 4").stream()
                .map(name -> name.split(" ")).collect(Collectors.toList());

        splitUpValues.forEach(name -> log.info(String.format("Inserting votes record for %d %d",
                Integer.valueOf((String) name[0]), Integer.valueOf((String) name[1]))));

        jdbcTemplate.batchUpdate("INSERT INTO votes(poll_id, option_id) VALUES (?,?)", splitUpValues);

        log.info("Querying for all votes");
        jdbcTemplate
                .query("SELECT id, poll_id, option_id FROM votes",
                        (rs, rowNum) -> new Votes(rs.getLong("id"), rs.getInt("poll_id"), rs.getInt("option_id")))
                .forEach(vote -> log.info(vote.toString()));

        jdbcTemplate.execute("DELETE FROM votes WHERE poll_id = 1 AND option_id not in (1, 3)");

        log.info("Querying for all votes after batch delete");
        jdbcTemplate
                .query("SELECT id, poll_id, option_id FROM votes",
                        (rs, rowNum) -> new Votes(rs.getLong("id"), rs.getInt("poll_id"), rs.getInt("option_id")))
                .forEach(vote -> log.info(vote.toString()));

    }
}