1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
| StringBuilder nativeSql = new StringBuilder("select o.* from t_order o,t_order_detail od,t_order_goods og where o.id = od.order_id and o.id = og.order_id and o.company_id=:companyId");
StringBuilder countSql = new StringBuilder("select count(*) from t_order o,t_order_detail od,t_order_goods og where o.id = od.order_id and o.id = og.order_id and o.company_id=:companyId");
if (StringUtils.isNotBlank(orderQueryVM.getGoodsName())) {
nativeSql.append(" and ").append("og.goods_name like :goodsName");
countSql.append(" and ").append("og.goods_name like :goodsName");
}
if (StringUtils.isNotBlank(orderQueryVM.getCustomerName())) {
nativeSql.append(" and ").append("od.customer_name like :customerName");
countSql.append(" and ").append("od.customer_name like :customerName");
}
if (orderQueryVM.getOrderStatus() != null) {
nativeSql.append(" and ").append("o.order_status=:orderStatus");
countSql.append(" and ").append("o.order_status=:orderStatus");
}
if (orderQueryVM.getSettleStatus() != null) {
nativeSql.append(" and ").append("o.settle_status=:settleStatus");
countSql.append(" and ").append("o.settle_status=:settleStatus");
}
if (orderQueryVM.getStartDate() != null && orderQueryVM.getEndDate() != null) {
nativeSql.append(" and ").append("o.order_date<=:endDate");
countSql.append(" and ").append("o.order_date<=:endDate");
nativeSql.append(" and ").append("o.order_date>=:startDate");
countSql.append(" and ").append("o.order_date>=:startDate");
}
nativeSql.append(" order by created_date desc ");
Pageable pageable = new PageRequest(page,pagesize,new Sort(Direction.DESC,"created_date"))
Query nativeQuery = em.createNativeQuery(nativeSql.toString(), Order.class);
//设置分页
nativeQuery.setFirstResult(pageable.getOffset());
nativeQuery.setMaxResults(pageable.getPageSize());
nativeQuery.setParameter("companyId", orderQueryVM.getCompanyId());
Query countQuery = em.createNativeQuery(countSql.toString());
countQuery.setParameter("companyId", orderQueryVM.getCompanyId());
if (StringUtils.isNotBlank(orderQueryVM.getGoodsName())) {
nativeQuery.setParameter("goodsName", "%" + orderQueryVM.getGoodsName() + "%");
countQuery.setParameter("goodsName", "%" + orderQueryVM.getGoodsName() + "%");
}
if (StringUtils.isNotBlank(orderQueryVM.getCustomerName())) {
nativeQuery.setParameter("customerName", "%" + orderQueryVM.getCustomerName() + "%");
countQuery.setParameter("customerName", "%" + orderQueryVM.getCustomerName() + "%");
}
if (orderQueryVM.getOrderStatus() != null) {
nativeQuery.setParameter("orderStatus", orderQueryVM.getOrderStatus().name());
countQuery.setParameter("orderStatus", orderQueryVM.getOrderStatus().name());
}
if (orderQueryVM.getSettleStatus() != null) {
nativeQuery.setParameter("settleStatus", orderQueryVM.getSettleStatus().name());
countQuery.setParameter("settleStatus", orderQueryVM.getSettleStatus().name());
}
if (orderQueryVM.getStartDate() != null && orderQueryVM.getEndDate() != null) {
nativeQuery.setParameter("endDate", orderQueryVM.getEndDate());
countQuery.setParameter("endDate", orderQueryVM.getEndDate());
nativeQuery.setParameter("startDate", orderQueryVM.getStartDate());
countQuery.setParameter("startDate", orderQueryVM.getStartDate());
}
List<Order> orderList = nativeQuery.getResultList();
//获取总数
BigInteger count = (BigInteger) countQuery.getSingleResult();
Page<Order> page = new PageImpl(orderList, pageable, count.intValue());
return page;
|
