01-04-02【Nhibernate (版本3.3.1.4000) 出入江湖】HQL查询

 

 

        public IList<Customer> GetAllHql()
        {
            IList<Customer> result = null;
            ISession session = _sessionManager.GetSession();

            try
            {
                
                result = session.CreateQuery("from Customer").List<Customer>();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                session.Close();
            }

            return result;
        }

 

注意：

    

result = session.CreateQuery("from Customer").List<Customer>();

Customer是类名，而不是数据库表名。

这正是Nhibernate的好处：屏蔽对数据库的底层操作，直接面向对象（实体类）编程。


实例：

        public IList<Customer> GetAllCustomers_HQL(){
           return session.CreateQuery("from Customer  c").List<Customer>();
        }

        public IList<int> GetCustomerId_HQL() {
            return session.CreateQuery("select c.CustomerId from Customer c").List<int>();
        }

        public IList<Object[]> GetCustomerInfo_HQL() {
            return session.CreateQuery("select c.FirstName,count(c.FirstName) from Customer c group by c.FirstName").List<Object[]>();
        }

        public IList<Object[]> AggregateFunction()
        {
            return session.CreateQuery("select avg(c.CustomerId),sum(c.CustomerId),count(c) from Customer c")
                .List<Object[]>();
        }

        public IList<String> Distinct_HQL() {
            return session.CreateQuery("select distinct c.FirstName from Customer c").List<String>();
        }

        public IList<Customer> Where_HQL() {
            return session.CreateQuery("from Customer c where c.FirstName='aaaa'").List<Customer>();
        }

        public IList<Customer> Where_HQL(String firstname)
        {
            String hql = String.Format("from Customer c where c.FirstName='{0}'",firstname);
            return session.CreateQuery(hql).List<Customer>();
        }

        public IList<Customer> WhereLike_HQL(String firstname)
        {
            String hql = String.Format("from Customer c where c.FirstName like'%{0}%'", firstname);
            return session.CreateQuery(hql).List<Customer>();
        }

        public IList<Customer> WhereLike_HQL2(String firstname)
        {
            String hql = "from Customer c where c.FirstName like ?";
            return session.CreateQuery(hql).SetString(0,"%"+firstname+"%").List<Customer>();
        }

        public IList<Customer> WhereLike_HQL3(String firstname)
        {
            String hql = "from Customer c where c.FirstName like :fname";
            return session.CreateQuery(hql).SetString("fname", "%" + firstname + "%").List<Customer>();
        }

        public IList<Customer> OrderBy_HQL() {
            return session.CreateQuery("from Customer c order by c.FirstName asc,c.LastName desc").List<Customer>();
        }

        //查询下定单的客户信息
        public IList<Customer> InnerJoin_HQL()
        {
            string hql = "select distinct a from Customer a inner join a.Orders b ";
            return session.CreateQuery(hql).List<Customer>();
        }

        //public IList<Customer> UseCriteriaAPI_GetCustomersWithOrders(DateTime orderDate)(){
        //      return null;
        //}

        //查询下定单的客户信息()
        public IList<Customer> InnerJoin_HQL(String orderDate)
        {
            string hql = "select distinct a from Customer a inner join a.Orders b where b.OrderDate>:orderDate";
            return session.CreateQuery(hql).SetString("orderDate",orderDate).List<Customer>();
        }

 

------------------------------------------------------------------------------
HQL查询返回不规则对象：                   

        /// <summary>
        /// HQL返回不规则对象
        /// </summary>
        /// <param name="orderDate"></param>
        /// <returns></returns>
        public IList<Object[]> GetObjectByHQL(String orderDate)
        {
            //Object的成员依次是Customer，OrderDate
            string hql = "select distinct a,b.OrderDate from Customer a " +
                         "inner join a.Orders b where b.OrderDate=:orderDate";
            return _session.CreateQuery(hql)
                .SetString("orderDate", orderDate)
                .List<Object[]>();
        }

Object的成员依次是Customer，OrderDate，

            IList<Object[]> objects = customerService.GetObjectByHQL(order1.OrderDate.ToString());

            foreach (var objectse in objects)
            {
                Console.WriteLine("{0}---{1}",
                    ((Customer)objectse[0]).CustomerId,
                    objectse[objectse.Length-1].ToString());
            }

 

调用者必须知道Object的成员及其类型，才能正确使用查询结果，这样很不好。

改进的做法是：
    创建一个类Object的成员类型，将HQL返回不规则对象转化为通用对象，减低调用着使用的的复杂度

    public class HQLQueryResult
    {
        public Customer Customer { get; set; }
        public DateTime OrderDate { get; set; }
    }

        /// <summary>
        /// 将HQL返回不规则对象转化为通用对象，减低调用着使用的的复杂度
        /// </summary>
        /// <param name="orderDate"></param>
        /// <returns></returns>
        public IList<HQLQueryResult> GetObjectByHQLAndTransferToEasyObject(String orderDate)
        {
            List<HQLQueryResult> results = new List<HQLQueryResult>();
            //Object的成员依次是Customer，OrderDate
            string hql = "select distinct a,b.OrderDate from Customer a " +
                         "inner join a.Orders b where b.OrderDate=:orderDate";
            IList<Object[]> objects =_session.CreateQuery(hql).SetString("orderDate", orderDate).List<Object[]>();
            foreach (var objectse in objects)
            {
               HQLQueryResult tmp = new HQLQueryResult()
               {
                    Customer= ((Customer)objectse[0]),
                    OrderDate = (DateTime)objectse[objectse.Length - 1]
               };


               results.Add(tmp);
            }
            return results;
        }

 

调用者可以这要调用：

            IList<HQLQueryResult> objects = customerService.GetObjectByHQLAndTransferToEasyObject(order1.OrderDate.ToString());

            foreach (var objectse in objects)
            {
                Console.WriteLine("{0}---{1}",
                    objectse.Customer.CustomerId,
                    objectse.OrderDate.ToString());
            }