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

Dudden

Answer 1

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

Rijeka

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... – Dudden

...Just found an answer to the above question (but sadly not what I wanted to hear): stackoverflow.com/questions/782915#2481458 – Dudden

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 – Rijeka