八、hibernate的查询(HQL)

时间:2022-09-17 14:56:22

HQL:Hibernate Query Language

  • 提供更加丰富灵活、更为强大的查询能力
  • HQL更接近SQL语句查询语法
  • 面向对象的查询
    • "from Children where cid<?"  :这里Children指的是类不是children表;cid指的是Children类中的对象而不是children表中的字段

HQL查询(单表)

以一对多关系映射为例(parent[1]<——>children[n])

简单查询

实体类

1.Parent.java

八、hibernate的查询(HQL)八、hibernate的查询(HQL)
 1 package com.qf.entity;
 2 
 3 import java.util.HashSet;
 4 import java.util.Set;
 5 
 6 public class Parent {
 7 
 8     private Long pid;
 9     private String pname;
10     private Integer age;
11     private Set<Children> childs = new HashSet<>();
12     
13     @Override
14     public String toString() {
15         return "Parent [pid=" + pid + ", pname=" + pname + ", age=" + age + "]";
16     }
17     
18     public Long getPid() {
19         return pid;
20     }
21     public void setPid(Long pid) {
22         this.pid = pid;
23     }
24     public String getPname() {
25         return pname;
26     }
27     public void setPname(String pname) {
28         this.pname = pname;
29     }
30     public Integer getAge() {
31         return age;
32     }
33     public void setAge(Integer age) {
34         this.age = age;
35     }
36     public Set<Children> getChilds() {
37         return childs;
38     }
39     public void setChilds(Set<Children> childs) {
40         this.childs = childs;
41     }
42     
43 }
View Code

2.Children.java

八、hibernate的查询(HQL)八、hibernate的查询(HQL)
 1 package com.qf.entity;
 2 
 3 public class Children {
 4 
 5     private Long cid;
 6     private String cname;
 7     private Character sex;
 8     private Parent p;
 9     
10     @Override
11     public String toString() {
12         return "Children [cid=" + cid + ", cname=" + cname + ", sex=" + sex + ", p=" + p + "]";
13     }
14     
15     public Long getCid() {
16         return cid;
17     }
18     public void setCid(Long cid) {
19         this.cid = cid;
20     }
21     public String getCname() {
22         return cname;
23     }
24     public void setCname(String cname) {
25         this.cname = cname;
26     }
27     public Character getSex() {
28         return sex;
29     }
30     public void setSex(Character sex) {
31         this.sex = sex;
32     }
33     public Parent getP() {
34         return p;
35     }
36     public void setP(Parent p) {
37         this.p = p;
38     }
39 }
View Code

注:两个实体类的toString()方法重写时

  • Parent类的toString()方法如果包含Set<Children>的属性,那么Children类的toString()方法就不能包含Parent的属性,否则查询输出对象信息时会陷入死循环,导致*Error
package com.qf.util;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class HibernateUtils {

	private static Configuration cfg ;
	private static SessionFactory factory;
	
	static {
		cfg = new Configuration().configure();
		factory = cfg.buildSessionFactory();
	}
	
	public static Session getCurrentSession() {
		return factory.getCurrentSession();
	}
}
@Test
/**
 * HQL简单查询
 */
public void queryByHQL() {
	Session session = HibernateUtils.getCurrentSession();
	Transaction tx = session.beginTransaction();
	
	Query query = session.createQuery("from Children");
	List<Children> list = query.list();
	
	for (Children children : list) {
		System.out.println(children);
	}
	
	tx.commit();
}

别名查询

String hql = "select c from Children c order by cid desc";

HQL中支持数据库的带有别名的查询

@Test
/**
 * HQL别名查询
 */
public void queryByHQL02() {
	Session session = HibernateUtils.getCurrentSession();
	Transaction tx = session.beginTransaction();
	
//		String hql = "select c from Children c";
	String hql = "select c from Children c order by cid desc";
	Query query = session.createQuery(hql);
	List<Children> list = query.list();
	
	for (Children children : list) {
		System.out.println(children);
	}
	
	tx.commit();
}

条件查询

参数绑定

  • 位置绑定
    String hql = "select c from Children c where cid<? and cname like ?";
    Query query = session.createQuery(hql);
    query.setParameter(0, 20L);
    query.setParameter(1, "%张%");
    • hql中参数以"?"代替
    • 索引从0开始
  • 名称绑定
    String hql = "select c from Children c where cid<:aaa and cname like :bbb";
    Query query = session.createQuery(hql);
    query.setParameter("aaa", 20L);
    query.setParameter("bbb", "%张%");
    • hql中参数以 ":自定义名称"的形式代替
    • 不使用索引,使用(名称,值)的形式
@Test
/**
 * HQL条件查询
 */
public void queryByHQL03() {
	Session session = HibernateUtils.getCurrentSession();
	Transaction tx = session.beginTransaction();
	
	/*//一个条件(位置绑定)
	String hql = "select c from Children c where cid<?";
	Query query = session.createQuery(hql);
	query.setInteger(0, 3);*/
	
	//多个条件(位置绑定)
	/*String hql = "select c from Children c where cid<? and cname like ?";
	Query query = session.createQuery(hql);
	query.setParameter(0, 20L);
	query.setParameter(1, "%张%");*/
	
	//多个条件(名称绑定)
	String hql = "select c from Children c where cid<:aaa and cname like :bbb";
	Query query = session.createQuery(hql);
	query.setParameter("aaa", 20L);
	query.setParameter("bbb", "%张%");
	
	List<Children> list = query.list();
	
	for (Children children : list) {
		System.out.println(children);
	}
	
	tx.commit();
}

投影查询

查询表中部分字段

方式一:返回Object[ ]

@Test
/**
 * HQL简单查询
 */
public void queryByHQL() {
	Session session = HibernateUtils.getCurrentSession();
	Transaction tx = session.beginTransaction();
	
	Query query = session.createQuery("select cname,sex from Children");
	List<Object[]> list = query.list();
	
	for (Object[] objs : list) {
		System.out.println(Arrays.toString(objs));
	}
	tx.commit();
}

方式二:返回实体类对象

实体类

  • 添加无参构造方法
  • 添加有参构造方法(构造方法参数是你所要查询的属性)
package com.qf.entity;

public class Children {

	private Long cid;
	private String cname;
	private Character sex;
	private Parent p;
	
	public Children(String cname, Character sex) {
		super();
		this.cname = cname;
		this.sex = sex;
	}

	public Children() {
		super();
	}

        ......
}

测试方法

@Test
/**
 * HQL简单查询
 */
public void queryByHQL() {
	Session session = HibernateUtils.getCurrentSession();
	Transaction tx = session.beginTransaction();
	
	Query query = session.createQuery("select new Children(cname,sex) from Children");
	List<Children> list = query.list();
	
	for (Children children : list) {
		System.out.println(children);
	}
	tx.commit();
}

分页查询

  • query.setFirstResult(int start):从第几条记录开始查询
  • query.setMaxResults(int num):本页一共查询多少条记录
@Test
public void query() {
    Session session = HibernateUtils.getCurrentSession();
    Transaction tx = session.beginTransaction();
     
    String sql = "from Children";
    Query query = session.createQuery(sql);
    query.setFirstResult(2);
    query.setMaxResults(3);
    List<Children> list = query.list();
    for (Children c : list) {
        System.out.println(c);
    }
     
    tx.commit();
}

分组查询

max()、min()、count()、sum()、avg()

Object obj = query.uniqueResult():返回唯一结果

@Test
/**
 * HQL简单查询
 */
public void queryByHQL() {
	Session session = HibernateUtils.getCurrentSession();
	Transaction tx = session.beginTransaction();
	
	Query query = session.createQuery("select pid,pname,count(*) from Parent group by pid");
	
	List<Object[]> list = query.list();
	
	for (Object[] objects : list) {
		System.out.println(Arrays.toString(objects));
	}
	
	tx.commit();
}

HQL查询(多表)

内连接查询

正常的内连接sql:select * from Children c inner join Parent p

HQL:"from Children c inner join c.p "

  • 其中c.p是Children类的Parent属性变量p
  • 返回的是对象数组
package com.qf.entity;

public class Children {

	private Long cid;
	private String cname;
	private Character sex;
	private Parent p;
	....
}
@Test
/**
 * HQL简单查询
 */
public void queryByHQL() {
	Session session = HibernateUtils.getCurrentSession();
	Transaction tx = session.beginTransaction();
	
	Query query = session.createQuery("from Children c inner join c.p ");
	
	List<Object[]> list = query.list();
	
	for (Object[] objects : list) {
		System.out.println(Arrays.toString(objects));
	}
	
	tx.commit();
}

------------------------------console-------------------------------

[Children [cid=1, cname=张三8, sex=1, p=Parent [pid=1, pname=老张, age=45]], Parent [pid=1, pname=老张, age=45]]
[Children [cid=2, cname=张三0, sex=0, p=Parent [pid=1, pname=老张, age=45]], Parent [pid=1, pname=老张, age=45]]
[Children [cid=3, cname=张三2, sex=1, p=Parent [pid=1, pname=老张, age=45]], Parent [pid=1, pname=老张, age=45]]
...............
[Children [cid=28, cname=王五0, sex=0, p=Parent [pid=3, pname=老王, age=43]], Parent [pid=3, pname=老王, age=43]]
[Children [cid=29, cname=王五8, sex=0, p=Parent [pid=3, pname=老王, age=43]], Parent [pid=3, pname=老王, age=43]]
[Children [cid=30, cname=王五6, sex=1, p=Parent [pid=3, pname=老王, age=43]], Parent [pid=3, pname=老王, age=43]]

迫切内连接

Query query = session.createQuery("from Children c inner join fetch c.p ");

  • 普通内连接HQL的inner join后加上fetch即可
  • 效果:返回的是内连接的左边对象(该例中Parent作为Children属性)
@Test
/**
 * HQL简单查询
 */
public void queryByHQL() {
	Session session = HibernateUtils.getCurrentSession();
	Transaction tx = session.beginTransaction();
	
	Query query = session.createQuery("from Children c inner join fetch c.p ");
	
	List<Children> list = query.list();
	
	for (Children c : list) {
		System.out.println(c);
	}
	
	tx.commit();
}

------------------------------console-------------------------------

Children [cid=1, cname=张三8, sex=1, p=Parent [pid=1, pname=老张, age=45]]
Children [cid=2, cname=张三0, sex=0, p=Parent [pid=1, pname=老张, age=45]]
Children [cid=3, cname=张三2, sex=1, p=Parent [pid=1, pname=老张, age=45]]
....................
Children [cid=28, cname=王五0, sex=0, p=Parent [pid=3, pname=老王, age=43]]
Children [cid=29, cname=王五8, sex=0, p=Parent [pid=3, pname=老王, age=43]]
Children [cid=30, cname=王五6, sex=1, p=Parent [pid=3, pname=老王, age=43]]

左外连接、右外连接(没有迫切右外连接)以及迫切左外连接使用也类似