使用批处理更新的PostgreSQL In In子句 [英] postgresql Not In clause using batch update

问题描述

我需要的是一个查询，以删除除我指定的ID外的所有ID.因此，我在春季有这样的查询:

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 <> ?";

我正在使用jdbcTemplate和batchUpdate来这样做.我还使用<>运算符来指示NOT IN子句.这是我的代码:

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);
}

上面的代码id中的

是一个整数列表，指示查询中的option_id. 我不知道为什么它相反，并删除所有给定的ID！每件事看起来都很好，batchArges包含对(poll_id，option_id)的指示，这些对指示不应删除的特定poll_id和option_id.

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.

出什么问题了?

推荐答案

这是因为，每个不等于都会删除其余记录.解决方案之一是通过动态创建sql使用jdbcTemplate.execute.

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()));

    }
}

这篇关于使用批处理更新的PostgreSQL In In子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！