LEFT JOIN with AND operator using Criteria API
<p>I am trying to build dynamic queries using JPA and Criteria API but without metamodels.</p>

<p>With the tables:</p>

<pre><code>Foo:
ID
1
2
3
4
</code></pre>

<hr>

<pre><code>Bar: 
ID   FooID   Number
1    2       44
2    2       55
3    3       55
</code></pre>

<p>I want to retrieve all Foo entities where no matching Bar has Number 44. (Expecting Foo 1, 3, 4)</p>

<p>The SQL should look something like this:</p>

<pre><code>select distinct *
from Foo foo0_
left join Bar bar0_ on foo0_.ID=bar0_.FooID and bar0_.Number=44
where bar0__.id is null;
</code></pre>

<p>My code looks something like this:</p>

<pre><code>CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery&lt;Foo&gt; cq = cb.createQuery(Foo.class);
Root&lt;Foo&gt; fooRoot = cq.from(Foo.class);
cq.select(fooRoot).distinct(true);

List&lt;Predicate&gt; allPredicates = new ArrayList&lt;Predicate&gt;();
List&lt;Predicate&gt; orPredicates = new ArrayList&lt;Predicate&gt;();
List&lt;Predicate&gt; andPredicates = new ArrayList&lt;Predicate&gt;();
andPredicates.add(cb.equal(fooRoot.join("bars", JoinType.LEFT).get("number"), 44));
andPredicates.add(cb.isNull(fooRoot.join("bars", JoinType.LEFT).get("ID")));
orPredicates.add(cb.and(andPredicates.toArray(new Predicate[andPredicates.size()])));
allPredicates.add(cb.or(orPredicates.toArray(newPredicate[orPredicates.size()])));

cq.where(allPredicates.toArray(new Predicate[allPredicates.size()]));
TypedQuery&lt;Foo&gt; query = em.createQuery(cq);
query.getResultList();
</code></pre>

<p>But that generates this SQL:</p>

<pre><code>select distinct *
from Foo foo0_
left outer join Bar bar1_ on foo0_.id=bar1_.FooID
left outer join Bar bar2_ on foo0_.id=bar2_.FooID
where bar1_.Number=44 and (bar2_.id is null);
</code></pre>

<p>and returns no Foo.</p>

<p>Any ideas? Thank you!</p>