Castle ActiveRecord学习（五）使用HQL语句查询

来源：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

?

1 2 3 4 5 6

//根据标题 模糊查询         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接口），如下例所示：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

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;         }     } }

　　

使用：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

//自定义查询         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自定义查询

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

/// <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;         }

　　

调用：

?

1 2 3 4 5 6 7 8 9

//自定义查询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);//设置从哪一条开始，读取多少条