Hibernate 多种查询

1.HQL查询：Hibernate Query Language.

           特点：1.与SQL 相似, SQL 中的语法基本上都可以使用。

                      2.SQL查询的是表和表中的列; HQL 查询的是对象。

                      3.HQL的关键字不区分大小写，类名和属性名是区分大小写的。

                      4.select 可以省略

           1.简单的查询：Employee 为实体名而不是数据库表名(面向对象性)
            hql="from Employee"

            hql="from Employee as e"//使用别名

            hql="from Employee e"// as 可以省略

           2.带上过滤条件：where

             hql="from Employee where id <10"

             hql="from Employee e where e.id<10"

             hql="from Employee e where e.id<10 and e.id>5"

          3.带上排序条件:order by

            hql="from Employee e where e.id<10 order by e.name";

            hql="from Employee e where e.id<10 order by e.name desc";

            hql="from Employee e where e.id<10 order by e.name desc, id asc";

           4.制定select子句（不可以select *）

            hql="select e from Employee e";//相当于"from Employee e"

            hql="select e.name from Employee e";//只查询一个列，返回的集合元素就是这个属性的类型

            hql="select e.name,e.id from Employee e";//查询多个列的元素类型是Object 数组

            hql="select new Employee(e.id,e.name) from Employ e"//可以使用new语法，把查询的部分属性封装到对象中      

            5.查询：list uniqueResult ,分页

            Query query=session.createQuery("from Employee e where id<3");

            query.setFirstResult(0);

            query.setMaxResults(10);//等同于limit 0,10

            //两种查询结果 list ,uniqueResult

              List list=query.list();// list

                      Employee employee=(Employee)query.uniqueResult();//查询的结果是唯一的一个结果

若有多个结果，会抛异常

              6.方法链

              List<Employee> list=session.createQuery("from Employee").setFirstResult(0).setMaxResults(10).list();

                      7.聚焦(合)函数 count(),max(),min(),sum()

               hql="select count(*) from Employee"//返回结果是long型的

               hql="select min(id) from Employee "//返回的结果是id属性的类型

             8.分组：Gruop By ....Having

             hql="select e.name,count(e.id) from Employee e group by e.name"

             hql="select e.name,count(e.id) from Employee e group by e.name having count(e.id)>1"

                      hql="select e.name,count(e.id) from Employee e where id<9 group by e.name having count(e.id)>1 order by count(e.id) desc"

                      hql="select e.name,count(e.id) as c from Employee e where id<9 group by e.name having count(e.id)>1 order by c desc"

              9.连接查询：(仔细品味)

               内联：inner 可以省略

                       hql="select e.id,e.name,d.name from Employee e (inner) join  e.department d"

               左外联：outer 可以省略   右(right)外联同理

               hql="select e.id,e.name,d.name from Employee e left outer join e.department d"

               //简便：

               hql="select e.id, e.name,e.department.name from Employee e"

            10.查询使用参数

               1.?

                hql="from Employee where id between ? and ?"

        List list2 = session.createQuery(hql)
	  .setParameter(0, 5)// 设置参数，第1个参数的索引为0。
	  .setParameter(1, 15)//
	  .list();

              2.变量名

             hql="from Employee e where id between :min and :max";

       List list3 = session.createQuery(hql)
	 .setParameter("max", 15)//
	 .setParameter("min", 5)//
	 .list();

             3.当参数是集合时，使用setParmeterList(),设置参数值

             hql="from Employee where id in(:ids)";

             List list=session.createQuery(sql).setParmeterList("ids",new Object[]{1,2,3}).list;

                   11.update ,delete ,不会通知session缓存

             update

             int result=sess.createQuery("update Employee e set e.name=? where id >15")

.setParmeter(0,"d").executeUpdate();//返回int型结果

                    delete

                    int result=sess.createQuery("delete Employee e where id >15")

.setParmeter(0,"d").executeUpdate();//返回int型结果

看一看呦