java - 具有规范、分页和标准获取连接的 Spring Data JPA 存储库

Question

我正在为实体列表实现搜索/过滤服务，使用具有规范和分页功能的 Spring Data JPA 存储库。我正在尝试减少查询数量(n+1 问题)并使用条件获取机制获取嵌套数据。

我有两个实体类:

@Entity
@Table(name = "delegations")
public class Delegation {

    @Id
    @GeneratedValue(strategy = IDENTITY)
    private Long id;

    @ManyToOne
    private Customer customer;

    // more fields, getters, setters, business logic...

}

和

@Entity
@Table(name = "customers")
public class Customer {

    @Id
    @GeneratedValue(strategy = IDENTITY)
    private Long id;

    // more fields, getters, setters, business logic...
}

DTO 过滤器类:

public class DelegationFilter {

    private String customerName;

    // more filters, getters, setters...
}

和搜索/过滤服务:

public class DelegationService {
    public Page<Delegation> findAll(DelegationFilter filter, Pageable page) {
        Specifications<Delegation> spec = Specifications.where(
                customerLike(filter.getCustomerName())
        );
        return delegationRepository.findAll(spec, page);
    }

    public List<Delegation> findAll(DelegationFilter filter) {
        Specifications<Delegation> spec = Specifications.where(
                customerLike(filter.getCustomerName())
        );
        return delegationRepository.findAll(spec);
    }

    private Specification<Delegation> customerLike(String customerName) {
        return (root, query, cb) -> {
            Join<Delegation,Customer> join = (Join) root.fetch(Delegation_.customer);
            return cb.like(cb.lower(join.get(Customer_.name)), addWildCards(customerName.toLowerCase()));
        };
    }

    private static String addWildCards(String param) {
        return '%' + param + '%';
    }
}

问题:

当我调用 findAll(DelegationFilter filter, Pageable page) 时出现异常:

org.springframework.dao.InvalidDataAccessApiUsageException: 
org.hibernate.QueryException: query specified join fetching, but the owner 
of the fetched association was not present in the select list

有没有办法解决这个问题？

findAll(DelegationFilter filter)(没有分页的方法)像魅力一样工作......仅使用 join(没有 fetch)也可以正常工作(即使有分页)

我知道有针对 JPQL 的解决方案: Spring-Data FETCH JOIN with Paging is not working但我想坚持标准 api...

我正在使用 Spring Boot 1.4(spring 4.3.2、spring-data-jpa 1.10.2)和 Hibernate 5.0.9

Answer 1

我遇到了同样的问题，并且找到了解决方法 ( source )。

您可以在运行时检查查询的返回类型，如果它是 Long(计数查询返回的类型)，您就加入，否则就获取。在您的代码中，它将如下所示:

...
private Specification<Delegation> customerLike(String customerName) {
    return (root, query, cb) -> {
        if (query.getResultType() != Long.class && query.getResultType() != long.class) {
          Join<Delegation,Customer> join = (Join) root.fetch(Delegation_.customer);
        } else {
          Join<Delegation,Customer> join = root.join(Delegation_.customer);
        }
        return cb.like(cb.lower(join.get(Customer_.name)), addWildCards(customerName.toLowerCase()));
    };
}
...

我知道它不是很干净，但这是我从 ATM 上找到的唯一解决方案。