注意:HQL语言,是基于对象进行查询的,不是基于数据库的表。
1、基本的HQL查询
1.1 使用HQL查询的一般步骤
[java] view plain copy public void test01() { Session session = HibernateUtils.openSession(); Query query = session.createQuery("from Student"); List<Student> stus = query.list(); for (Student stu : stus) { System.out.println(stu); } }
1.2 在HQL中,不能使用 select * from Student ,但是可以使用别名 select stu from Student stu
[java] view plain copy public void test02() { Session session = HibernateUtils.openSession(); Query query = session.createQuery("select stu from Student stu"); List<Student> stus = query.list(); for (Student stu : stus) { System.out.println(stu); } }
1.3 链式查询的用法
[java] view plain copy public void test03() { Session session = HibernateUtils.openSession(); List<Student> stus = session.createQuery(" from Student ") .list(); for (Student stu : stus) { System.out.println(stu); } }
1.4 基于?的查询 (JDBC的下标从1开始,Hibernate的下标从0开始)
[java] view plain copy public void test04() { Session session = HibernateUtils.openSession(); Student stu = (Student) session .createQuery(" from Student where id = ? ").setParameter(0, 1) .uniqueResult(); System.out.println(stu); }
1.5 基于别名的查询 id = :id (使用Map进行,别名的封装)
[java] view plain copy public void test05() { Session session = HibernateUtils.openSession(); // 利用一个Map对,用到的别名进行封装 Map<String, Object> map = new HashMap<String, Object>(); map.put("id", 1); map.put("age", 18); // 查询 Student stu = (Student) session .createQuery(" from Student where id = :id and age = :age")// .setParameter("id", map.get("id"))// .setParameter("age", map.get("age"))// .uniqueResult(); // 输出查询结果 System.out.println(stu); }
2、常用的HQL查询
2.1 查询一个表里面的记录数(当查询结果唯一时,可以使用 uniqueResult( ) )
[html] view plain copy public void test06() { Session session = HibernateUtils.openSession(); Long countRecord = (Long) session.createQuery( "select count(*) from Student").uniqueResult(); int count = countRecord.intValue(); System.out.println(count); }
2.2 查询一个表中的某几个字段,返回的是一个 Object [ ] 的数组(投影查询) 按照年龄的分组查询
[java] view plain copy public void test07() { Session session = HibernateUtils.openSession(); // 按照年龄的分组查询 List<Object[]> stus = session.createQuery( "select stu.age , count(*) from Student stu group by stu.age") .list(); // 输出查询结果 for (Object[] obj : stus) { System.out.println("年龄:" + obj[0] + " 的人数为:" + obj[1]); } }
2.3 DTO 数据传输对象的使用,
2.3.1 要将查询出来的列,利用 as 关键字进行重新的命名
[sql] view plain copy select stu.age as age , count(*) as count from Student stu group by stu.age
2.3.2 使用,setResultTransformer(Transformers.aliasToBean(AgeGroup.class)),进行结果的转化
2.3.3 DTO数据传输对象 ,AgeGroup.java的代码
[java] view plain copy public class AgeGroup { private int age; private Long count; public AgeGroup() { } // get set @Override public String toString() { return "AgeGroup [age=" + age + ", count=" + count + "]"; } }
2.3.4 使用DTO,查询的代码如下
[java] view plain copy public void test08() { Session session = HibernateUtils.openSession(); // 按照年龄的分组查询 List<AgeGroup> stus = session .createQuery(// "select stu.age as age , count(*) as count from Student stu group by stu.age")// .setResultTransformer(Transformers.aliasToBean(AgeGroup.class))// .list(); // 输出查询结果 for (AgeGroup stu : stus) { System.out .println("年龄:" + stu.getAge() + " 的人数为:" + stu.getCount()); } }
2.4 实现分页查询
[java] view plain copy public void test09() { Session session = HibernateUtils.openSession(); List<Student> stus = session.createQuery(" from Student ")// .setFirstResult(0)// .setMaxResults(2)// .list(); for (Student stu : stus) { System.out.println(stu); } }
2.5 利用 is null 判断数据库中,字段是否为空(HQL和SQL一样,不能使用=来判断null)
[sql] view plain copy where stu.classroom is null
2.6 可以使用 in 来设置基于列表的查询,但是要使用别名查询 (使用 in 的查询应该在其他查询之后)
[java] view plain copy public void test10() { Session session = HibernateUtils.openSession(); // 查询 List<Student> stus = session .createQuery( " select stu from Student stu where stu.name like ? and stu.id in ( :ids )")// .setParameter(0, "%1%")// .setParameterList("ids", new Integer[] { 1, 2 })// .list(); // 输出查询结果 for (Student stu : stus) { System.out.println(stu); } }
3、HQL的连接查询
3.1 如果一个对象中存在相应的导航对象,可以直接利用导航完成表的连接查询
[java] view plain copy public void test01() { Session session = HibernateUtils.openSession(); Query query = session.createQuery("select stu from Student stu where stu.classRoom.id = stu.id "); List<Student> stus = query.list(); for (Student stu : stus) { System.out.println(stu); } }
3.2 由于使用对象的导航完成的连接查询是基于Cross Join的,效率比较低。随意使用Join 来完成连接查询
[java] view plain copy public void test02() { Session session = HibernateUtils.openSession(); Query query = session .createQuery("select stu from Student stu join stu.classRoom cla where cla.id = ? "); List<Student> stus = query.setParameter(0, 1).list(); for (Student stu : stus) { System.out.println(stu); } }
转载自:http://blog.csdn.net/zbw18297786698/article/details/51993850