HQL:Hibernate Query Language
- 提供更加丰富灵活、更为强大的查询能力
- HQL更接近SQL语句查询语法
- 面向对象的查询
- "from Children where cid<?" :这里Children指的是类不是children表;cid指的是Children类中的对象而不是children表中的字段
HQL查询(单表)
以一对多关系映射为例(parent[1]<——>children[n])
简单查询
实体类
1.Parent.java
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 }
2.Children.java
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 }
注:两个实体类的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]]
左外连接、右外连接(没有迫切右外连接)以及迫切左外连接使用也类似