title: JPA: How to create a query to join on values list?
body: <p>I have a query where I expect 1000s of rows and I'm looking for values in two columns. Something like this</p>
  <pre class="lang-sql prettyprint-override"><code>select
    *
  from
    foo
  WHERE
   (name, version)
  in
    (('a','2.0.0'),('b','1.0.0'));
  </code></pre>
  <p>Then I learned that this is not possible in JPA and I converted the query to multiple <code>OR</code> and <code>AND</code> queries.</p>
  <pre class="lang-sql prettyprint-override"><code>select
    *
  from
    foo
  WHERE
    name = 'a' and version = '2.0.0'
    or name = 'b' and version = '1.0.0'
  </code></pre>
  <p>I'm using a specification to build the query.</p>
  <pre class="lang-java prettyprint-override"><code>      var predicates =
            values.entrySet().stream()
                .map(
                    entry -&gt; {
                      return cb.and(
                          cb.equal(root.get(&quot;name&quot;),entry.getKey()),
                          cb.equal(root.get(&quot;version&quot;), entry.getValue()));
                    })
                .toList();
  
        return cb.or(predicates.toArray(Predicate[]::new));
  </code></pre>
  <p>That works but it's very slow and other answer support my findings.</p>
  <ul>
  <li><a href="https://dba.stackexchange.com/questions/91247/optimizing-a-postgres-query-with-a-large-in">https://dba.stackexchange.com/questions/91247/optimizing-a-postgres-query-with-a-large-in</a></li>
  <li><a href="https://stackoverflow.com/questions/24647503/performance-issue-in-update-query">Performance issue in update query</a></li>
  <li><a href="https://dba.stackexchange.com/questions/129844/cross-product-between-table-column-and-input-values">https://dba.stackexchange.com/questions/129844/cross-product-between-table-column-and-input-values</a></li>
  </ul>
  <p>So now I'm trying to do something like this.</p>
  <pre class="lang-sql prettyprint-override"><code>select
    *
  from
  (
      values
          ('a', '2.0.0'),
          ('b', '1.0.0')
  ) t(name, version)
  left join
    foo
  on t.name = foo.name and t.version = foo.version
  </code></pre>
  <p>Here is the official documentation.</p>
  <p><a href="https://www.postgresql.org/docs/current/queries-values.html" rel="nofollow noreferrer">https://www.postgresql.org/docs/current/queries-values.html</a></p>
  <p>I'm having trouble translating this in to JPA or using a specification / criteria builder.</p>
  <p>Any ideas? Or do I have to use a native query?</p>
  <p>Thank you!</p>