JPA 多表左连接多条件分页查询
发布时间
阅读量：
阅读量
业务场景：
主表：订单表，与客户表、产品表、物流表存在一对一关系，映射字段为id，现需要根据订单编号、订单日期、客户名称、客户编号、产品名称、产品编号、快递单号查询该笔订单，需要支持模糊查询和分页。
Order实体类中的需要进行一对一关系映射：
@OneToOne
@JoinColumn(name = "express_id")
private Express express;
@OneToOne
@JoinColumn(name = "product_id")
private Product product;
@OneToOne
@JoinColumn(name = "customer_id")
private Customer customer;Dao中的需要继承JpaRepository，JpaSpecificationExecuto两个接口:
@Repository
public interface OrderDao<T, ID extends Serializable> extends JpaRepository<T, ID>, JpaSpecificationExecutor<T> {
}Service中的写法：
Pageable通过前端传入的pageSize 和 pageNum进行创建
Pageable pageable = PageRequest.of(pageNum - 1, pageSize); @Autowired
OrderDao licenseDao;
@Autowired
KeyPairService keyPairService;
public Page<Order> getLicenseList(Order order, Pageable pageable) {
Specification<License> specification = new Specification<Order>() {
@Override
public Predicate toPredicate(Root<License> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
List<Predicate> list = new ArrayList<Predicate>();
Join<Order, Express> expressJoin = root.join("express", JoinType.LEFT);
Join<Order, Product> proJoin = root.join("product", JoinType.LEFT);
Join<Order, Customer> customerJoin = root.join("customer", JoinType.LEFT);
if (null != order.getCustomer() && !StringUtils.isEmpty(order.getCustomer().getCode())) {
list.add(criteriaBuilder.like(customerJoin.get("code").as(String.class), "%" + order.getCustomer().getCode() + "%"));
}
if (null != order.getCustomer() && !StringUtils.isEmpty(order.getCustomer().getName())) {
list.add(criteriaBuilder.like(customerJoin.get("name").as(String.class), "%" + order.getCustomer().getName() + "%"));
}
if (null != order.getProduct() && !StringUtils.isEmpty(order.getProduct().getCode())) {
list.add(criteriaBuilder.like(proJoin.get("code").as(String.class), "%" + order.getProduct().getCode() + "%"));
}
if (null != order.getProduct() && !StringUtils.isEmpty(order.getProduct().getName())) {
list.add(criteriaBuilder.like(proJoin.get("name").as(String.class), "%" + order.getProduct().getName() + "%"));
}
if (null != order.getExpress() && !StringUtils.isEmpty(order.getExpress().getCode())) {
list.add(criteriaBuilder.like(expressJoin.get("code").as(String.class), "%" + order.getExpress().getCode() + "%"));
}
if (!StringUtils.isEmpty(order.getCode())) {
list.add(criteriaBuilder.like(root.get("code").as(String.class), "%" + order.getCode() + "%"));
}
if (null != order.getCreateDate()) {
list.add(criteriaBuilder.lessThan(root.get("createDate").as(Date.class), order.getCreateDate()));
}
Predicate[] p = new Predicate[list.size()];
return criteriaBuilder.and(list.toArray(p));
}
};
return orderDao.findAll(specification, pageable);
}全部评论 (0)
还没有任何评论哟~