dao

/ 更新Session
        Session session = super.getSession();
       
        // 初始化一个空列表
        List<DeptResInstalBO> objectBOList = new ArrayList();

        // 初始化一个查询总记录SQL语句的字符串
        StringBuffer queryAcountString = new StringBuffer(
                "SELECT COUNT(distinct a.DEPARTMENT_ID) FROM CLM_GFS_DEPARTMENTS a " +
                "LEFT JOIN CLM_GFS_DEPT_HOLDERS b ON a.DEPARTMENT_ID = b.DEPARTMENT_ID WHERE '1'='1' ");

        // 初始化一个查询的SQL语句的字符串
        StringBuffer queryString = new StringBuffer(
                "SELECT DEPARTMENT_NAME, DEPARTMENT_ID, wmsys.wm_concat(USERNAME) as USERNAME " +
                "FROM (SELECT a.DEPARTMENT_ID, a.DEPARTMENT_NAME, b.USERNAME " +
                       "FROM CLM_GFS_DEPARTMENTS a " +
                       "LEFT JOIN (SELECT DEPARTMENT_ID, b.LAST_NAME || b.FIRST_NAME AS USERNAME " +
                                  "FROM CLM_GFS_DEPT_HOLDERS a, PER_ALL_PEOPLE_F b " +
                                  "WHERE a.USER_ID = b.PERSON_ID) b ON a.DEPARTMENT_ID = b.DEPARTMENT_ID WHERE '1'='1' ");

        // 判断查询条件人员名称是否为空
        if (object.getPerson_name() != null
                && object.getPerson_name()!="")
        {
            queryAcountString = queryAcountString
                    .append(" AND b.USER_ID = :USER_ID");
            queryString = queryString.append("AND a.DEPARTMENT_ID IN (SELECT d.DEPARTMENT_ID " +
                                             "FROM CLM_GFS_DEPT_HOLDERS d " +
                                             "WHERE d.user_id = :USER_ID) ");
        }
       
        // 判断查询条件科室名称是否为空
        if (object.getDept_name() != null
                && object.getDept_name()!="" )
        {
            queryAcountString = queryAcountString
                    .append(" AND a.DEPARTMENT_NAME LIKE :DEPARTMENT_NAME");
            queryString = queryString
                    .append(" AND a.DEPARTMENT_NAME LIKE :DEPARTMENT_NAME");
        }
       
        queryString = queryString
                .append(") GROUP BY DEPARTMENT_NAME, DEPARTMENT_ID ORDER BY DEPARTMENT_ID DESC ");
       
        // 将queryAcountString中的属性放入Query集中
        Query qCount = session.createSQLQuery(queryAcountString.toString());

        // 将queryString中的属性放入Query集中
        Query q = session.createSQLQuery(queryString.toString());
       
        // 判断查询条件人员名称是否为空，给查询条件赋值
        if (object.getPerson_name() != null
                && object.getPerson_name()!="")
        {
            q.setParameter("USER_ID", object.getPerson_id());
            qCount.setParameter("USER_ID", object.getPerson_id());
        }
       
        // 判断查询条件科室名称是否为空，给查询条件赋值
        if (object.getDept_name() != null
                && object.getDept_name()!="")
        {
            q.setParameter("DEPARTMENT_NAME",  "%"+object.getDept_name()+"%");
            qCount.setParameter("DEPARTMENT_NAME", "%"+object.getDept_name()+"%");
        }
       
        // 设置记录总数
        object.getPageData().setTotalRow((Integer.parseInt(qCount.list().get(0)+"")));

        // 设置页面第一行记录
        q.setFirstResult((object.getPageData().getPageNum() - 1)
                * object.getPageData().getPageSize());

        // 设置每页记录的最大数
        q.setMaxResults(object.getPageData().getPageSize());
       
        // 存放q里的记录
        List<Object[]> objectList = q.list();
       
        // 遍历列表，将列表中的属性取出，最后赋给DeptResInstalBO对象
        for (int i = 0; i < objectList.size(); i++)
        {
           
            // 取出查询后的数据的科室名称
            String departmentName=objectList.get(i)[0]+"";
           
            // 取出查询后的数据的科室id
            Integer departmentId=Integer.parseInt(objectList.get(i)[1]+"");
           
            // 取出查询后的数据的姓名和id的组合字符串
            String userIdAndName=objectList.get(i)[2]+"";
           
            //判断userIdAndName是否null值
            if("null".equals(userIdAndName))
            {
                userIdAndName="";
            }
           
            // 科室信息实体
            GFSDeptsEntity deptsEntity=new GFSDeptsEntity();
           
            // 给科室信息实体赋值
            deptsEntity.setDepartment_id(departmentId);
            deptsEntity.setDepartment_name(departmentName);
           
            // DeptResInstalBO对象
            DeptResInstalBO deptResInstalBO=new DeptResInstalBO();
           
            // 科室信息实体赋给DeptResInstalBO对象
            deptResInstalBO.setDeptsEntity(deptsEntity);
           
            // 姓名和id的组合字符串赋给DeptResInstalBO对象
            deptResInstalBO.setUserIdAndName(userIdAndName);
           
            // 赋给objectBOList集合,返回数据给页面显示
            objectBOList.add(deptResInstalBO);
           
        }
       
        return objectBOList;