jeecg datagraid 分页查询速度优化备份

@RequestMapping(params = "datagridsbgf")
    public void datagridsbgf(LdcCommonStateEntity ldcCommonState, HttpServletRequest request,
            HttpServletResponse response, DataGrid dataGrid) {

        CriteriaQuery cq = new CriteriaQuery(LdcCommonStateEntity.class, dataGrid);
        //查询条件组装器
        org.jeecgframework.core.extend.hqlsearch.HqlGenerateUtil.installHql(cq, ldcCommonState, request.getParameterMap());
        cq.add();

        //分页
        int row = dataGrid.getRows();
        int page = dataGrid.getPage();
        int firstRow = row*(page-1);
        int endrow=row+firstRow;

        StringBuilder where = new StringBuilder();
        String sysOrgCode = request.getParameter("sysOrgCode");
        if(StringUtils.isNotEmpty(sysOrgCode)) {
            where.append(" and d.sys_Org_Code like '"+sysOrgCode+"%'");
        }

        // 当核注清单回执和数据订阅回执会插入大量数据到state_comment字段中，这边过滤state_operator !='系统'不然会报错
        where.append(" and state_operator !='系统' ");

        TSUser user = ResourceUtil.getSessionUser();

        //2.获取用户的角色
        List<TSRoleUser> rUsers1 = systemService.findByProperty(TSRoleUser.class, "TSUser.id", user.getId());
        boolean isture=true;
        for (TSRoleUser ru : rUsers1) {
            System.out.println(ru.getTSRole().getRoleName());
            if(ru.getTSRole().getRoleName().equals("通富管理员")||ru.getTSRole().getRoleName().equals("管理员")){
                isture=false;
            }
        }
        if(isture){
        String orgcode=jdbcTemplate.queryForObject(" select  ORG_CODE from T_S_DEPART where ID=?", String.class,user.getDepartid())    ;
        where.append(" and d.sys_Org_Code like '"+orgcode+"%'");
        }






        // 报关单号
        String entryid1 = request.getParameter("entryid1");
        if(StringUtils.isNotEmpty(entryid1)) {
            where.append(" and entry_id like '%"+entryid1+"%'");
        }
        // 业务编号
        String clientno = request.getParameter("clientno");
        if(StringUtils.isNotEmpty(clientno)) {
            where.append(" and d.client_no like '%"+clientno+"%'");
        }

        // 申报日期开始
        String declarationDataBegin = request.getParameter("declarationData_begin");
        // 申报日期结束
        String declarationDataEnd = request.getParameter("declarationData_end");

        if(StringUtils.isNotEmpty(declarationDataBegin)) {
            where.append(" and d.declaration_data >= '"+declarationDataBegin+"'");
        }
        if(StringUtils.isNotEmpty(declarationDataEnd)) {
            StringBuffer plsb = new StringBuffer(declarationDataEnd);
            plsb.append(" 23:59:59");
            where.append(" and d.declaration_data <= '"+plsb.toString()+"'");
        }

        // 进口/出口
        String ieFlag = request.getParameter("ieFlag");
        if(StringUtils.isNotEmpty(ieFlag)) {
            where.append(" and d.ieFlag like '%"+ieFlag+"%'");
        }

        // 操作
        String state = request.getParameter("state");
        if(StringUtils.isNotEmpty(state)) {
            where.append(" and to_char(state_comment) like '%"+state+"%'");
        }else{
            where.append(" and (to_char(state_comment) like '%审核通过%'  or to_char(state_comment) like '%审核不通过%')   ");
        }

        /*String sql ="SELECT d.id,entry_id as entryId,d.client_no as clientNo,d.bill_no as billNo,d.voy_no as voyNo,d.declaration_data as declarationData,"
                + "d.ieFlag,lcs.state_pname as statePname,lcs.state_comment as stateComment,lcs.state_operator as stateOperator,lcs.state_time as stateTime,lcs.state_pid as statePid "
                + " FROM ldc_common_state lcs LEFT JOIN dec_main d ON d.id = lcs.state_fid  "
                + " where d.id is not null and lcs.state_pid is not null "+where.toString()+" and rownum>= "+firstRow+" and rownum<="+firstRow+row;
*/
        String sql="SELECT  sysOrgCode,ID, entryId, clientNo, billNo, voyNo, declarationData, ieFlag, statePname, stateComment, stateOperator, stateTime, statePid, rn from( SELECT D.sys_Org_Code  as sysOrgCode,D.ID, entry_id AS entryId, D.client_no AS clientNo, D.bill_no AS billNo, D.voy_no AS voyNo, D.declaration_data AS declarationData, D.ieFlag, lcs.state_pname AS statePname, lcs.state_comment AS stateComment, lcs.state_operator AS stateOperator, lcs.state_time AS stateTime, lcs.state_pid AS statePid, rownum rn FROM ldc_common_state lcs LEFT JOIN dec_main D ON D.ID = lcs.state_fid WHERE D.ID IS NOT NULL AND lcs.state_pid IS NOT NULL "
                +where.toString()+"and rownum"
                + " <= "+endrow+"   ) where rn > "+firstRow;

        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);

        // 节点名通过state_pid取重新查找，进度情况包含操作和备注，需要拆分
        for (Map map2 : list) {
            // 节点名
            String pid = (String) map2.get("statePid");
            if(StringUtils.isNotBlank(pid)) {
                String num = "Select count(*) from ldc_common_state where id = '"+pid+"' ";
                num = jdbcTemplate.queryForObject(num, String.class);
                if("1".equals(num)) {
                    String statePname = "Select state_pname as statePname from ldc_common_state where id = '"+pid+"' ";
                    statePname = jdbcTemplate.queryForObject(statePname, String.class);
                    map2.put("statePname", statePname);
                }
            }
            // 操作和备注+++++状态放在第一行，审核不通过为Y其他都为N
            String stateComment = (String) map2.get("stateComment");
            if(StringUtils.isNotBlank(stateComment)) {
                if(stateComment.contains("_")) {
                    String[] stateComments = stateComment.split("_");
                    // 获取第一个_之前的字符串
                    map2.put("state", stateComments[0]);
                    // 获取第一个_之后的字符串
                    map2.put("comment", stateComment.substring(stateComment.indexOf("_")+1));
                    if(stateComments[0].equals("审核不通过")) {
                        map2.put("zhuangtai", "N");
                    }else {
                        map2.put("zhuangtai", "Y");
                    }
                }else {
                    map2.put("state", stateComment);
                    if(stateComment.equals("审核不通过")) {
                        map2.put("zhuangtai", "N");
                    }else {
                        map2.put("zhuangtai", "Y");
                    }
                }
            }
        }

        String sql1 = "SELECT count(*) "
                + " FROM ldc_common_state lcs LEFT JOIN dec_main d ON d.id = lcs.state_fid  "
                + " where d.id is not null and lcs.state_pid is not null "+where.toString();

        int count= jdbcTemplate.queryForObject(sql1, Integer.class);
        dataGrid.setTotal(count);
        dataGrid.setResults(list);
        TagUtil.datagrid(response, dataGrid);
    }