主要內容
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)
4．Where子句
from Blog blog where blog.Name = ‘Terry Lee’
from Blog blog where blog.Name is not null
詳細可以參考http://www.hibernate.org/hib_docs/reference/en/html/queryhql.html。
二．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);
}
}
看一下簡單的測試代碼：
[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);
}
三．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);
}
}
看一下簡單的測試代碼
[Test]
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);
}
}
使用我們自定義的類
/**//// <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);
}
看一下簡單的測試代碼
[Test]
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;
}
}
編寫測試代碼
[Test]
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查詢可以解決我們開發中的絕大多數的復雜查詢問題。
