本文通过实战演示了Hibernate查询语言(HQL)的各种查询方式，包括位置参数查询、命名参数查询、IN查询、like查询等，并介绍了如何使用DTO进行投影查询和多表联合查询。
话说：
各位读者，晚上好！前一篇博客介绍了Hibernate的API,如果我们一同体验过JDBC的优化之路，对比之下，会发现Hibernate真的是So Easy！
今天就要跟Hibernate暂时说拜拜了，这篇博客作为暂别的礼物吧！
目录：
一、准备工作
1、整体架构图
2、几个实体类
Employee| Department|EmployeeDTO
二、HQL测试
1 、基于位置参数查询
2、 命名参数查询
3、 IN查询
4、 like查询
5、单个对象查询
6、聚合函数查询
7、基于DTO对象查询
8、分页查询
9、导航查询
10、多表联合查询
三、总结
一、准备工作
1、整体架构如图
2、几个实体类
Employee、Department、EmployeeDTO
News和上一篇博客同，不在赘述。
Employee
package com.hmc.hibernate.model;
import org.hibernate.annotations.GenericGenerator;
import javax.persistence.*;
/**
* User:Meice
* 2017/10/27
*/
@Entity
@Table(name = "t_emp")
public class Employee {
private int id;
private String name;
/**
* 定义一个对象，虽然方便，但是执行效率
* 没有单个定义属性高，会发关联SQL语句查询
*/
// private int dept_id;
// private String dept_name;
private Department dept;
public Employee () {}
public Employee(int id, String name) {
this.id = id;
this.name = name;
}
public Employee(String name){
this.name = name;
}
@Id
@GenericGenerator(name = "id",strategy = "native")
@GeneratedValue(generator = "id")
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
/**
* 这里用到的就是ORM——Object Relational Mapping 对象关系映射
* E-R图
*/
@ManyToOne
@JoinColumn(name = "dept_id")
public Department getDept() {
return dept;
}
public void setDept(Department dept) {
this.dept = dept;
}
/**
* 运行test后会打印一下信息，并在t_emp中增加dept_id字段
* Hibernate: alter table t_emp add column dept_id integer
* Hibernate: alter table t_emp add constraint FKiek4ls8f4fx7g6i38ceoj5jcu foreign key (dept_id) references t_dept (id)
* 十月 27, 2017 9:10:03 下午
* 以上可知：第一条增加了字段；第二条关联了外键,类型一致
*/
}
Department
package com.hmc.hibernate.model;
import org.hibernate.annotations.GenericGenerator;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
/**
* User:Meice
* 2017/10/27
*/
@Entity
@Table(name = "t_dept")
public class Department {
private int id;
private String name;
public Department() {}
public Department(int id, String name) {
this.id = id;
this.name = name;
}
@Id
@GenericGenerator(name = "id",strategy = "native")
@GeneratedValue(generator = "id")
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
/**
* 创建好后，随便运行一个test(),运行结果：
* Hibernate: create table t_dept (id integer not null auto_increment, name varchar(255), primary key (id))
* 十月 27, 2017 8:57:33 下午 org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl processGetTableResults
* INFO: HHH000262: Table not found: t_emp
* 十月 27, 2017 8:57:33 下午 org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl processGetTableResults
* INFO: HHH000262: Table not found: t_emp
* Hibernate: create table t_emp (id integer not null auto_increment, name varchar(255), primary key (id))
* 十月 27
*/
}
EmployeeDTO
package com.hmc.hibernate.model;
/**
* User:Meice
* 2017/10/27
*/
public class EmployeeDTO {
private int eid;
private String ename;
private String dname;
public EmployeeDTO() {}
public EmployeeDTO(int eid,String ename,String dname) {
this.eid = eid;
this.ename = ename;
this.dname = dname;
}
public int getEid() {
return eid;
}
public void setEid(int eid) {
this.eid = eid;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
}
二、HQL测试
为方便测试，整体就写在了一个类里面，嘻嘻。
package com.hmc.hibernate;
import com.hmc.hibernate.model.Employee;
import com.hmc.hibernate.model.EmployeeDTO;
import com.hmc.hibernate.model.News;
import com.hmc.hibernate.util.HibernateUtil;
import org.hibernate.Session;
import org.hibernate.type.IntegerType;
import org.junit.Test;
import java.util.List;
/**
* User:Meice
* 2017/10/26
*/
public class TestHQL {
Session session = null;
//这个类主要测试HQL
/**
* 1 基于位置参数查询
* 2 命名参数查询
* 3 IN查询
* 4 like查询
*.....
*
*/
@Test
public void test01 () {
Session session = null;
try {
session = HibernateUtil.getSession();
/**
* 基于位置参数查询
* 参数从0开始
*
*/
List<News> list = session.createQuery("from News where author=? and id=?")
.setParameter(1,100)
.setParameter(0,"小美")
.list();
//
/**
* 这里位置参数从0开始，不同于PreparedStatement对象 ;
* 否则报错： java.lang.IllegalArgumentException: Positional parameter does not exist: 1 in query: from News where id =?
*/
System.out.println(list);
} catch (Exception e) {
e.printStackTrace();
}finally{
HibernateUtil.closeSession(session);
}
/**
* 执行结果：
* 十月 26, 2017 7:40:40 上午 org.hibernate.hql.internal.ast.HqlSqlWalker generatePositionalParameter
* WARN: [DEPRECATION] Encountered positional parameter near line 1, column 45 in HQL: [from com.hmc.hibernate.model.News where id =?]. Positional parameter are considered deprecated; use named parameters or JPA-style positional parameters instead.
* Hibernate: select news0_.id as id1_1_, news0_.author as author2_1_, news0_.pic as pic3_1_, news0_.title as title4_1_ from t_news news0_ where news0_.id=?
* [News{id=100, title='利用页面跳转，重复生成新闻', author='小美', pic='images/default.jpg'}]
*
*/
}
@Test
public void test02() {
Session session = null;
try {
session = HibernateUtil.getSession();
/**
*命名参数查询
* 格式：属性=:参数名称
*
*/
List<News> list = session.createQuery("from News where author=:author and id = :id")
.setParameter("author","小美")
.setParameter("id",100)
.list();
System.out.println(list);
} catch (Exception e) {
e.printStackTrace();
}finally{
HibernateUtil.closeSession(session);
}
/**
* 执行结果：
* 十月 26, 2017 7:52:32 上午 org.hibernate.hql.internal.QueryTranslatorFactoryInitiator initiateService
* INFO: HHH000397: Using ASTQueryTranslatorFactory
* Hibernate: select news0_.id as id1_1_, news0_.author as author2_1_, news0_.pic as pic3_1_, news0_.title as title4_1_ from t_news news0_ where news0_.author=? and news0_.id=?
* [News{id=100, title='利用页面跳转，重复生成新闻', author='小美', pic='images/default.jpg'}]
*
*/
}
/**
* 注意：IDEA里面如果出现红色波浪线，并不代表有错误，只是显示问题，重新复制粘贴即可或者直接运行
* 一直.下去就是链式编程，Scala的链式编程类似这个样子.
*/
@Test
public void test03() {
Session session = null;
try {
session = HibernateUtil.getSession();
/**
* IN查询
* setParameter
*/
List<News> list = session.createQuery("from News where id in (?,?,?)")
.setParameter(0,105)
.setParameter(1,106)
.setParameter(2,107)
.list();
System.out.println(list);
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtil.closeSession(session);
}
/**
* 执行结果：
* 十月 26, 2017 7:59:58 上午 org.hibernate.hql.internal.ast.HqlSqlWalker generatePositionalParameter
* WARN: [DEPRECATION] Encountered positional parameter near line 1, column 52 in HQL: [from com.hmc.hibernate.model.News where id in (?,?,?)]. Positional parameter are considered deprecated; use named parameters or JPA-style positional parameters instead.
* Hibernate: select news0_.id as id1_1_, news0_.author as author2_1_, news0_.pic as pic3_1_, news0_.title as title4_1_ from t_news news0_ where news0_.id in (? , ? , ?)
* [News{id=105, title='利用页面跳转，重复生成新闻', author='小美', pic='images/default.jpg'},
* News{id=106, title='利用页面跳转，重复生成新闻', author='小美', pic='images/default.jpg'},
* News{id=107, title='利用页面跳转，重复生成新闻', author='小美', pic='images/default.jpg'}]
*
*/
}
@Test
public void test04 () {
Session session = null;
try {
session = HibernateUtil.getSession();
Integer[] ids = new Integer[] {109,200,201};
/**
* IN查询
* setParameterList
*/
List<News> list = session.createQuery("from News where id in (:ids)")
.setParameterList("ids",ids)
.list();
System.out.println(list);
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtil.closeSession(session);
}
/**
* 执行结果
* 十月 26, 2017 8:07:25 上午 org.hibernate.hql.internal.QueryTranslatorFactoryInitiator initiateService
* INFO: HHH000397: Using ASTQueryTranslatorFactory
* Hibernate: select news0_.id as id1_1_, news0_.author as author2_1_, news0_.pic as pic3_1_, news0_.title as title4_1_ from t_news news0_ where news0_.id in (? , ? , ?)
* [News{id=109, title='利用页面跳转，重复生成新闻', author='小美', pic='images/default.jpg'},
* News{id=200, title='利用页面跳转，重复生成新闻', author='小美', pic='images/default.jpg'},
* News{id=201, title='利用页面跳转，重复生成新闻', author='小美', pic='images/default.jpg'}]
*/
}
@Test
public void test05() {
Session session = null;
try {
session = HibernateUtil.getSession();
/**
* like查询
*/
List<News> list = session.createQuery("from News where title like ?")
.setParameter(0,"%十九大%")
.list();
System.out.println(list);
} catch (Exception e) {
e.printStackTrace();
}finally{
HibernateUtil.closeSession(session);
}
/**
* 结果：
* 十月 26, 2017 8:50:17 下午 org.hibernate.hql.internal.ast.HqlSqlWalker generatePositionalParameter
* WARN: [DEPRECATION] Encountered positional parameter near line 1, column 52 in HQL: [from com.hmc.hibernate.model.News where title like ?]. Positional parameter are considered deprecated; use named parameters or JPA-style positional parameters instead.
* Hibernate: select news0_.id as id1_1_, news0_.author as author2_1_, news0_.pic as pic3_1_, news0_.title as title4_1_ from t_news news0_ where news0_.title like ?
* [News{id=25, title='[喜迎十九大 精彩广西关键词:放管服]“放管', author='羊城晚报', pic='images/default.jpg'},
* News{id=29, title='党和国家领导人是如何当选十九大代表的？', author='Meixiao', pic='images/default.jpg'},
* News{id=60, title='《不忘初心 继续前进》 喜迎十九大 家国网事11', author='人民日报33', pic='images/default.jpg'},
* News{id=602, title='“我们的自信”制度篇——天下归心 喜迎十九大', author='Xiaomei', pic='images/news20171015205739.jpg'},
* News{id=606, title='微视频《中国的红色梦想》 十九大专题', author='Meixiao', pic='null'}]
*/
}
@Test
public void test06() {
Session session = null;
try {
session = HibernateUtil.getSession();
/**
* Like查询2
*/
List<News> list = session.createQuery("from News where title like:like")
.setParameter("like","%十九大%")
.list();
System.out.println(list);
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtil.closeSession(session);
}
/**
* 运行结果：
* 同上
*/
}
@Test
public void test07 () {
Session session = null;
try {
session = HibernateUtil.getSession();
/**
*
* 单个对象
* uniqueResult
*/
News news =(News) session.createQuery("from News where id = ?")
.setParameter(0,105)
.uniqueResult();
System.out.println(news);
} catch (Exception e) {
e.printStackTrace();
}
/**
* 如果没有设置参数，那么会报错：
* QueryException: Expected positional parameter count: 1,
* actual parameters: [] [from News where id = ?]
*
*运行结果：
* 十月 27, 2017 9:04:32 上午 org.hibernate.hql.internal.ast.HqlSqlWalker generatePositionalParameter
*WARN: [DEPRECATION] Encountered positional parameter near line 1, column 46 in HQL: [from com.hmc.hibernate.model.News where id = ?]. Positional parameter are considered deprecated; use named parameters or JPA-style positional parameters instead.
*Hibernate: select news0_.id as id1_1_, news0_.author as author2_1_, news0_.pic as pic3_1_, news0_.title as title4_1_ from t_news news0_ where news0_.id=?
*News{id=105, title='利用页面跳转，重复生成新闻', author='小美', pic='images/default.jpg'}
*
*/
}
@Test
public void test08() {
Session session = null;
try {
session = HibernateUtil.getSession();
/**
* 聚合函数查询
* count(*)返回的是Long类型；avg(*)返回的是double类型
*/
Long count = (Long) session.createQuery("select count(*) from News")
.uniqueResult();
System.out.println(count);
} catch (Exception e) {
e.printStackTrace();
}
/**
*
* 开始不知道返回什么类型：报错：
* java.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Integer
* 运行结果:
* 十月 27, 2017 12:56:07 下午 org.hibernate.hql.internal.QueryTranslatorFactoryInitiator initiateService
* INFO: HHH000397: Using ASTQueryTranslatorFactory
* Hibernate: select count(*) as col_0_0_ from t_news news0_
* 509
*/
}
@Test
public void test09() {
Session session = null;
try {
session = HibernateUtil.getSession();
/**
* 投影查询
* 因为不确定你要查询的字段个数及字段是否包含在对象属性中，所以没发返回对象
*
*/
List<Object[]> list = session.createQuery("select id,title,author from News")
.list();
// System.out.println(list);
//输出方式一：
/*for(Object[] obj:list) {
for(Object obj2:obj) {
System.out.print(obj2+" ");
}
System.out.println();
}*/
//输出方式二:
for(Object[] obj :list) {
System.out.println(obj[0]+"---"+obj[1]+"---"+obj[2]);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtil.closeSession(session);
}
/**
* 运行结果：
* 直接输出list<News> list
* 十月 27, 2017 1:25:34 下午 org.hibernate.hql.internal.QueryTranslatorFactoryInitiator initiateService
* INFO: HHH000397: Using ASTQueryTranslatorFactory
* Hibernate: select news0_.id as col_0_0_, news0_.title as col_1_0_, news0_.author as col_2_0_, news0_.pic as col_3_0_ from t_news news0_
* [[Ljava.lang.Object;@655f7ea, [Ljava.lang.Object;@549949be, [Ljava.lang.Object;@4b3a45f1,
*
* 1发现存储的是Object[]
* 十月 27, 2017 1:31:46 下午 org.hibernate.hql.internal.QueryTranslatorFactoryInitiator initiateService
* INFO: HHH000397: Using ASTQueryTranslatorFactory
* Hibernate: select news0_.id as col_0_0_, news0_.title as col_1_0_, news0_.author as col_2_0_, news0_.pic as col_3_0_ from t_news news0_
* 3
* baby烟瘾这么重，为什么黄晓明还这么爱她？黄晓明
* 羊城晚报1
* images/default.jpg
*/
}
@Test
public void test10() {
Session session = null;
try {
session = HibernateUtil.getSession();
/**
* 基于DTO(Data Transfer Object)对象查询
* new News(id, title,author)
* 这个很有趣.如果对上面投影查询遍历数组不满意，就用这个，前提是有构造方法
*/
List<News> list = session.createQuery("select new News(id,title,author,pic) from News")
.list();
System.out.println(list);
} catch (Exception e) {
e.printStackTrace();
}finally{
HibernateUtil.closeSession(session);
}
/**
* 运行结果：
* 十月 27, 2017 2:07:00 下午 org.hibernate.hql.internal.QueryTranslatorFactoryInitiator initiateService
* INFO: HHH000397: Using ASTQueryTranslatorFactory
* Hibernate: select news0_.id as col_0_0_, news0_.title as col_1_0_, news0_.author as col_2_0_, news0_.pic as col_3_0_ from t_news news0_
* [News{id=3, title='baby烟瘾这么重，为什么黄晓明还这么爱她？黄晓明', author='羊城晚报1', pic='images/default.jpg'},
* News{id=4, title='一言不合就圈粉！为陈小春儿子和嗯哼的反差萌疯狂打c..', author='人民日报', pic='images/default.jpg'},
*/
}
@Test
public void test11() {
try {
session = HibernateUtil.getSession();
/**
* 分页
* select * from t_news limit (?,?);
* setFirstResult就是第一个？
* setMaxResults就是第二个?
*/
List<News> list = session.createQuery("from News")
.setFirstResult(5)
.setMaxResults(5)
.list();
for(News news:list) {
System.out.println(news);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
HibernateUtil.closeSession(session);
}
/**
* 运行结果
* 十月 27, 2017 8:47:03 下午 org.hibernate.hql.internal.QueryTranslatorFactoryInitiator initiateService
* INFO: HHH000397: Using ASTQueryTranslatorFactory
* Hibernate: select news0_.id as id1_1_, news0_.author as author2_1_, news0_.pic as pic3_1_, news0_.title as title4_1_ from t_news news0_ limit ?, ?
* News{id=24, title='密云水库昨起开河捕鱼', author='南方周末', pic='images/default.jpg'}
* News{id=25, title='[喜迎十九大 精彩广西关键词:放管服]“放管', author='羊城晚报', pic='images/default.jpg'}
* News{id=26, title='上海13所高校校徽月饼，你爱哪款？', author='上海日报', pic='images/default.jpg'}
* News{id=29, title='党和国家领导人是如何当选十九大代表的？', author='Meixiao', pic='images/default.jpg'}
* News{id=35, title='雪域高原逢盛世 砥砺奋进谱新篇', author='南方周末', pic='images/default.jpg'}
*/
}
//根据部门查询员工
@Test
public void test12() {
try {
session = HibernateUtil.getSession();
/**
* 导航查询
*/
List<Employee> list = session.createQuery("from Employee e where e.dept.id = ?")
.setParameter(0,1)
.list();
for(Employee emp:list) {
System.out.println(emp.getId()+"---"+emp.getName()+"---"+emp.getDept().getName());
}
} catch (Exception e) {
e.printStackTrace();
}finally{
HibernateUtil.closeSession(session);
}
/**
* 运行结果：
* 十月 27, 2017 10:11:24 下午 org.hibernate.hql.internal.ast.HqlSqlWalker generatePositionalParameter
* WARN: [DEPRECATION] Encountered positional parameter near line 1, column 55 in HQL: [from com.hmc.hibernate.model.Employee where dept.id = ?]. Positional parameter are considered deprecated; use named parameters or JPA-style positional parameters instead.
* Hibernate: select employee0_.id as id1_2_, employee0_.dept_id as dept_id3_2_, employee0_.name as name2_2_ from t_emp employee0_ where employee0_.dept_id=?
* Hibernate: select department0_.id as id1_1_0_, department0_.name as name2_1_0_ from t_dept department0_ where department0_.id=?
* 3---贾宝玉---企管部
*
* 发现，本来一条语句可以搞定的事情，发了2条HQL语句，效率不高呢!怎么只查询一次呢？往下看
*/
}
@Test
public void test13 () {
try {
session = HibernateUtil.getSession();
/**
* 多表联合查询
*/
List<Object[]> list = session.createQuery("select e.id,e.name,d.name from Employee e inner join Department d on e.dept.id = d.id")
.list();
for(Object[] obj :list) {
System.out.println(obj[0]+"---"+obj[1]+"---"+obj[2]);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
HibernateUtil.closeSession(session);
}
/**
* 运行结果：
* 十月 27, 2017 10:29:46 下午 org.hibernate.hql.internal.QueryTranslatorFactoryInitiator initiateService
* INFO: HHH000397: Using ASTQueryTranslatorFactory
* Hibernate: select employee0_.id as col_0_0_, employee0_.name as col_1_0_, department1_.name as col_2_0_ from t_emp employee0_ inner join t_dept department1_ on (employee0_.dept_id=department1_.id)
* 3---贾宝玉---企管部
* 4---林黛玉---财务部
* 5---史湘云---企管部
*/
}
//既然多表联合查询，类似于投影查询，返回的是Object[]数组，可不可以也类似DTO那样直接查询，然后返回一个对象
@Test
public void test14 () {
try {
session = HibernateUtil.getSession();
/**
* 多表联合查询，如果取别名，必须加as
* 否则报错
*/
List<Object[]> list = session.createQuery("select e.id as eid ,e.name as ename,d.name as dname from Employee e inner join Department d on e.dept.id = d.id")
.list();
for(Object[] obj:list) {
System.out.println(obj[0]+"--"+obj[1]+"--"+obj[2]);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
HibernateUtil.closeSession(session);
}
/**
* 上面一定要加as ，否则报类似异常：unexpected token: dname
*
* 运行结果：
* 十月 27, 2017 10:42:59 下午 org.hibernate.hql.internal.QueryTranslatorFactoryInitiator initiateService
* INFO: HHH000397: Using ASTQueryTranslatorFactory
* Hibernate: select employee0_.id as col_0_0_, employee0_.name as col_1_0_, department1_.name as col_2_0_ from t_emp employee0_ inner join t_dept department1_ on (employee0_.dept_id=department1_.id)
* 3--贾宝玉--企管部
* 4--林黛玉--财务部
* 5--史湘云--企管部
*/
}
@Test
public void test15() {
try {
session = HibernateUtil.getSession();
/**
* 多表联合查询DTO
* 一定要加关键字as
*/
List<EmployeeDTO> list = session.createQuery("select new com.hmc.hibernate.model.EmployeeDTO(e.id as eid ,e.name as ename ,d.name as dname) from Employee e inner join Department d on e.dept.id = d.id")
.list();
//因为没有toString，所以这样取值，不然直接可以输出
for(EmployeeDTO emp:list) {
System.out.println(emp.getEid()+"--"+emp.getEname()+"--"+emp.getDname());
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtil.closeSession(session);
}
/**
* 如果不写类全名：报错：Caused by: java.lang.ClassNotFoundException: Could not load requested class : EmployeeDTO
* 加上类全名后，运行结果如下：
* 十月 27, 2017 11:00:53 下午 org.hibernate.hql.internal.QueryTranslatorFactoryInitiator initiateService
* INFO: HHH000397: Using ASTQueryTranslatorFactory
* Hibernate: select employee0_.id as col_0_0_, employee0_.name as col_1_0_, department1_.name as col_2_0_ from t_emp employee0_ inner join t_dept department1_ on (employee0_.dept_id=department1_.id)
* 3--贾宝玉--企管部
* 4--林黛玉--财务部
* 5--史湘云--企管部
*/
}
}
三、总结
1、HQL实现多元化的查询会更简单，完全不像之前JDBC系列那么麻烦和“落后”；
2、非常有特点的一个特征是：DTO-Data Transfer Object数据传输对象在投影查询和多表联合查询都用到，很高级的功能！投影查询的时候，News类本来就存在，所以可以直接用；在多表联合查询中，涉及2个表，所以需要重新创建一个类，来满足需求。如果没有，我们就创造！
3、分页查询也变得灰常简单。setFirstResult，setMaxResult就搞定啦。
4、表与表之间的关系，用到了ORM-Object Relational Mapping，即对象关系映射。具体用法就是在实体类中通过注解方式来实现ManyToOne、OneToMany、OneToOne、ManyToMany等关系；指定关联列名通过JoinColumn(name=”dept_id”)来实现。
5、总而言之，Hibernate很潇洒！用起来得心应手！希望读者和我一样喜欢。
好了，晚安！下期再会！
扫一扫
584
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
