Criteria Builder

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 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
```

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 the where() 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));
}

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;
};

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);

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.

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.

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.

Requirements​

Generating the query​

Operations​

Joins​

Sub-Queries​

Examples​

Limitations​

Additional links​