Error when trying to filter data in Spring Boot

Question

I get this error:

InvalidDataAccessApiUsageException: Already registered a copy: SqmBasicValuedSimplePath
com.example.employee.model.Employee(1234567890).name)

I have created a Spring Boot application that does sorting, filtering, and paging. Everything works, except for filtering because this error pops up for me when I attempt to filter. I'll explain how.

When I type

localhost:8080/employees/?name=ben

In the URL, I expect to get employees whose name matches Ben. I get this response back:

{
    "status": 500,
    "reason": "Already registered a copy: SqmBasicValuedSimplePath(com.example.employee.model.Employee(1234567890).name)",
    "timeStamp": "11:16:33"
}

This doesn't produce any errors however:

localhost:8080/employees/?nameeee=ben

When I use a wrong/misspelled property variable, no errors are produced, and objects are retrieved (without filtering) obviously

What I want is to get back employee objects whose name matches "Ben"

Below is my is my search criteria model:

@Data
public class EmployeeSearchCriteria {
    private String name;
    private String department;
}

Below is my criteria repo:

@Repository
public class EmployeeCriteriaRepo {
    private final EntityManager entityManager;
    private final CriteriaBuilder criteriaBuilder;

    public EmployeeCriteriaRepo(EntityManager entityManager) {
        this.entityManager = entityManager;
        this.criteriaBuilder = entityManager.getCriteriaBuilder();
    }

    public Page<Employee> findAllWithFilters(EmployeePage employeePage, EmployeeSearchCriteria searchCriteria) {
        CriteriaQuery<Employee> criteriaQuery = criteriaBuilder.createQuery(Employee.class);
        Root<Employee> employeeRoot = criteriaQuery.from(Employee.class);
        Predicate predicate = getPredicate(searchCriteria, employeeRoot);
        criteriaQuery.where(predicate);
        setOrder(employeePage, criteriaQuery, employeeRoot);

        TypedQuery<Employee> typedQuery = entityManager.createQuery(criteriaQuery);
        typedQuery.setFirstResult(employeePage.getPageNumber() * employeePage.getPageSize());
        typedQuery.setMaxResults(employeePage.getPageSize());

        Pageable pageable = getPageable(employeePage);
        long employeesCount = getEmployeesCount(predicate);
        return new PageImpl<>(typedQuery.getResultList(), pageable, employeesCount);
    }

    private long getEmployeesCount(Predicate predicate) {
        CriteriaQuery<Long> countQuery = criteriaBuilder.createQuery(Long.class);
        Root<Employee> countRoot = countQuery.from(Employee.class);
        countQuery.select(criteriaBuilder.count(countRoot)).where(predicate);
        return entityManager.createQuery(countQuery).getSingleResult();
    }

    private Pageable getPageable(EmployeePage employeePage) {
        Sort sort = Sort.by(employeePage.getSortDirection(), employeePage.getSortBy());
        return PageRequest.of(employeePage.getPageNumber(), employeePage.getPageSize(), sort);
    }

    private void setOrder(EmployeePage employeePage,
                          CriteriaQuery<Employee> query,
                          Root<Employee> employeeRoot) {

        if (employeePage.getSortDirection().isAscending()) {
            query.orderBy(criteriaBuilder.asc(employeeRoot.get(employeePage.getSortBy())));
        }
        else {
            query.orderBy(criteriaBuilder.desc(employeeRoot.get(employeePage.getSortBy())));
        }
    }

    private Predicate getPredicate(EmployeeSearchCriteria searchCriteria, Root<Employee> employeeRoot) {
        List<Predicate> predicateList = new ArrayList<>();

        if (Objects.nonNull(searchCriteria.getName())) {
            predicateList.add(criteriaBuilder.like(
                    employeeRoot.get("name").as(String.class),
                    "%" + searchCriteria.getName() + "%")
            );
        }

            if (Objects.nonNull(searchCriteria.getDepartment())) {
                predicateList.add(criteriaBuilder.like(
                        employeeRoot.get("department").as(String.class),
                        "%" + searchCriteria.getDepartment() + "%")
                );
        }
            return criteriaBuilder.and(predicateList.toArray(new Predicate[0]));
    }

Answer 1

In Hibernate 6 it's no longer possible to reuse Predicates across different CriteriaQueries. In your case it's straightforward to fix with a simple refactor to create a new Predicate for the count query:

private long getEmployeesCount(EmployeeSearchCriteria searchCriteria) {
  CriteriaQuery<Long> countQuery = criteriaBuilder.createQuery(Long.class);
  Root<Employee> countRoot = countQuery.from(Employee.class);
  Predicate predicate = getPredicate(searchCriteria, countRoot);
  countQuery.select(criteriaBuilder.count(countRoot)).where(predicate);
  return entityManager.createQuery(countQuery).getSingleResult();
}