Spring JPA 自定义关联分页查询（动态条件）

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;