今天项目中遇到了一个特殊的要求,要按照指定的顺序,在分页查询的时候进行排序(jpa)。
原始的排序方式:
criteriaQuery.where(predicateList.toArray(new Predicate[0])) .orderBy( criteriaBuilder.asc(root.get(Order_.adminDate)), criteriaBuilder.desc(root.get(Order_.centerCode)), criteriaBuilder.desc(root.get(Order_.productId)), criteriaBuilder.desc(root.get(Order_.cancelType)) );
要在此基础上进行指定排序:
版本1:对当前也的查询结果进行分页——通过对结果list进行排序,但是不能满足需求。
1 String[] regulation = {"AA", "BB", "CC"}; 2 final List<String> regulationOrder = Arrays.asList(regulation); 3 return orderCancelReviewLogVoList.stream().sorted( 4 Comparator.comparing(OrderVo::getProductId, new Comparator<String>() { 5 @Override 6 public int compare(String o1, String o2) { 7 int io1 = regulationOrder.indexOf(o1.toUpperCase(Locale.ROOT)); 8 int io2 = regulationOrder.indexOf(o2.toUpperCase(Locale.ROOT)); 9 return io1 - io2; 10 } 11 }) 12 .thenComparing(new Comparator<OrderVo>() { 13 @Override 14 public int compare(OrderVoo1, OrderVoo2) { 15 Integer centerCodeOne = Integer.parseInt(o1.getCenterCode()); 16 Integer centerCodeTwo = Integer.parseInt(o2.getCenterCode()); 17 return centerCodeOne - centerCodeTwo; 18 } 19 }) 20 .thenComparing(OrderVo::getAdminDate) 21 ).collect(Collectors.toList());
版本2:直接在jpa,分页查询时,对Order进行自定义,同时通过As方法进行类型转换
1 List<Order> orders = new ArrayList<>(); 2 orders.add(criteriaBuilder.asc(criteriaBuilder.selectCase() 3 .when(criteriaBuilder.equal(root.get(OrderPo_.productId), "AA"), 1) 4 .when(criteriaBuilder.equal(root.get(OrderPo_.productId), "BB"), 2) 5 .when(criteriaBuilder.equal(root.get(OrderPo_.productId), "CC"), 3) 6 .otherwise(4))); 7 orders.add(criteriaBuilder.asc(root.get(OrderPo_.centerCode).as(Integer.TYPE))); 8 orders.add(criteriaBuilder.asc(root.get(OrderPo_.adminDate))); 9 orders.add(criteriaBuilder.desc(root.get(OrderPo_.cancelType))); 10 11 criteriaQuery.where(predicateList.toArray(new Predicate[0])) 12 .orderBy(orders);
生成SQL
此时等于WHEN后面的字段内容就会按照THEN后面的1234的顺序进行ASC升序排序。
先来看pgsql中的写法 SELECT a.A FROM table_a a ORDER BY CASE a.A WHEN '你好' THEN 1 WHEN '我好' THEN 2 WHEN '大家好' THEN 3 WHEN '是真滴好' THEN 4 END ASC
标签:类型转换,OrderPo,get,Specification,_.,criteriaBuilder,排序,root,orders From: https://www.cnblogs.com/hobby0524/p/17296624.html