HQL查询实例

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate;
using NHibernate.Cfg;
using DomainModel.Entities;
namespace HQL
{
    public class QueryHQL
    {
        private ISession _session;
        public ISession Session
        {
            set
            {
                _session = value;
            }
        }
        public QueryHQL(ISession session)
        {
            _session = session;
        }
        #region 基本语法学习
        /// <summary>
        /// from子句：简单用法
        /// </summary>
        /// <returns></returns>
        public IList<Customer> From()
        {
            //返回所有Customer类的实例
            return _session.CreateQuery("from Customer")
                .List<Customer>();
        }
        /// <summary>
        /// from子句：使用别名
        /// </summary>
        /// <returns></returns>
        public IList<Customer> FromAlias()
        {
            //返回所有Customer类的实例，Customer赋予了别名customer（as可以省略）
            return _session.CreateQuery("from Customer as customer").List<Customer>();
        }
        /// <summary>
        /// from子句：笛卡尔积
        /// </summary>
        /// <returns></returns>
        public IList<Customer> FromCartesianproduct()
        {
            //出现多个类，或者分别使用别名,返回笛卡尔积，或者称为“交叉”连接。
            return _session.CreateQuery("from Customer")
                .List<Customer>();
        }
        /// <summary>
        /// select子句：简单用法
        /// </summary>
        /// <returns></returns>
        public IList<int> Select()
        {
            //返回在结果集中返回哪些对象和属性：返回所有Customer的CustomerId
            return _session.CreateQuery("select c.CustomerId from Customer c")
                .List<int>();
            //注意：包括组件类型的属性select c.Name.Firstname from Customer c
        }
        /// <summary>
        /// select子句：数组
        /// </summary>
        /// <returns></returns>
        public IList<object[]> SelectObject()
        {
            //用Object[]的数组返回多个对象和/或多个属性，或者使用特殊的elements功能，注意一般要结合group by使用
            return _session.CreateQuery("select c.Firstname, count(c.Firstname) from Customer c group by c.Firstname")
                .List<object[]>();
        }

        /// <summary>
        /// 统计函数
        /// </summary>
        /// <returns></returns>
        public IList<object[]> AggregateFunction()
        {
            //用Object[]的数组返回属性的统计函数的结果
            return _session.CreateQuery("select avg(c.CustomerId),sum(c.CustomerId),count(c) from Customer c")
                .List<object[]>();
            //注意：统计函数的变量也可以是集合count( elements(c.CustomerId) )
        }
        /// <summary>
        /// Distinct用法
        /// </summary>
        /// <returns></returns>
        public IList<string> Distinct()
        {
            //distinct和all关键字的用法和语义与SQL相同。
            return _session.CreateQuery("select distinct c.Firstname from Customer c")
                .List<string>();
        }
        /// <summary>
        /// Where子句
        /// </summary>
        /// <returns></returns>
        public IList<Customer> Where()
        {
            //where子句让你缩小你要返回的实例的列表范围
            return _session.CreateQuery("select from Customer c where c.Firstname='YJing'")
                .List<Customer>();
            //where Order.Customer.Firstname is not null被翻译为带有一个表间（inner)join的SQL查询
            //注意：where c.id=1 小写的id可以用来表示一个对象的惟一标识。（你可以使用它的属性名。）
            //例如where Order.Customer.id=1 高效率,不需要进行表间连接
        }

        /// <summary>
        /// Where子句：表达式
        /// </summary>
        /// <returns></returns>
        public IList<Customer> WhereExpression()
        {
            return _session.CreateQuery("from Customer c where c.Firstname between 'A%' and 'Y%'")
                .List<Customer>();
            //或者如下
            //from Customer c where c.Firstname not in ('YJing','Lee','li')
            //from Customer c where c.Firstname.size > 0 
        }
        /// <summary>
        /// order by子句
        /// </summary>
        /// <returns></returns>
        public IList<Customer> Orderby()
        {
            //查询返回的列表可以按照任何返回的类或者组件的属性排序
            return _session.CreateQuery("select from Customer c order by c.Firstname asc,c.Lastname desc")
                .List<Customer>();
            //asc和desc是可选的，分别代表升序或者降序
        }
        /// <summary>
        /// group by子句
        /// </summary>
        /// <returns></returns>
        public IList<object[]> Groupby()
        {
            //查询返回的列表可以按照任何返回的类或者组件的属性分组
            return _session.CreateQuery("select c.Firstname, count(c.Firstname) from Customer c group by c.Firstname")
                .List<object[]>();
        }
        /// <summary>
        /// 子查询
        /// </summary>
        /// <returns></returns>
        public IList<Customer> Subquery()
        {
            return _session.CreateQuery("from Customer c where c.Lastname=some"
                +"( select c.Lastname from Customer c)")
                .List<Customer>();
        }
        #endregion 

        #region 实例学习
        /// <summary>
        /// 按Firstname查询顾客
        /// </summary>
        /// <param name="firstname"></param>
        /// <returns>顾客列表</returns>
        public IList<Customer> GetCustomersByFirstname(string firstname)
        {
            //ISession _session = GetSession();

            //写法1
            //return _session.CreateQuery("select from Customer c where c.Firstname='" + firstname + "'").List<Customer>();

            //写法2
            //return _session.CreateQuery("select from Customer c where c.Firstname=?")
            //    .SetString(0, firstname)
            //    .List<Customer>();
            
            //写法3
            return _session.CreateQuery("select from Customer c where c.Firstname=:fn")
                .SetString("fn", firstname)
                .List<Customer>();
        }
        /// <summary>
        /// 按Firstname和Lastname查询顾客
        /// </summary>
        /// <param name="firstname"></param>
        /// <param name="lastname"></param>
        /// <returns>顾客列表</returns>
        public IList<Customer> GetCustomersByFirstnameAndLastname(string firstname, string lastname)
        {
            return _session.CreateQuery("select from Customer c where c.Firstname=:fn and c.Lastname=:ln")
                .SetString("fn", firstname)
                .SetString("ln", lastname)
                .List<Customer>();
        }
        /// <summary>
        /// 获取顾客ID大于CustomerId的顾客
        /// </summary>
        /// <param name="customerId">顾客ID</param>
        /// <returns>顾客列表</returns>
        public IList<Customer> GetCustomersWithCustomerIdGreaterThan(int customerId)
        {
            return _session.CreateQuery("select from Customer c where c.CustomerId > :cid")
                .SetInt32("cid", customerId)
                .List<Customer>();
        }

        public IList<string> GetDistinctCustomerFirstnames()
        {
            return _session.CreateQuery("select distinct c.Firstname from Customer c")
                .List<string>();
        }

        public IList<Customer> GetCustomersOrderedByLastnames()
        {
            return _session.CreateQuery("select from Customer c order by c.Lastname")
                .List<Customer>();
        }
        #endregion
    }
}