摘要:虽然
ActiveRecord
为我们提供了
Find()
和
FindAll()
这样两个静态的查询方法,并且有
Where
特性可供使用,但是仍然不能解决实际开发中一些复杂的查询,这时我们就需要通过
HQL
查询来实现。
主要内容
1
.
HQL
概述
2
.
SimpleQuery
查询
3
.
ScalarQuery
查询
4
.自定义查询
5
.使用
CallBack
一.
HQL
简单介绍
HQL
全名是Hibernate Query Language,它是一种完全面向对象的查询语言。先来看一下HQL最基本的一些用法
1
.From子句
from
Post
你也可以为Post起一个别名
from
Post
as
post
或者省略as
from
Post post
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 )
count ( * )
count (), count ( distinct ), count ( all )
4
.Where子句
from
Blog blog
where
blog.Name
=
‘Terry Lee’
from
Blog blog
where
blog.Name
is
not
null
二.SimpleQuery查询
SimpleQuery
是一种最简单的查询,它直接处理HQL语句,并返回一个集合,没有复杂的参数处理。具体用法可以参考下例:
[ActiveRecord(
"
Posts
"
)]
public class Post : ActiveRecordBase
{
//
/// <summary>
/// 查询某一类别的所有Posts
/// </summary>
/// <param name="_strCategory">类别名称</param>
/// <returns></returns>
public static Post[] GetPostsByCategory(string _strCategory)
{
SimpleQuery query = new SimpleQuery(
typeof(Post),
@"from Post post where post.Category = ?",
_strCategory
);
return (Post[])ExecuteQuery(query);
}
/// <summary>
/// 查询某一时间段内发表的所有Posts
/// </summary>
/// <param name="start">开始时间</param>
/// <param name="end">结束时间</param>
/// <returns></returns>
public static int[] GetPostsInterval(DateTime start,DateTime end)
{
SimpleQuery query = new SimpleQuery(
typeof(Post),typeof(int),
@"select post.Id from Post post where post.Created between ? and ?",
start,end
);
return (int[])ExecuteQuery(query);
}
}
public class Post : ActiveRecordBase
{
//
/// <summary>
/// 查询某一类别的所有Posts
/// </summary>
/// <param name="_strCategory">类别名称</param>
/// <returns></returns>
public static Post[] GetPostsByCategory(string _strCategory)
{
SimpleQuery query = new SimpleQuery(
typeof(Post),
@"from Post post where post.Category = ?",
_strCategory
);
return (Post[])ExecuteQuery(query);
}
/// <summary>
/// 查询某一时间段内发表的所有Posts
/// </summary>
/// <param name="start">开始时间</param>
/// <param name="end">结束时间</param>
/// <returns></returns>
public static int[] GetPostsInterval(DateTime start,DateTime end)
{
SimpleQuery query = new SimpleQuery(
typeof(Post),typeof(int),
@"select post.Id from Post post where post.Created between ? and ?",
start,end
);
return (int[])ExecuteQuery(query);
}
}
看一下简单的测试代码:
[Test]
public void TestGetPostsByCategory()
{
string StrCategory = "Castle";
IList list = (IList)Post.GetPostsByCategory(StrCategory);
int expectedCount = 2;
Assert.IsNotNull(list);
Assert.AreEqual(expectedCount,list.Count);
}
[Test]
public void TestGetPostsInterval()
{
DateTime start = Convert.ToDateTime("2006-01-01");
DateTime end = DateTime.Now;
IList list = (IList)Post.GetPostsInterval(start,end);
int expectedCount = 2;
Assert.IsNotNull(list);
Assert.AreEqual(expectedCount,list.Count);
}
public void TestGetPostsByCategory()
{
string StrCategory = "Castle";
IList list = (IList)Post.GetPostsByCategory(StrCategory);
int expectedCount = 2;
Assert.IsNotNull(list);
Assert.AreEqual(expectedCount,list.Count);
}
[Test]
public void TestGetPostsInterval()
{
DateTime start = Convert.ToDateTime("2006-01-01");
DateTime end = DateTime.Now;
IList list = (IList)Post.GetPostsInterval(start,end);
int expectedCount = 2;
Assert.IsNotNull(list);
Assert.AreEqual(expectedCount,list.Count);
}
三.ScalarQuery查询
ScalarQuery
查询也是一种简单的直接处理HQL的查询,它也没有复杂的参数处理,只不过返回的值不是集合而是单一的值,具体用法参考下例:
[ActiveRecord(
"
Blogs
"
)]
public class Blog : ActiveRecordBase
{
//
/// <summary>
/// 查询某作者发表的所有Posts数量
/// </summary>
/// <param name="_StrAuthor">作者姓名</param>
/// <returns></returns>
public static int GetPostNumByAuthor(string _StrAuthor)
{
ScalarQuery query = new ScalarQuery(
typeof(Blog),
@"select count(elements(blog.Posts)) from Blog blog where blog.Author = ?",
_StrAuthor
);
return (int)ExecuteQuery(query);
}
}
public class Blog : ActiveRecordBase
{
//
/// <summary>
/// 查询某作者发表的所有Posts数量
/// </summary>
/// <param name="_StrAuthor">作者姓名</param>
/// <returns></returns>
public static int GetPostNumByAuthor(string _StrAuthor)
{
ScalarQuery query = new ScalarQuery(
typeof(Blog),
@"select count(elements(blog.Posts)) from Blog blog where blog.Author = ?",
_StrAuthor
);
return (int)ExecuteQuery(query);
}
}
看一下简单的测试代码
[Test]
public void TestGetPostNumByAuthor()
{
string _StrAuthor = "Terry Lee";
int result = Blog.GetPostNumByAuthor(_StrAuthor);
int expectedCount = 2;
Assert.AreEqual(expectedCount,result);
}
public void TestGetPostNumByAuthor()
{
string _StrAuthor = "Terry Lee";
int result = Blog.GetPostNumByAuthor(_StrAuthor);
int expectedCount = 2;
Assert.AreEqual(expectedCount,result);
}
四.自定义查询
在实际开发中,我们所面对的查询远不止上面所说得这么简单,有时候我们需要处理一些自定义的参数,或者执行自定义的查询语句,这时需要我们编写自定义的ActiveRecord查询,首先要添加一个类,让它继承于基类
ActiveRecordBaseQuery
,并覆写Execute()方法(或者实现IactiveRecordQuery接口),如下例所示
public
class
QueryWithNamedParameters : ActiveRecordBaseQuery
{
private string _authorName = null;
private int _maxResults = 2;
public QueryWithNamedParameters()
: base(typeof(Blog))
{
}
public string AuthorName
{
get { return _authorName; }
set { _authorName = value; }
}
public int MaxResults
{
get { return _maxResults; }
set { _maxResults = value; }
}
public override object Execute(ISession session)
{
String hql = "from Blog blog";
if (_authorName != null)
hql += " where blog.Author = :author";
IQuery q = session.CreateQuery(hql);
if (_authorName != null)
q.SetString("author", _authorName);
q.SetMaxResults(_maxResults);
return base.GetResultsArray(typeof(Blog), q.List(), null, false);
}
}
{
private string _authorName = null;
private int _maxResults = 2;
public QueryWithNamedParameters()
: base(typeof(Blog))
{
}
public string AuthorName
{
get { return _authorName; }
set { _authorName = value; }
}
public int MaxResults
{
get { return _maxResults; }
set { _maxResults = value; }
}
public override object Execute(ISession session)
{
String hql = "from Blog blog";
if (_authorName != null)
hql += " where blog.Author = :author";
IQuery q = session.CreateQuery(hql);
if (_authorName != null)
q.SetString("author", _authorName);
q.SetMaxResults(_maxResults);
return base.GetResultsArray(typeof(Blog), q.List(), null, false);
}
}
使用我们自定义的类
/// <summary>
/// 自定义查询
/// </summary>
/// <param name="authorName"></param>
/// <returns></returns>
public static Blog[] GetThreeBlogsFromAuthor( string authorName )
{
QueryWithNamedParameters q = new QueryWithNamedParameters();
q.AuthorName = authorName;
q.MaxResults = 3;
return (Blog[]) ExecuteQuery(q);
}
/// 自定义查询
/// </summary>
/// <param name="authorName"></param>
/// <returns></returns>
public static Blog[] GetThreeBlogsFromAuthor( string authorName )
{
QueryWithNamedParameters q = new QueryWithNamedParameters();
q.AuthorName = authorName;
q.MaxResults = 3;
return (Blog[]) ExecuteQuery(q);
}
看一下简单的测试代码
[Test]
public void TestCustomQuery()
{
string _StrAuthor = "Terry Lee";
IList list = Blog.GetThreeBlogsFromAuthor(_StrAuthor);
int expectedCount = 3;
Assert.IsNotNull(list);
Assert.AreEqual(expectedCount,list.Count);
}
public void TestCustomQuery()
{
string _StrAuthor = "Terry Lee";
IList list = Blog.GetThreeBlogsFromAuthor(_StrAuthor);
int expectedCount = 3;
Assert.IsNotNull(list);
Assert.AreEqual(expectedCount,list.Count);
}
五.使用CallBack
还有一种实现方式是使用Execute()方法,这种方式与我们前面所讲的自定义查询是差不多的,只不过不用增加额外的自定义类。
[ActiveRecord(
"
Blogs
"
)]
public class Blog : ActiveRecordBase
{
//
/// <summary>
/// 通过CallBack执行
/// </summary>
/// <param name="author"></param>
/// <returns></returns>
public static Blog[] GetPostsFromAuthor( string author )
{
return (Blog[]) Execute( typeof(Blog), new NHibernateDelegate(GetPostsFromAuthorCallback), author);
}
private static object GetPostsFromAuthorCallback(ISession session, object instance )
{
// 创建查询
IQuery query = session.CreateQuery( "from Blog blog where blog.Author = :author" );
// 设置参数
query.SetString("author", (string) instance);
// 获取结果
IList results = query.List();
// 转化结果为Array
Blog[] blogs = new Blog[results.Count];
results.CopyTo(blogs, 0);
// 返回结果
return blogs;
}
}
public class Blog : ActiveRecordBase
{
//
/// <summary>
/// 通过CallBack执行
/// </summary>
/// <param name="author"></param>
/// <returns></returns>
public static Blog[] GetPostsFromAuthor( string author )
{
return (Blog[]) Execute( typeof(Blog), new NHibernateDelegate(GetPostsFromAuthorCallback), author);
}
private static object GetPostsFromAuthorCallback(ISession session, object instance )
{
// 创建查询
IQuery query = session.CreateQuery( "from Blog blog where blog.Author = :author" );
// 设置参数
query.SetString("author", (string) instance);
// 获取结果
IList results = query.List();
// 转化结果为Array
Blog[] blogs = new Blog[results.Count];
results.CopyTo(blogs, 0);
// 返回结果
return blogs;
}
}
编写测试代码
[Test]
public void TestGetPostsFromAuthor()
{
string _StrAuthor = "Terry Lee";
IList list = Blog.GetPostsFromAuthor(_StrAuthor);
int expectedCount = 4;
Assert.IsNotNull(list);
Assert.AreEqual(expectedCount,list.Count);
}
public void TestGetPostsFromAuthor()
{
string _StrAuthor = "Terry Lee";
IList list = Blog.GetPostsFromAuthor(_StrAuthor);
int expectedCount = 4;
Assert.IsNotNull(list);
Assert.AreEqual(expectedCount,list.Count);
}
关于使用HQL查询就介绍到这儿了,相信通过HQL查询可以解决我们开发中的绝大多数的复杂查询问题。
本文转自lihuijun51CTO博客,原文链接:
http://blog.51cto.com/terrylee/67665
,如需转载请自行联系原作者