Hibernate 原生SQL不能分页

yupeng_1001 2010-12-07 11:58:02

用原生SQL查询视图，ViewOfExceptionNetUnit 没用hibernate影射，用了分页就查询不出来，List为空，不用就可以查出来。
代码如下：
public List findExceptionSearch(final int startIndex, final int pageSize,
final String netUnitAreaManagerFullIdSeq, final String netUnitName,
final String netUnitCode, final String areas, final String exceptiontype,
final String BeginDate, final String EndDate, final String order) {

// 调用模板的execute方法，参数是实现了HibernateCallback接口的匿名类，
return (List) this.getHibernateTemplate().execute(
new HibernateCallback() {
// 重写其doInHibernate方法返回一个object对象，
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
//String sql = "SELECT nunit.AreaManagerName,nunit.NetUnitName,nunit.NetUnitCode,nunit.ExceptionState,max(ExceptionDate) as LasterExceptionTime,nunit.NetUnitBusinessState,count(ExceptionState) as ExceptionTimes FROM ViewOfExceptionNetUnit as nunit WHERE 1 = 1 and nunit.NetUnitCode NOT IN (select F_AreaCode from IgnoreGlobal) AND nunit.ExceptionState = '7' AND nunit.ExceptionDate >='"+BeginDate+"' AND nunit.ExceptionDate <='"+EndDate+"' AND NetUnitAreaManagerFullIdSeq like '"+netUnitAreaManagerFullIdSeq+"%"+"' group by concat(nunit.NetUnitCode,nunit.ExceptionState) order by ExceptionTimes desc";
String sql = "SELECT nunit.AreaManagerName,nunit.NetUnitName,nunit.NetUnitCode,nunit.ExceptionState,max(ExceptionDate) as LasterExceptionTime,nunit.NetUnitBusinessState,count(ExceptionState) as ExceptionTimes FROM ViewOfExceptionNetUnit as nunit WHERE 1 = 1 and nunit.NetUnitCode NOT IN (select F_AreaCode from IgnoreGlobal) ";
if(netUnitAreaManagerFullIdSeq != null && !netUnitAreaManagerFullIdSeq.equals("")) {
sql += "AND NetUnitAreaManagerFullIdSeq like '"+netUnitAreaManagerFullIdSeq+"%"+"' ";
}
if(netUnitName != null && !netUnitName.equals("")) {
sql += "and nunit.NetUnitName = '"+netUnitName+"' ";
}
if(netUnitCode != null && !netUnitCode.equals("")) {
sql += "and nunit.NetUnitCode = '"+netUnitCode+"' ";
}
if(areas != null && !areas.equals("")) {
sql += "and nunit.NetUnitAreaManagerID = '"+areas+"' ";
}
if(exceptiontype != null && !exceptiontype.equals("")) {
sql += "and nunit.ExceptionState = '"+exceptiontype+"' ";
}
if(BeginDate != null && !BeginDate.equals("")) {
sql += "and nunit.ExceptionDate >= '"+BeginDate+"' ";
}
if( EndDate!= null && !EndDate.equals("")) {
sql += "and nunit.ExceptionDate <= '"+EndDate+"' ";
}
sql += "group by concat(nunit.NetUnitCode,nunit.ExceptionState) ";
if( order!= null && !order.equals("")) {
if(order.equals("asc")) {
sql += "order by ExceptionTimes";
}
if(order.equals("desc")) {
sql += "order by ExceptionTimes desc";
}
}
//System.out.println("=========sql=========="+sql);
Query query= session.createSQLQuery(sql)
.setFirstResult(startIndex)
.setMaxResults(pageSize);

List list = query.list();
System.out.println("=========****=========="+query.list().size());
return list;

}

});
}

...全文