一、HQL简介 HQL(Hibernate Query Language)是Hibernate特有的面向对象操作的查询语言,提供了类似于SQL的查询方式,更加面向对象的封装,是Hibernate官方推荐的标准查询方式。HQL的语法和SQL基本一样,不同的是HQL是面向对象的查询,查询的是对象 ,以及对象的属性 。HQL的关键字不区分大小写,但是对象,对象属性区分大小写。基本语句如下:
1 2 3 4 5 6 SELECT 别名/属性名/表达式FROM 实体 AS 别名WHERE 过滤条件GROUP BY 分组条件HAVING 分组后结果的过滤条件ORDER BY 排序条件
二、例子 1. 查询所有的客户 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 @Test public void test1 () { Session session = HibernateUtils.openSession(); Query query = session.createQuery("FROM Customer" ); List<Customer> list = query.list(); for (Customer customer : list) { System.out.println(customer); } session.close(); }
2. 选择查询 根据具体的条件选择查询,也就是条件查询。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 @Test public void test2 () { Session session = HibernateUtils.openSession(); Query query = session.createQuery("FROM Customer WHERE id = ?" ); query.setParameter(0 , 9 ); Customer customer = (Customer) query.uniqueResult(); System.out.println(customer); session.close(); }
3. 投影查询 SELECT * FROM TABLE_NAME WHERE condition
这样的查询返回的结果的表结构和原来的表结构是相同的,也就是查询所有的列。投影查询 就是不返回所有的列,而是只返回指定的列,如 SELECT ID,NAME FROM USER WHERE CONDICTION
返回id以及name两个列。
投影查询-方法1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 @Test public void test3 () { Session session = HibernateUtils.openSession(); Query query = session.createQuery("select c.id, c.name from Customer c" ); List<Object[]> list = query.list(); for (Object[] objects : list) { for (Object object : objects) { System.out.println(object); } System.out.println("------分割线------" ); } session.close(); }
投影查询-方法2
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 @Test public void test4 () { Session session = HibernateUtils.openSession(); Query query = session.createQuery("select new Customer(c.id, c.name) from Customer c" ); List<Customer> list = query.list(); for (Customer customer : list) { System.out.println(customer); } session.close(); }
4. 排序 1 2 3 4 5 6 7 8 9 10 11 12 13 14 @Test public void test5 () { Session session = HibernateUtils.openSession(); Query query = session.createQuery("from Customer c order by c.id desc" ); List<Customer> list = query.list(); for (Customer customer : list) { System.out.println(customer); } session.close(); }
5. 分页 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 @Test public void test6 () { Session session = HibernateUtils.openSession(); Query query = session.createQuery("from Customer c order by c.id desc" ); query.setFirstResult(1 ); query.setMaxResults(2 ); List<Customer> list = query.list(); for (Customer customer : list) { System.out.println(customer); } session.close(); }
6. 聚合函数 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 @Test public void test7 () { Session session = HibernateUtils.openSession(); Query query = session.createQuery("select count (*) from Customer" ); Long count = (Long) query.uniqueResult(); System.out.println("客户表的总记录数:" + count); query = session.createQuery("select avg (c.id) from Customer c" ); Double id_avg = (Double) query.uniqueResult(); System.out.println("客户表id的平均值:" + id_avg); session.close(); }
7. 分组查询 比如有一张表如下,需要按照customer_id分组,查询一个customer_id有多少个记录:
SQL语句:
1 2 3 4 5 6 select o.`customer_id` , count (*) from t_order o group by customer_id;
结果是:
HQL查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 @Test public void test8 () { Session session = HibernateUtils.openSession(); Query query = session.createQuery("" + "select " + "o.customer.name, count(o.customer.id)" + "from " + "Order o " + "group by " + "o.customer" ); List<Object[]> list = query.list(); for (Object[] objects : list) { for (Object object : objects) { System.out.println(object); } System.out.println("---------" ); } session.close(); }
8. 连接查询
交叉连接,等效笛卡尔积
1 2 3 4 5 6 7 8 9 10 11 12 13 14 @Test public void test9 () { Session session = HibernateUtils.openSession(); session.getTransaction().begin(); List<Object[]> list = session.createQuery("from Customer ,Order " ).list(); for (Object[] objects : list) { System.out.println(objects[0 ] + ":" + objects[1 ]); } session.getTransaction().commit(); session.close(); }
隐式内连接,在笛卡尔积的基础上加过滤条件,根据共有的列的值匹配两个表的行。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 @Test public void test10 () { Session session = HibernateUtils.openSession(); session.getTransaction().begin(); List<Object[]> list = session.createQuery("from Customer c, Order o where c = o.customer" ).list(); for (Object[] objects : list) { System.out.println(objects[0 ] + ":" + objects[1 ]); } session.getTransaction().commit(); session.close(); }
生成的SQL语句,是使用关键字join
:
1 2 3 4 5 6 7 8 9 10 11 12 13 Hibernate: select customer0_.id as id1_0_, order1_.id as id2_1_, customer0_.name as name1_0_, order1_.name as name2_1_, order1_.customer_id as customer3_2_1_ from t_customer customer0_ cross join t_order order1_ where customer0_.id=order1_.customer_id
内连接,inner join
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 @Test public void test11 () { Session session = HibernateUtils.openSession(); session.getTransaction().begin(); List<Object[]> list = session.createQuery("from Customer c inner join c.orders" ).list(); for (Object[] objects : list) { System.out.println(objects[0 ] + ":" + objects[1 ]); } session.getTransaction().commit(); session.close(); }
生成的SQL:
1 2 3 4 5 6 7 8 9 10 11 select customer0_.id as id1_0_, orders1_.id as id2_1_, customer0_.name as name1_0_, orders1_.name as name2_1_, orders1_.customer_id as customer3_2_1_ from t_customer customer0_ inner join t_order orders1_ on customer0_.id=orders1_.customer_id
返回的数据,是经过过滤的记录:
迫切内连接,inner join fetch
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 @Test public void test12 () { Session session = HibernateUtils.openSession(); session.getTransaction().begin(); List<Customer> list = session.createQuery("from Customer c inner join fetch c.orders" ).list(); for (Customer customer : list) { System.out.println(customer + ":" + customer.getOrders()); } session.getTransaction().commit(); session.close(); }
和内连接一样底层都是使用SQL的内连接,但是返回的是封装好的Customer对象的集合
左外连接
左向外连接的结果集包括LEFT OUTER子句中指定的左表的所有行,而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 @Test public void test13 () { Session session = HibernateUtils.openSession(); session.getTransaction().begin(); List<Object[]> list = session.createQuery("from Customer c left outer join c.orders" ).list(); for (Object[] objects : list) { System.out.println(objects[0 ] + ":" + objects[1 ]); } session.getTransaction().commit(); session.close(); }
迫切左外连接,结果与左外连接类似,但是返回的是Customer集合
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 @Test public void test13 () { Session session = HibernateUtils.openSession(); session.getTransaction().begin(); List<Customer> list = session.createQuery("from Customer c left outer join fetch c.orders" ).list(); for (Customer customer : list) { System.out.println(customer); } session.getTransaction().commit(); session.close(); }
右外连接(迫切右外连接),与SQL的右外连接类似;
9. 命名查询 将HQL语句写在Java文件中有时候会很不灵活,比如在项目编译打包之后如果要修改HQL语句就要重新编译打包。HQL可以写在hbm.xml
配置文件中,Java文件从 xxx.hbm.xml
文件中取出HQL语句,当要修改hql语句就可以直接修改配置文件,而无需将整个项目重新编译打包。
HQL可以xxx.hbm.xml
的两个位置:
class标签内:局部命名查询,放在class标签的末尾
hibernate-mapping标签内:全局的命名查询,放在hibernate-mapping标签的末尾
java文件中获取配置文件中的HQL :getNamedQuery()方法
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 @Test public void test14 () { Session session = HibernateUtils.openSession(); Query query1 = session.getNamedQuery("cn.zhuobo.web.domain.Customer.hql_find_all" ); List<Customer> list1 = query1.list(); System.out.println(list1); Query query2 = session.getNamedQuery("hql_find_one" ); query2.setParameter(0 ,"Hugh" ); Customer customer = (Customer) query2.uniqueResult(); System.out.println(customer); session.close(); }