title: Criteria api query in Hibernate with not exist
body: <p>Im trying to write query, which returns me list of Drivers wich is not assigned to route.</p>
  
  <p>My database a set up as following.</p>
  
  <pre><code>Route:
  route_id
  user_id//specified as driver
  
  User:
  user_id
  role // need to select user, which is Driver role
  </code></pre>
  
  <p>Only route sees user(driver), user(driver) doesn`t see route.</p>
  
  <p>This is my try to write such query.</p>
  
  <pre><code>public List&lt;User&gt; getUnsignedDrivers(){
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
  
      CriteriaQuery&lt;User&gt; query = criteriaBuilder.createQuery(User.class);
      Root&lt;User&gt; user = query.from(User.class);
      query.select(user);
  
      Subquery&lt;Route&gt; subquery = query.subquery(Route.class);
      Root&lt;Route&gt; subRootEntity = subquery.from(Route.class);
      Predicate correlatePredicate = criteriaBuilder.equal(subRootEntity.get("Route_.User"), user);
      subquery.where(correlatePredicate);
      query.where(criteriaBuilder.not(criteriaBuilder.exists(subquery)));
  
      TypedQuery&lt;User&gt; typedQuery = entityManager.createQuery(query);
      return typedQuery.getResultList();
  }
  </code></pre>
  
  <p>I`m new to jpa, so that is the problem.</p>
  
  <p>To be more specific, i need to select user with role driver, which are not set to any route</p>
  
  <p>My entities are set up as following:</p>
  
  <pre><code>    @Entity
  public class Route {
      @Id
      @GeneratedValue(strategy = GenerationType.AUTO)
      private Long id;
  
  @OneToOne(fetch = FetchType.EAGER, cascade = {})
  @JoinColumn(name = "user_id", nullable = true)
  private User driver;
  .....
  @Entity
  public class User {
  
  public static enum Role {
      ADMIN, MANAGER, DRIVER;
  }
  
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Long id;
  
  @Enumerated(EnumType.STRING)
  @Column(nullable = false)
  private Role role;
  </code></pre>
  
  <p><strong>UPDATE:</strong> current query</p>
  
  <pre><code>CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
  
      CriteriaQuery&lt;User&gt; query = criteriaBuilder.createQuery(User.class);
      Root&lt;User&gt; user = query.from(User.class);
      Predicate predicateRole = criteriaBuilder.equal(user.get("role"), User.Role.DRIVER);
      query.where(predicateRole);
      query.select(user);
  
  
      Subquery&lt;Route&gt; subquery = query.subquery(Route.class);
      Root&lt;Route&gt; subRootEntity = subquery.from(Route.class);
      Predicate correlatePredicate = criteriaBuilder.equal(subRootEntity.get("driver"), user);
      subquery.where(correlatePredicate);
      query.where(criteriaBuilder.not(criteriaBuilder.exists(subquery)));
  
      TypedQuery&lt;User&gt; typedQuery = entityManager.createQuery(query);
      return typedQuery.getResultList();
  </code></pre>
  
  <p>problem still exist</p>
  
  <p>i get this exception:</p>
  
  <pre><code>java.lang.IllegalStateException: No explicit selection and an implicit one cold not be determined
  at org.hibernate.ejb.criteria.QueryStructure.locateImplicitSelection(QueryStructure.java:296)
  at org.hibernate.ejb.criteria.QueryStructure.render(QueryStructure.java:249)
  at org.hibernate.ejb.criteria.CriteriaSubqueryImpl.render(CriteriaSubqueryImpl.java:282)
  at org.hibernate.ejb.criteria.predicate.ExistsPredicate.render(ExistsPredicate.java:58)
  at org.hibernate.ejb.criteria.QueryStructure.render(QueryStructure.java:258)
  at org.hibernate.ejb.criteria.CriteriaQueryImpl.render(CriteriaQueryImpl.java:340)
  at org.hibernate.ejb.criteria.CriteriaQueryCompiler.compile(CriteriaQueryCompiler.java:217)
  at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:587)
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  at java.lang.reflect.Method.invoke(Method.java:601)
  at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:240)
  at $Proxy25.createQuery(Unknown Source)
  </code></pre>
  
  <p>throws at this line <code>TypedQuery&lt;User&gt; typedQuery = entityManager.createQuery(query);</code></p>
  
  <p><strong>Work around</strong>
  this works for me quite well.
  I wrote this, because i can`t use inverse relationship.</p>
  
  <pre><code>public List&lt;User&gt; getUnsignedDrivers(){
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
  
      CriteriaQuery&lt;User&gt; query = criteriaBuilder.createQuery(User.class);
      Root&lt;User&gt; user = query.from(User.class);
      Predicate predicateRole = criteriaBuilder.equal(user.get("role"), User.Role.DRIVER);
      query.where(predicateRole);
      query.select(user);
  
      TypedQuery&lt;User&gt; typedQuery = entityManager.createQuery(query);
      List&lt;User&gt; allDrivers = typedQuery.getResultList();
      List&lt;User&gt; notAssignedDrivers = new ArrayList&lt;User&gt;();
      List&lt;Route&gt; haveDriverRoutes = getRouteWithNoDrives();
      for (User driver : allDrivers){
          if (!isDriverAssigned(haveDriverRoutes,driver.getId())){
              notAssignedDrivers.add(driver);
          }
      }
      return notAssignedDrivers;
  }
  
  private boolean isDriverAssigned(List&lt;Route&gt; haveDriverRoutes, long driverId){
      for(Route route : haveDriverRoutes){
          if (route.getDriver().getId() == driverId){
              return true;
          }
      }
      return false;
  }
  
  @SuppressWarnings("unchecked")
  public List&lt;Route&gt; getRouteWithNoDrives() {
      Query query = entityManager.createQuery("SELECT o FROM " + type.getSimpleName() + " o WHERE o.driver != null");
      return  query.getResultList();
  }
  </code></pre>