Criteria Builder
1. Requirements
In order to use Criteria Builder and benefit from Specifications, we need to adjust the Repository.
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; ... }
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> { }
(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
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);
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 thewhere()
method as a rule.Specification<T> specification = Specification.where(getFirstSpecification()).and(getSecondSpecification()).and(getThirdSpecification())...and(getNthSpecification()); return findAll(specification, sort/pageable);
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 thecriteriaBuilder
object, which results in a newPredicate
.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 thecriteriaBuilder
object, which results in a newPredicate
.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).
- 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.
- 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 arenull
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.