Hibernate中的HQL根本语法

? HQL是个中立的语言,即它跟任何数据库都没关系，你如果使用HQL的话通过数据库方言都会翻译成相应的数据库的SQL语句,包括[排序,子查询,调用SQL中的函数].
? HQL中关键字是不区分大小写的，但是属性和类型是区分大小写的。
?? 1>.简单属性查询
????? 1>).单一属性查询,返回结果集属性列表,元素类型和实体类中相应的属性类型一致。如：
????????? session = HibernateUtils.getSession();
????????? session.beginTransaction();
???????????
????????? //返回结果集属性列表，元素类型和实体类中相应的属性类型一致
?????????? List students = session.createQuery("select name from Student").list();
?????????? for (Iterator iter=students.iterator(); iter.hasNext();) {
????????????????? String name = (String)iter.next();
????????????????? System.out.println(name);
?????????? }
????? session.getTransaction().commit();
????? 2>).多个属性查询,返回的集合元素是对象数组，数组元素的类型和对
????????? 应的属性在实体类中的类型一致，数组的长度取决于select中属性的个数。如：
????????? List students = session.createQuery("select id, name from Student").list();
???????? for (Iterator iter=students.iterator(); iter.hasNext();) {
??????????????? Object[] obj = (Object[])iter.next();
??????????????? System.out.println(obj[0] + "," + obj[1]);
????????? }
????? 3>).如果认为返回数组不够对象化，可以采用HQL动态实例化Student对象，
????????? 但是Strudent类中必须提供相应的构造函数。如：
????????? List students = session.createQuery("select new Student(id, name) from Student").list();
????????? for (Iterator iter=students.iterator(); iter.hasNext();) {
??????????????? Student student = (Student)iter.next();
??????????????? System.out.println(student.getId() + "," + student.getName());
????????? }
??? 2>.实体对象查询
?????? 1>).返回Student对象的集合,可以忽略select,表可以使用别名,别名可以用as也可以不用。如：
????????? List students = session.createQuery("from Student s").list();
????????? //List students = session.createQuery("from Student as s").list();
???????? for (Iterator iter=students.iterator(); iter.hasNext();) {
???????????????? Student student = (Student)iter.next();
???????????????? System.out.println(student.getName());
?????????? }
?????? 2>).返回Student对象的集合,使用select查询实体对象，必须采用别名。如：
????????? List students = session.createQuery("select s from Student as s").list();
????????? for (Iterator iter=students.iterator(); iter.hasNext();) {
??????????????? Student student = (Student)iter.next();
???????????????? System.out.println(student.getName());
????????? }
?????? 3>).不支持select * from .....这样的查询语句。如：下面的语句是错误的
?????????? List students = session.createQuery("select * from Student").list();
?????? 4>).list和iterator的区别?
?????????? 我们知道,session.createQuery()得到个Query,而从Hibernate API中我们可
?????????? 以发现Query有两个方法list()和iterator()用于返回结果集。它们的区别在
?????????? 于：
?????????? *list每次都会发出sql语句，list会向缓存中放入数据，而不利用缓存中的数据
?????????? *iterator在默认情况下利用缓存数据,但如果缓存中不存在数据有可能出现N+1问题
??????? 5>).N+1问题
??????????? 在默认情况下,使用query.iterator()查询，有可能出现N+1问题
??????????? 1: 首先发出一条查询对象id列表的sql语句
??????????? N: 根据id列表到缓存中查询,如果缓存中不存在与之匹配的数据,那么会根据
?????????????? id发出相应的sql语句
??????????? 如：
??????????? Iterator iter = session.createQuery("from Student").iterate();
??????? while(iter.hasNext()) {
??????????? Student student = (Student)iter.next();
??????????? System.out.println(student.getName());
??????? }
??? 3>.条件查询
?????? 1>).可以采用拼字符串的方式传递参数。如：
?????????????? List students = session.createQuery("select s.id, s.name from Student s where s.name like '%1%'").list();
?????????????? for (Iterator iter=students.iterator(); iter.hasNext();) {
????????????????????? Object[] obj = (Object[])iter.next();
?????????????????????? System.out.println(obj[0] + "," + obj[1]);
??????????????? }
?????? 2>).可以使用？方式传递参数,参数的索引从0开始;传递的参数值如果是字符型的，不用
?????????? 单引号引起来;可以使用方法链编程。如：
??????????? List students = session.createQuery("select s.id, s.name from Student s where s.name like ?")
??????????????????????????????????????????????? .setParameter(0, "%1%")
??????????????????????????????????????????????? .list();
?????????? for (Iterator iter=students.iterator(); iter.hasNext();) {
?????????????????????? Object[] obj = (Object[])iter.next();
?????????????????????? System.out.println(obj[0] + "," + obj[1]);
?????????? }
?????? 3>).可以使用 ：参数名称 的方式传递参数值。如：
????????????? List students = session.createQuery("select s.id, s.name from Student s where s.name like :myname")
?????????????????????????????????????????????????? .setParameter("myname", "%1%")
?????????????????????????????????????????????????? .list();
????????????? for (Iterator iter=students.iterator(); iter.hasNext();) {
???????????????????????? Object[] obj = (Object[])iter.next();
???????????????????????? System.out.println(obj[0] + "," + obj[1]);
???????????? }
?????? 4>).如果传递多个参数，可以使用setParameterList方法。如：
?????????? 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();
?????????? for (Iterator iter=students.iterator(); iter.hasNext();) {
?????????????????? Object[] obj = (Object[])iter.next();
?????????????????? System.out.println(obj[0] + "," + obj[1]);
?????????? }
?????? 5>).在HQL中可以使用数据库的函数,像mysql中的date_format。如：
???????????? List students = session.createQuery("select s.id, s.name from Student s where date_format(s.createTime, '%Y-%m')=?")
????????????????????????????????????????????????? .setParameter(0, "2008-02")
????????????????????????????????????????????????? .list();
????????????? for (Iterator iter=students.iterator(); iter.hasNext();) {
???????????????????? Object[] obj = (Object[])iter.next();
????????????????????? System.out.println(obj[0] + "," + obj[1]);
?????????????? }
?????? 6>).查询某个日期段的数据。如：
?????????? SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
?????????? //查询2008-01-10到2008-02-15创建的学生
?????????? 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();
????????????? for (Iterator iter=students.iterator(); iter.hasNext();) {
???????????????????? Object[] obj = (Object[])iter.next();
????????????????????? System.out.println(obj[0] + "," + obj[1]);
??????????????? }

??? 4>.Hibernate也支持直接使用sql查询。如：
????????? List students = session.createSQLQuery("select * from t_student").list();
????????? for (Iterator iter=students.iterator(); iter.hasNext();) {
??????????????? Object[] obj = (Object[])iter.next();
??????????????? System.out.println(obj[0] + "," + obj[1]);
????????? }
??? 5>.外置命名查询,主要完成hql语句和程序的解耦合.
?????? 1>).在映射文件中采用<query>标签来定义HQL语句。如：
??????????? <query name="searchStudents">
??????????????? <![CDATA[
????????????????????? SELECT s FROM Student s where s.id<?
???????????????? ]]>
??????????? </query>
?????? 2>).在程序中采用session.getNamedQuery()方法得到HQL查询串。如：
????????????? List students = session.getNamedQuery("searchStudents")
?????????????????????????????????????????????????? .setParameter(0, 10)
?????????????????????????????????????????????????? .list();
????????????? for (Iterator iter=students.iterator(); iter.hasNext();) {
???????????????????? Student student = (Student)iter.next();
???????????????????? System.out.println(student.getName());
?????????????? }
???? 6>.查询过滤器
?????? 1>).在影射文件中定义过滤器参数。如：
??????? <filter-def name="filtertest">
??????????????? <filter-param name="myid" type="integer"/>
??????? </filter-def>???
?????? 2>).在类的影射中使用这些参数。如：
???????? <filter name="filtertest" condition="id &lt; :myid"/><!--&lt;转义字符"<"-->
?????? 3>).在程序中启用过滤器。如：
???????? session.enableFilter("filtertest")
???????????????????? .setParameter("myid", 10);???
???????? List students = session.createQuery("from Student").list();//会在该session中所有的查询后自动加上过滤器中的条件
???????? for (Iterator iter=students.iterator(); iter.hasNext();) {
?????????????? Student student = (Student)iter.next();
?????????????? System.out.println(student.getName());
???????? }
?????? 参见:Student.hbm.xml,FilterQueryTest.java
???? 7>.分页查询
??????? 1>).setFirstResult()从0开始
??????? 2>).setMaxResults每页显示多少条数据。如：
????????????? List students = session.createQuery("from Student")
?????????????????????????????????????????????????? .setFirstResult(1)
?????????????????????????????????????????????????? .setMaxResults(2)
?????????????????????????????????????????????????? .list();
????????????? for (Iterator iter=students.iterator(); iter.hasNext();) {
???????????????????? Student student = (Student)iter.next();
???????????????????? System.out.println(student.getName());
???????????? }
???? 8>.对象导航查询
??????? Hibernate支持对象的，因此可以用对象导航查询。如：
??????? Student类总有Classes类型的属性classes，而Classes类有name属性。我们通过classes的name来查询student:
??????? List students = session.createQuery("select s.name from Student s where s.classes.name like '%1%'").list();
??????? for (Iterator iter=students.iterator(); iter.hasNext();) {
???????????? String name = (String)iter.next();
???????????? System.out.println(name);
?????? }
???? 9>.连接查询
??????? 1>).内连接:只是把两边都有的记录查出来,可以省略inner，并且只要影射文件中已经设好了表之间的关系，无须用on表示查询条件。如：
?????????? //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();
?????????? for (Iterator iter=students.iterator(); iter.hasNext();) {
??????????????? Object[] obj = (Object[])iter.next();
??????????????? System.out.println(obj[0] + "," + obj[1]);
?????????? }
??????? 2>).外左连接:把左边表的所有记录都显示出来。如：
???????????? List students = session.createQuery("select c.name, s.name from Classes c left join c.students s").list();
???????????? for (Iterator iter=students.iterator(); iter.hasNext();) {
????????????????? Object[] obj = (Object[])iter.next();
????????????????? System.out.println(obj[0] + "," + obj[1]);
???????????? }
??????? 3>).外右连接:把右边表的所有记录都显示出来。如：
???????????? List students = session.createQuery("select c.name, s.name from Classes c right join c.students s").list();
????????????? for (Iterator iter=students.iterator(); iter.hasNext();) {
?????????????????? Object[] obj = (Object[])iter.next();
?????????????????? System.out.println(obj[0] + "," + obj[1]);
???????????? }
???? 10>.统计查询
??????? 1>).count。如：
??????????? Long count = (Long)session.createQuery("select count(*) from Student").uniqueResult();//返回的是单条记录
??????????? System.out.println(count);
??????? 2>).group by。如：
??????????? 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();
?????????? for (Iterator iter=students.iterator(); iter.hasNext();) {
??????????????? Object[] obj = (Object[])iter.next();
??????????????? System.out.println(obj[0] + ", " + obj[1]);
?????????? }
???? 11>.DML风格的操作(尽量少用,因为缓存不同步)
??????? update和delete只会更新数据库中的数据,而缓存中不会改，此时如果取得该数据还是缓存中的,而不会是更新后的，因此缓
??????? 存中的数据就成了脏数据了。如果批量更新的话，还会给内存带来很大压力。如：
??????? session.createQuery("update Student s set s.name=? where s.id < ?")
??????????????????? .setParameter(0, "李四")
??????????????????? .setParameter(1, 5)
??????????????????? .executeUpdate();