bosent---多表查询

 public static Map<String, Object> queryRecruitmentPlanList(DispatchContext dctx, Map<String, Object> context) throws BusinessException {
      <span style="white-space:pre"></span>

<span style="white-space:pre">	</span>GenericDelegator delegator = dctx.getDelegator();<span style="color: rgb(51, 51, 255); ">//取得实体引擎实例</span>
        List<Map<String,Object>> recruitmentList = FastList.newInstance();
        EntityListIterator eli = null;<span style="color: rgb(63, 127, 95); ">//</span><span style="color:#3F7F5F;">实体集合迭代器</span><span style="color:#3F7F5F;">, </span><span style="color:#3F7F5F;">用来操作数据的</span><span style="color:#3F7F5F;">.</span><span style="color:#3F7F5F;">和数据库中的结果集功能类似</span>
        Map<String, Object> returnMap = ReturnMapUtil.getSuccessMap();
        
        String recruitmentMonth = StringUtils.objectToString(context.get("recruitmentMonth"));
        String recruitmentType = StringUtils.objectToString(context.get("recruitmentType"));
        String statusId = StringUtils.objectToString(context.get("statusId"));
        String companyId = StringUtils.objectToString(context.get("companyId"));
        String departmentId = StringUtils.objectToString(context.get("departmentId"));
        String position = StringUtils.objectToString(context.get("position"));
        
        /** 建立动态视图 */
        DynamicViewEntity view = new DynamicViewEntity();
        view.addMemberEntity("RP","RecruitmentPlan");
        view.addAlias("RP","recPlanId");
        view.addAlias("RP","recPlanNo");
        view.addAlias("RP","companyId");
        
        view.addMemberEntity("R", "Recruitment");
        view.addAlias("R","recruitmentId");
        view.addAlias("R","recPlanId");
        view.addAlias("R","reportDutyDate");
        view.addAlias("R","departmentId");
        view.addAlias("R","position");
        view.addAlias("R","recruitmentNum");
        view.addAlias("R","probationerNum");
        view.addAlias("R","salaryPoint");
        view.addAlias("R","statusId");
        view.addAlias("R","recruitmentType");
     <span style="background-color: rgb(51, 204, 0);">   <span style="color:#ffffff;">//把两张表连接起来</span></span>
        view.addViewLink("RP","R", 
                Boolean.TRUE, UtilMisc.toList(new ModelKeyMap("recPlanId", "recPlanId")));
        List<EntityExpr> conditionList = new FastList<EntityExpr>();
        if(UtilValidate.isEmpty(recruitmentMonth)){
            throw new BusinessException("计划月份不可以为空");
        }
        if(UtilValidate.isEmpty(companyId)){
            throw new BusinessException("公司id不可以为空");
        }
        conditionList.add(EntityCondition.makeCondition("recPlanNo", EntityOperator.EQUALS,recruitmentMonth));
        conditionList.add(EntityCondition.makeCondition("companyId", EntityOperator.EQUALS,companyId));
        if(UtilValidate.isNotEmpty(recruitmentType)){
            conditionList.add(EntityCondition.makeCondition("recruitmentType", EntityOperator.EQUALS,recruitmentType));
        }
        if(UtilValidate.isNotEmpty(statusId)){
            conditionList.add(EntityCondition.makeCondition("statusId", EntityOperator.EQUALS,statusId));
        }

        if(UtilValidate.isNotEmpty(departmentId)){
            conditionList.add(EntityCondition.makeCondition("departmentId", 
                    EntityOperator.EQUALS,departmentId));
        }
        if(UtilValidate.isNotEmpty(position)){
            conditionList.add(EntityCondition.makeCondition("position", 
                    EntityOperator.EQUALS,position));
        }

<span style="white-space:pre">	</span><span style="color:#3366ff;">//取得查询条件</span>
        EntityCondition whereEntityCondition = EntityCondition.makeCondition(conditionList,EntityOperator.AND);
        List<String> fieldsToSelect = FastList.newInstance();
        fieldsToSelect.add("recPlanId");
        fieldsToSelect.add("recPlanNo");
        fieldsToSelect.add("companyId");
        fieldsToSelect.add("recruitmentId");
        fieldsToSelect.add("reportDutyDate");
        fieldsToSelect.add("departmentId");
        fieldsToSelect.add("position");
        fieldsToSelect.add("recruitmentNum");
        fieldsToSelect.add("probationerNum");
        fieldsToSelect.add("salaryPoint");
        fieldsToSelect.add("statusId");
        fieldsToSelect.add("recruitmentType");
        
        try {

<span style="color:#ff0000;">//delegator.findListIteratorByCondition(dynamicViewEntity, whereEntityCondition, havingEntityCondition, fieldsToSelect, orderBy, findOptions)</span>
            eli = delegator.findListIteratorByCondition(view,whereEntityCondition, null, fieldsToSelect, null, null);
            GenericValue genericValue = null;
            while((genericValue = eli.next()) != null){
                Map<String,Object> recruitmentMap = FastMap.newInstance();
                recruitmentMap.put("recruitmentId", StringUtils.objectToString(genericValue.get("recruitmentId")));
                recruitmentMap.put("recruitmentMonth", StringUtils.objectToString(genericValue.get("recPlanNo")));
                recruitmentMap.put("reportDutyDate", StringUtils.objectToString(genericValue.get("reportDutyDate")));
                recruitmentMap.put("departmentId", StringUtils.objectToString(genericValue.get("departmentId")));
                recruitmentMap.put("position", StringUtils.objectToString(genericValue.get("position")));
                recruitmentMap.put("recruitmentNum", StringUtils.objectToInteger(genericValue.get("recruitmentNum")));
                recruitmentMap.put("probationerNum", StringUtils.objectToInteger(genericValue.get("probationerNum")));
                recruitmentMap.put("salaryBudget", StringUtils.objectToBigDecimal(genericValue.get("salaryPoint")));
                recruitmentMap.put("recruitmentType", StringUtils.objectToString(genericValue.get("recruitmentType")));
                recruitmentMap.put("statusId", StringUtils.objectToString(genericValue.get("statusId")));

<span style="white-space:pre">		</span><span style="color:#3333ff;">//Date处理有别于String和Integer,得到日期后，应先判断日期是否为空，如果不空再对其进行格式转换，如果为空，则为其置空。</span>
              <span style="color:#ff0000;">  Object reportDutyDate = genericValue.get("reportDutyDate");
                if(UtilValidate.isNotEmpty(reportDutyDate)){
                    recruitmentMap.put("reportDutyDate",DateUtil.toFormatString(reportDutyDate, ConstantBusiness.YYYYMMDD));
                }else {
                    recruitmentMap.put("reportDutyDate","");
                }</span>
                
                recruitmentList.add(recruitmentMap);
            }
        } catch (GenericEntityException e) {
            Debug.logError(e,module);
            throw new BusinessException("招聘计划列表查询失败");
        }finally {

<span style="white-space:pre"></span><div><p align="left"> <span style="white-space:pre">		</span><span style="color:#3F7F5F;">// </span><span style="color:#3F7F5F;">关闭迭代器</span><span style="color:#3F7F5F;">(</span><span style="color:#3F7F5F;">一定要记得关闭哦</span><span style="color:#3F7F5F;">,</span><span style="color:#3F7F5F;">不然就死定了</span><span style="color:#3F7F5F;">)</span></p></div>            if (UtilValidate.isNotEmpty(eli)) {
                try {
                    eli.close();
                } catch (GenericEntityException e) {
                    Debug.logError(e,module);
                    throw new BusinessException("招聘计划列表查询失败");
                }                                           
            }
        }
        
        /**统计信息查询*/
        String helperName = StringUtils.objectToString(SqlLockUtil.getHelperName(delegator));
        
        SQLProcessor sqlp = new SQLProcessor(helperName);
        ResultSet rs = null;
        try {
            StringBuffer sb = new StringBuffer();
            sb.append(" select count(DISTINCT DEPARTMENT_ID) depNum ,count(DISTINCT POSITION) posNum ,sum(number) total ");
            sb.append(" from ( ");
            sb.append(" select rp.COMPANY_ID,r.DEPARTMENT_ID,r.POSITION,sum(r.RECRUITMENT_NUM) number from recruitment_plan rp ,recruitment r ");
            sb.append(" where rp.REC_PLAN_ID = r.REC_PLAN_ID and rp.REC_PLAN_NO=? and rp.COMPANY_ID=? ");
            

            if(UtilValidate.isNotEmpty(recruitmentType)){
                sb.append(" and RECRUITMENT_TYPE = ? ");
            }
            if(UtilValidate.isNotEmpty(statusId)){
                sb.append(" and STATUS_ID = ? ");
            }

            if(UtilValidate.isNotEmpty(departmentId)){
                sb.append(" and DEPARTMENT_ID = ? ");
            }
            if(UtilValidate.isNotEmpty(position)){
                sb.append(" and POSITION = ? ");
            }
            sb.append(" group by r.DEPARTMENT_ID,r.POSITION ");
            sb.append(" ) c");
            
            sqlp.prepareStatement(sb.toString());
            sqlp.setValue(recruitmentMonth);
            sqlp.setValue(companyId);
            if(UtilValidate.isNotEmpty(recruitmentType)){
                sqlp.setValue(recruitmentType);
            }
            if(UtilValidate.isNotEmpty(statusId)){
                sqlp.setValue(statusId);
            }

            if(UtilValidate.isNotEmpty(departmentId)){
                sqlp.setValue(departmentId);
            }
            if(UtilValidate.isNotEmpty(position)){
                sqlp.setValue(position);
            }
            rs = sqlp.executeQuery();
            Integer depNum =null;
            Integer posNum =null;
            Integer total =null;
            if (rs.next()) {
                 depNum = rs.getInt("depNum");
                 posNum = rs.getInt("posNum");
                 total = rs.getInt("total");
                 returnMap.put("depNum", depNum);
                 returnMap.put("posNum", posNum);
                 returnMap.put("total", total);
                 returnMap.put("companyId", companyId);
                 returnMap.put("recruitmentMonth", recruitmentMonth);
            }

        } catch (GenericDataSourceException e) {
            Debug.logError(e.getMessage(), module);
            throw new BusinessException(ErrorCodeCommon.INV612L01009);
        } catch (GenericEntityException e) {
            Debug.logError(e.getMessage(), module);
            throw new BusinessException(ErrorCodeCommon.INV612L01009);
        } catch (SQLException e) {
            Debug.logError(e.getMessage(), module);
            throw new BusinessException(ErrorCodeCommon.INV612L01009);
        }finally {
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    Debug.logError(e.getMessage(), module);
                    throw new BusinessException(ErrorCodeCMS.CMS612F02184);
                }
            }
        }
        returnMap.put("recruitmentList", recruitmentList);
        return returnMap;
    }