As shown in my previous article on searching using JPA, Examples is a fast and easy way to search for records in a database. But it comes with limitations, most notably it can’t search in child collections. To support this, we have to turn to JPA Specifications.

In this article, I will use the MySQL Employees database via the docker container genschsa/mysql-employees, see my previous post for relevant instructions about this container. Entity model for Employee is given below.

@Entity
@Table(name = "employees")
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Employee {
    
    @Id
    @Column(name = "emp_no")
    private int employeeId;

    @Column(name = "birth_date")
    private LocalDate birthDate;
    
    @Column(name = "first_name")
    private String firstName;
    
    @Column(name = "last_name")
    private String lastName;
    
    @Column(name = "gender", columnDefinition = "ENUM('M', 'F')", nullable = false)
    @Enumerated(EnumType.STRING)
    private Gender gender;

    @Column(name = "hire_date")
    private LocalDate hireDate;

    @OneToMany(mappedBy = "employeeId", cascade = CascadeType.ALL, orphanRemoval = true)
    private Set<Salary> salaries;

    @OneToMany(mappedBy = "employeeId", cascade = CascadeType.ALL, orphanRemoval = true)
    private Set<Title> titles;
}

Title shown below, Salary has an identical structure with from and to.

@Data
@Entity
@Table(name = "titles")
@IdClass(TitleKey.class)
public class Title {

    @Id
    @Column(name = "emp_no")
    private int employeeId;
    
    @Id
    @Column(name = "title")
    private String title;
    
    @Id
    @Column(name = "from_date")
    private LocalDate from;
    
    @Column(name = "to_date")
    private LocalDate to;
}
view raw

Let’s assume we have a use case where we want to find employees with a simple search term, i.e. passing a String would search for employees using the names and that we also want to support explicit filters such as birthDate, hireDate, title and salary.

Repository and Specification

To perform searches using Specifications, we must first adapt our JPA Repository to extend from JpaSpecificationExecutor and create a Specification.

package se.predictly.support.employee.persistence;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;

import se.predictly.support.employee.persistence.model.Employee;

@Repository
public interface EmployeeRepository extends
    JpaRepository<Employee, Integer>, JpaSpecificationExecutor<Employee> {

}

And the secret sauce, our Specification.

@Data
@Builder
public class EmployeeSpecification implements Specification<Employee> {

    private static final LocalDate CURRENTLY_ACTIVE_ENTRY = LocalDate.of(9999, 1, 1);

    private LocalDate birthDate;
    private LocalDate hireDate;
    private Integer salary;
    private String title;

    private String searchTerm;

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

        Predicate birthDatePred = ofNullable(birthDate)
            .map(b -> equals(cb, root.get("birthDate"), birthDate))
            .orElse(null);
        Predicate hireDatePred = ofNullable(hireDate)
            .map(h -> equals(cb, root.get("hireDate"), hireDate))
            .orElse(null);

        Predicate salaryPred = salaryPredicate(root, cb);
        Predicate titlePred = titlePredicate(root, cb);

        if (nonNull(salaryPred) || nonNull(titlePred)) {
            query.distinct(true);
        }

        Predicate searchPred = null;
        if (StringUtils.isNoneBlank(searchTerm)) {
            Predicate firstNamePred = like(cb, root.get("firstName"), searchTerm);
            Predicate lastNamePred = like(cb, root.get("lastName"), searchTerm);

            searchPred = cb.or(firstNamePred, lastNamePred);
        }

        List<Predicate> predicates = new ArrayList<>();

        ofNullable(birthDatePred).ifPresent(predicates::add);
        ofNullable(hireDatePred).ifPresent(predicates::add);
        ofNullable(salaryPred).ifPresent(predicates::add);
        ofNullable(titlePred).ifPresent(predicates::add);
        ofNullable(searchPred).ifPresent(predicates::add);

        return cb.and(predicates.toArray(new Predicate[predicates.size()]));
    }

    private Predicate salaryPredicate(Root<Employee> root, CriteriaBuilder cb) {
        if (isNull(salary)) {
            return null;
        }

        Join<Employee, Title> salaryJoin = root.join("salaries", JoinType.INNER);

        int salaryLow = Double.valueOf(salary * 0.9).intValue();
        int salaryHigh = Double.valueOf(salary * 1.1).intValue();

        return cb.and(
                between(cb, salaryJoin.get("salary"), salaryLow, salaryHigh),
                equals(cb, salaryJoin.get("to"), CURRENTLY_ACTIVE_ENTRY));
    }

    private Predicate titlePredicate(Root<Employee> root, CriteriaBuilder cb) {
        if (isAllBlank(title)) {
            return null;
        }

        Join<Employee, Title> titleJoin = root.join("titles", JoinType.INNER);

        return cb.and(
                like(cb, titleJoin.get("title"), title),
                equals(cb, titleJoin.get("to"), CURRENTLY_ACTIVE_ENTRY));
    }

    private Predicate equals(CriteriaBuilder cb, Path<Object> field, Object value) {
        return cb.equal(field, value);
    }

    private Predicate like(CriteriaBuilder cb, Path<String> field, String searchTerm) {
        return cb.like(cb.lower(field), "%" + searchTerm.toLowerCase() + "%");
    }

    private Predicate between(CriteriaBuilder cb, Path<Integer> field, int min, int max) {
        return cb.between(field, min, max);
    }
}

Quite a lot to unpack here, let’s go through it step by step.

Line #17 and #20 — Predicate

Specifications works by combining predicates and our job is to create them according to our use case. Here we specify that if birthDate or hireDate is set, it should match using an equal statement.

Line #24 and #50 — Salary

If salary is set, we should find employees that currently match the provided salary. But since forcing users to specify a exact salary is bad design, we check for matches between 90% and 110% of the user provided value.

First we create a Join between our Employee and Salary and specify what type of join operation should be performed, in this case an INNER join should be performed so that we find an employee that has this salary.

Finally, on line #60 we create a predicate saying that the employee must have a salary between min and max and that it should only consider the currently active salary, modeled as to having a value of 9999–01–01.

Line #25 and #65 — Title

Similar to salary but we perform a like search instead.

Line #31 — Search Term

Finally we check if the user has provided a searchTerm which we use to match against both first and last name. This is done by creating two individual predicates, one for each attribute, and then combine to create one or predicate.

Line #48 — Predicate result

The last step is to create a final predicate specifying that a row in the database must match all individual predicates, i.e. and.

Creating and Using our Specification
@Autowired
private EmployeeRepository employeeRepo;

@Transactional(readOnly = true)
public List<EmployeeVO> listEmployeesUsingSpecification(int page, int pageSize, String sortField, Sort.Direction sortDirection, LocalDate birthDate,
        LocalDate hireDate, String title, Integer salary, String searchTerm) {

    Pageable pageable = pageable(page, pageSize, sortField, sortDirection);

    Specification<Employee> spec = EmployeeSpecification.builder()
            .birthDate(birthDate)
            .hireDate(hireDate)
            .salary(salary)
            .title(title)
            .searchTerm(searchTerm)
            .build();

    Page<Employee> employees = employeeRepo.findAll(spec, pageable);

    return employees.stream()
            .map(EmployeeMapper::map)
            .collect(Collectors.toList());
}

private Pageable pageable(int page, int pageSize, String sortField, Direction sortDirection) {
    return PageRequest.of(page, pageSize, sortDirection, sortField);
}
Generated SQL

If we run this code, we get the following SQL

-- searching using only searchTerm
select
    emp_no,
    birth_date,
    first_name,
    gender,
    hire_date,
    last_name 
from
    employees 
where
    lower(first_name) like ? 
    or lower(last_name) like ? 
order by
        emp_no desc limit ?
 
-- searching using title, salary and searchTerm
select
    distinct employee.emp_no, -- distinct issued since we need to perform join
    employee.birth_date,
    employee.first_name,
    employee.gender,
    employee.hire_date,
    employee.last_name 
from
    employees employee 
inner join
    salaries salary 
        on employee.emp_no=salary.emp_no 
inner join
    titles title 
        on employee.emp_no=title.emp_no 
where
    (salary.salary between 45000 and 55000) 
    and salary.to_date=? 
    and (lower(title.title) like ?) 
    and titleto_date=? 
    and (lower(employee.first_name) like ? or lower(employee.last_name) like ?)
order by
        employee.emp_no desc limit ?

As can be seen, our Specification has been used to generate the conditions we expect in the SQL issued towards the database. A few caveats are very important to take note of.

Distinct passed on to database

Once we perform a join we need to instruct Hibernate to not return an instance for each row in the result; this is because the join will create multiple rows in the result set.

What we actually want here is for Hibernate/JPA to not return an instance for each row but when using a Specification, we can’t provide a hint to JPA not to send the distinct on to the database (HINT_PASS_DISTINCT_THROUGH).

Paging increases load on database

If the result contains more rows than what fits in the requested page, a second query with count and distinct is issued to find the total number of rows. This could create heavy load on the database so care need to be taken that it isn’t overloaded.

n+1 loading child entities

If the call on line #21 to EmployeeMapper::map would map all salaries and titles for the current employee, JPA would issue one more select statement towards the database for each Employee in the result. This has the potential to overload the database and it degrades response times considerably.

This is because our Specification does not contain a fetch (root.fetch()), this is possible to add but it will not work with paging since the count query isn’t compatible with the fetch.

Categories: Applikation, Java, JPA, Spring

Fler insikter och blogginlägg

När vi stöter på intressanta tekniska saker på våra äventyr så brukar vi skriva om dom. Sharing is caring!