title: JPA criteria API order by NULL last
body: <p>I use JPA criteria API to fetch records from the datebase.
  I have entity <strong>Record</strong> with field <strong>dateTime</strong> which can be null. I would code:</p>
  
  <pre><code>public List&lt;Record&gt; find(RecordFilter recordFilter, int page, int pageSize) {
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery&lt;Record&gt; criteriaQuery = criteriaBuilder.createQuery(Record.class);
      Root&lt;Record&gt; recordRoot = criteriaQuery.from(Record.class);
  
      /*
       * JOINS. Left Joins are used for optional fields, or fields inside of the optional fields.
       */
      Join&lt;Record, Agency&gt; recordAgencyJoin = recordRoot.join(RecordTable.FIELD_AGENCY);
      //Some other joins
  
      //This is where I had the problem. 
      applyOrderBy(criteriaQuery, criteriaBuilder, recordRoot);
  
      /*
       * Specify which columns to select and their order.
       * criteriaQuery.multiselect(....);
       */              
      applyMultiSelect(recordRoot, recordAgencyJoin, /*other joins*/ criteriaQuery);
  
      /*
       * criteriaQuery.where(somePredicate);
       */
      applyFilter(recordFilter, criteriaQuery, criteriaBuilder,
              recordRoot, recordAgencyJoin /*, other joins*/);
      TypedQuery&lt;Record&gt; query = entityManager.&lt;Record&gt;createQuery(criteriaQuery);
      RepositoryUtils.applyPagination(query, page, pageSize);
      return query.getResultList();
  }
  
  
  private void applyOrderBy(CriteriaBuilder criteriaBuilder, Root&lt;Record&gt; recordRoot, CriteriaQuery&lt;Record&gt; criteriaQuery) {
      //Other fields to be added to the final sort.
  
      Order dateTimeDescOrder = criteriaBuilder.desc(recordRoot.get(RecordTable.FIELD_DATE_TIME));
      criteriaQuery.orderBy(dateTimeDescOrder /*, other orders by*/);
  }
  </code></pre>
  
  <p>It turns out, records with NULL dateTimeField are shown first.
  I use Postrgres database.
  I will answer this question because I found a solution.
  Here is a similar post.
  <a href="https://stackoverflow.com/questions/31113030/jpa-criteria-query-api-and-order-by-null-last">JPA Criteria Query API and order by null last</a></p>