NHibernate分页问题

[ManFirst]

我在使用NHIBERNATE分页时遇到下列问题,我想找到Nhibernate分页的方法.SQL分页已经解决了。但是,基于NHIBERNATE的对象分页还没找到，不知道大家明白我的意思不，针对对象分页，那么查询条件中写的则是对象的属性，而不是真实的字段名字。
我目前遇到的问题是，在使用select count()的时候NHIBERNATE并不支持。不知道NHIBERNATE是怎么回事情和hibernate有所不同，下面是我的代码，请高手指正。
using System;
using System.Collections.Generic;
using System.Text;
using LongSun.Platform.Util.Page;
using System.Collections;
using log4net;
using NHibernate;
using LongSun.Platform.Util.Exceptions;

namespace LongSun.Platform.Helper
{
///<summary>
///project：　龙讯科技
///author： ZJW/zhjw
///version : V1.0
///time: 04/07/2007 16:56:02
///history：
///04/07/2007 16:56:02 zhjw 创建了 QueryHandler
///<see> function</see>
///</summary>
public class QueryHandler
{
private static QueryHandler _instance = new QueryHandler();
/// <summary>
/// loger self.
/// </summary>
private static readonly ILog log = LogManager.GetLogger(typeof(QueryHandler));
/// <summary>
/// 获得实例，调用直接QueryHandler.Instance.方法名。
/// </summary>
public static QueryHandler Instance
{
get { return QueryHandler._instance; }
}
public QueryHandler() { }


/// <summary>
/// 取得分页数据，返回IPage接口
/// </summary>
/// <param name="pageNumber">当前第几页</param>
/// <param name="pageSize">页面大小</param>
/// <param name="values">值对象</param>
/// <param name="hql">hsql</param>
/// <returns></returns>
public IPage QueryByPage(int pageNumber, int pageSize,
ArrayList valuesList, String hql)
{


if (hql == null || hql.Equals(""))
return null;
IPage page = null;
ISession session = null;
try {
session = NHibernateHelper.GetInstance().GetSession();
int total = GetTotalCount(session, hql, valuesList);
ArrayList querylist =new ArrayList(Query(session, hql, valuesList, pageNumber, pageSize));
page = new NHibernatePage(querylist, total, pageNumber, pageSize);
}
catch (Exception e) {
log.Error("分页查询时发生数据库错误, hql：" + hql + "\r\n错误：" + e);
}
finally {
if (session != null)
session.Close();
}
return page;
}
/// <summary>
/// 得到记录总数
/// </summary>
/// <param name="session"></param>
/// <param name="hql"></param>
/// <param name="values"></param>
/// <returns></returns>
private int GetTotalCount(ISession session, String hql, ArrayList valuesList){
int count = 0;
/**
* 去掉排序
*/
int sql_orderby = hql.IndexOf("order by");
int sql_from=hql.ToLower().IndexOf("from");
int sql_select=hql.ToLower().IndexOf("select");
int sql_field=hql.ToLower().IndexOf(",");
if(sql_from==-1) sql_from=0;
if(sql_select==-1) sql_select=0;
else{sql_select=sql_select+6;}
if (sql_field>=sql_from){
sql_field=0;
}
else if(sql_field==-1){
sql_field=0;
}

String fieldName=(hql.Substring(sql_select,sql_field).Trim().Length==0
)?"*":hql.Substring(sql_select,sql_field);
StringBuilder countStr = new StringBuilder("select count(1) ");
if (sql_orderby > 0)
countStr.Append(hql.Substring(sql_from, sql_orderby - sql_from));
else
countStr.Append(hql.Substring(sql_from, hql.Length - sql_from));

try {
IQuery query = session.CreateQuery(countStr.ToString());
for (int i = 0; i < valuesList.Count; i++) {
query.SetParameter(i, valuesList[i]);
}
IEnumerator e=query.List().GetEnumerator();
e.MoveNext();
count=System.Convert.ToInt32(e.Current);


return count;
} catch (Exception e) {
log.Error("查找数据总数时数据库查询错误" + e);
throw new DAOException(e.Message);
}
}

/// <summary>
/// 按页数得到需要记录数
/// </summary>
/// <param name="session"></param>
/// <param name="hql"></param>
/// <param name="valuesList"></param>
/// <param name="pageNo"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
private ArrayList Query(ISession session, String hql, ArrayList valuesList,
int pageNo, int pageSize) {
try {
IQuery query = session.CreateQuery(hql);
for (int i = 0; i < valuesList.Count; i++) {
query.SetParameter(i, valuesList[i]);
}
query.SetFirstResult((pageNo - 1) * pageSize);
query.SetMaxResults(pageSize);

return new ArrayList(query.List());
} catch (Exception e) {
log.Error("查找数据时数据库查询错误" + e);
throw new DAOException(e.Message);
}
}

/// <summary>
/// 取得全部记录
/// </summary>
/// <param name="hql"></param>
/// <param name="valuesList"></param>
/// <returns></returns>
public ArrayList QueryAll(String hql, ArrayList valuesList) {
ISession session = null;
try {
session = NHibernateHelper.GetInstance().GetSession();
IQuery query = session.CreateQuery(hql);
for (int i = 0; i < valuesList.Count; i++) {
query.SetParameter(i, valuesList[i]);
}
return new ArrayList(query.List());
} catch (Exception e) {
log.Error("查找全部记录时数据库查询错误" + e);
throw new DAOException(e.Message);
} finally {
if (session != null)
session.Close();
}
}

}
}

[CathySun118]

好麻烦，你可以使用分页的存储过程啊

[geek007]

up

[csShooter]

不会吧，一个分页也用Nhibrate

[ManFirst]

up

[ManFirst]

以前我们在JAVA的里头hibernate是支持的,分页用存储过程固然是可以的,但是,我们是要让数据对象化的话,必然还会回到这个问题上来,所以,我实现了个SQL的还要实现一个NHibernate 的

[ManFirst]

搞定原来NHIBERNATE的SQL解析器做得不是很好,不能分析count(1)这样的语句。在COUNT里只能填写字段：select m.CustomerName from Customers m where CustomerName like ?"
String fieldName=(hql.Substring(sql_select,sql_field).Trim().Length==0
)?"*":hql.Substring(sql_select,sql_field);
StringBuilder countStr = new StringBuilder("select count(1) ");
原来的语句NHIBERNATE转换出来是：select customer.* from customer 是customer对象
count=System.Convert.ToInt32(e.Current);并不是select count(*) from customer
现在填入字段或者直接写成HSQL:FROM CUSTOMER。写select * from customer还是会报错。
改成以下就不会出错了。
String fieldName=(hql.Substring(sql_select,sql_field).Trim().Length==0
)?"*":hql.Substring(sql_select,sql_field);
StringBuilder countStr = new StringBuilder("select count(" + fieldName + ") ");
另外在选择字段的时候必须注意必须加上别名如：
select m.CustomerName from Customers m where CustomerName like ?"
否则将会报 undefined alias or unknown mapping: CustomerID [select CustomerID from LongSun.Entity.Sample.Customers.Customers where CustomerName like ?]
目前我用的Nhibernate版本是：NHibernate-1.2.0.CR1。
Nhibernate版本比hibernate版本还是差好多啊。希望它赶快稳定下来，否则只有自己去改它的代码了，看得一头的包。