利用上一节的数据:Nhibernate 3.0 cookbook学习笔记 一对多与多对多映射 我们来做查询
1 Criteria Queries
简单的单表查询:
public IEnumerable<Movie> GetMoviesDirectedBy(string directorName) { return _session.CreateCriteria(typeof(Movie)) .Add(Restrictions.Eq("Director", directorName)) .List<Movie>(); }
设置返回最多结果,类似Top:
public IEnumerable<Movie> GetMovies() { return _session.CreateCriteria<Movie>() .SetMaxResults(10).List<Movie>(); }
添加排序:
public IEnumerable<Movie> GetMovies() { return _session.CreateCriteria<Movie>() .AddOrder(Order.Desc("Name")) .List<Movie>(); }
使用Between,有两种方法:
public IEnumerable<Product> GetProductByPrice(decimal minPrice, decimal maxPrice) { return _session.CreateCriteria<Product>() .Add(Restrictions.Between("UnitPrice", minPrice, maxPrice)) .List<Product>(); //也可以用下面这种方法 //return _session.CreateCriteria<Product>() //.Add(Restrictions.And( //Restrictions.Ge("UnitPrice", minPrice), //Restrictions.Le("UnitPrice", maxPrice) //)) //.List<Product>(); }
使用Like:
public IEnumerable<Movie> GetMoviesDirectedBy(string directorName) { return _session.CreateCriteria(typeof(Movie)) .Add(Restrictions.Like("Director", directorName,MatchMode.Anywhere)) .List<Movie>(); }
返回唯一结果:
public Book GetBookByISBN(string isbn) { return _session.CreateCriteria<Book>() .Add(Restrictions.Eq("ISBN", isbn)) .UniqueResult<Book>(); }
分组GroupBy:
public IEnumerable<object[]> GetMovieGroupByDirector() { return _session.CreateCriteria<Movie>() .SetProjection(Projections.GroupProperty("Director").As("director"), Projections.GroupProperty("UnitPrice").As("UnitPrice")). AddOrder(Order.Asc("director")).List<object[]>(); }
统计Count:
public int GetMovieCount() { return (int)_session.CreateCriteria<Movie>() .SetProjection(Projections.RowCount()).UniqueResult(); }
In:
public IEnumerable<Movie> GetMoviesWithDirectors() { return _session.CreateCriteria<Movie>() .Add(Restrictions.In("Director", new string[] { "宁浩", "Steven Spielberg" })).List<Movie>(); }
多表查询:
public IEnumerable<Movie> GetMoviesWith(string actorName) { return _session.CreateCriteria<Movie>() .CreateCriteria("ActorRoles", JoinType.InnerJoin) .Add(Restrictions.Eq("Actor", actorName)) .List<Movie>(); }
PS:上面查询中的"Director","Name"等字段都是对应类中的属性,而不是表名或表中的字段。
2 QueryOver
QueryOver允许我们用lambda表达式来执行查询。
简单的单表查询:
public IEnumerable<Movie> GetMoviesDirectedBy(string directorName) { return _session.QueryOver<Movie>() .Where(m => m.Director == directorName) .List(); }
Top:
_session.QueryOver<Movie>().Take(10).List<Movie>();
OrderBy:
_session.QueryOver<Movie>().OrderBy(m => m.Name).Desc.List<Movie>();
Between:
public IEnumerable<Product> GetProductByPrice(decimal minPrice,decimal maxPrice) { return _session.QueryOver<Product>() .Where(p => p.UnitPrice >= minPrice && p.UnitPrice <= maxPrice) .OrderBy(p => p.UnitPrice).Asc .List(); }
Between也可以用下面这个方法:
public IEnumerable<Product> GetProductByPrice(decimal minPrice,decimal maxPrice) { return _session.QueryOver<Product>().WhereRestrictionOn(p => p.UnitPrice) .IsBetween(minPrice).And(maxPrice).List(); }
Like:
_session.QueryOver<Movie>().WhereRestrictionOn(m => m.Name).IsLike("The Bucket List").List<Movie>();
返回唯一结果:
public Book GetBookByISBN(string isbn) { return _session.QueryOver<Book>() .Where(b => b.ISBN == isbn) .SingleOrDefault(); }
Count:
_session.QueryOver<Movie>().RowCount();
In:
_session.QueryOver<Movie>().And(Restrictions.On<Movie>(m => m.Director).IsIn(new string[] { "宁浩", "Steven Spielberg" })).List<Movie>();
多表查询:
public IEnumerable<Movie> GetMoviesWith(string actorName) { return _session.QueryOver<Movie>() .OrderBy(m => m.UnitPrice).Asc .Inner.JoinQueryOver<ActorRole>(m=>m.ActorRoles). Where(a => a.Actor == actorName).List(); }
3 Hibernate Query Language
HQL有点类似于SQL,我们可以写出类似SQL的语句来执行查询
简单查询:
public IEnumerable<Movie> GetMoviesDirectedBy(string directorName) { var hql = @"from Movie m where m.Director = :director"; return _session.CreateQuery(hql) .SetString("director", directorName) .List<Movie>(); }
Top:
var hql = @"select m from Movie m"; return _session.CreateQuery(hql).SetMaxResults(10).List<Movie>();
OrderBy:
var hql = @"select m from Movie m order by m.Name desc"; return _session.CreateQuery(hql).List<Movie>();
Between:
public IEnumerable<Product> GetProductByPrice(decimal minPrice,decimal maxPrice) { var hql = @"from Product p where p.UnitPrice >= :minPrice and p.UnitPrice <= :maxPrice order by p.UnitPrice asc"; return _session.CreateQuery(hql) .SetDecimal("minPrice", minPrice) .SetDecimal("maxPrice", maxPrice) .List<Product>(); }
唯一值,也可以在sql中加distinct :
public Book GetBookByISBN(string isbn) { var hql = @"from Book b where b.ISBN = :isbn"; return _session.CreateQuery(hql) .SetString("isbn", isbn).UniqueResult<Book>(); }
Group by:
var hql = @"select m.Name,count(m.UnitPrice) from Movie m Group by m.Name"; return _session.CreateQuery(hql).List<object[]>();
多表查询:
public IEnumerable<Movie> GetMoviesWith(string actorName) { var hql = @"select m from Movie m inner join m.ActorRoles as ar where ar.Actor = :actorName"; return _session.CreateQuery(hql) .SetString("actorName", actorName) .List<Movie>(); }
代码下载:点我。