hibernate hql

一，基础知识

     hql中关键字不区分大小写，但属性 和类名 区分大小写

 

二，简单属性查询

List students = session.createQuery("select name from Student ").list();

List students = session.createQuery("select id, name from Student ").list();

List students = session.createQuery("select new Student(id, name) from Student ").list();

List students = session.createQuery("select s.id, s.name from Student s ").list();

List students = session.createQuery("select s.id, s.name from Student as s ").list();

 

三，实体对象查询

List students = session.createQuery("from Student ").list();

List students = session.createQuery("from Student s ").list();

List students = session.createQuery("from Student as s ").list();

List students = session.createQuery("select s from Student as s ").list();

List students = session.createQuery("select * from Student ").list();   ---不支持“*”

 

List students = session.createQuery("from Student").list();               ----不支持缓存

Iterator students = session.createQuery("from Student").iterate();  ----出现N+1问题（如果没缓存）

 

四，条件查询

List students = session.createQuery("select s.id, s.name from Student s where s.name like '%1%' ").list();

List students = session.createQuery("select s.id, s.name from Student s where s.name like ? ").setParameter(0 , "%1% ").list();

List students = session.createQuery("select s.id, s.name from Student s where s.name like :myname ").setParameter("myname ", "%1% ").list();

List students = session.createQuery("select s.id, s.name from Student s where s.id in(:myids) ").setParameterList ("myids ", new Object[]{1, 2, 3, 4, 5} ).list();

List students = session.createQuery("select s.id, s.name from Student s where date_format(s.createTime, '%Y-%m')=? ").setParameter(0 , "2008-02 ").list();

List students = session.createQuery("select s.id, s.name from Student s where s.createTime between ? and ? ").setParameter(0 , sdf.parse("2008-01-10 00:00:00 ")) .setParameter(1 , sdf.parse("2008-02-15 23:59:59 ")).list();

 

五，原生sql查询

List students = session.createSQLQuery("select * from t_student ").list();

 

六，外置命名查询

<query name="searchStudents">
	<![CDATA[
		SELECT s FROM Student s where s.id<?
	]]>
</query>

 

List students = session.getNamedQuery ("searchStudents ").setParameter(0, 10).list();

 

七，查询过滤器

<class name="Student" table="t_student">
	<filter name="filtertest" condition="id &lt; :myid"/>
</class>

<filter-def name="filtertest">
	<filter-param name="myid" type="integer"/>
</filter-def>

 

session.enableFilter("filtertest ").setParameter("myid ", 10);

 

八，分页查询

List students = session.createQuery("from Student").setFirstResult (0 ).setMaxResults (20 ).list();

九，对象导航查询（内联查询）

List students = session.createQuery("select s.name from Student s where s.classes .name like '%1%' ").list();

 

十，连接查询

   10.1内连接

          List students = session.createQuery("select c.name, s.name from Student s join s.classes c ").list();
          List students = session.createQuery("select c.name, s.name from Student s inner join s.classes c ").list();

   10.2外连接（左连接，右连接）

          List students = session.createQuery("select c.name, s.name from Classes c left join c.students s ").list();

          List students = session.createQuery("select c.name, s.name from Classes c right join c.students s ").list();

 

十一，统计查询

List students =session.createQuery("select count(*) from Student ").list ();

Long count = (Long)session.createQuery("select count(*) from Student ").uniqueResult ();

 

List students =session.createQuery("select c.name, count(s) from Student s join s.classes c group by c.name order by c.name ").list();

十二，DML风格查询（尽量少用它，因为它跟缓存不同步 ）

session.createQuery ("update Student s set s.name=? where s.id < ? ")
                    .setParameter(0, "李四")
                    .setParameter(1, 5)
                    .executeUpdate();