来源：http://www.cnblogs.com/Terrylee/archive/2006/04/12/372823.html
一．HQL简单介绍
HQL全名是Hibernate Query Language，它是一种完全面向对象的查询语言。
1.from 子句
from UserInfo
from UserInfo as userinfo
from UserInfo userinfo
UserInfo：是类名称，而不是[ActiveRecord(Table = "UserInfo")]中指定的UserInfo
2.select 子句
select Name,Author from Blog
也可以使用elements函数来查询一个集合
select elements(blog.Posts) from Blog blog
3．使用聚合函数
HQL中也可以使用一些聚合函数
select count(*) from Blog blog
select count(elements(blog.Posts)) from Blog blog
HQL支持的聚合函数有
avg(...),sum(...),min(...),max(...),count(*),count(...), count(distinct ...), count(all...)
4．Where子句
from Blog blog where blog.Name = 'test'
from Blog blog where blog.Name is not null
//根据标题 模糊查询
public static ThemeInfo[] FindBySubject(string _content)
{
SimpleQuery query = new SimpleQuery(typeof(ThemeInfo), @"from ThemeInfo themeInfo where themeInfo.Subject like ?", "%" + _content + "%");
return (ThemeInfo[])ExecuteQuery(query);
}
5.自定义查询
在实际开发中，我们所面对的查询远不止上面所说得这么简单，有时候我们需要处理一些自定义的参数，或者执行自定义的查询语句，这时需要我们编写自定义的ActiveRecord查询。
首先要添加一个类，让它继承于基类ActiveRecordBaseQuery，并覆写CreateQuery()方法（或者实现IactiveRecordQuery接口），如下例所示：
using Castle.ActiveRecord;
using NHibernate;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks; namespace Models
{
public class QueryWithNamedParameters : ActiveRecordBaseQuery
{
private string _Name = null;
private int _maxResults = 2; public QueryWithNamedParameters()
: base(typeof(UserInfo))
{ } public string Name
{
get { return _Name; }
set { _Name = value; }
} public int MaxResults
{
get { return _maxResults; }
set { _maxResults = value; }
} protected override IQuery CreateQuery(ISession session)
{
String hql = "from UserInfo userinfo";
if (!string.IsNullOrEmpty(_Name))
{
hql += " where userinfo.Name like :author";//设置查询语句
} IQuery q = session.CreateQuery(hql);
if (!string.IsNullOrEmpty(_Name))
{
q.SetString("author", "%" + _Name + "%");//设置查询参数
}
q.SetMaxResults(_maxResults);
return q;
}
}
}
使用：
//自定义查询
public static ArrayList GetThreeEntityByName(string Name)
{
QueryWithNamedParameters q = new QueryWithNamedParameters();
q.Name = Name;
q.MaxResults = 3;
return (ArrayList)ExecuteQuery(q);
} //自定义查询
StringBuilder htmlStr3 = new StringBuilder();
ArrayList list3 = Models.UserInfo.GetThreeEntityByName("jay"); foreach (Models.UserInfo item in list3)
{
htmlStr3.Append(" 编号：" + item.ID + " 名称：" + item.Name + "<br />");
}
Literal4.Text = htmlStr3.ToString();
6.使用CallBack自定义查询
/// <summary>
/// 通过CallBack执行
/// </summary>
/// <param name="author"></param>
/// <returns></returns>
public static UserInfo[] GetUserByName(string Name)
{
return (UserInfo[])Execute(typeof(UserInfo), new NHibernateDelegate(GetDataByNameCallback), Name);
} private static object GetDataByNameCallback(ISession session, object instance)
{
// 创建查询
IQuery query = session.CreateQuery("from UserInfo userinfo where userinfo.Name like :author"); // 设置参数
query.SetString("author", "%" + Convert.ToString(instance) + "%"); // 获取结果
IList results = query.List(); // 转化结果为Array
UserInfo[] users = new UserInfo[results.Count];
results.CopyTo(users, 0); // 返回结果
return users;
}
调用：
//自定义查询2
StringBuilder htmlStr4 = new StringBuilder();
Models.UserInfo[] list4 = Models.UserInfo.GetUserByName("jay"); foreach (Models.UserInfo item in list4)
{
htmlStr4.Append(" 编号：" + item.ID + " 名称：" + item.Name + "<br />");
}
Literal5.Text = htmlStr4.ToString();
7.如果不使用HQL语句查询，可以配置使用Sql查询：
SimpleQuery<RecruitmentInfo> query = new SimpleQuery<RecruitmentInfo>(QueryLanguage.Sql,hql,paras[]);
如果需要查询前几条 sql：select top 10 * from TableName；hql使用：query.SetQueryRange(BeginIndex, PageSize);//设置从哪一条开始，读取多少条