How to build a CriteriaQuery predicate IN clause with multiple columns?

Question

Given a Predicate used in a CriteriaQuery, e.g. this:

Predicate predicate = root.get(MyTable.col1).in("col1Val1", "col1Val2");

Can this be extended to use multiple ANDed fields, e.g. the same as the SQL below?

SELECT *
FROM MyTable
WHERE (col1, col2, col3) IN (
    ("col1Val1", "col2Val1", "col3Val1"),
    ("col1Val2", "col2Val2", "col3Val2")
);

score 4 · Accepted Answer · answered Nov 08 '16 at 00:47

4

Not so elegant method, using JPA criteria builder

    Path<String> col1Path=root.get("col1");
    Path<String> col2Path=root.get("col2");
    Path<String> col3Path=root.get("col3");

    Predicate p0=criteriaBuilder.concat(col1Path,col2Path,col3Path)
         .in("col1Val1"||"col2Val1"||"col3Val1",
              "col1Val2"|| "col2Val2"|| "col3Val2");

Second Method

    Path<String> col1Path=root.get("col1");
    Path<String> col2Path=root.get("col2");
    Path<String> col3Path=root.get("col3");

    Predicate p1=criteriaBuilder.or(
          criteriaBuilder.and(criteriaBuilder.equal(col1Path,"col1Val1"),
                              criteriaBuilder.equal(col2Path,"col2Val1"),
                              criteriaBuilder.equal(col3Path,"col3Val1") 
                              ), 
          criteriaBuilder.and(criteriaBuilder.equal(col1Path,"col1Val2"),
                   criteriaBuilder.equal(col2Path,"col2Val2"),
                   criteriaBuilder.equal(col3Path,"col3Val2") 
                   )
           );

answered Nov 08 '16 at 00:47

Massimo Petrus

1,881
2
13
26

Thanks for the answer! The first method presumably suffers from the problem of concatenated strings not uniquely identifying the individual strings (e.g. column values "col1Val1col2", "Val1col3" and "Val1" would also match). The second method seems better - but in my case there will be a very large list of values being matched so am wondering how performance will compare... – Steve Chambers Nov 08 '16 at 08:05
2

...Just found an answer to the above question (but sadly not what I wanted to hear): http://stackoverflow.com/questions/782915#2481458 – Steve Chambers Nov 08 '16 at 08:16
Normally the IN clause is not efficent, expecially for large compare set.It seems that JPA does not support where clauses in tuples, i tried to find exactly that but i was not successfull but anyway the or - and solution works and has almost the same performance than the IN. If you have large sets to compare maybe you can find some more general condition – Massimo Petrus Nov 08 '16 at 08:21

How to build a CriteriaQuery predicate IN clause with multiple columns?

1 Answers1