hibernate 全面学习【hibernate hsql语句学习 】

Hibernate hql

* 注意hql的大小写敏感性

1、实体对象的查询，查询的是实体对象的数据【重要】
  * n+1问题，在默认配置的情况下，使用query.iterate()操作，有可能有n＋1问题，所谓
  n+1，指在查询对象数据的时候，发出了n＋1条查询语句。
  1：首先发出了一条查询语句，查询对象的id列表
  n:在迭代访问每个对象的时候，如果缓存中没有对象数据，Hibernate会在此发出一条查询语句，
     查询相应的对象
 
  *List操作与Iterate操作的区别
   list,每次都会发出一条查询语句，查询所有的对象
   iterate,首先发出一条查询语句，查询对象的id列表，然后根据缓存情况，决定
    是否发出更多的查询语句，来查询对象数据
   参见：SimpleObjectQueryTest2.java
  
2、简单属性查询
  * 单个属性查询，返回的结果集属性的列表，其元素和属性的类型一致
  * 多个属性查询,返回的结果集是数组类型，数组的长度和查询的属性数量相关，数组中元素的类型与相应的属性类型相同
  参见：SimplePropertyQueryTest.java
 
3、条件查询【重要】                 
  * 可以拼字符串的形式传递参数
  * 可以用过？来传递参数（注意索引值是从0开始的，跟jdbc不同，jdbc是从1开始的）
  * 可以通过 ：参数名 来传递参数（即命名参数方式）
  * 如果对应的参数值是多个，可以调用setParamterList()方法开传递
  * 在HQL中可以使用数据库的函数，如date_format()
  参见：SimpleConditionQueryTest.java

4、hibernate也支持直接使用原生sql进行查询
  参见：SqlQueryTest.java
 
5、外置的命名查询
  * 在映射文件中通过<query>标签定义hql
  * 在程序中使用session.getNameQuery()方法获得这个查询
  参见：Student.hbm.xml,NameQueryTest.java
 
6、查询过滤器
  * 定义过滤器参数
  * 在类映射文件中使用这些参数
  * 在session中启用过滤器
  参见：Student.hbm.xml,FilterQueryTest.java
 
7、对象导航查询，在HQL语句中，可以使用.的方式进行对象导航【重要】
   参见：ObjectNavQueryTest.java

8、连接查询（在对象里可以直接导航）【重要】
  * 内连接
  * 外连接(左连接/右连接)  
   参见：JionQueryTest
  
9、统计查询
  参见：StatQueryTest.java
 
10、分页查询【重要】
  * 通过query接口中的setFirstResult()和setMaxResults() 进行分页
  参见：PageQueryTest
 
11、DML风格的操作（尽量） 

package com.bjsxt.hibernate; import java.util.Iterator; import java.util.List; import org.hibernate.Session; import junit.framework.TestCase; public class JionQueryTest extends TestCase { /** * 内连接，从Student连接到Classes * */ public void testQuery1() { Session session = null; try { session = HibernateUtils.getSession(); List students = session.createQuery("select c.name, s.name from Student s join s.classes c ") .list(); for (Iterator iter = students.iterator();iter.hasNext();) { Object[] obj = (Object[])iter.next(); System.out.println(obj[0] + ", " + obj[1]); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } /** * 左外连接 * */ public void testQuery2() { Session session = null; try { session = HibernateUtils.getSession(); 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]); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } /** * 右外连接 * */ public void testQuery3() { Session session = null; try { session = HibernateUtils.getSession(); 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]); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } /** * 内连接，从Classes连接到Student * */ private void testQuery4() { } } package com.bjsxt.hibernate; import java.util.Iterator; import java.util.List; import org.hibernate.Session; import junit.framework.TestCase; /** * 外置的命名查询测试 * @author Administrator * */ public class NameQueryTest extends TestCase { public void testQuery1() { Session session = null; try { session = HibernateUtils.getSession(); 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()); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } } package com.bjsxt.hibernate; import java.util.Iterator; import java.util.List; import org.hibernate.Session; import junit.framework.TestCase; /** * 对象导航查询测试 * @author Administrator * */ public class ObjectNavQueryTest extends TestCase { public void testQuery1() { Session session = null; try { session = HibernateUtils.getSession(); List students = session.createQuery("select s.name from Student s where s.classes.id < 2").list(); for (Iterator iter = students.iterator();iter.hasNext();) { String name = (String)iter.next(); System.out.println(name); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } } package com.bjsxt.hibernate; import java.util.Iterator; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; import junit.framework.TestCase; /** * 分页查询测试 * @author Administrator * */ public class PageQueryTest extends TestCase { public void testQuery1() { Session session = null; try { session = HibernateUtils.getSession(); Query query = session.createQuery("from Student"); query.setFirstResult(27); query.setMaxResults(5); List students = query.list(); for (Iterator iter = students.iterator();iter.hasNext();) { Student student = (Student)iter.next(); System.out.println(student.getId() + ", " + student.getName()); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } } package com.bjsxt.hibernate; import java.text.SimpleDateFormat; import java.util.Iterator; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; import junit.framework.TestCase; public class SimpleConditionQueryTest extends TestCase { public void testQuery1() { Session session = null; try { session = HibernateUtils.getSession(); //条件查询，拼字符串 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]); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } public void testQuery2() { Session session = null; try { session = HibernateUtils.getSession(); //条件查询，使用 ？ 的方式传递参数 Query query = session.createQuery("SELECT s.id, s.name FROM Student s WHERE s.name LIKE ?"); //传递参数 //参数的索引是从0开始的 //传递的字符串，无需用''单引号括起来 query.setParameter(0, "%1%"); List students = query.list(); for (Iterator iter = students.iterator();iter.hasNext();) { Object[] obj = (Object[])iter.next(); System.out.println(obj[0] + ", " + obj[1]); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } public void testQuery3() { Session session = null; try { session = HibernateUtils.getSession(); //条件查询，使用 ：参数名称 的方式传递参数 Query query = session.createQuery("SELECT s.id, s.name FROM Student s WHERE s.name LIKE :myname"); //传递参数 query.setParameter("myname", "%1%"); List students = query.list(); for (Iterator iter = students.iterator();iter.hasNext();) { Object[] obj = (Object[])iter.next(); System.out.println(obj[0] + ", " + obj[1]); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } public void testQuery4() { Session session = null; try { session = HibernateUtils.getSession(); //条件查询,因为setParameter方法返回Query接口，所以可以用省略的方式来查询 List students = session.createQuery("SELECT s.id, s.name FROM Student s WHERE s.name LIKE :myname and s.id = :myid") .setParameter("myname", "%1%") .setParameter("myid", 15) .list(); for (Iterator iter = students.iterator();iter.hasNext();) { Object[] obj = (Object[])iter.next(); System.out.println(obj[0] + ", " + obj[1]); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } public void testQuery5() { Session session = null; try { session = HibernateUtils.getSession(); //条件查询,支持in，需要用setParameterList()进行参数传递 List students = session.createQuery("SELECT s.id, s.name FROM Student s WHERE s.id in(:myids)") .setParameterList("myids", new Object[]{1, 3, 5}) .list(); for (Iterator iter = students.iterator();iter.hasNext();) { Object[] obj = (Object[])iter.next(); System.out.println(obj[0] + ", " + obj[1]); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } public void testQuery6() { Session session = null; try { session = HibernateUtils.getSession(); //条件查询,支持in，需要用setParameter进行参数传递 // List students = session.createQuery("SELECT s.id, s.name FROM Student s WHERE " + // "date_format(s.createTime, '%Y-%m') =?") // .setParameter(0, "2007-01") // .list(); List students = session.createQuery("SELECT s.id, s.name FROM Student s WHERE " + "substring(s.createTime, 1, 7) =?") .setParameter(0, "2007-01") .list(); for (Iterator iter = students.iterator();iter.hasNext();) { Object[] obj = (Object[])iter.next(); System.out.println(obj[0] + ", " + obj[1]); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } public void testQuery7() { Session session = null; try { session = HibernateUtils.getSession(); //条件查询,支持in，需要用setParameter进行参数传递 SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); List students = session.createQuery("SELECT s.id, s.name FROM Student s WHERE " + "s.createTime between ? and ? ") .setParameter(0, format.parseObject("2007-01-01")) .setParameter(1, format.parseObject("2007-03-01")) .list(); for (Iterator iter = students.iterator();iter.hasNext();) { Object[] obj = (Object[])iter.next(); System.out.println(obj[0] + ", " + obj[1]); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } } package com.bjsxt.hibernate; import java.util.Iterator; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; import junit.framework.TestCase; /** * 对象查询中的list操作和iterator操作的差异 * @author Administrator * */ public class SimpleObjectQueryTest2 extends TestCase { public void testQueryWithListMethod() { Session session = null; try { session = HibernateUtils.getSession(); /** * 将发出一条查询语句，获取Student的集合数据 * select student0_.id as id1_, student0_.name as name1_, * student0_.createTime as createTime1_, student0_.classid as classid1_ * from t_student student0_ */ List students = session.createQuery("from Student").list(); for (Iterator iter = students.iterator();iter.hasNext();) { Student student = (Student)iter.next(); System.out.println(student.getName()); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } public void testQueryWithIterateMethod() { Session session = null; try { session = HibernateUtils.getSession(); //先发出查询id的列表语句 //select student0_.id as col_0_0_ from t_student student0_ //再依次发出查询对象的sql（根据id） //select student0_.id as id1_0_, student0_.name as name1_0_, //student0_.createTime as createTime1_0_, student0_.classid as classid1_0_ //from t_student student0_ where student0_.id=? Query query = session.createQuery("from Student"); Iterator students = query.iterate(); while (students.hasNext()) { Student student = (Student)students.next(); System.out.println(student.getName()); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } public void testQueryWithListAndIterate() { Session session = null; try { session = HibernateUtils.getSession(); Query query = session.createQuery("from Student"); List students = query.list(); for (Iterator iter = students.iterator();iter.hasNext();) { Student student = (Student)iter.next(); System.out.println(student.getName()); } //如果使用iterate进行查询 //因为list操作已经将对象加载到了session的一级缓存，所以 //再使用iterate操作的时候，它先会发出查询id列表的查询语句 //再根据id到缓存中获取相关的数据 //只有再缓存中找不到相关数据的情况下，才会再次发出sql进行查询 Iterator studentsIter = query.iterate(); while (studentsIter.hasNext()) { Student student = (Student)studentsIter.next(); System.out.println(student.getName()); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } public void testQueryWithListAndList() { Session session = null; try { session = HibernateUtils.getSession(); Query query = session.createQuery("from Student"); List students = query.list(); for (Iterator iter = students.iterator();iter.hasNext();) { Student student = (Student)iter.next(); System.out.println(student.getName()); } //再次发出发出sql //在默认情况下，list每次都会向数据库发出查询对象数据的sql， //除非配置了查询缓存，所以下面的list()操作，虽然在session已经有了 //对象缓存数据，但list()并不理会这个中缓存，而再次发出查询语句进行查询 students = query.list(); for (Iterator iter = students.iterator();iter.hasNext();) { Student student = (Student)iter.next(); System.out.println(student.getName()); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } } package com.bjsxt.hibernate; import java.util.Iterator; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; import junit.framework.TestCase; /** * 对象查询的基本测试 * @author Administrator * */ public class SimpleObjectQueryTest extends TestCase { public void testQuery1() { Session session = null; try { session = HibernateUtils.getSession(); //可以忽略select语句 List students = session.createQuery("from Student").list(); for (Iterator iter = students.iterator();iter.hasNext();) { //返回值是Student对象 Student student = (Student)iter.next(); System.out.println(student.getName()); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } public void testQuery2() { Session session = null; try { session = HibernateUtils.getSession(); //可以忽略select语句,表可以加别名 List students = session.createQuery("from Student s").list(); for (Iterator iter = students.iterator();iter.hasNext();) { //返回值是Student对象 Student student = (Student)iter.next(); System.out.println(student.getName()); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } public void testQuery3() { Session session = null; try { session = HibernateUtils.getSession(); //可以忽略select语句,表可以加别名 List students = session.createQuery("from Student as s").list(); for (Iterator iter = students.iterator();iter.hasNext();) { //返回值是Student对象 Student student = (Student)iter.next(); System.out.println(student.getName()); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } public void testQuery4() { Session session = null; try { session = HibernateUtils.getSession(); //使用select关键字时，必须定义别名 List students = session.createQuery("select s from Student s").list(); for (Iterator iter = students.iterator();iter.hasNext();) { //返回值是Student对象 Student student = (Student)iter.next(); System.out.println(student.getName()); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } public void testQuery5() { Session session = null; try { session = HibernateUtils.getSession(); //不支持select * ....这样的HQL查询语句 List students = session.createQuery("select * from Student").list(); for (Iterator iter = students.iterator();iter.hasNext();) { //返回值是Student对象 Student student = (Student)iter.next(); System.out.println(student.getName()); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } } package com.bjsxt.hibernate; import java.util.Iterator; import java.util.List; import org.hibernate.Session; import junit.framework.TestCase; /** * 简单的属性查询 * @author Administrator * */ public class SimplePropertyQueryTest extends TestCase { /** * 单一属性查询 * */ public void testQuery1() { Session session = null; try { session = HibernateUtils.getSession(); 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); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } /** * 多个属性的查询 * */ public void testQuery2() { Session session = null; try { session = HibernateUtils.getSession(); //查询多个属性，其集合元素是对象数组 //数组元素的类型，跟实体类的属性的类型相关 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]); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } public void testQuery3() { Session session = null; try { session = HibernateUtils.getSession(); //查询多个属性，其集合元素是对象数组 //数组元素的类型，跟实体类的属性的类型相关 //更参见的用法，给对象起个别名，然后通过别名来引用属性值 List students = session.createQuery("select s.id, s.name from Student s").list(); for (Iterator iter = students.iterator();iter.hasNext();) { Object[] obj = (Object[])iter.next(); System.out.println(obj[0] + ", " + obj[1]); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } } package com.bjsxt.hibernate; import java.util.Iterator; import java.util.List; import org.hibernate.SQLQuery; import org.hibernate.Session; import junit.framework.TestCase; /** * 嵌入原生sql测试 * @author Administrator * */ public class SqlQueryTest extends TestCase { public void testQuery1() { Session session = null; try { session = HibernateUtils.getSession(); SQLQuery sqlQuery = session.createSQLQuery("select * from t_student"); List students = sqlQuery.list(); for (Iterator iter = students.iterator();iter.hasNext();) { Object[] obj = (Object[])iter.next(); System.out.println(obj[0] + ", " + obj[1]); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } } package com.bjsxt.hibernate; import java.util.Iterator; import java.util.List; import org.hibernate.Session; import junit.framework.TestCase; public class StatQueryTest extends TestCase { public void testQuery1() { Session session = null; try { session = HibernateUtils.getSession(); List students = session.createQuery("select count(*) from Student").list(); Long count = (Long)students.get(0); System.out.println(count); // for (Iterator iter = students.iterator();iter.hasNext();) { // Long count = (Long)iter.next(); // System.out.println(count); // } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } public void testQuery2() { Session session = null; try { session = HibernateUtils.getSession(); List students = session.createQuery("select c.id, c.name, count(s) from Student s join " + "s.classes c group by c.id, c.name").list(); for (Iterator iter = students.iterator();iter.hasNext();) { Object[] obj = (Object[])iter.next(); System.out.println(obj[0] + ", " + obj[1] + ", " + obj[2]); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } } package com.bjsxt.hibernate; import java.util.Iterator; import java.util.List; import org.hibernate.Session; import junit.framework.TestCase; /** * 查询过滤器测试 * @author Administrator * */ public class FilterQueryTest extends TestCase { public void testQuery1() { Session session = null; try { session = HibernateUtils.getSession(); session.enableFilter("idfilter") .setParameter("myid", 10); List students = session.createQuery("from Student") .list(); for (Iterator iter = students.iterator();iter.hasNext();) { Student student = (Student)iter.next(); System.out.println(student.getName()); } }catch(Exception e) { e.printStackTrace(); }finally { HibernateUtils.closeSession(session); } } } package com.bjsxt.hibernate; import java.util.Iterator; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; import junit.framework.TestCase; public class DMLQueryTest extends TestCase { public void testQuery1() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); /** * 使用这种风格的操作，将会造成内存中的对象与数据库中的数据不同步，所以 * 建议尽量少使用 */ Query updateQuery = session.createQuery("update Student a set a.name=? where a.id in(:ids)"); updateQuery.setParameter(0, "张三"); updateQuery.setParameterList("ids", new Object[]{1, 2}); updateQuery.executeUpdate(); session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } }