使用Spring boot + Hibernate + JPA+ EntityManager + Query + Pageable 分页以及动态查询

package com.yonyou.occ.report.service.impl;

import com.yonyou.occ.report.entity.PromRuleEntity;
import com.yonyou.occ.report.service.PromRuleExportService;
import com.yonyou.occ.report.service.dto.PromRuleDto;
import com.yonyou.occ.report.utils.BeanConverterUtils;
import com.yonyou.occ.report.vo.PromRuleVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 
 * @date 2022-02-22 17:34
 */
@Service
public class PromRuleExportServiceImpl implements PromRuleExportService {

    @Autowired
    private EntityManager entityManager;

    /**
     * 获取数据
     *
     * @param searchParams
     * @return
     */
    @Override
    public Page<PromRuleDto> getData(Map<String, Object> searchParams, Pageable pageable) {
        if(StringUtils.isEmpty(pageable)){
        	//pageabel = new PageRequest(0, 10);//已过时的方法
            pageable = PageRequest.of(0, 10);
        }

        Page<PromRuleDto> promRuleDtos = getDataByParams(searchParams, pageable);

        return promRuleDtos;
    }

    private Page<PromRuleDto> getDataByParams(Map<String, Object> searchParams, Pageable pageable) {
        //查询数据，并设置分页
        StringBuffer querySql = new StringBuffer("");
        createSql(searchParams, querySql);
        Query query = this.entityManager.createNativeQuery(querySql.toString(), PromRuleEntity.class);

        query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
        query.setMaxResults(pageable.getPageSize());

        List<PromRuleDto> promRuleDtos = query.getResultList();

        //获取总数
        StringBuilder totalSql = pageTotalSql(searchParams);
        Query queryCount = this.entityManager.createNativeQuery(totalSql.toString());
        Long pageTotal = Long.valueOf(queryCount.getSingleResult().toString());
		
		//或
		//BigInteger count = new BigInteger("0");
		//count = (BigInteger) queryCount.getSingleResult();
		//Long pageTotal = count.longValue()

        //封装数据
        Page<PromRuleDto> page = new PageImpl<>(promRuleDtos, pageable, pageTotal);

        return page;
    }
    
    /**
     * 构建sql
     * @param searchParams
     * @param querySql
     */
    private void createSql(Map<String, Object> searchParams, StringBuffer querySql){
        querySql.append("with product as\n" +
                " (select id as goods_id, code as goods_code, name as goods_name\n" +
                "    from product_combination\n" +
                "   where nvl(dr, 0) = 0\n" +
                "  union all\n" +
                "  select id as goods_id, code as goods_code, name as goods_name\n" +
                "    from base_goods\n" +
                "   where nvl(dr, 0) = 0)\n" +
                "select c.id,\n" +
                "       c.prom_rule_id,\n" +
                "       a.code as prom_rule_code,\n" +
                "       a.name as prom_rule_name,\n" +
                "       case a.prom_way\n" +
                "         when 3 then\n" +
                "          '仅买'\n" +
                "         when 4 then\n" +
                "          '仅赠'\n" +
                "         else\n" +
                "          '其他'\n" +
                "       end as prom_way,\n" +
                "       case\n" +
                "         when a.dr = 1 then\n" +
                "          '已删除'\n" +
                "         else\n" +
                "          case\n" +
                "            when a.is_enable = 1 then\n" +
                "             '已启用'\n" +
                "            else\n" +
                "             '已停用'\n" +
                "          end\n" +
                "       end as enable_status,\n" +
                "       a.creator,\n" +
                "       a.creation_time,\n" +
                "       a.modifier,\n" +
                "       a.modified_time,\n" +
                "       c.row_num,\n" +
                "       g.goods_id,\n" +
                "       g.goods_code,\n" +
                "       g.goods_name,\n" +
                "       c.pro_type,\n" +
                "       case c.pro_type when 1 then '商品' when 2 then '产品' when 3 then '组合' when 4 then '分类' else '其他' end as pro_type_name,\n" +
                "       c.reference_price,\n" +
                "       c.discount,\n" +
                "       c.price,\n" +
                "       c.boundary_value,\n" +
                "       c.num,\n" +
                "       c.amount\n" +
                "  from prom_rule a\n" +
                "  left join prom_rule_detail_hanhoo c\n" +
                "    on a.id = c.prom_rule_id\n" +
                "  left join product g\n" +
                "    on c.pro_id = g.goods_id\n" +
                " where nvl(a.dr, 0) = 0\n" +
                "   and a.is_show = 1\n");

        if(!StringUtils.isEmpty(searchParams)){
            //规则编码
            if (!StringUtils.isEmpty(searchParams.get("promRuleCode"))) {
                querySql.append(" and a.code = '");
                querySql.append(searchParams.get("promRuleCode"));
                querySql.append("'\n");
            }

            //规则名称
            if (!StringUtils.isEmpty(searchParams.get("promRuleName"))) {
                querySql.append(" and a.name = '");
                querySql.append(searchParams.get("promRuleName"));
                querySql.append("'\n");
            }

            //启用状态
            if (!StringUtils.isEmpty(searchParams.get("enableStatus"))) {
                querySql.append(" and a.is_enable = '");
                querySql.append(searchParams.get("enableStatus"));
                querySql.append("'\n");
            }

            //促销方式
            if (!StringUtils.isEmpty(searchParams.get("promWay"))) {
                querySql.append(" and a.prom_way = '");
                querySql.append(searchParams.get("promWay"));
                querySql.append("'\n");
            }
        }

        querySql.append(" order by a.code, c.row_num, a.creation_time desc");
    }

    /**
     * 统计数据总条数
     * @param searchParams
     */
    private StringBuilder pageTotalSql(Map<String, Object> searchParams) {
        StringBuilder querySql = new StringBuilder("");
        querySql.append("with product as\n" +
                " (select id as goods_id, code as goods_code, name as goods_name\n" +
                "    from product_combination\n" +
                "   where nvl(dr, 0) = 0\n" +
                "  union all\n" +
                "  select id as goods_id, code as goods_code, name as goods_name\n" +
                "    from base_goods\n" +
                "   where nvl(dr, 0) = 0)\n" +
                "select count(c.id)\n" +
                "  from prom_rule a\n" +
                "  left join prom_rule_detail_hanhoo c\n" +
                "    on a.id = c.prom_rule_id\n" +
                "  left join product g\n" +
                "    on c.pro_id = g.goods_id\n" +
                " where nvl(a.dr, 0) = 0\n" +
                "   and a.is_show = 1\n");

        if (!StringUtils.isEmpty(searchParams)) {
            //规则编码
            if (!StringUtils.isEmpty(searchParams.get("promRuleCode"))) {
                querySql.append(" and a.code = '");
                querySql.append(searchParams.get("promRuleCode"));
                querySql.append("'\n");
            }

            //规则名称
            if (!StringUtils.isEmpty(searchParams.get("promRuleName"))) {
                querySql.append(" and a.name = '");
                querySql.append(searchParams.get("promRuleName"));
                querySql.append("'\n");
            }

            //启用状态
            if (!StringUtils.isEmpty(searchParams.get("enableStatus"))) {
                querySql.append(" and a.is_enable = '");
                querySql.append(searchParams.get("enableStatus"));
                querySql.append("'\n");
            }

            //促销方式
            if (!StringUtils.isEmpty(searchParams.get("promWay"))) {
                querySql.append(" and a.prom_way = '");
                querySql.append(searchParams.get("promWay"));
                querySql.append("'\n");
            }
        }

        querySql.append(" order by a.code, c.row_num, a.creation_time desc");

        return querySql;
    }
}