hibernate 分页步骤
hibernate 分页方法
public List<DoubtMissForeCastBean> findMissPageByParam(final QueryParam queryParam,String [] deptValue,int deptLevel)throws Exception {
final List<Object> paramList=new ArrayList<Object>();
StringBuffer sb = new StringBuffer();
String sql = SQLProvider.getSQL("queryMissForecast");
sb.append(sql);
final String sqlString=generateSQL(queryParam,paramList,deptValue,deptLevel,sb);
return (List<DoubtMissForeCastBean>) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException {
Query query = session.createSQLQuery(sqlString);
query.setFirstResult(queryParam.getPageSize()*(queryParam.getPageIndex()));
query.setMaxResults(queryParam.getPageSize());
if (paramList != null && paramList.size() > 0) {
Object[] values = paramList.toArray();
for (int i = 0; i < values.length; i++) {
query.setParameter(i, values[i]);
}
}
return findDoubtMissPage(query.list());
}
});
}
public String generateSQL(QueryParam queryParam,List<Object> paramList,String [] deptValue,int deptLevel,StringBuffer sb)throws Exception {
String queryValue = null;
//最后操作网点
if(!BlankUtil.isBlank(deptValue)){
switch(deptLevel){
case 1:sb.append(" and t.hq_code in "); break;
case 2:sb.append(" and t.area_code in ");break;
case 3:sb.append(" and t.dept_code in ");break;
case 4:sb.append(" and t.dist_code in ");break;
}
if(deptLevel!=0){
sb.append("(");
for(String deptCode:deptValue){
sb.append("?,");
paramList.add(deptCode);
}
sb.replace(sb.toString().length()-1, sb.toString().length(),"");
sb.append(")");
}
}
//开始日期
queryValue = queryParam.getQueryValue("query_startDate");
if (StringUtils.isNotEmpty(queryValue)) {
sb.append(" and m.doubt_miss_tm >=TO_DATE(?,'yyyy-mm-dd hh24:mi:ss')");
paramList.add(queryValue+" 00:00:00");
}
//结束日期
queryValue = queryParam.getQueryValue("query_endDate");
if (StringUtils.isNotEmpty(queryValue)) {
sb.append(" and m.doubt_miss_tm <=TO_DATE(?,'yyyy-mm-dd hh24:mi:ss')");
paramList.add(queryValue+" 23:59:59");
}
sb.append(")A WHERE countnum = RW ");
//最后操作班次
queryValue = queryParam.getQueryValue("query_lastOperateBatch");
if (StringUtils.isNotEmpty(queryValue)) {
sb.append(" AND A.OPERATE_BATCH_CODE = ? ");
paramList.add(queryValue);
}
sb.append(" ORDER BY DOUBT_MISS_TM ASC ");
return sb.toString();
}
public List<DoubtMissForeCastBean> findMissPageByParam(final QueryParam queryParam,String [] deptValue,int deptLevel)throws Exception {
final List<Object> paramList=new ArrayList<Object>();
StringBuffer sb = new StringBuffer();
String sql = SQLProvider.getSQL("queryMissForecast");
sb.append(sql);
final String sqlString=generateSQL(queryParam,paramList,deptValue,deptLevel,sb);
return (List<DoubtMissForeCastBean>) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException {
Query query = session.createSQLQuery(sqlString);
query.setFirstResult(queryParam.getPageSize()*(queryParam.getPageIndex()));
query.setMaxResults(queryParam.getPageSize());
if (paramList != null && paramList.size() > 0) {
Object[] values = paramList.toArray();
for (int i = 0; i < values.length; i++) {
query.setParameter(i, values[i]);
}
}
return findDoubtMissPage(query.list());
}
});
}
public String generateSQL(QueryParam queryParam,List<Object> paramList,String [] deptValue,int deptLevel,StringBuffer sb)throws Exception {
String queryValue = null;
//最后操作网点
if(!BlankUtil.isBlank(deptValue)){
switch(deptLevel){
case 1:sb.append(" and t.hq_code in "); break;
case 2:sb.append(" and t.area_code in ");break;
case 3:sb.append(" and t.dept_code in ");break;
case 4:sb.append(" and t.dist_code in ");break;
}
if(deptLevel!=0){
sb.append("(");
for(String deptCode:deptValue){
sb.append("?,");
paramList.add(deptCode);
}
sb.replace(sb.toString().length()-1, sb.toString().length(),"");
sb.append(")");
}
}
//开始日期
queryValue = queryParam.getQueryValue("query_startDate");
if (StringUtils.isNotEmpty(queryValue)) {
sb.append(" and m.doubt_miss_tm >=TO_DATE(?,'yyyy-mm-dd hh24:mi:ss')");
paramList.add(queryValue+" 00:00:00");
}
//结束日期
queryValue = queryParam.getQueryValue("query_endDate");
if (StringUtils.isNotEmpty(queryValue)) {
sb.append(" and m.doubt_miss_tm <=TO_DATE(?,'yyyy-mm-dd hh24:mi:ss')");
paramList.add(queryValue+" 23:59:59");
}
sb.append(")A WHERE countnum = RW ");
//最后操作班次
queryValue = queryParam.getQueryValue("query_lastOperateBatch");
if (StringUtils.isNotEmpty(queryValue)) {
sb.append(" AND A.OPERATE_BATCH_CODE = ? ");
paramList.add(queryValue);
}
sb.append(" ORDER BY DOUBT_MISS_TM ASC ");
return sb.toString();
}