Criteria Builder

1. Requirements

In order to use Criteria Builder and benefit from Specifications, we need to adjust the Repository.

1. Metamodel: The metamodel is used to refer to the columns of a table, in an object-oriented way. For this, each entity needs to have a corresponding metamodel class. (Artemis already fulfills this requirement)

   @Generated(value = "org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor")
   @StaticMetamodel(User.class)
   public abstract class User_ extends de.tum.cit.aet.artemis.domain.AbstractAuditingEntity_ {
   
       public static volatile SingularAttribute<User, String> lastName;
       public static volatile SingularAttribute<User, Instant> resetDate;
       public static volatile SingularAttribute<User, ZonedDateTime> hideNotificationsUntil;
       public static volatile SetAttribute<User, String> groups;
       public static volatile SetAttribute<User, GuidedTourSetting> guidedTourSettings;
       public static volatile SingularAttribute<User, String> login;
       public static volatile SingularAttribute<User, String> activationKey;
       public static volatile SingularAttribute<User, String> resetKey;
       public static volatile SetAttribute<User, Authority> authorities;
       ...
   }
   

2. JpaSpecificationExecutor: To execute Specifications and generate SQL statements, we need to extend the JpaSpecificationExecutor interface in our Spring Data JPA Repository.

   @Repository
   public interface UserRepository extends ArtemisJpaRepository<User, Long>, JpaSpecificationExecutor<User> {
   }
   

3. (Optional) Show queries: To ease debugging, generated queries can be displayed by enabling the output of executed SQL probes.

   jpa:
       database: MYSQL
       show-sql: true
   

2. Generating the query

1. Query Generation: In most occasions, it is sufficient to execute any one of the following methods:

   List<T> findAll(Specification<T> spec);
   
   Page<T> findAll(Specification<T> spec, Pageable pageable);
   
   List<T> findAll(Specification<T> spec, Sort sort);
   

2. Defining the initial Specification: To generate a query with multiple Specifications, we can use the and() method for concatenation. However, the first Specification must always be called via the where() method as a rule.

   Specification<T> specification = Specification.where(getFirstSpecification()).and(getSecondSpecification()).and(getThirdSpecification())...and(getNthSpecification());
   return findAll(specification, sort/pageable);
   

3. Defining Specifications: A specification is a functional interface with a single method. This method has three parameters - a root, a query and a criteria builder. You don’t need to specify these arguments manually because they are provided during chaining.

   public interface Specification<T> {
       Predicate toPredicate(Root<T> root, CriteriaQuery query, CriteriaBuilder cb);
   }
   

   Now we can create Specifications. We can achieve this in two ways:

   • Anonymous new Specification<User>():

     private Specification<User> getAllUsersMatchingEmptyCourses() {
         return new Specification<User>() {
             @Override
             public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                 return criteriaBuilder.isEmpty(root.get(User_.GROUPS));
             }
         };
     }
     

   • Lambda expression (preferred version):

     private Specification<User> getAllUsersMatchingEmptyCourses() {
         return (root, query, criteriaBuilder) -> criteriaBuilder.isEmpty(root.get(User_.GROUPS));
     }
     

3. Operations

• AND: We can perform the and operation on an arbitrary number of predicates via the criteriaBuilder object, which results in a new Predicate.

  return (root, query, criteriaBuilder) -> {
      Predicate one = criteriaBuilder.equal(x, z);
      Predicate two = criteriaBuilder.notEqual(a, b);
  
      return criteriaBuilder.and(one, two, ...);
  };
  

• OR: We can perform the or operation on an arbitrary number of predicates via the criteriaBuilder object, which results in a new Predicate.

  return (root, query, criteriaBuilder) -> {
      Predicate one = criteriaBuilder.equal(x, z);
      Predicate two = criteriaBuilder.notEqual(a, b);
  
      return criteriaBuilder.or(one, two, ...);
  };
  

• EQUAL / NOT EQUAL:

  return (root, query, criteriaBuilder) -> {
      Predicate one = criteriaBuilder.equal(root.get(User_.IS_INTERNAL), true);
      Predicate two = criteriaBuilder.notEqual(root.get(User_.ACTIVATED), true);
  
      return criteriaBuilder.and(one, two, ...);
  };
  

• NOT:

  return (root, query, criteriaBuilder) -> {
      ...
      Predicate predicate = criteriaBuilder.exists(subQuery).not();
  
      return criteriaBuilder.equals(predicate);
  };
  

• IN: To check if the collection contains a value.

  return (root, query, criteriaBuilder) -> {
      Predicate in = criteriaBuilder.in(root.get(User_.ID)).value(ids);
      return in;
  };
  

4. Joins

Different joins are available (e.g. Join, ListJoin, SetJoin, CollectionJoin, …) - please choose the right one.

• If we want to join from X to Y, we need to define the column and the join type. Please mind that when the join type is not specified an Inner Join is made by default.

  Join<X, Y> join = root.join(X_.COLUMN, JoinType.LEFT);
  

• We can define custom on clauses to specify the join condition.

  Join<X, Y> join = root.join(X_.COLUMN, JoinType.LEFT);
  join.on(criteriaBuilder.in(join.get(Y_.NAME)).value(names));
  

• We can concatenate joins.

  Join<X, Z> join = root.join(X_.COLUMN, JoinType.LEFT).join(Y_.COLUMN, JoinType.LEFT);
  

5. Sub-Queries

Sub-queries are usually fine unless they are dependent sub-queries (also known as correlated sub queries).

1. Dependent Sub-Query:

   In an SQL database query, a correlated sub-query is a sub-query (a query nested inside another query) that uses values from the outer query. But with a dependent sub-query you might run into performance problems because a dependent sub-query typically needs to be run once for each row in the outer query, e.g. if your outer query has 1000 rows, the sub-query will be run 1000 times.

2. Independent Sub-Query:

   An independent sub-query is a sub-query that can be run on its own, without the main (sub-)query. Therefore, an independent sub-query typically only needs to be evaluated once.

You can find additional information on dependent sub-queries and how to identify them here.

6. Examples

• Specification that matches the specified string:

  public static Specification<User> getSearchTermSpecification(String searchTerm) {
      String extendedSearchTerm = "%" + searchTerm + "%";
      return (root, query, criteriaBuilder) -> {
          String[] columns = new String[] { User_.LOGIN, User_.EMAIL, User_.FIRST_NAME, User_.LAST_NAME };
          Predicate[] predicates = Arrays.stream(columns).map((column) -> criteriaBuilder.like(root.get(column), extendedSearchTerm)).toArray(Predicate[]::new);
  
          return criteriaBuilder.or(predicates);
      };
  }
  

  SELECT DISTINCT user FROM jhi_user user
  WHERE user.login LIKE ?
      OR user.email LIKE ?
      OR user.first_name LIKE ?
      OR user.last_name LIKE ?
  ORDER BY user.id ASC limit ?
  

• Specification that matches all selected courses:

  public static Specification<User> getAllUsersMatchingCourses(Set<Long> courseIds) {
      return (root, query, criteriaBuilder) -> {
          Root<Course> courseRoot = query.from(Course.class);
  
          Join<User, String> group = root.join(User_.GROUPS, JoinType.LEFT);
  
          // Select all possible group types
          String[] columns = new String[] { Course_.STUDENT_GROUP_NAME, Course_.TEACHING_ASSISTANT_GROUP_NAME, Course_.EDITOR_GROUP_NAME, Course_.INSTRUCTOR_GROUP_NAME };
          Predicate[] predicates = Arrays.stream(columns).map((column) -> criteriaBuilder.in(courseRoot.get(column)).value(group)).toArray(Predicate[]::new);
  
          // The course needs to be one of the selected
          Predicate inCourse = criteriaBuilder.in(courseRoot.get(Course_.ID)).value(courseIds);
  
          group.on(criteriaBuilder.or(predicates));
  
          query.groupBy(root.get(User_.ID)).having(criteriaBuilder.equal(criteriaBuilder.count(group), courseIds.size()));
  
              return criteriaBuilder.in(courseRoot.get(Course_.ID)).value(courseIds);
      }
  }
  

  SELECT DISTINCT user FROM jhi_user user
  CROSS JOIN course course
  LEFT OUTER JOIN user_groups groups ON user.id = groups.user_id
  AND (course.student_group_name IN ( groups.user_groups )
      OR course.teaching_assistant_group_name IN ( groups.user_groups )
      OR course.editor_group_name IN ( groups.user_groups )
      OR course.instructor_group_name IN ( groups.user_groups )
  WHERE (user.login LIKE ?
      OR user.email LIKE ?
      OR user.first_name LIKE ?
      OR user.last_name LIKE ?)
  AND ( course.id IN ( ? ) )
  GROUP BY user.id
  HAVING Count(groups.user_groups) = ?
  ORDER BY user.id ASC
  LIMIT ?
  

• Specification to get distinct results:

  public static Specification<User> distinct() {
      return (root, query, criteriaBuilder) -> {
          query.distinct(true);
          return null;
      };
  }
  

  SELECT DISTINCT ...
  

  We can simply return null, since specifications/predicates that are null are ignored when combining multiple specifications (e.g., specification.and(otherSpecification)) or when constructing a predicate from it.

7. Limitations

• Executing simple queries becomes more complex — but reusable.

• Multiple “group by” are not combined but overwritten → you need a specification that combines them.

• Pagination feature of Spring Data JPA does not support the use of specifications with “group by”. See issue.

8. Additional links

• https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl

• https://www.baeldung.com/hibernate-criteria-queries

• https://docs.oracle.com/javaee/7/api/javax/persistence/criteria/CriteriaBuilder.html