title: JPA dynamic criteria-api query
body: <p>I wonder if there is a generic way to use the criteria api in combination with a little more complex model?</p>
  
  <p>I have an entity class that has one-to-one relationships to other entities. My service wrapper that does the database query via the criteria api gets the parameters from front end to figure out pagination, sorting and filtering.</p>
  
  <p>Entities</p>
  
  <pre><code>@Entity
  public class Person implements Serializable {
      @Id
      private Long id;
      private String name;
      private String givenName;
  
      @Temporal(TemporalType.DATE)
      private Date birthdate;
  
      @OneToOne(cascade = CascadeType.ALL)
      @JoinColumn(name = "INFORMATION_ID")
      private Information information;
  
      @OneToOne(cascade = CascadeType.ALL)
      @JoinColumn(name = "ADDRESS_ID")
      private Address address;
      ...
  }
  </code></pre>
  
  
  
  <pre><code>@Entity
  public class Information implements Serializable {
      @Id
      private Long id;
      private String detail;
      ...
  }
  </code></pre>
  
  
  
  <pre><code>@Entity
  public class Address implements Serializable {
      @Id
      private Long id;
      private String street;
      private String city;
      ...
  }
  </code></pre>
  
  <p>Service</p>
  
  <pre><code>@Stateless
  public class PersonService {
      @PersistenceContext(unitName = "ProblemGenericDatatableFilterPU")
      private EntityManager em;
      public List&lt;Person&gt; findAllPersons222(int first, int pageSize, String sortField, SortOrder sortOrder, Map&lt;String, Object&gt; filters) {
          CriteriaBuilder builder = em.getCriteriaBuilder();
          CriteriaQuery&lt;Person&gt; criteriaQuery = builder.createQuery(Person.class);
          Root&lt;Person&gt; rootPerson = criteriaQuery.from(Person.class);
          Join&lt;Person, Information&gt; joinPersonInformation = rootPerson.join(Person_.information);
          Join&lt;Person, Address&gt; joinPersonAddress = rootPerson.join(Person_.address);
  
          // select
          criteriaQuery.select(rootPerson);
  
          // filter
          List&lt;Predicate&gt; allPredicates = new ArrayList&lt;&gt;();
          for(Entry&lt;String, Object&gt; currentEntry : filters.entrySet()) {
              Predicate currentPredicate;
  
              if(currentEntry.getKey().startsWith("information_")) {
                  currentPredicate = builder.like(
                          builder.lower(joinPersonInformation.&lt;String&gt;get(currentEntry.getKey())),
                      builder.lower(builder.literal(String.valueOf(currentEntry.getValue())))
                  );
              }
              else if(currentEntry.getKey().startsWith("address_")) {
                  currentPredicate = builder.like(
                          builder.lower(joinPersonAddress.&lt;String&gt;get(currentEntry.getKey())),
                      builder.lower(builder.literal(String.valueOf(currentEntry.getValue())))
                  );
              }
              else {
                  currentPredicate = builder.like(
                          builder.lower(rootPerson.&lt;String&gt;get(currentEntry.getKey())),
                      builder.lower(builder.literal(String.valueOf(currentEntry.getValue())))
                  );
              }
              allPredicates.add(currentPredicate);
          }
          criteriaQuery.where(builder.and(allPredicates.toArray(new Predicate[0])));
  
          // order
          if(sortField != null &amp;&amp; !sortField.isEmpty()) {
              Order orderBy;
              if(sortField.startsWith("information_")) {
                  orderBy = (sortOrder == SortOrder.DESCENDING
                          ? builder.desc(joinPersonInformation.get(sortField))
                          : builder.asc(joinPersonInformation.get(sortField)));
              }
              else if(sortField.startsWith("address_")) {
                  orderBy = (sortOrder == SortOrder.DESCENDING
                          ? builder.desc(joinPersonAddress.get(sortField))
                          : builder.asc(joinPersonAddress.get(sortField)));
              }
              else {
                  orderBy = (sortOrder == SortOrder.DESCENDING
                          ? builder.desc(rootPerson.get(sortField))
                          : builder.asc(rootPerson.get(sortField)));
              }
              criteriaQuery.orderBy(orderBy);
          }
  
          Query query = em.createQuery(criteriaQuery);
          // pagination
          query.setFirstResult(first);
          query.setMaxResults(pageSize);
          return query.getResultList();
      }
  }
  </code></pre>
  
  <p>I need to do a distinction of cases for filtering and sorting depending on the root/join on which I am accessing the property. Plus I need to use a naming convention in the facelet. The same goes for the <em>count</em>-query except for sorting.</p>
  
  <p>Now I ask myself whether there is some "dot-notation" or anything which makes the case dispensable. In e. g. native SQL I would do something like create a subquery and select all alias values from the inner projection (<code>select * from (select person.name as name, address.street as street, ...) where name = ... and street like ...</code>).</p>
  
  <p>I would be grateful for any advice.</p>