title: Combining conditional expressions with "AND" and "OR" predicates using the JPA criteria API
body: <p>I need to adapt the following code example.</p>
  
  <p>I've got a MySQL query, which looks like this (2015-05-04 and 2015-05-06 are dynamic and symbolize a time range)</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT * FROM cars c WHERE c.id NOT IN ( SELECT fkCarId FROM bookings WHERE 
      (fromDate &lt;= '2015-05-04' AND toDate &gt;= '2015-05-04') OR
      (fromDate &lt;= '2015-05-06' AND toDate &gt;= '2015-05-06') OR
      (fromDate &gt;= '2015-05-04' AND toDate &lt;= '2015-05-06'))
  </code></pre>
  
  <p>I've got a <code>bookings</code> table, and a <code>cars</code> table. I'd like to find out which car is available in a time range. The SQL query works like a charm.</p>
  
  <p>I'd like to "convert" this one into a <code>CriteriaBuilder</code> output. I've read documentation during the last 3 hours with this output (which, obviously, does not work). And I even skipped the where parts in the sub queries.</p>
  
  <pre><code>CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
  CriteriaQuery&lt;Cars&gt; query = cb.createQuery(Cars.class);
  Root&lt;Cars&gt; poRoot = query.from(Cars.class);
  query.select(poRoot);
  
  Subquery&lt;Bookings&gt; subquery = query.subquery(Bookings.class);
  Root&lt;Bookings&gt; subRoot = subquery.from(Bookings.class);
  subquery.select(subRoot);
  Predicate p = cb.equal(subRoot.get(Bookings_.fkCarId),poRoot);
  subquery.where(p);
  
  TypedQuery&lt;Cars&gt; typedQuery = getEntityManager().createQuery(query);
  
  List&lt;Cars&gt; result = typedQuery.getResultList();
  </code></pre>
  
  <p>Another issue: the <code>fkCarId</code> is not defined as a foreign key, it's just an integer. Any way to get it fixed that way?</p>