Spring Boot数据过滤触发InvalidDataAccessApiUsageException异常

问题原因 #

报错Already registered a copy: SqmBasicValuedSimplePath(com.example.employee.model.Employee(1234567890).name)的核心原因是：主查询中基于employeeRoot创建的Predicate被直接复用给了统计查询的countRoot。JPA Criteria API中，每个Root对应查询的独立实体路径，跨Root复用Predicate会触发路径注册冲突——同一个属性路径无法关联到两个不同的Root实例上。

当参数名拼写错误时，searchCriteria的属性值为null，getPredicate返回空的and Predicate（无实际过滤条件），此时不会触发路径冲突，统计查询正常执行，返回所有员工数据。

修复方案 #

修改统计查询逻辑，让它基于自身的countRoot重新构建Predicate，而非复用主查询的Predicate。

修改后的完整代码 #

@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(searchCriteria);
        return new PageImpl<>(typedQuery.getResultList(), pageable, employeesCount);
    }

    private long getEmployeesCount(EmployeeSearchCriteria searchCriteria) {
        CriteriaQuery<Long> countQuery = criteriaBuilder.createQuery(Long.class);
        Root<Employee> countRoot = countQuery.from(Employee.class);
        Predicate countPredicate = getPredicate(searchCriteria, countRoot);
        countQuery.select(criteriaBuilder.count(countRoot)).where(countPredicate);
        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]));
    }
}

关键修改点 #

1. 调整getEmployeesCount方法参数：从接收Predicate改为接收EmployeeSearchCriteria
2. 独立构建统计查询的Predicate：在getEmployeesCount内部，基于自身创建的countRoot调用getPredicate生成专属过滤条件
3. 更新主查询调用逻辑：调用getEmployeesCount时传入searchCriteria，而非主查询已构建好的Predicate

修改后，主查询与统计查询各自使用独立的Root和对应的Predicate，彻底避免路径冲突问题，过滤功能即可正常运行。

内容的提问来源于stack exchange，提问作者meniman98