Introduction:

In most web applications, we would have come across a requirement to filter, sort, and paginate the data by joining multiple tables. If you are using Spring JPA then there are multiple ways to retrieve and paginate the data on joined table.

  1. Writing native queries.
  2. Using JPQL query.
  3. Using inbuilt repository methods.

These options are good for the use case where we will have to construct the query based on a fixed set of conditions/where clauses. We can’t add where clause at runtime using these options.

Spring JPA also provides the capability to generate dynamic SQL queries with help of Criteria API and Specification. In this article, we will see how we can leverage JPA Criteria query support to build generic specifications which can retrieve rows from joins on multiple tables with sorting and pagination.

Context

Let us consider the following simple database schema:

  • Employee entity with basic details and Many-to-One association with Department entity.

  • Department entity with basic details and One-to-Many associations with Employee entity

Filtering on joined tables using criteria API

To illustrate how to use search and filter data based on conditions let’s consider the following implementation of the problem where we wanted to get employees from the specific department with a salary greater than 90k. Please refer to the comments in the code snippet for a better explanation.

/* Create tuple query */

CriteriaQuery<Tuple> tupleQuery = entityManager.getCriteriaBuilder().createTupleQuery();
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
Root<Employee> employeeRoot = tupleQuery.from(Employee.class);

/* Define specification for getting employees for which salary gte 90k and from "Engineering" dept */

Specification<Employee> specification = (root, query, criteriaBuilder) -> {

   /* Join tables Employee and Department. We will have to pass JoinType that can be INNER for inner joins,
LEFT for left outer join and RIGHT for right outer join */
   
   Join<Object, Object> joinDepartment = root.join("department", JoinType.INNER);
   Predicate employeeSalaryPredicate = criteriaBuilder.greaterThanOrEqualTo(root.get("salary"), 90);
   Predicate departmentNamePredicate = criteriaBuilder.equal(joinDepartment.get("name"), "Engineering");
   
   /*     We can select all columns from the join for that we will have to pass Join objects to multiselect().
      Alternatively, if we have to select individual columns then we can pass the expression with an alias as given below.
      e.g  query.multiselect(root.get("id").alias("employeeId"),root.get("salary").alias("employeeSalary"),joinDepartment.get("name").alias("deptName"));
   */
    
   query.multiselect(root, joinDepartment);
   return criteriaBuilder.and(employeeSalaryPredicate, departmentNamePredicate);
};

/* Apply specification. Get the predicate from the specification and use it in tupleQuery  */
Predicate predicate = specification.toPredicate(employeeRoot tupleQuery, builder);
tupleQuery.where(predicate);

/* Get TypedQuery from tupleQuery */
TypedQuery<Tuple> typedQuery = entityManager.createQuery(tupleQuery);

/* Get result */
List<Tuple> result = typedQuery.getResultList();

/* Retrieving column data or entity
   If all columns are selected in multiselect() function then we can get Entity object as shown in the code.
   If few columns expressions are provided to multiselect function then we can retrieve them as shown below:
   1: Using alias as provided in multiselect() function
      Integer employeeId = tuple.get("employeeId",Integer.class)

   2:Using index of the position in the result tuple
      Integer employeeId = tuple.get(0,Integer.class)
      Double employeeSalary = tuple.get(1,Double.class)
 */
 
Tuple tuple = result.get(0);
Employee emp = tuple.get(0, Employee.class);
Department dept = tuple.get(1, Department.class);

Dynamic queries with specification:

In this section, we will build a filtering model which can allow us to create any level of nested AND/OR queries by leveraging JPA criteria query and specification support.

  • Define the filter object to capture details of the filter such as the field on which the filter is to be applied, operator, value, and join identifier to get the correct path of the attribute.
  • Define the AbstractFilter class with a method to convert the filter to predicate with the help of criteria query support. AbstractFilter class is extended by classes that are
    • SimpleFilter - This will be used for creating a single filter.
    • AndFilter - This will be used to define the conjunction of multiple filters of any type.
    • OrFilter - This will be used to define the disjunction of multiple filters of any type.

  • We will define a function to convert the Filter to a predicate. We will need information about all the joins to get the correct attribute path. This function will create the predicate as per the filter operator. Please refer to the method getPredicateFromFilter from the AbstractFilter class.

  • Let’s define GenericSpecification class which converts complex filters to predicates. This class can be used for creating a specification for any root entity.

  • Implementation:
public class Filter {

   /* Name of the variable from Entity class on which filter has to be applied */
   String field;
   
   /* Filter operator */
    FilterOperator operator;
    
   /* Filter value */
    Object value;
    
   /* Join identifier */
    String entityName;
}

public abstract class AbstractFilter {

    public abstract Predicate toPredicate(Root root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder, Map<String, Join<Object, Object>> attributeToJoin);

    public Predicate getPredicate(Filter filter, CriteriaBuilder criteriaBuilder, Path expression) {

        Predicate predicate = null;
        switch (filter.getOperator()) {
            case EQUAL_TO:
                predicate = criteriaBuilder.equal(expression, filter.getValue());
                break;
            case LIKE:
                predicate = criteriaBuilder.like(expression, "%" + filter.getValue() + "%");
                break;
            case IN:
                predicate = criteriaBuilder.in(expression).value(filter.getValue());
                break;
            case GT:
                predicate = criteriaBuilder.greaterThan(expression, (Comparable) filter.getValue());
                break;
            case LT:
                predicate = criteriaBuilder.lessThan(expression, (Comparable) filter.getValue());
                break;
            case GTE:
                predicate = criteriaBuilder.greaterThanOrEqualTo(expression, (Comparable) filter.getValue());
                break;
            case LTE:
                predicate = criteriaBuilder.lessThanOrEqualTo(expression, (Comparable) filter.getValue());
                break;
            case NOT_EQUAL:
                predicate = criteriaBuilder.notEqual(expression, filter.getValue());
                break;
            case IS_NULL:
                predicate = criteriaBuilder.isNull(expression);
                break;
            case NOT_NULL:
                predicate = criteriaBuilder.isNotNull(expression);
                break;
            default:
                log.error("Invalid Operator");
                throw new IllegalArgumentException(filter.getOperator() + " is not valid operator");
        }
        return predicate;
    }

    public Predicate getPredicateFromFilter(Filter filter, Root root, CriteriaBuilder criteriaBuilder, Map<String, Join<Object, Object>> attributeToJoin) {
        Assert.notNull(filter,"Filter must not be null");
        if (attributeToJoin != null && attributeToJoin.get(filter.getEntityName()) != null) {
            return  getPredicate(filter, criteriaBuilder, attributeToJoin.get(filter.getEntityName()).get(filter.getField()));
        } else {
            return getPredicate(filter, criteriaBuilder, root.get(filter.getField()));
        }
}

/* SimpleFilter */
public class SimpleFilter extends AbstractFilter {

    private Filter filter;

    @Override
    public Predicate toPredicate(Root root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder, Map<String, Join<Object, Object>> attributeToJoin) {
        return getPredicateFromFilter(filter,root,criteriaBuilder,attributeToJoin);
    }
}
-----------------------------------------------------

/* AndFilter */
public class AndFilter extends AbstractFilter{

    private List<AbstractFilter> filters;

    @Override
    public Predicate toPredicate(Root root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder, Map<String, Join<Object, Object>> attributeToJoin ) {
        return criteriaBuilder.and(filters.stream().map(filter -> filter.toPredicate(root,query,criteriaBuilder,attributeToJoin)).collect(Collectors.toList()).toArray(Predicate[]::new));
    }
}

-------------------------------------------------------

/* OrFilter */
public class OrFilter extends AbstractFilter {

    private List<AbstractFilter> filters;

    @Override
    public Predicate toPredicate(Root root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder, Map<String, Join<Object, Object>> attributeToJoi) {
       return criteriaBuilder.or(filters.stream().map(filter -> filter.toPredicate(root,query,criteriaBuilder,attributeToJoi)).collect(Collectors.toList()).toArray(Predicate[]::new));
    }
}

public class GenericSpecification<T> implements Specification<T> {

   private AbstractFilter filter;
   private JoinDataSupplier<T> joinDataSupplier;

   @Override
   public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {

      if (joinDataSupplier != null && filter !=null) {
         return filter.toPredicate(root, query, criteriaBuilder, joinDataSupplier.getJoinData(root,query));
      }
      return criteriaBuilder.conjunction();
   }
}

Example

Let’s consider a hypothetical example where we need to retrieve employee data whose salary is greater than 90k from cost center “CostCenter-1” or employee data whose salary is greater than 80k from cost center “CostCenter-2”. This query requires a join between Employee and Department tables. Let’s create a specification from the filter and join data. We can use the same tuple-typed query as explained in Section 2 to get the results for the given specification. Please refer to the following code snippet for the same.

public Specification<Employee> getEmployeeDepartmentJoinSpecification(AbstractFilter filter) {

   GenericSpecification<Employee> serviceOrderSpecification = new GenericSpecification<Employee>();
   serviceOrderSpecification.setFilter(filter);
   serviceOrderSpecification.setJoinDataSupplier(new JoinDataSupplier<Employee>() {
      @Override
      public Map<String, Join<Object, Object>> getJoinData(Root<Employee> root, CriteriaQuery<?> query) {

         Map<String, Join<Object, Object>> attributeToJoinMap = new LinkedHashMap<>();
         Join<Object, Object> joinDepartment = root.join("department", JoinType.INNER);
         attributeToJoinMap.put("Department", joinDepartment);
         query.multiselect(root.get("id"), root.get("firstName"), joinDepartment.get("name"));
         return attributeToJoinMap;
      }
   });
   return serviceOrderSpecification;
 }


//Creating filter

SimpleFilter salaryFilter = new SimpleFilter(new Filter("salary",FilterOperator.GTE,90,"Employee"));
SimpleFilter departmentNameFilter = new SimpleFilter(new Filter("costCenter",FilterOperator.EQUAL_TO,"CostCenter-1","Department"));
AndFilter firstAndFilter = new AndFilter(Arrays.asList(salaryFilter,departmentNameFilter));

SimpleFilter salaryFilter1 = new SimpleFilter(new Filter("salary",FilterOperator.GTE,80,"Employee"));
SimpleFilter departmentNameFilter1 = new SimpleFilter(new Filter("costCenter",FilterOperator.EQUAL_TO,"CostCenter-2","Department"));
AndFilter secondAndFilter = new AndFilter(Arrays.asList(salaryFilter1,departmentNameFilter1));
OrFilter orFilter = new OrFilter(Arrays.asList(firstAndFilter,secondAndFilter));

/* we can use this specifcation for tuple query */
Specification<Employee> specification = getEmployeeDepartmentJoinSpecification(orFilter);

The query generated from the above filters:

select e1_0.id, e1_0.department_id, e1_0.email_id, e1_0.first_name, e1_0.last_name, e1_0.salary, d1_0.id, d1_0.cost_centre, d1_0.name from employee e1_0 join department d1_0 on d1_0.id=e1_0.department_id where e1_0.salary>=? and d1_0.cost_centre=? or e1_0.salary>=? and d1_0.cost_centre=?

We can generate any combination of AND/OR queries with this filtering model. e.g: [ (A or B) and (U or V)]

Pagination and Sorting

  • Adding sorting to the query

    We can make use of QueryUtils.java class from the spring-data-jpa library. This gives util method to get an Order object which can be applied to a tuple query.

tupleQuery.orderBy(org.springframework.data.jpa.repository.query.QueryUtils.toOrders(sort, root, builder));

  • Adding pagination to the query

    There is no direct support for pagination when we are dealing with criteria queries, but we can still achieve this with the help of spring data Pageable and Page interface. PageRequest is an implementation of Pageable which we will use here to provide pagination metadata like page number, page size, and sorting metadata. We can make use of the PageableExecutionUtils.java class from the spring-data-jpa library to get the paged data from a query. We will also need to know the total number of rows that can be retrieved with given filters, so we will have to create a count criteria query. Please refer to the following code snippet for the implementation details, this implementation is inspired by SimpleJpaRepository.java from spring-data-jpa library.

/* Function to get paged data */
public <S> Page<Tuple> getPage(TypedQuery<Tuple> query, final Class<S> rootClass, Pageable pageable, Specification<S> spec) {
   if (pageable.isPaged()) {
      query.setFirstResult((int) pageable.getOffset());
      query.setMaxResults(pageable.getPageSize());
   }
   return PageableExecutionUtils.getPage(query.getResultList(), pageable,() -> executeCountQuery(getCountQuery(spec, rootClass)));
}

/* Function to execute count query */
public long executeCountQuery(TypedQuery<Long> countQuery) {
   List<Long> totals = countQuery.getResultList();
   long total = 0L;
   for (Long element : totals) {
      total += element == null ? 0 : element;
   }
   return total;
}

/* Function to create count query */
protected <S> TypedQuery<Long> getCountQuery(Specification<S> spec, Class<S> rootClass) {
   CriteriaBuilder builder = entityManager.getCriteriaBuilder();
   CriteriaQuery<Long> query = builder.createQuery(Long.class);
   Root<S> root = query.from(rootClass);
   Predicate predicate = spec.toPredicate(root, query, builder);
   if (predicate != null) {
      query.where(predicate);
   }
   query.select(builder.count(root));
   query.orderBy(Collections.<Order> emptyList());
   return entityManager.createQuery(query);
}

Conclusion

We can construct any level of nested queries with the help of the above filtering model and specification. This implementation will not be helpful if the query has to be generated on properties from JSON type columns.You can find the working code here

Thanks for reading…!!!