javax.persistence.Query和javax.persistence.EntityManager的分页查询使用

本文主要是介绍javax.persistence.Query和javax.persistence.EntityManager的分页查询使用，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
javax.persistence.Query和javax.persistence.EntityManager都是JAP发布的接口，是用于对象持久化API。
EntityManager接口用于与持久化上下文交互。一个EntityManager实例与持久化上下文相关的。一个持久化上下文是一组中的实体实例的任何持久的实体的身份有一个独特的实体实例。在持久性上下文中，实体实例及其生命周期管理。这个EntityManagerAPI是用于创建和删除持久的实体实例，根据主键找到实体，并查询实体。实体可以通过一个给定的管理设置EntityManager实例是由一个存储单元定义。一个存储单元定义的所有类相关或由应用程序分组设置，且必须放在他们的映射到一个数据库。
Query接口常见用法：
getResultList()：执行 SELECT 查询并将查询结果作为无类型列表返回。
getSingleResult ()：执行返回单个无类型结果的 SELECT 查询。
getFirstResult ()：查询对象设置为检索的第一个结果的位置。如果setFirstResult未应用于查询对象，则返回 0 。
executeUpdate()：执行更新或删除语句。
getMaxResults ()：查询对象设置为检索的最大结果数。返回Integer.MAX_VALUEifsetMaxResults未应用于查询对象。
setFirstResult (int startPosition)：设置要检索的第一个结果的位置。
setMaxResults (int maxResult)：设置要检索的最大结果数。
setParameter (int position, Object value)：将参数绑定到位置参数。
setParameter（字符串名称，对象值）：将参数绑定到命名参数。
EntityManager接口常见用法：
persist() :添加实体Bean
flush() ：将实体的改变立刻刷新到数据库中
merge () :比较麻烦，用好了很不错，配合flush
Remove() :删除对象
createQuery() ：返回Query对象,以执行JPQL语句
createNativeQuery() ：返回Query对象,以执行SQL语句
refresh() ：刷新实体Bean,以得到对新对象
contains()：检测实体当前是否被管理中
clear() 分离所有当前正在被管理的实体
更多Query和EntityManager用法，请参考api文档：https://www.objectdb.com/api/java/jpa/Query，https://www.objectdb.com/api/java/jpa/EntityManager
使用方法如下例子：
package com.yonyou.occ.cr.service;

import com.yonyou.occ.cr.entity.ActivityCreditRecordSum;
import com.yonyou.occ.cr.entity.CreditLimit;
import com.yonyou.occ.cr.entity.CreditLimitExt;
import com.yonyou.occ.cr.service.dto.CreditCtrlStrategyDto;
import com.yonyou.occ.cr.service.dto.CreditLimitDto;
import com.yonyou.occ.cr.service.dto.CreditLimitExtDto;
import com.yonyou.ocm.common.annos.IndustryExt;
import com.yonyou.ocm.common.datapermission.DataPermissionApi;
import com.yonyou.ocm.common.exception.BusinessException;
import com.yonyou.ocm.common.utils.CommonUtils;
import com.yonyou.ocm.common.web.rest.util.HeaderUtil;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
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.http.ResponseEntity;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import javax.annotation.Generated;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.net.URLDecoder;
import java.util.*;

/**
 * 信用余额服务扩展类，由于增加了促销活动的标识，故需复制源码出来做相应的调整

 */
@Service
@IndustryExt
@Generated(value = "com.yonyou.ocm.util.codegenerator.CodeGenerator")
public class CreditBalanceExtService extends CreditBalanceService{

    @Autowired
    private ActivityCreditRecordService activityCreditRecordService;
    @Autowired
    private EntityManager entityManager;
    @Autowired
    private DataPermissionApi dataPermissionApi;
    @Autowired
    private CreditCtrlStrategyService strategyService;
    @Autowired
    private JpaProperties jpaProperties;

    /**
    *  信用查询增加促销活动维度 modify source
    * @param:
     * @param creditLimitDtoList
     * @param recalculation
     * @param prefixKey
    * @return:
    */
    @Override
    public List<CreditLimitDto> queryLimit(List<CreditLimitDto> creditLimitDtoList, Boolean recalculation, String prefixKey) {
        if(CollectionUtils.isEmpty(creditLimitDtoList)){
            return null;
        }
        return queryLimitByActivity(creditLimitDtoList,recalculation,prefixKey);
    }

    /**
     * 此处直接复制CreditBalanceService.queryLimit源码出来修改 modify source
     */
    public List<CreditLimitDto> queryLimitByActivity(List<CreditLimitDto> creditLimitDtoList, Boolean recalculation, String prefixKey) {
        if(CollectionUtils.isEmpty(creditLimitDtoList)){
            return null;
        }
        Set<String> creditCtrlStrategyId = new HashSet<>();
        Set<String> customerId = new HashSet<>();
        Set<String> productLineId = new HashSet<>();
        Set<String> currencyId = new HashSet<>();
        Set<String> creditCtrlStrategyIdWithProductLineId = new HashSet<>();
        Set<String> customerIdWithProductLineId = new HashSet<>();
        Set<String> currencyIdWithProductLineId = new HashSet<>();

        Set<String> activityId = new HashSet<>();
 
        for (CreditLimitDto creditLimitDto : creditLimitDtoList) {
            Boolean withProductLine = false;
            if (StringUtils.isNotBlank(creditLimitDto.getProductGroupId())) {
                productLineId.add(creditLimitDto.getProductGroupId());
                withProductLine = true;
            }

            CreditLimitExtDto creditLimitExtDto = (CreditLimitExtDto)creditLimitDto;
            if(StringUtils.isNotBlank(creditLimitExtDto.getActivity())){
                activityId.add(creditLimitExtDto.getActivity());
            }

            if (withProductLine) {
                if (StringUtils.isNotBlank(creditLimitDto.getCreditCtrlStrategyId())) {
                    creditCtrlStrategyIdWithProductLineId.add(creditLimitDto.getCreditCtrlStrategyId());
                }
                if (StringUtils.isNotBlank(creditLimitDto.getCustomerId())) {
                    customerIdWithProductLineId.add(creditLimitDto.getCustomerId());
                }
                if (StringUtils.isNotBlank(creditLimitDto.getCurrencyId())) {
                    currencyIdWithProductLineId.add(creditLimitDto.getCurrencyId());
                }
            }else {
                if (StringUtils.isNotBlank(creditLimitDto.getCreditCtrlStrategyId())) {
                    creditCtrlStrategyId.add(creditLimitDto.getCreditCtrlStrategyId());
                }
                if (StringUtils.isNotBlank(creditLimitDto.getCustomerId())) {
                    customerId.add(creditLimitDto.getCustomerId());
                }
                if (StringUtils.isNotBlank(creditLimitDto.getCurrencyId())) {
                    currencyId.add(creditLimitDto.getCurrencyId());
                }
            }
        }
        List<ActivityCreditRecordSum> actCreditRecordSumList = new ArrayList<>();
        if(recalculation){
            if (StringUtils.isBlank(prefixKey)) {
                throw new BusinessException("查询重算结果，本次查询唯一标识不能为空");
            }

            if(CollectionUtils.isNotEmpty(creditCtrlStrategyId)&&
                    CollectionUtils.isNotEmpty(customerId)&&
                    CollectionUtils.isNotEmpty(currencyId)){
                if (customerId.size() > 1000) {
                    String[] customerIdArray = customerId.toArray(new String[customerId.size()]);
                    int beginIndex = 0;
                    boolean continueflag = true;
                    do {
                        Set<String> tempCustomerIds = new HashSet<>();
                        if (customerIdArray.length > (beginIndex + 1000)) {
                            tempCustomerIds.addAll(Arrays.asList(ArrayUtils.subarray(customerIdArray,beginIndex,beginIndex+1000)));
                        }else{
                            tempCustomerIds.addAll(Arrays.asList(ArrayUtils.subarray(customerIdArray,beginIndex,customerIdArray.length)));
                            continueflag = false;
                        }
                        if (CollectionUtils.isNotEmpty(activityId)) {
                            actCreditRecordSumList.addAll(activityCreditRecordService.querySumByActivityOrNullWithRecalculationCreditRecord(creditCtrlStrategyId,tempCustomerIds,currencyId,activityId,prefixKey));
                        }else{
                            actCreditRecordSumList.addAll(activityCreditRecordService.querySumNoActivityWithRecalculationCreditRecord(creditCtrlStrategyId,customerId,currencyId,prefixKey));
                        }
                        beginIndex += 1000;
                    } while (continueflag);
                }else{
                    if (CollectionUtils.isNotEmpty(activityId)) {
                        actCreditRecordSumList.addAll(activityCreditRecordService.querySumByActivityOrNullWithRecalculationCreditRecord(creditCtrlStrategyId,customerId,currencyId,activityId,prefixKey));
                    }else{
                        actCreditRecordSumList.addAll(activityCreditRecordService.querySumNoActivityWithRecalculationCreditRecord(creditCtrlStrategyId,customerId,currencyId,prefixKey));
                    }
                }
            }
            if(CollectionUtils.isNotEmpty(creditCtrlStrategyIdWithProductLineId)&&
                    CollectionUtils.isNotEmpty(customerIdWithProductLineId)&&
                    CollectionUtils.isNotEmpty(productLineId)&&
                    CollectionUtils.isNotEmpty(currencyIdWithProductLineId)){
                
                //List<ActivityCreditRecordSum> creditRecordSumListWithProductLine = new ArrayList<>();
                if (customerIdWithProductLineId.size() > 1000) {
                    String[] customerIdArray = customerIdWithProductLineId.toArray(new String[customerIdWithProductLineId.size()]);
                    int beginIndex = 0;
                    boolean continueflag = true;
                    do {
                        Set<String> tempCustomerIds = new HashSet<>();
                        if (customerIdArray.length > (beginIndex + 1000)) {
                            tempCustomerIds.addAll(Arrays.asList(ArrayUtils.subarray(customerIdArray,beginIndex,beginIndex+1000)));
                        }else{
                            tempCustomerIds.addAll(Arrays.asList(ArrayUtils.subarray(customerIdArray,beginIndex,customerIdArray.length)));
                            continueflag = false;
                        }
                        List<ActivityCreditRecordSum> creditRecordSumListWithProductLine = null;
                        if (CollectionUtils.isNotEmpty(activityId)) {
                            creditRecordSumListWithProductLine = activityCreditRecordService.querySumByActivityOrNullWithRecalculationCreditRecord(creditCtrlStrategyIdWithProductLineId,tempCustomerIds,productLineId,currencyIdWithProductLineId,activityId,prefixKey);
                        }else{
                            creditRecordSumListWithProductLine = activityCreditRecordService.querySumByActivityOrNullWithRecalculationCreditRecord(creditCtrlStrategyIdWithProductLineId, tempCustomerIds, productLineId, currencyIdWithProductLineId, prefixKey);
                        }

                        if (CollectionUtils.isNotEmpty(creditRecordSumListWithProductLine)) {
                            actCreditRecordSumList.addAll(creditRecordSumListWithProductLine);
                        }
                        beginIndex += 1000;
                    } while (continueflag);
                }else{
                    List<ActivityCreditRecordSum> creditRecordSumListWithProductLine = null;
                    if (CollectionUtils.isNotEmpty(activityId)) {
                        creditRecordSumListWithProductLine = activityCreditRecordService.querySumByActivityOrNullWithRecalculationCreditRecord(creditCtrlStrategyIdWithProductLineId,customerIdWithProductLineId,productLineId,currencyIdWithProductLineId,activityId,prefixKey);
                    }else{
                        creditRecordSumListWithProductLine = activityCreditRecordService.querySumByActivityOrNullWithRecalculationCreditRecord(creditCtrlStrategyIdWithProductLineId, customerIdWithProductLineId, productLineId, currencyIdWithProductLineId, prefixKey);
                    }

                    if (CollectionUtils.isNotEmpty(creditRecordSumListWithProductLine)) {
                        actCreditRecordSumList.addAll(creditRecordSumListWithProductLine);
                    }
                }
//                if(CollectionUtils.isNotEmpty(creditRecordSumListWithProductLine)){
//                    actCreditRecordSumList.addAll(creditRecordSumListWithProductLine);
//                }
            }

        }else{
            if(CollectionUtils.isNotEmpty(creditCtrlStrategyId)&&
                    CollectionUtils.isNotEmpty(customerId)&&
                    CollectionUtils.isNotEmpty(currencyId)
                    ){
                if (customerId.size() > 1000) {
                    String[] customerIdArray = customerId.toArray(new String[customerId.size()]);
                    int beginIndex = 0;
                    boolean continueflag = true;
                    do {
                        Set<String> tempCustomerIds = new HashSet<>();
                        if (customerIdArray.length > (beginIndex + 1000)) {
                            tempCustomerIds.addAll(Arrays.asList(ArrayUtils.subarray(customerIdArray,beginIndex,beginIndex+1000)));
                        }else{
                            tempCustomerIds.addAll(Arrays.asList(ArrayUtils.subarray(customerIdArray,beginIndex,customerIdArray.length)));
                            continueflag = false;
                        }
                        List<ActivityCreditRecordSum> creditRecordSumListWithProductLine = null;
                        if (CollectionUtils.isNotEmpty(activityId)) {
                            creditRecordSumListWithProductLine = activityCreditRecordService.querySumByActivityOrNullWithCreditRecord(creditCtrlStrategyId,tempCustomerIds,currencyId,activityId);
                        }else {
                            creditRecordSumListWithProductLine = activityCreditRecordService.querySumNoActivityWithCreditRecord(creditCtrlStrategyId,tempCustomerIds,currencyId);
                        }
                        if (CollectionUtils.isNotEmpty(creditRecordSumListWithProductLine)) {
                            actCreditRecordSumList.addAll(creditRecordSumListWithProductLine);
                        }
                        beginIndex += 1000;
                    } while (continueflag);
                }else{
                    List<ActivityCreditRecordSum> creditRecordSumListWithProductLine = null;
                    if (CollectionUtils.isNotEmpty(activityId)) {
                        creditRecordSumListWithProductLine = activityCreditRecordService.querySumByActivityOrNullWithCreditRecord(creditCtrlStrategyId,customerId,currencyId,activityId);
                    }else {
                        creditRecordSumListWithProductLine = activityCreditRecordService.querySumNoActivityWithCreditRecord(creditCtrlStrategyId,customerId,currencyId);
                    }
                    if (CollectionUtils.isNotEmpty(creditRecordSumListWithProductLine)) {
                        actCreditRecordSumList.addAll(creditRecordSumListWithProductLine);
                    }
                }
            }
            if(CollectionUtils.isNotEmpty(creditCtrlStrategyIdWithProductLineId)&&
                    CollectionUtils.isNotEmpty(customerIdWithProductLineId)&&
                    CollectionUtils.isNotEmpty(productLineId)&&
                    CollectionUtils.isNotEmpty(currencyIdWithProductLineId)
                    ){
                if (customerIdWithProductLineId.size() > 1000) {
                    String[] customerIdArray = customerIdWithProductLineId.toArray(new String[customerIdWithProductLineId.size()]);
                    int beginIndex = 0;
                    boolean continueflag = true;
                    do {
                        Set<String> tempCustomerIds = new HashSet<>();
                        if (customerIdArray.length > (beginIndex + 1000)) {
                            tempCustomerIds.addAll(Arrays.asList(ArrayUtils.subarray(customerIdArray,beginIndex,beginIndex+1000)));
                        }else{
                            tempCustomerIds.addAll(Arrays.asList(ArrayUtils.subarray(customerIdArray,beginIndex,customerIdArray.length)));
                            continueflag = false;
                        }
                        List<ActivityCreditRecordSum> creditRecordSumListWithProductLine = new ArrayList<>();
                        if (CollectionUtils.isNotEmpty(activityId)) {
                            creditRecordSumListWithProductLine = activityCreditRecordService.querySumByActivityOrNullWithCreditRecord(creditCtrlStrategyIdWithProductLineId,tempCustomerIds,productLineId,currencyIdWithProductLineId,activityId);
                        }else {
                            creditRecordSumListWithProductLine = activityCreditRecordService.querySumNoActivityWithCreditRecord(creditCtrlStrategyIdWithProductLineId,tempCustomerIds,productLineId,currencyIdWithProductLineId);
                        }
                        if(CollectionUtils.isNotEmpty(creditRecordSumListWithProductLine)){
                            actCreditRecordSumList.addAll(creditRecordSumListWithProductLine);
                        }
                        beginIndex += 1000;
                    } while (continueflag);
                }else{
                    List<ActivityCreditRecordSum> creditRecordSumListWithProductLine = new ArrayList<>();
                    if (CollectionUtils.isNotEmpty(activityId)) {
                        creditRecordSumListWithProductLine = activityCreditRecordService.querySumByActivityOrNullWithCreditRecord(creditCtrlStrategyIdWithProductLineId,customerIdWithProductLineId,productLineId,currencyIdWithProductLineId,activityId);
                    }else {
                        creditRecordSumListWithProductLine = activityCreditRecordService.querySumNoActivityWithCreditRecord(creditCtrlStrategyIdWithProductLineId,customerIdWithProductLineId,productLineId,currencyIdWithProductLineId);
                    }
                    if(CollectionUtils.isNotEmpty(creditRecordSumListWithProductLine)){
                        actCreditRecordSumList.addAll(creditRecordSumListWithProductLine);
                    }
                }
            }
        }
        //将查询出来的数值赋值到展示的Dto中
        copyValuesInToResult(creditLimitDtoList,actCreditRecordSumList);
        return creditLimitDtoList;
    }

    /**
     * 此处直接复制CreditBalanceService.copyValuesInToResult源码出来修改 modify source
     */
    //为查询出来的信用额度表赋值上对应的信用余额，预占金额，收款金额，占用金额
    private void copyValuesInToResult(List<CreditLimitDto> creditLimitDtoList, List<ActivityCreditRecordSum> creditRecordSumList) {
        if(!CollectionUtils.isEmpty(creditRecordSumList)){
            for(CreditLimitDto creditLimitDto:creditLimitDtoList){
                // 增加促销活动的分类
                CreditLimitExtDto creditLimitExtDto = (CreditLimitExtDto)creditLimitDto;
                String limitActivity = creditLimitExtDto.getActivity()==null?"":creditLimitExtDto.getActivity();

                for(ActivityCreditRecordSum creditRecordSum:creditRecordSumList){
                    String sumActivity = creditRecordSum.getActivity()==null?"":creditRecordSum.getActivity();
                    if(creditLimitDto.getCreditCtrlStrategyId().equals(creditRecordSum.getCreditCtrlStrategyId())&&
                            creditLimitDto.getCustomerId().equals(creditRecordSum.getCustomerId())&&
                            (StringUtils.isBlank(creditLimitDto.getProductGroupId())||
                                    StringUtils.isNotBlank(creditLimitDto.getProductGroupId())&&StringUtils.isNotBlank(creditRecordSum.getProductLineId())&&creditLimitDto.getProductGroupId().equals(creditRecordSum.getProductLineId()))&&
                            creditLimitDto.getCurrencyId().equals(creditRecordSum.getCurrencyId())&&
                            limitActivity.equals(sumActivity)){
                        creditLimitDto.setOccupyLimit(creditRecordSum.getReceivableAmount().subtract(creditRecordSum.getReceiptAmount()).add(creditRecordSum.getOccupyLimit()).subtract(creditRecordSum.getAccountDeductionAmount()));
                        creditLimitDto.setPreoccupyLimit(creditRecordSum.getReceivableAmount().subtract(creditRecordSum.getReceiptAmount()).add(creditRecordSum.getPreoccupyLimit()).subtract(creditRecordSum.getAccountDeductionAmount()));
                        creditLimitDto.setOrderOccupyLimit(creditRecordSum.getOccupyLimit());
                        creditLimitDto.setOrderPreoccupyLimit(creditRecordSum.getPreoccupyLimit());
                        creditLimitDto.setOccupyBalance(creditLimitDto.getCreditLimit().subtract(creditLimitDto.getOccupyLimit()));
                        creditLimitDto.setPreoccupyBalance(creditLimitDto.getCreditLimit().subtract(creditLimitDto.getPreoccupyLimit()));
                        creditLimitDto.setReceiptAmount(creditRecordSum.getReceiptAmount());
                        creditLimitDto.setReceivableAmount(creditRecordSum.getReceivableAmount());
                        creditLimitDto.setAccountDeductionAmount(creditRecordSum.getAccountDeductionAmount());
                        break;
                    }
                    creditLimitDto.setOccupyBalance(creditLimitDto.getCreditLimit());
                    creditLimitDto.setPreoccupyBalance(creditLimitDto.getCreditLimit());
                }
            }
        }else{
            for(CreditLimitDto creditLimitDto:creditLimitDtoList){
                creditLimitDto.setPreoccupyBalance(creditLimitDto.getCreditLimit());
                creditLimitDto.setOccupyBalance(creditLimitDto.getCreditLimit());
            }
        }
    }

    /**
     * 此处直接复制CreditBalanceService.findAllWithComputer源码出来修改
     */
    @Override
    public Page<CreditLimitDto> findAllWithComputer(Map<String, Object> searchParams, Pageable pageable, boolean recalculation) {
        // 默认按照创建时间倒序排列
        if (pageable.getSort() == null) {
            pageable = new PageRequest(pageable.getPageNumber(), pageable.getPageSize(), getDefaultSort());
        }
        //公共参数
        List<CreditLimit> entityList = null;
        int count = 0;
        Map<String, Object> params = new HashMap<>();
        StringBuilder jpql = new StringBuilder();
        StringBuilder jpqlCount = new StringBuilder();

        //如果是Oracle数据库，则执行下面的查询，否则执行MySQL的查询
        if ("ORACLE".equalsIgnoreCase(jpaProperties.getDatabase().name())) {
            StringBuilder whereClause = new StringBuilder();
            StringBuilder sql = new StringBuilder();
            whereClause.append(" where rn <= 1 and c.dr=0 ");
            //增加促销活动的标识
            sql.append("select * from (select lim.*,limext.ACTIVITY_ID, " +
//                "row_number() over(partition by lim.customer_id,lim.pk_org,lim.sale_org_id,lim.product_line_id " +
                    "row_number() over(partition by lim.customer_id,lim.credit_ctrl_strategy_id,lim.product_line_id,limext.ACTIVITY_ID " +
                    "order by lim.start_date desc,lim.end_date asc) rn " +
                    "from cr_credit_limit lim,cr_credit_limit_ext limext where lim.id=limext.id and lim.start_date <= :sysdate " +
                    "and lim.end_date >= :sysdate and lim.dr=0 ) c ");

            jpql.append(sql);
            jpqlCount.append(sql);
            int flag = 0;
            if (searchParams.containsKey("EQ_creditCtrlStrategy.id")||
                    searchParams.containsKey("EQ_creditCtrlStrategy.organization") || searchParams.containsKey("EQ_creditCtrlStrategy.saleOrg")
                    || searchParams.containsKey("EQ_customer") || searchParams.containsKey("IN_customer") || searchParams.containsKey("EQ_productGroup")) {

                flag = 1;
                jpql.append(" join cr_credit_ctrl_strategy str on c.credit_ctrl_strategy_id = str.id ");         //组织
                jpqlCount.append(" join cr_credit_ctrl_strategy str on c.credit_ctrl_strategy_id = str.id ");
                if (searchParams.containsKey("EQ_creditCtrlStrategy.id")) {
                    String creditCtrlStrategyId = (String) searchParams.get("EQ_creditCtrlStrategy.id");
                    whereClause.append(" and str.id =:creditCtrlStrategyId");
                    params.put("creditCtrlStrategyId", creditCtrlStrategyId);
                }
                if (searchParams.containsKey("EQ_creditCtrlStrategy.organization")) {
                    String organization = (String) searchParams.get("EQ_creditCtrlStrategy.organization");
                    whereClause.append(" and str.pk_org =:organization");
                    params.put("organization", organization);
                }
                if (searchParams.containsKey("EQ_creditCtrlStrategy.saleOrg")) {            //销售组织
                    String saleOrg = (String) searchParams.get("EQ_creditCtrlStrategy.saleOrg");
                    whereClause.append(" and str.sale_org_id =:saleOrg");
                    params.put("saleOrg", saleOrg);
                }
                if (searchParams.containsKey("EQ_customer")) {                                 //客户
                    String customer = (String) searchParams.get("EQ_customer");
                    whereClause.append(" and c.customer_id =:customer");
                    params.put("customer", customer);
                }
//            2019年5月10日 14:45:22 按照客户查询查不出数据
                if (searchParams.containsKey("IN_customer")) {                                                      //客户
                    String customerIdstr = (String) searchParams.get("IN_customer");
                    whereClause.append(" and c.CUSTOMER_ID in(:customerIds)");
                    params.put("customerIds", Arrays.asList(customerIdstr.split(",")));
                }
                if (searchParams.containsKey("EQ_productGroup")) {                                 //产品线
                    String productGroup = (String) searchParams.get("EQ_productGroup");
                    whereClause.append(" and c.product_line_id =:productGroup");
                    params.put("productGroup", productGroup);
                }
            }

			//增加促销活动的查询条件
			if (searchParams.containsKey("IN_activity")) {
				String activityIdstr = (String) searchParams.get("IN_activity");
				whereClause.append(" and c.ACTIVITY_ID in(:activityIds)");
				params.put("activityIds", Arrays.asList(activityIdstr.split(",")));
			}
			//增加促销活动的查询条件

            //如果是有带促销活动的参数则不校验数据权限
            if (!searchParams.containsKey("IN_activity")) {
                //拼接数据权限过滤条件
                String userId = CommonUtils.getCurrentUserId();
                ResponseEntity<Map<String, Set<String>>> orgAuthParams = dataPermissionApi.getAuthParamByApp("creditoccupy", userId);
                if (HeaderUtil.hasError(orgAuthParams)) {
                    logger.error(URLDecoder.decode(orgAuthParams.getHeaders().get("X-OCC-message").get(0)));
                } else {
                    Map<String, Set<String>> orgAuthParamsData = orgAuthParams.getBody();
                    if (flag == 0 && orgAuthParams.getBody().size() != 0) {
                        jpql.append(" join cr_credit_ctrl_strategy str on c.credit_ctrl_strategy_id = str.id ");
                        jpqlCount.append(" join cr_credit_ctrl_strategy str on c.credit_ctrl_strategy_id = str.id ");
                    }
                    if (orgAuthParamsData.containsKey("creditCtrlStrategy.organization")) {
                        whereClause.append(" and str.pk_org in :orgAuth");
                        params.put("orgAuth", orgAuthParamsData.get("creditCtrlStrategy.organization"));
                    }
                    if (orgAuthParamsData.containsKey("creditCtrlStrategy.saleOrg")) {
                        whereClause.append(" and str.sale_org_id in :saleOrgAuth");
                        params.put("saleOrgAuth", orgAuthParamsData.get("creditCtrlStrategy.saleOrg"));
                    }
                    if (orgAuthParamsData.containsKey("customer")) {
                        whereClause.append(" and c.customer_id in :customerAuth");
                        params.put("customerAuth", orgAuthParamsData.get("customer"));
                    }

                }
            }

            params.put("sysdate", new Date());
            jpql.append(whereClause);
            jpqlCount.append(whereClause);

        }

        //执行查询
        Query query = entityManager.createNativeQuery(jpql.toString(), CreditLimitExt.class);
        Query queryCount = entityManager.createNativeQuery(jpqlCount.toString(), CreditLimitExt.class);
        //设置分页查询参数
        query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
        query.setMaxResults(pageable.getPageSize());
        this.setParameters(query, params);
        this.setParameters(queryCount, params);
        entityList = query.getResultList();
        count = queryCount.getResultList().size();

        if (CollectionUtils.isEmpty(entityList)) {
            return new PageImpl<>(new ArrayList<>(0), pageable, count);
        }
        Set<String> ids = new HashSet<>();
        for (int i = 0; i < entityList.size(); i++) {
            ids.add(entityList.get(i).getCreditCtrlStrategy().getId());
        }
        List<CreditCtrlStrategyDto> strategyList = strategyService.findByIdIn(ids.toArray(new String[0]));
        if (strategyList == null || strategyList.size() <= 0) {
            throw new BusinessException("没有找到对应的信用控制策略");
        }
        List<CreditLimitDto> dtoList = this.entitiesToDtos(entityList);
        //封装map拆分双重循环
        Map<String, CreditCtrlStrategyDto> map = new HashMap<>();
        for (CreditCtrlStrategyDto ctrlStrategyDto : strategyList) {
            map.put(ctrlStrategyDto.getId(), ctrlStrategyDto);
        }
        CreditCtrlStrategyDto dto = new CreditCtrlStrategyDto();
        for (int i = 0; i < dtoList.size(); i++) {
            dto = map.get(dtoList.get(i).getCreditCtrlStrategyId());
            if (dto != null) {
                dtoList.get(i).setOrganizationId(dto.getOrganizationId());
                dtoList.get(i).setOrganizationCode(dto.getOrganizationCode());
                dtoList.get(i).setOrganizationName(dto.getOrganizationName());
                dtoList.get(i).setSaleOrgId(dto.getSaleOrgId());
                dtoList.get(i).setSaleOrgCode(dto.getSaleOrgCode());
                dtoList.get(i).setSaleOrgName(dto.getSaleOrgName());
            }
        }
        dtoList = queryLimit(dtoList, recalculation, (String) searchParams.get("EQ_prefixKey"));
        Page<CreditLimitDto> page = new PageImpl<>(dtoList, pageable, count);
        return page;
    }

    /**
     * 此处直接复制CreditBalanceService.setParameters源码出来修改 modify source
     */
    private void setParameters(Query query, Map<String,Object> params){
        for(Map.Entry<String,Object> entry:params.entrySet()){
            query.setParameter(entry.getKey(),entry.getValue());
        }
    }

	/**
	 *  根据参数查询信用有效期范围内的信用信息，只查询前1000条数据
	 * @param:
	 * @param customerId 客户主键
	 * @param activityIds 促销活动主键
	 * @return: key=促销活动主键 value=信用实体
	 */
	public Map<String, CreditLimitExtDto> findCreditBalance(String customerId, String[] activityIds) {
		Assert.notNull(customerId, "查询信用客户主键不能为空");
		if (ArrayUtils.isEmpty(activityIds)) {
			throw new BusinessException("查询信用活动主键不能为空");
		}
		if (activityIds.length > 50) {
			throw new BusinessException("查询信用活动个数不能超过50");
		}
		//只查询1000条
		Pageable pageable = new PageRequest(0, 1000);
		Map<String, Object> searchParams = new HashMap<>();
		//客户主键条件
		searchParams.put("IN_customer", customerId);
		//活动主键条件
		searchParams.put("IN_activity", StringUtils.join(activityIds,","));
		Page<CreditLimitDto> results = super.findAll(searchParams, pageable);
		List<CreditLimitDto> creditLimitDtos = results.getContent();
		Map<String, CreditLimitExtDto> creditLimitMap = new HashMap<>();
		if (CollectionUtils.isEmpty(creditLimitDtos)) {
			return creditLimitMap;
		}
		for (CreditLimitDto creditLimitDto : creditLimitDtos) {
			CreditLimitExtDto creditLimitExtDto = (CreditLimitExtDto)creditLimitDto;
			String activity = creditLimitExtDto.getActivity();
			if (StringUtils.isNotBlank(activity)) {
				if (creditLimitMap.containsKey(activity)) {
					throw new BusinessException("存在客户【"+creditLimitExtDto.getCustomerName()+"】+活动【"+
							creditLimitExtDto.getActivityName()+"】有效期范围内的多条信用额度，请检查");
				}
				creditLimitMap.put(activity,creditLimitExtDto);
			}
		}
		return creditLimitMap;
	}
}
这篇关于javax.persistence.Query和javax.persistence.EntityManager的分页查询使用的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
Java教程

javax.persistence.Query和javax.persistence.EntityManager的分页查询使用

前端开发

后端开发

移动端开发

数据库

服务器运维

人工智能

区块链

游戏开发

网站运营

大数据/云计算

软件工程

软件/开发工具使用

资讯
