• Nhibernate 3.0 cookbook学习笔记 查询


    利用上一节的数据: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>();
            }

    代码下载:点我

  • 相关阅读:
    scrapy+Lucene搭建小型搜索引擎
    普通程序员如何转向AI方向
    python:单引号,双引号和三引号
    【推荐】你必须知道的EF知识和经验
    Quartz.NET 入门
    论如何学习一门编程语言
    Android解析ActivityManagerService(一)AMS启动流程和AMS家族
    python的数据可视化库 matplotlib 和 pyecharts
    pythonWeb -- Django开发- Admin
    Android内存优化(四)解析Memory Monitor、Allocation Tracker和Heap Dump
  • 原文地址:https://www.cnblogs.com/Gyoung/p/2511660.html
Copyright © 2020-2023  润新知