江陵：hibernate分页查询

 public PageBean<VideoRmpPatrolJob> getPatrolJobPage(VideoRmpPatrolJobDTO dto, List<Integer> regionIDs, List<Map<String, Date>> dateMapList, Integer userId) { ???????Integer pageNo = dto.getPageNo(); ???????Integer pageSize = dto.getPageSize(); ???????if (pageNo == 0) { ???????????pageNo = 1; ???????} ???????String listString = "("; ???????if (regionIDs != null && regionIDs.size() > 0) { ???????????for (Integer regionID : ???????????????????regionIDs) { ???????????????listString += regionID + ","; ???????????} ???????????listString = listString.substring(0, listString.length() - 1) + ")"; ???????} else { ???????????listString = ""; ???????} ???????StringBuffer querySql = new StringBuffer(); ???????querySql.append("SELECT\n" + ???????????????"\tjob.*\n" + ???????????????"FROM\n" + ???????????????"\tvideo_rmp_patrol_job job\n" + ???????????????"LEFT JOIN s_region s ON job.region_id = s.region_id\n" + ???????????????"WHERE "); ???????for (int i = 0; i < dateMapList.size(); i++) { ???????????if (i > 0) { ???????????????querySql.append(" OR ("); ???????????} ???????????querySql.append("\t1 = 1\n" + ???????????????????"\t\t\tAND job.circle_start_date >= ‘" + ??RmpDateTimeUtil.dateToString( dateMapList.get(i).get(RmpReportConstant.START_TIME),RmpDateTimeUtil.DATA_FORMAT_yyyy_MM_dd_HH_mm_ss) + "‘\n" + ???????????????????"\t\t\tAND job.circle_start_date <= ‘" + RmpDateTimeUtil.dateToString( dateMapList.get(i).get(RmpReportConstant.END_TIME),RmpDateTimeUtil.DATA_FORMAT_yyyy_MM_dd_HH_mm_ss) + "‘\n" + ???????????????????"\t\t\tAND s.region_level = 0\n" + ???????????????????" and job.is_delete =0 "); ???????????if(userId!=null && userId!=4){ ???????????????querySql.append(" and job.patrol_person_id = "+userId); ???????????} ???????????if (StringUtils.isNotBlank(dto.getStoreName())) { ???????????????querySql.append(" ??and s.name like ‘%" + dto.getStoreName() + "%‘ ESCAPE ‘/‘ "); ???????????} ???????????querySql.append("\t\t\tAND job.region_id IN " + listString + " ?" + ???????????????????" ?"); ???????????if (i > 0) { ???????????????querySql.append("\t) "); ???????????} ???????????querySql.append(" or(" + ???????????????????"1=1" + ???????????????????"\t\t\tAND job.circle_end_date >= ‘" + RmpDateTimeUtil.dateToString( dateMapList.get(i).get(RmpReportConstant.START_TIME),RmpDateTimeUtil.DATA_FORMAT_yyyy_MM_dd_HH_mm_ss) + "‘\n" + ???????????????????"\t\t\tAND job.circle_end_date <= ‘" + RmpDateTimeUtil.dateToString( dateMapList.get(i).get(RmpReportConstant.END_TIME),RmpDateTimeUtil.DATA_FORMAT_yyyy_MM_dd_HH_mm_ss) + "‘\n" + ???????????????????"\t\t\tAND s.region_level = 0\n" + ???????????????????" and job.is_delete =0 "); ???????????if(userId!=null && userId!=4){ ???????????????querySql.append(" and job.patrol_person_id = "+userId); ???????????} ???????????if (StringUtils.isNotBlank(dto.getStoreName())) { ???????????????querySql.append(" ?and s.name like ‘%" + dto.getStoreName() + "%‘ ESCAPE ‘/‘ "); ???????????} ???????????querySql.append("\t\t\tAND job.region_id IN " + listString + " ?" + ???????????????????" ) "); ???????} ???????querySql.append(" order BY " + ???????????????" circle_start_date LIMIT " + pageSize + " OFFSET " + (pageNo - 1) * pageSize); ???????final String sql = querySql.toString(); ???????List<Object[]> list = (List<Object[]>) this.getHibernateTemplate().execute(new HibernateCallback() { ???????????@Override ???????????public Object doInHibernate(Session session) throws HibernateException, SQLException { ???????????????SQLQuery query = session.createSQLQuery(sql); ???????????????return query.list(); ???????????} ???????}); ???????ArrayList<VideoRmpPatrolJob> jobs = new ArrayList<>(); ???????if (list != null && list.size() > 0) { ???????????for (Object[] o : list) { ???????????????VideoRmpPatrolJob job = new VideoRmpPatrolJob(); ???????????????job.setId((Integer) o[0]); ???????????????job.setPatrolJobName((String) o[1]); ???????????????job.setPlanId((Integer) o[2]); ???????????????job.setCircleStartDate((Date) o[3]); ???????????????job.setCircleEndDate((Date) o[4]); ???????????????job.setPatrolPersonId((Integer) o[5]); ???????????????job.setPatrolPersonName((String) o[6]); ???????????????job.setStoreId((Integer) o[7]); ???????????????job.setRegionId((Integer) o[8]); ???????????????job.setStatus((Integer) o[9]); ???????????????job.setCreateTime((Timestamp) o[10]); ???????????????job.setUpdateTime((Timestamp) o[11]); ???????????????job.setRemark((String) o[12]); ???????????????job.setIsDelete((Integer) o[13]); ???????????????jobs.add(job); ???????????} ???????} ???????//计算总数 ???????StringBuffer stringBuffer2 = new StringBuffer(); ???????stringBuffer2.append("SELECT\n" + ???????????????" count(*) " + ???????????????"FROM\n" + ???????????????"\tvideo_rmp_patrol_job job\n" + ???????????????"LEFT JOIN s_region s ON job.region_id = s.region_id\n" + ???????????????"WHERE "); ???????for (int i = 0; i < dateMapList.size(); i++) { ???????????if (i > 0) { ???????????????stringBuffer2.append(" OR ("); ???????????} ???????????stringBuffer2.append("\t1 = 1\n" + ???????????????????"\t\t\tAND job.circle_start_date >= ‘" + RmpDateTimeUtil.dateToString( dateMapList.get(i).get(RmpReportConstant.START_TIME),RmpDateTimeUtil.DATA_FORMAT_yyyy_MM_dd_HH_mm_ss) + "‘\n" + ???????????????????"\t\t\tAND job.circle_start_date <= ‘" + RmpDateTimeUtil.dateToString( dateMapList.get(i).get(RmpReportConstant.END_TIME),RmpDateTimeUtil.DATA_FORMAT_yyyy_MM_dd_HH_mm_ss) + "‘\n" + ???????????????????"\t\t\tAND s.region_level = 0\n" + ???????????????????" and job.is_delete =0 "); ???????????if(userId!=null && userId!=4){ ???????????????stringBuffer2.append(" and job.patrol_person_id = "+userId); ???????????} ???????????if (StringUtils.isNotBlank(dto.getStoreName())) { ???????????????stringBuffer2.append(" ?and s.name like ‘%" + dto.getStoreName() + "%‘ ESCAPE ‘/‘ "); ???????????} ???????????stringBuffer2.append("\t\t\tAND job.region_id IN " + listString + " ?" + ???????????????????" "); ???????????if (i > 0) { ???????????????stringBuffer2.append("\t) "); ???????????} ???????????stringBuffer2.append(" or(" + ???????????????????"1=1" + ???????????????????"\t\t\tAND job.circle_end_date >= ‘" + RmpDateTimeUtil.dateToString( dateMapList.get(i).get(RmpReportConstant.START_TIME),RmpDateTimeUtil.DATA_FORMAT_yyyy_MM_dd_HH_mm_ss) + "‘\n" + ???????????????????"\t\t\tAND job.circle_end_date <= ‘" + RmpDateTimeUtil.dateToString( dateMapList.get(i).get(RmpReportConstant.END_TIME),RmpDateTimeUtil.DATA_FORMAT_yyyy_MM_dd_HH_mm_ss) + "‘\n" + ???????????????????"\t\t\tAND s.region_level = 0\n" + ???????????????????" and job.is_delete =0 "); ???????????if(userId!=null && userId!=4){ ???????????????stringBuffer2.append(" and job.patrol_person_id = "+userId); ???????????} ???????????if (StringUtils.isNotBlank(dto.getStoreName())) { ???????????????stringBuffer2.append(" ?and s.name like ‘%" + dto.getStoreName() + "%‘ ESCAPE ‘/‘ "); ???????????} ???????????stringBuffer2.append("\t\t\tAND job.region_id IN " + listString + " ?" + ???????????????????" ) "); ???????} ???????final String sql2 = stringBuffer2.toString(); ???????Integer count = (Integer) this.getHibernateTemplate().execute(new HibernateCallback() { ???????????@Override ???????????public Object doInHibernate(Session session) throws HibernateException, SQLException { ???????????????SQLQuery query = session.createSQLQuery(sql2); ???????????????return Integer.valueOf(query.uniqueResult().toString()); ???????????} ???????}); ???????PageBean<VideoRmpPatrolJob> page = new PageBean<>(count, pageNo, pageSize, jobs); ???????return page; ???} ?