Parameterized IN clause using multiple columns with hibernate SQL query

Question

How to set parameters to a hibernate SQL query in case of multiple columns IN clause?

For ex, the SQL query should be generate as like below:

SELECT * FROM TABLE TA WHERE (TA.COLUMN1 , TA.COLUMN2) IN ((C1v, C2v), (C1v, C2v), .....);

I see this can be achieved with Spring jdbcTemplate as mentioned here, but i want this in Hibernate SQL Query.

I tried below scenarios but none of them works for me:

String sql = "SELECT * FROM TABLE TA WHERE (TA.COLUMN1 , TA.COLUMN2) IN (:ids)";
// 1. List of BigDecimal array's
List<BigDecimal[]> ids = new ArrayList<>();
BigDecimal[] bd1 = {new BigDecimal(100119401), new BigDecimal(2)};
BigDecimal[] bd2 = {new BigDecimal(100119000), new BigDecimal(1)};
ids.add(bd1);
ids.add(bd2);

Query query = session.createSQLQuery(sql);
query.setParameterList("ids", ids);

// 2. List of Object arrays
List<Object []> ids = new ArrayList<>();
Object [] bd1 = {new BigDecimal(100119401), new BigDecimal(2)};
Object [] bd2 = {new BigDecimal(100119000), new BigDecimal(1)};
ids.add(bd1);
ids.add(bd2);

Query query = session.createSQLQuery(sql);
query.setParameterList("ids", ids);

// 3. List of inner lists with ids
List<BigDecimal> ob1 = new ArrayList<BigDecimal>();
ob1.add(new BigDecimal(100119401));ob1.add(new BigDecimal(2));
List<BigDecimal> ob2 = new ArrayList<BigDecimal>();
ob2.add(new BigDecimal(100119401));ob2.add(new BigDecimal(2));
List<List<BigDecimal>> ids = new ArrayList<List<BigDecimal>>(); 
ids.add(ob1);ids.add(ob2);

Query query = session.createSQLQuery(sql);
query.setParameterList("ids", ids);

In all the cases am getting “ORA-00920: invalid relational operator” error.

Exception:

Caused by: java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3867)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1502)
    at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)
    ... 141 more

this might help.. https://stackoverflow.com/a/26416368/2893693 — Jobin, Jun 08 '17 at 11:02
The query is executing if I can set only one tuple. 'BigDecimal[] arr1 = {new BigDecimal(100119401), new BigDecimal(2)}; query.setParameterList("ids", arr1);'. I think we should use Two Dimensional array. Can anybody suggest how to set Two Dimensional array to a Query? — user3779369, Jun 09 '17 at 10:51

score 1 · Answer 1 · answered Jun 08 '17 at 11:00

1

Something like that should work:

String sql = "SELECT * FROM TABLE TA WHERE CONCAT(TA.COLUMN1, '|', TA.COLUMN2) IN (:values)";
List<String> values = new ArrayList<>();
values.add("100119401|2");
values.add("100119000|1");

// ...

Query query = session.createSQLQuery(sql);
query.setParameterList("values", values);

NB: make sure the separator used, here |, is not in your data

answered Jun 08 '17 at 11:00

Thanks RC. This works well with slight modification of the query _String sql = "SELECT * FROM TABLE TA WHERE CONCAT(CONCAT(TA.COLUMN1, '|'), TA.COLUMN2) IN (:values)";_. But with this approach there is a lot of performance impact on the query :( – user3779369 Jun 08 '17 at 11:39
The answer was to give you the idea, not a ready to use snippet ;) – Jun 08 '17 at 11:39
you can [and should if full table scan creates performance issues] create a function based index on the concatenated columns – Roger Cornejo Jun 08 '17 at 13:32

Parameterized IN clause using multiple columns with hibernate SQL query

1 Answers1