Hibernate中使用HQL
1.HQL是什么?
HQL(Hibernate Query Language)是hibernate专门用于查询数据的语句,有别于SQL,HQL 更接近于面向对象的思维方式。
2.为什么使用HQL?
使用HQL 可以避免使用JDBC 查询的一些弊端:
不需要再编写繁琐的SQL 语句,针对实体类及其属性进行查询;
查询结果是直接存放在List 中的对象,不需要再次封装;
独立于数据库,对不同的数据库根据Hibernate dialect 属性的配置自动生成不同的SQL 语句执行。
3.HQL的语法
[select/update/delete……] from Entity [where……] [group by……] [having……] [order by……].
【注意】:HQL语句中关键字不区分大小写,但是实体类和对象属性要区分大小写。
4.在JUnit测试类测试HQL
示例1:
public void hql1() {
String hql = "select t from TeacherEntity t";
//创建查询对象
Query query = session.createQuery(hql);
//执行查询
List<Object> list = query.list();
for (Object obj : list) {
System.out.println(((TeacherEntity) obj).getTeacherName() + "/" + ((TeacherEntity) obj).getTeacherClass());
}
}
@Test
public void hql2() {
String hql = "from TeacherEntity";
List<TeacherEntity> list = session.createQuery(hql).list();
for (TeacherEntity t : list) {
System.out.println(t.getTeacherName() + "/" + t.getTeacherClass());
}
}
这两个测试的结果是相同的:
(1)在什么情况下,不用写select
当我们查询的是TeacherEntity的所有属性时,我们可以不写select。如:from TeacherEntity;当然也可以写,但格式要发生改变:select t from TeacherEntity (as) t ;括号中的as可写可不写;
(2)在什么情况下,写select
当我们查询的不再是Man的所有属性是,我们必须要写上select。如:我们查询TeacherEntity中的name和class属性时,我们必须这么写:select t.name,t.class from TeacherEntity t;也可以不用别名:select name,class from TeacherEntity;
(3)当查询结果是一个对象,可以用Object接收,也可以用该实体接收。
示例2:
@Test
public void hql3() {
String hql = "from TeacherEntity where teacherName=? ";
List<TeacherEntity> list = session.createQuery(hql).setParameter(0, "陈老师").list();
for (TeacherEntity t : list) {
System.out.println(t.getTeacherName() + "/" + t.getTeacherClass());
}
}
@Test
public void hql33() {
String hql = "from TeacherEntity where teacherName=:name ";
List<TeacherEntity> list = session.createQuery(hql).setParameter("name", "陈老师").list();
for (TeacherEntity t : list) {
System.out.println(t.getTeacherName() + "/" + t.getTeacherClass());
}
}
HQL中使用占位符:
(1)占位符从下标0开始计算位置;
(2)命名参数的使用如上。
示例3:
@Test
public void hql4() {
String hql = "select teacherName,teacherClass from TeacherEntity where teacherClass=?";
List<Object[]> list = session.createQuery(hql).setParameter(0, "Java1").list();
for (Object[] objs : list) {
System.out.println(objs[0].toString() + "/" + objs[1].toString());
}
}
@Test
public void hql5() {
String hql = "select new com.project.hibernate.dto.TeacherDto(teacherName,teacherClass) from TeacherEntity where teacherClass=?";
List<TeacherDto> list = session.createQuery(hql).setParameter(0, "Java1").list();
for (TeacherDto dto : list) {
System.out.println(dto.getUserName() + "/" + dto.getUserClass());
}
}
@Test
public void hql6() {
String hql = "select new map(teacherName,teacherClass) from TeacherEntity where teacherClass=?";
List<Map> list = session.createQuery(hql).setParameter(0, "Java1").list();
for (Map map : list) {
System.out.println(map.get("0") + "/" + map.get("1"));
}
}
当我们查询的对象属性超过一个,可以用对象数组来接收结果;
也可以用一个临时的数据传输对象来接收(dto),该dto中要有对应的构造方法;
还可以用Map接收,得到的结果map键值对中键分别是"0","1","2","3".....,值则是对应的属性。
示例4(模糊查询):
//模糊查询
@Test
public void hql8() {
String hql = "select new map(teacherName,teacherClass) from TeacherEntity where teacherClass like ?";
List<Map> list = session.createQuery(hql).setParameter(0, "%J%").list();
for (Map map : list) {
System.out.println(map.get("0") + "/" + map.get("1"));
}
}
示例5(关联查询):
//关联查询
@Test
public void hql9() {
String hql = "select new map(s.studentName,t.teacherName,t.teacherClass) from StudentEntity s left join s.teacher t where t.teacherName=? ";
List<Map> list = session.createQuery(hql).setParameter(0, "陈老师").list();
for (Map map : list) {
System.out.println(map.get("0") + "/" + map.get("1") + "/" + map.get("2"));
}
}
因为在实体类中已经映射了学生表外键和老师表主键,所以这里不用像SQL一样写“on s.f_t_id=t.t_id”。
示例6(分页查询):
//分页查询
@Test
public void hql10() {
//当前页码
int currPage = 4;
//每页显示条数
int pageSize = 5;
String hql = "from TeacherEntity where teacherName like ?";
List<TeacherEntity> list = session.createQuery(hql).setParameter(0, "%刘老师%").
setFirstResult((currPage - 1) * pageSize).setMaxResults(pageSize).list();
for (TeacherEntity t : list) {
System.out.println(t.getTeacherName());
}
}
Query对象提供了简便的分页方法:
setFirstResult(int firstResult)方法 设置起始下标 (等同于limit(a,b)中的a);
setMaxResults(int maxResults)方法 设置最大返回的记录条数(等同于limit(a,b)中的b)。
示例7(结果返回一个对象):
//返回一个对象
@Test
public void hql11() {
String hql = "from TeacherEntity where teacherName=?";
TeacherEntity t = (TeacherEntity) session.createQuery(hql).setParameter(0, "陈老师").uniqueResult();
System.out.println(t.getTeacherName() + "/" + t.getTeacherClass());
}
当确定返回的实例只有一个或者null时 用uniqueResult()方法。
示例7(删除和修改对象):
//删除对象
@Test
public void hql12() {
String hql = "delete from TeacherEntity where teacherName=?";
int i = session.createQuery(hql).setParameter(0, "刘老师19").executeUpdate();
System.out.println(i);
}
//修改对象
@Test
public void hql13() {
String hql = "update TeacherEntity set teacherName=? where teacherId=?";
int i = session.createQuery(hql).setParameter(0, "测试修改").setParameter(1, "40288ae670f5d8a30170f5d8a5e90011").executeUpdate();
System.out.println(i);
}
Hibernate中使用SQL
@Test
public void sql() {
String sql = "select * from sys_teacher";
NativeQuery query = session.createNativeQuery(sql);
//把查询出来的数据放入实体中,该实体需要被hibernate管理
query.addEntity(TeacherEntity.class);
List<TeacherEntity> list = query.list();
for (TeacherEntity t : list) {
System.out.println(t.getTeacherName());
}
}
@Test
public void sql2() {
String sql = "select t_name,t_class from sys_teacher";
List<Object[]> list = session.createNativeQuery(sql).list();
for (Object[] objs : list) {
System.out.println(objs[0].toString() + "/" + objs[1].toString());
}
}