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

歡迎大家積極留言交流學習心得，點贊的人最美麗，謝謝