HIbernate查询

simgsg

浏览: 91391 次
性别:
来自: 北京

最近访客更多访客>>

yf2000

l544295719

tjmljw

marsiii

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (116)

社区版块

存档分类

在hql中关键字不区分大小写，但是属性和类名区分大小写 1、简单属性查询【重要】 * 单一属性查询，返回结果集属性列表，元素类型和实体类中相应的属性类型一致 /** * 单一属性查询 */ public void testQuery1() { Session session = null; try { 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(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } HibernateUtils.closeSession(session); } * 多个属性查询,返回的集合元素是对象数组,数组元素的类型和对应的属性在实体类中的类型一致 数组的长度取决与select中属性的个数 /** * 多个属性查询 */ public void testQuery2() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); //查询多个属性，其集合元素是对象数组 //数组元素的类型和对应的属性在实体类中的类型一致 //数组的长度取决与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]); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } * 如果认为返回数组不够对象化，可以采用hql动态实例化Student对象(需要相应对象有相应的构造函数，以次例子为例，需要明白students有以name和id为标准的构造函数) /** * 返回Student实体对象 */ public void testQuery3() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); //如果认为返回数组不够对象化，可以采用hql动态实例化Student对象 //此时list中为Student对象集合 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()); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } 参见：SimplePropertyQueryTest.java *使用别名 /** * 使用别名 */ public void testQuery4() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); //可以使用别名 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]); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } /** * 使用别名 */ public void testQuery5() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); //可以使用as命名别名 List students = session.createQuery("select s.id, s.name from Student as s").list(); for (Iterator iter=students.iterator(); iter.hasNext();) { Object[] obj = (Object[])iter.next(); System.out.println(obj[0] + "," + obj[1]); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } 2、实体对象查询【重要】 * N + 1问题，在默认情况下，使用query.iterate查询，有可以能出现N+1问题 所谓的N+1是在查询的时候发出了N+1条sql语句 1: 首先发出一条查询对象id列表的sql N: 根据id列表到缓存中查询，如果缓存中不存在与之匹配的数据，那么会根据id发出相应的sql语句 * list和iterate的区别？ * list每次都会发出sql语句，list会向缓存中放入数据，而不利用缓存中的数据 * iterate：在默认情况下iterate利用缓存数据，但如果缓存中不存在数据有可以能出现N+1问题 参见：SimpleObjectQueryTest1.java/SimpleObjectQueryTest2.java import java.util.Iterator; import java.util.List; import org.hibernate.Session; import junit.framework.TestCase; /** * 实体对象查询 * @author Administrator * */ public class SimpleObjectQueryTest2 extends TestCase { public void testQuery1() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); /** * 采用list查询发出一条查询语句，取得Student对象数据、 * * Hibernate: select student0_.id as id1_, student0_.name as name1_, * student0_.createTime as createTime1_, student0_.classesid as classesid1_ * 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()); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } public void testQuery2() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); /** * 出现N+1问题 * * 1：发出查询id列表的sql * Hibernate: select student0_.id as col_0_0_ from t_student student0_ * * N：在依次发出根据id查询Student对象的sql * Hibernate: select student0_.id as id1_0_, student0_.name as name1_0_, * student0_.createTime as createTime1_0_, student0_.classesid as classesid1_0_ * from t_student student0_ where student0_.id=? * */ Iterator iter = session.createQuery("from Student").iterate(); while(iter.hasNext()) { Student student = (Student)iter.next(); System.out.println(student.getName()); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } public void testQuery3() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); List students = session.createQuery("from Student").list(); for (Iterator iter=students.iterator(); iter.hasNext();) { Student student = (Student)iter.next(); System.out.println(student.getName()); } System.out.println("---------------------------------------------"); /** * 不会出现N+1问题 * * 因为list操作已经将Student对象放到了一级缓存中，所以再次使用iterate操作的时候 * 它首先发出一条查询id列表的sql，在根据id到缓存中去数据，只有在缓存中找不到相应的 * 数据时，才会发出sql到数据库中查询 * */ Iterator iter = session.createQuery("from Student").iterate(); while(iter.hasNext()) { Student student = (Student)iter.next(); System.out.println(student.getName()); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } public void testQuery4() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); List students = session.createQuery("from Student").list(); for (Iterator iter=students.iterator(); iter.hasNext();) { Student student = (Student)iter.next(); System.out.println(student.getName()); } System.out.println("---------------------------------------------"); /** * 再次发出查询sql * * 在默认情况下list每次都会向数据库发出查询对象的sql，除非配置查询缓存，所以下面的list操作 * 虽然在一级缓存中已经有了对象数据，但list默认情况下不会利用缓存，而再次发出sql * * 默认情况下，list会向缓存中放入数据，但不会利用数据 * */ students = session.createQuery("from Student").list(); for (Iterator iter=students.iterator(); iter.hasNext();) { Student student = (Student)iter.next(); System.out.println(student.getName()); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } } 3、条件查询【重要】 * 可以采用拼字符串的方式传递参数 * 可以采用？来传递参数（索引从0开始） * 可以采用 :参数名来传递参数 * 如果传递多个参数，可以采用setParamterList方法 * 在hql中可以使用数据库的函数，如：date_format 参见：SimpleConditionQueryTest.java /** * 条件查询 * @author Administrator * */ public class SimpleConditionQueryTest extends TestCase { public void testQuery1() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); //可以拼字符串 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]); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } public void testQuery2() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); // Query query = session.createQuery("select s.id, s.name from Student s where s.name like ?"); // query.setParameter(0, "%1%"); // List students = query.list(); //可以使用？方式传递参数 //参数的索引从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]); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } public void testQuery3() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); //使用：参数名称的方式传递参数值 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]); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } public void testQuery4() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); //使用：参数名称的方式传递参数值 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", 12) .list(); for (Iterator iter=students.iterator(); iter.hasNext();) { Object[] obj = (Object[])iter.next(); System.out.println(obj[0] + "," + obj[1]); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } public void testQuery5() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); //支持in，需要使用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]); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } public void testQuery6() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); //查询2008年2月创建的学生 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]); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } public void testQuery7() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); 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]); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } } 4、hibernate也支持直接使用sql进行查询 参见：SqlQueryTest.java session = HibernateUtils.getSession(); session.beginTransaction(); 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]); } session.getTransaction().commit(); 5、外置命名查询 * 在映射文件中采用<query>标签来定义hql</query> * 在程序中采用session.getNamedQuery()方法得到hql查询串 参见：Student.hbm.xml、NameQueryTest.java public class NameQueryTest extends TestCase { public void testQuery1() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); 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()); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } } <query name="searchStudents"></query> SELECT s FROM Student s where s.id ]]> 6、查询过滤器 * 在映射文件中定义过滤器参数 * 在类的映射中使用这些参数 * 在程序中启用过滤器 参见：Student.hbm.xml、FilterQueryTest.java <?xml version="1.0"?> /p> 
"-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping></hibernate-mapping> <class name="com.bjsxt.hibernate.Student" table="t_student"></class> <id name="id"></id> <generator class="native"></generator> <property name="name"></property> <property name="createTime"></property> <many-to-one name="classes" column="classesid"></many-to-one> <filter name="filtertest" condition="id &lt; :myid"></filter> <query name="searchStudents"></query> 
SELECT s FROM Student s where s.id ]]> <filter-def name="filtertest"></filter-def> <filter-param name="myid" type="integer"></filter-param> /** * 查询过滤器 * @author Administrator * */ public class FilterQueryTest extends TestCase { public void testQuery1() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); session.enableFilter("filtertest") .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()); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } } 7、分页查询【重要】 * setFirstResult()，从0开始 * setMaxResults,每页显示多少条数据 参见：PageQueryTest.java /** * 分页查询 * @author Administrator * */ public class PageQueryTest extends TestCase { public void testQuery1() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); 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()); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } } 8、对象导航查询，在hql中采用 . 进行导航【重要】 参见：ObjectNavQueryTest.java /** * 对象导航查询 * @author Administrator * */ public class ObjectNavQueryTest extends TestCase { public void testQuery1() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); 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); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } } 9、连接查询【重要】 * 内连 * 外连接（左连接/右连接） 参见：JoinQueryTest.java package com.bjsxt.hibernate; import java.util.Iterator; import java.util.List; import org.hibernate.Session; import junit.framework.TestCase; /** * 连接查询 * @author Administrator * */ public class JoinQueryTest extends TestCase { public void testQuery1() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); //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]); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } public void testQuery2() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); 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]); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } public void testQuery3() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); 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]); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } } 10、统计查询【重要】 参见：StatQueryTest.java package com.bjsxt.hibernate; import java.util.Iterator; import java.util.List; import org.hibernate.Session; import junit.framework.TestCase; /** * 统计查询 * @author Administrator * */ public class StatQueryTest extends TestCase { public void testQuery1() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); // List students =session.createQuery("select count(*) from Student").list(); // Long count = (Long)students.get(0); // System.out.println(count); Long count = (Long)session.createQuery("select count(*) from Student").uniqueResult(); System.out.println(count); session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } public void testQuery2() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); 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]); } session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } } 11、DML风格的操作（尽量少用，因为和缓存不同步） 参见：DMLQueryTest.java package com.bjsxt.hibernate; import org.hibernate.Session; import junit.framework.TestCase; /** * DML风格的操作 * @author Administrator * */ public class DMLQueryTest extends TestCase { public void testQuery1() { Session session = null; try { session = HibernateUtils.getSession(); session.beginTransaction(); session.createQuery("update Student s set s.name=? where s.id 
.setParameter(0, "李四") .setParameter(1, 5) .executeUpdate(); session.getTransaction().commit(); }catch(Exception e) { e.printStackTrace(); session.getTransaction().rollback(); }finally { HibernateUtils.closeSession(session); } } }

分享到：