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

score 0 · Answer 1 · answered Oct 31 '22 at 12:26

0

My assumption is that you shouldn't be keeping the criteria builder around. It's meant to be used in a case-by-case execution, so in this method you would rather run:

public Page<Employee> findAllWithFilters(EmployeePage employeePage, EmployeeSearchCriteria searchCriteria) {
    CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder();
    CriteriaQuery<Employee> criteriaQuery = criteriaBuilder.createQuery(Employee.class);
...

answered Oct 31 '22 at 12:26

Thomas Jungblut

20,854
6
68
91

I get this: "reason": "Cannot invoke \"jakarta.persistence.criteria.CriteriaBuilder.and(jakarta.persistence.criteria.Predicate[])\" because \"this.criteriaBuilder\" is null", I removed the criteriaBuilder initialisation from the constructor and instead initialised it in the findAllWithFilters() method – meniman98 Oct 31 '22 at 15:18
`this.criteriaBuilder` doesn't exist anymore when you remove it, that's why you get a NPE – Thomas Jungblut Oct 31 '22 at 15:46

score 0 · Answer 2 · answered Jan 19 '23 at 13:12

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

I have tried this but it does not seems to work. I have created brand new criteria builder, criteria query, root and predicates for these objects, in order to use them for count, but the error stays the same. The only thing that's been reused was the EntityManager. Maybe this is relevant. — hypercube, Feb 02 '23 at 13:39

Error when trying to filter data in Spring Boot

2 Answers2

Linked