Hibernate中的HQL查询

第一、前言
阅读本篇文章之前，请先阅读：Hibernate中的事务控制
第二、HQL的查询
0、准备测试数据

1、基本查询

package com.demo;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.query.Query;
import com.demo.dto.Teacher;
import com.demo.util.HibernateUtil;
/**
 * 测试类
 * @author shixiangcheng
 * 2020-07-04
 */
public class Test {
	public static void main(String[] args) {
		Session s=HibernateUtil.getCurrentSession();
		Transaction tx=s.beginTransaction();
		//1.获取Query对象
		Query query=s.createQuery("from Teacher");
		//2.获取结果
		List<Teacher> list=query.list();
		for(Teacher o:list) {
			System.out.println(o);
		}
		tx.commit();
	}
}

执行结果

Hibernate: 
    select teacher0_.id as id1_0_,teacher0_.name as name2_0_  from t_teacher teacher0_
[id=1,name=王一]
[id=2,name=王二]
[id=3,name=王三]
[id=4,name=王四]
[id=5,name=王五]
[id=6,name=王六]
[id=7,name=王七]

2、条件查询
查询姓名中带有五字的教师信息

Session s=HibernateUtil.getCurrentSession();
Transaction tx=s.beginTransaction();
//1.获取Query对象
Query query=s.createQuery("from Teacher where name like ?");
//给参数占位符赋值
query.setString(0, "%五%");
//2.获取结果
List<Teacher> list=query.list();
for(Teacher o:list) {
	System.out.println(o);
}
tx.commit();

也可以按下面方式书写

//1.获取Query对象
Query query=s.createQuery("from Teacher where name like :name");
//给参数占位符赋值
query.setString("name", "%五%");
//query.setParameter("name","%五%"); 此种方式亦可

执行结果如下：

Hibernate: 
    select teacher0_.id as id1_0_,teacher0_.name as name2_0_ 
    from t_teacher teacher0_ where teacher0_.name like ?
[id=5,name=王五]

3、排序和分页查询

//1.获取Query对象
Query query=s.createQuery("from Teacher order by id desc");
query.setFirstResult(2);//设置查询的开始记录索引
query.setMaxResults(2);//设置每次查询的条数
//无论用何种数据库，涉及分页的都是这两个方法。因为sql语句的生成以及是Hibernate的事儿了。

执行结果

Hibernate: 
    select teacher0_.id as id1_0_,teacher0_.name as name2_0_ 
    from t_teacher teacher0_  order by teacher0_.id desc limit ?,?
[id=5,name=王五]
[id=4,name=王四]

4.统计查询
使用聚合函数:count,sum,avg,max,min

Session s=HibernateUtil.getCurrentSession();
Transaction tx=s.beginTransaction();
//1.获取Query对象
Query query=s.createQuery("select count(1) from Teacher");
//2.获取结果
System.out.println(query.uniqueResult());//当返回的结果唯一时，可以使用此方法;
tx.commit();

执行结果

Hibernate: 
    select count(1) as col_0_0_ from t_teacher teacher0_
7

4、投影查询
指定只查特定字段的值

Session s=HibernateUtil.getCurrentSession();
Transaction tx=s.beginTransaction();
//1.获取Query对象
Query query=s.createQuery("select id,name from Teacher");
//2.获取结果
List<Object[]> list=query.list();
for(Object[] obj:list) {
	for(Object o:obj) {
		System.out.print(o);
	}
	System.out.println();
}
tx.commit();

对于上述代码，遍历结果时需要遍历数组，多有不便。当我们在查询实体时，只需要部分字段，并且希望它的返回结果是用实体类来封装，而不是Object[]，称之为创建实体类的投影。

Session s=HibernateUtil.getCurrentSession();
Transaction tx=s.beginTransaction();
//1.获取Query对象(需要创建对应的构造函数)
Query query=s.createQuery("select new com.demo.dto.Teacher(id,name) from Teacher");
//2.获取结果
List<Teacher> list=query.list();
for(Teacher t:list) {
	System.out.println(t);
}
tx.commit();

实体类增加构造函数

package com.demo.dto;
import java.io.Serializable;
/**
 * 实体类
 * @author shixiangcheng
 * 2020-07-04
 */
public class Teacher implements Serializable {
	private int id;
	private String name;
	public Teacher() {}
	public Teacher(int id, String name) {
		super();
		this.id = id;
		this.name = name;
	}
	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;
	}
	@Override
	public String toString() {
		return "[id="+this.getId()+",name="+this.getName()+"]";
	}
}

执行结果

Hibernate: 
    select teacher0_.id as col_0_0_,teacher0_.name as col_1_0_  from t_teacher teacher0_
[id=1,name=王一]
[id=2,name=王二]
[id=3,name=王三]
[id=4,name=王四]
[id=5,name=王五]
[id=6,name=王六]
[id=7,name=王七]

欢迎大家积极留言交流学习心得，点赞的人最美丽，谢谢