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;
Spring JPA 自定义关联分页查询(动态条件)
©著作权归作者所有,转载或内容合作请联系作者
- 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
- 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
- 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
推荐阅读更多精彩内容
- 一.Springboot下JPA根据实体类自动建表(Demo使用mysql数据库,alibaba线程池)1.Mav...
- 我们使用JpaSpecificationExecutor的方法Page<T> findAll(Specificat...
- 实现 PagingAndSortingRepository,QueryDslPredicateExecutor,J...
- 1. Spring Data JPA 查询接收自定义对象 (非数据库对应的实体类)需要我们定义一个接收返回结果集的...
- 官方给出的自定义分页查询的示例是这样的: https://docs.spring.io/spring-data/j...