1

This query runs fine from SQL Developer : select * from MYTABLE where (field1, field2) IN (('A', '1'), ('B','2'), ('C','3')) ;

But when I try to call it from java I am getting exception.

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

One printing my list I am getting : [('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.

Zeeshan
  • 11,851
  • 21
  • 73
  • 98
  • 1
    There is a new aspect in this question that has not been answered in the other one. It's about nested maps of parameters. The important aspect of `PreparedStatement`'s parameters is that the list is flat. Only objects directly mappable to SQL types are accepted. – nolexa Feb 12 '16 at 16:56

1 Answers1

1

You can't use bind parameter like this in an "IN".

Either you do :

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

But it assumes you know exactly how many items you have (in this case 3) or you "materialize" your parameters in the SQL string:

String query = "select * from MYTABLE where (field1, field2) IN (('A', '1'), ('B', '2'), ('C', '3'))";
Marco Polo
  • 728
  • 5
  • 10