我们项目用的JPA实现与数据库的交互,这次遇到了一个需求要对订单进行一定规则的排序。因为之前项目都是用的pageable对返回数据进行封装,pageable本身就有对排序的处理,但是jpa还是刚还是用,所以查了下资料,发现也可以对数据进行排序,特此记录。
把Predicate应用到CriteriaQuery中去,因为还可以给CriteriaQuery添加其他的功能,比如排序、分组啥的。如下图使我们的源代码:
首先对已删除数据和locationInfoId对数据进行过滤,然后根据deliveryType,orderPaytime字段对数据进行排序,前三种deliveryType的订单排在前面,并按orderPaytime进行升序排列,后两种类型的订单排在后面,也按orderPaytime进行升序排列。
Specification specification =
(Specification<ShipOrderInfo>) (root, criteriaQuery, criteriaBuilder) -> {
List<Predicate> predicates = new ArrayList<>();
// 排除逻辑删除数据
predicates.add(criteriaBuilder.equal(root.get("dataStatus"), AuditModel.DATA_STATUS_ACTVIE));
if (null != locationInfoId) {
predicates.add(criteriaBuilder.equal(root.get("locationInfoId"), locationInfoId));
}
if (!StringUtils.isEmpty(shipStatus)) {
CriteriaBuilder.In<Object> in = criteriaBuilder.in(root.get("orderLatestStatus"));
String[] shipStatusArray = StringUtils.split(shipStatus, ',');
Arrays.stream(shipStatusArray).forEach(x -> {
in.value(Integer.valueOf(x));
});
predicates.add(in);
}
criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()]));
criteriaQuery.orderBy(
//先根据 deliveryType 权重升序排
criteriaBuilder.asc(
criteriaBuilder.selectCase()
.when(criteriaBuilder.equal(
root.get("deliveryType"), "Home delivery 2h"), 1)
.when(criteriaBuilder.equal(
root.get("deliveryType"), "C&C 2h"), 1)
.when(criteriaBuilder.equal(
root.get("deliveryType"), "tmalldss"), 1)
.when(criteriaBuilder.equal(
root.get("deliveryType"), "Home delivery"), 3)
.when(criteriaBuilder.equal(
root.get("deliveryType"), "C&C"), 3)
.otherwise(99)),
//再根据 orderPaytime 升序排
criteriaBuilder.asc(root.get("orderPaytime"))
);
return criteriaQuery.getRestriction();
};
- 当然了,还有其他的排序用法:like做模糊匹配,gt筛选大于填入参数的数据,lt筛选小于输入参数的数据。
Specification<ShipOrderInfo> spec = new Specification<ShipOrderInfo>() {
public Predicate toPredicate(Root<ShipOrderInfo> root,
CriteriaQuery<?> query, CriteriaBuilder cb) {
Predicate p1 = cb.like(root.get("name").as(String.class), "%"+um.getName()+"%");
Predicate p2 = cb.equal(root.get("uuid").as(Integer.class), um.getUuid());
Predicate p3 = cb.gt(root.get("age").as(Integer.class), um.getAge());
//把Predicate应用到CriteriaQuery中去,因为还可以给CriteriaQuery添加其他的功能,比如排序、分组啥的
query.where(cb.and(p3,cb.or(p1,p2)));
//添加排序的功能
query.orderBy(cb.desc(root.get("uuid").as(Integer.class)));
return query.getRestriction();
}
};
- 还有复杂条件组合查询
Specification specification =
(Specification<ShipOrderInfo>) (root, criteriaQuery, criteriaBuilder) -> {
List<Predicate> predicates = new ArrayList<>();
// 排除逻辑删除数据
predicates.add(criteriaBuilder.equal(root.get("dataStatus"), AuditModel.DATA_STATUS_ACTVIE));
if (null != locationInfoId) {
predicates.add(criteriaBuilder.equal(root.get("locationInfoId"), locationInfoId));
}
if (!StringUtils.isEmpty(shipStatus)) {
CriteriaBuilder.In<Object> in = criteriaBuilder.in(root.get("orderLatestStatus"));
String[] shipStatusArray = StringUtils.split(shipStatus, ',');
Arrays.stream(shipStatusArray).forEach(x -> {
in.value(Integer.valueOf(x));
});
predicates.add(in);
}
criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()]));
criteriaQuery.orderBy(
//先根据 deliveryType 权重升序排
criteriaBuilder.asc(
criteriaBuilder.selectCase()
.when(criteriaBuilder.equal(
root.get("deliveryType"), "Home delivery 2h"), 1)
.when(criteriaBuilder.equal(
root.get("deliveryType"), "C&C 2h"), 1)
.when(criteriaBuilder.equal(
root.get("deliveryType"), "tmalldss"), 1)
.when(criteriaBuilder.equal(
root.get("deliveryType"), "Home delivery"), 3)
.when(criteriaBuilder.equal(
root.get("deliveryType"), "C&C"), 3)
.otherwise(99)),
//再根据 orderPaytime 升序排
criteriaBuilder.asc(root.get("orderPaytime"))
);
return criteriaQuery.getRestriction();
};