java - 从 JAVA 调用具有多个 IN 子句的查询

Question

此查询在 SQL Developer 中运行良好: select * from MYTABLE where (field1, field2) IN (('A', '1'), ('B','2'), ('C' ,'3')) ;

但是当我尝试从 java 调用它时，我遇到了异常。

public List<MYVO> callDB(List<String> sourceAndIdList) {
        System.out.println(sourceAndIdList);
        String query = "select * from MYTABLE where (field1, field2) IN (:source_monitorId_list)";
        Map<String, List<String>> namedParameters = Collections.singletonMap("source_monitorId_list", sourceAndIdList);
        return this.namedParameterJdbcTemplate.query(query , namedParameters, new RowMapper<MYVO>() {
            @Override
            public MYVO mapRow(ResultSet rs, int rowNum) throws SQLException {
                ....
            }
        });
    }

打印我得到的列表:[('A', '1'), ('B', '2'), ('C', '3')]

异常(exception):

SEVERE: Servlet.service() for servlet [selfservice] in context with path [/mymonitoring] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select * from MYTABLE where (field1, field2) IN (?, ?, ?)]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator ] with root cause java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator

EDIT: This is not a duplicate as I am looking for a solution with springs NamedParameterJdbcTemplate for a query having multiple IN clause and the size of parameters are known only at runtime.

Answer 1

您不能在“IN”中使用这样的绑定(bind)参数。

要么你这样做:

IN ((:p1, :p2), (:p3,:p4), (:p5,:p6))

但它假设您确切地知道您有多少项(在本例中为 3)，或者您在 SQL 字符串中“具体化”了您的参数:

String query = "select * from MYTABLE where (field1, field2) IN (('A', '1'), ('B', '2'), ('C', '3'))";