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=王七]

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