hibernate 分页方法

@Repository
public class BulkCargoDao extends BaseEntityDao<BulkCargo> implements IBulkCargoDao {

@Resource
private IZoneCodeInfoBiz zoneCodeInfoBiz;

public void setZoneCodeInfoBiz(IZoneCodeInfoBiz zoneCodeInfoBiz) {
this.zoneCodeInfoBiz = zoneCodeInfoBiz;
}

/**
* 查询监控信息
* @param queryParam
* @return
* @throws DaoException
*/
@Override
@SuppressWarnings("unchecked")
public List<BulkCargo> findBulkCargoByParam(final QueryParam queryParam)
throws Exception {
final List<Object> paramList=new ArrayList<Object>();
final String sqlString = generateSQL(queryParam,paramList);
return (List<BulkCargo>) 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 findBulkCargoResult(query.list(),queryParam);
}
});
}

/**
* 拼装Sql
* @param queryParam
* @param paramList
* @return
*/
private String generateSQL(QueryParam queryParam,List<Object> paramList)throws Exception{
StringBuffer sb = new StringBuffer();
String sql="";
sql = SQLProvider.getSQL("query_bulkCargo").trim();
sb.append(sql);
return this.setCommonQueryCondition(queryParam, paramList, sb);
}

/**
* 设置共有的查询条件
* @param queryParam
* @param paramList
* @param sb
* @return
* @throws Exception
*/
private String setCommonQueryCondition(QueryParam queryParam,List<Object> paramList,StringBuffer sb)throws Exception{
String queryValue="";
String commonSql="";
//开始日期
queryValue= queryParam.getQueryValue("query_startDate");
if(!BlankUtil.isBlank(queryValue)){
sb.append(" AND T.REPORT_DT>=to_date(?,'yyyy-MM-dd')");
paramList.add(queryValue);
}
//结束日期
queryValue=queryParam.getQueryValue("query_endDate");
if(!BlankUtil.isBlank(queryValue)){
sb.append(" AND T.REPORT_DT<=to_date(?,'yyyy-MM-dd')");
paramList.add(queryValue);
}
//线路编码
queryValue=queryParam.getQueryValue("query_lineCode");
if(!BlankUtil.isBlank(queryValue)){
sb.append(" AND T.LINE_CODE= ? ");
paramList.add(queryValue);
}
//运力名称
queryValue=queryParam.getQueryValue("query_conveyanceName");
if(!BlankUtil.isBlank(queryValue)){
sb.append(" AND T.CONVEYANCE_NAME LIKE ? ");
paramList.add("%"+queryValue+"%");
}
//始发网点
queryValue=queryParam.getQueryValue("query_srcZoneCode");
if(!BlankUtil.isBlank(queryValue)){
commonSql=" AND EXISTS (SELECT 1 FROM TM_DEPARTMENT B WHERE T.SRC_ZONE=B.DEPT_CODE ";
this.convertSrcZoneCode(queryValue,sb, paramList,commonSql);
}
//到达网点
queryValue=queryParam.getQueryValue("query_destZoneCode");
if(!BlankUtil.isBlank(queryValue)){
commonSql=" AND EXISTS (SELECT 1 FROM TM_DEPARTMENT B WHERE T.DEST_ZONE=B.DEPT_CODE ";
this.convertSrcZoneCode(queryValue,sb, paramList,commonSql);
}
return sb.toString();
}

/**
* 输入查询条件为始发网点和到达网点的逻辑
* @param queryValue
* @param sb
* @param paramList
* @param commonSql
* @throws Exception
*/
private void convertSrcZoneCode(String queryValue,StringBuffer sb,List<Object> paramList,String commonSql)throws Exception{
String [] deptValue=null;
String res="";
int typeLevel=0;
if (!BlankUtil.isBlank(queryValue)){
String zoneCode=queryValue.toUpperCase().replace("，", ",");
deptValue=zoneCode.split(",");
res = zoneCodeInfoBiz.checkZoneCodesIsEffective(deptValue);
if(res.equals(OmpmgmtConstants.SUCCESS)){
typeLevel=zoneCodeInfoBiz.getZoneCodeLevel(deptValue[0]);
sb.append(commonSql);
switch(typeLevel){
case 1:sb.append(" AND B.HQ_CODE IN ");break;
case 2:sb.append(" AND B.AREA_CODE IN ");break;
case 3:sb.append(" AND B.DEPT_CODE IN ");break;
case 4:sb.append(" AND B.DIST_CODE IN ");break;
}
sb.append("(");
for(String deptCode:deptValue){
sb.append("?,");
paramList.add(deptCode);
}
sb.replace(sb.toString().length()-1, sb.toString().length(),"");
sb.append("))");
}
}
}

/**
* 封装返回结果集
* @param transferValue
* @param queryParam
* @return
*/
private List<BulkCargo> findBulkCargoResult(List<Object[]> transferValue,QueryParam queryParam){
List<BulkCargo> bulkCargoList=new ArrayList<BulkCargo>();
BulkCargo bulkCargo=null;
for(int i=0;i<transferValue.size();i++){
int number=0;
bulkCargo=new BulkCargo();
Object [] transferObj=transferValue.get(i);
//日期
bulkCargo.setQueryDate((Date)transferObj[number++]);
//始发网点
bulkCargo.setSrcZoneCode(transferObj[number++].toString());
//始发班次
bulkCargo.setSrcBatchCode(transferObj[number++].toString());
//线路编码
bulkCargo.setLineCode(transferObj[number++]==null?"":transferObj[number-1].toString());
//运力名称
bulkCargo.setConveyanceName(transferObj[number++]==null?"":transferObj[number-1].toString());
//运力类型
bulkCargo.setConveyanceType(ConveynceTypeEnum.getName(TypeUtil.intPrimValue(transferObj[number++])));
//线路等级
bulkCargo.setLineLevel(LineLevelEnum.getDesc(transferObj[number++]==null?"":transferObj[number-1].toString()));
//计划起飞,发车时间
bulkCargo.setPlanDepartTime((Date)transferObj[number++]);
//实际起飞,发车时间
bulkCargo.setActualDepartTime((Date)transferObj[number++]);
//计划落地,到达时间
bulkCargo.setPlanArriveTime((Date)transferObj[number++]);
//实际落地,到达时间
bulkCargo.setActualArriveTime((Date)transferObj[number++]);
//到达网点
bulkCargo.setDestZoneCode(transferObj[number++].toString());
//实际散货班次
bulkCargo.setActualBatchCode(transferObj[number++]==null?"":transferObj[number-1].toString());
//运力实到票数
bulkCargo.setActualLoadCnt(transferObj[number++]==null?"":transferObj[number-1].toString());
//实发班次编码
bulkCargo.setActualDepartBatchCode(transferObj[number++]==null?"":transferObj[number-1].toString());
//实发票数
bulkCargo.setActualDepartCnt(transferObj[number++]==null?"":transferObj[number-1].toString());
//实发占比
bulkCargo.setActualDepartRatio(transferObj[number++]==null?"":transferObj[number-1].toString());
bulkCargoList.add(bulkCargo);
}
return bulkCargoList;
}

/**
* 查询数量
* @param queryParam
* @return
* @throws DaoException
*/
@Override
@SuppressWarnings("unchecked")
public int findBulkCargoSize(QueryParam queryParam) throws Exception {
final List<Object> paramList=new ArrayList<Object>();
final String sqlString = generateCountSQL(queryParam,paramList);
return ((Number) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException {
Query query = session.createSQLQuery(sqlString);
if (paramList != null && paramList.size() > 0) {
Object[] values = paramList.toArray();
for (int i = 0; i < values.length; i++) {
query.setParameter(i, values[i]);
}
}
return query.uniqueResult();
}
})).intValue();
}

/**
* 拼装汇总Sql
* @param queryParam
* @param paramList
* @return
*/
private String generateCountSQL(QueryParam queryParam,List<Object> paramList)throws Exception{
StringBuffer sb = new StringBuffer();
String sql="";
sql = SQLProvider.getSQL("cont_bulkCargo").trim();
sb.append(sql);
return this.setCommonQueryCondition(queryParam, paramList, sb);
}