Hibernate查询技术
- HQL查询（最常用）
Hibernate Query Language：Hibernate提供的独有的查询语法 - QBC查询
Query By Criteria 完全面向对象的查询风格，无需写语句。 - 原生SQL
HQL查询
基本查询
- 基础查询
@Test //基础
public void testQuery1() {
Session session = HibernateSessionUtils.getSession();
//String hql = "from Dept"; //类名区分大小写
String hql = "select d from Dept d";
Query query = session.createQuery(hql);
List<Dept> list = query.list();
for (Dept dept : list) {
System.out.println(dept.getDeptno()+dept.getDname());
}
HibernateSessionUtils.closeSession();
}
- 属性查询
@Test //属性查询
public void testQuery2() {
Session session = HibernateSessionUtils.getSession();
String hql = "select e.ename,e.job,e.sal from Emp e";
Query query = session.createQuery(hql);
//Object[]表示每一行的记录的封装
List<Object[]> list = query.list();
for (Object[] row : list) {
System.out.println(row[0]+","+row[1]+","+row[2]);
}
HibernateSessionUtils.closeSession();
}
- 聚合统计
@Test //聚合统计查询：统计员工的最高工资，最低工资，平均工资
public void testQuery3() {
Session session = HibernateSessionUtils.getSession();
String hql = "select max(e.sal),min(e.sal),avg(e.sal) from Emp e";
Query query = session.createQuery(hql);
//Object[]表示每一行的记录的封装
// List<Object[]> list = query.list();
// for (Object[] row : list) {
// System.out.println(row[0]+","+row[1]+","+row[2]);
// }
Object[] row = (Object[]) query.uniqueResult(); //返回唯一行
System.out.println(row[0]+","+row[1]+","+row[2]);
HibernateSessionUtils.closeSession();
}
- 排序
@Test //排序
public void testQuery4() {
Session session = HibernateSessionUtils.getSession();
String hql = "select e from Emp e order by e.sal desc";
Query query = session.createQuery(hql);
List<Emp> list = query.list();
for (Emp emp : list) {
System.out.println(emp.getEname()+","+emp.getSal());
}
HibernateSessionUtils.closeSession();
}
- 参数绑定
@Test //参数绑定 1. 索引值 ? 2.命名参数
public void testQuery5() {
Session session = HibernateSessionUtils.getSession();
//String hql = "select e from Emp e where e.job=? and e.sal>?";
String hql = "select e from Emp e where e.job=:job and e.sal>:sal";
Query query = session.createQuery(hql);
//设置参数
// query.setString(0, "CLERK");
// query.setDouble(1, 2000D);
query.setString("job", "CLERK");
query.setDouble("sal", 2000D);
List<Emp> list = query.list();
for (Emp emp : list) {
System.out.println(emp.getEname()+","+emp.getSal()+","+emp.getJob());
}
HibernateSessionUtils.closeSession();
}
- 分页查询
@Test //分页查询
public void testQuery6() {
Session session = HibernateSessionUtils.getSession();
String hql = "select e from Emp e";
//分页参数
int pageNum = 2;
int pageSize = 4;
Query query = session.createQuery(hql);
query.setFirstResult((pageNum-1)*pageSize); //设置分页的开始位置
query.setMaxResults(pageSize); // 设置每页的查询数量
List<Emp> list = query.list();
for (Emp emp : list) {
System.out.println(emp.getEname()+","+emp.getSal());
}
HibernateSessionUtils.closeSession();
}
高级查询
按以下条件查询员工类 Emp
（1）统计现有员工的职位个数
@Test //统计现有员工的职位个数
public void testQuery7() {
Session session = HibernateSessionUtils.getSession();
String hql = "SELECT COUNT(DISTINCT e.job) FROM Emp e";
Query query = session.createQuery(hql);
Long counter = (Long) query.uniqueResult();
System.out.println(counter);
HibernateSessionUtils.closeSession();
}
（2）按职位统计员工数 分组
//按职位统计员工数 分组 输出 职位-->员工数
//分组查询的结果必须是分组的条件和聚合函数的结果
@Test
public void testQuery8() {
Session session = HibernateSessionUtils.getSession();
String hql = "select e.job,count(e) FROM Emp e group by e.job";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
for (Object[] row : list) {
System.out.println(row[0] + ","+row[1]);
}
HibernateSessionUtils.closeSession();
}
（3）统计各个部门的平均工资: 输出某个 “部门名称–>工资”
//统计各个部门的平均工资: 输出某个 "部门名称-->平均工资"
@Test
public void testQuery9() {
Session session = HibernateSessionUtils.getSession();
String hql = "select e.dept.dname,avg(e.sal) FROM Emp e group by e.dept.dname";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
for (Object[] row : list) {
System.out.println(row[0] + ","+row[1]);
}
HibernateSessionUtils.closeSession();
}
（4）统计各个职位的最高工资 和最低工资
//统计各个职位的最高工资 和最低工资
@Test
public void testQuery10() {
Session session = HibernateSessionUtils.getSession();
String hql = "select e.job, max(e.sal),min(e.sal) FROM Emp e group by e.job";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
for (Object[] row : list) {
System.out.println(row[0] + ","+row[1]+","+row[2]);
}
HibernateSessionUtils.closeSession();
}
（5）统计各个部门平均工资高于3000元的部门的名称 ，输出部门名和部门的平均工资 having
//统计各个部门平均工资高于3000元的部门的名称 ，输出部门名和部门的平均工资 having+聚合函数
@Test
public void testQuery11() {
Session session = HibernateSessionUtils.getSession();
String hql = "select e.dept.dname, avg(e.sal) FROM Emp e group by e.dept.dname having avg(e.sal)>5000";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
for (Object[] row : list) {
System.out.println(row[0] + ","+row[1]);
}
HibernateSessionUtils.closeSession();
}
连接查询
- 内连接
//内连接： 所有的员工和员工部门的名称。
@Test
public void testQuery12() {
Session session = HibernateSessionUtils.getSession();
String hql = "from Emp e inner join e.dept";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
for (Object[] row : list) {
Emp e = (Emp) row[0];
Dept d = (Dept) row[1];
System.out.println(e.getEname()+","+e.getJob()+","+e.getSal()+","+d.getDname());
}
HibernateSessionUtils.closeSession();
}
- 迫切内连接
//迫切内连接
@Test
public void testQuery14() {
Session session = HibernateSessionUtils.getSession();
String hql = "from Emp e inner join fetch e.dept";
Query query = session.createQuery(hql);
List<Emp> list = query.list();
for (Emp e : list) {
System.out.println(e.getEname()+","+e.getJob()+","+e.getSal()+","+e.getDept().getDname());
}
HibernateSessionUtils.closeSession();
}
- 左外连
//左外连: 查询所有的部门信息，以及该部门中所有的员工，如果该部门没有员工，提示“无员工”
@Test
public void testQuery15() {
Session session = HibernateSessionUtils.getSession();
String hql = "from Dept d left outer join d.emps";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
for (Object[] row : list) {
Dept d = (Dept) row[0];
Emp e = (Emp) row[1];
System.out.println(d.getDname());
if (e!=null) {
System.out.println("\t"+e.getEname()+","+e.getJob()+","+e.getSal());
} else {
System.out.println("\t无员工");
}
}
HibernateSessionUtils.closeSession();
}
- 迫切左外连
//迫切左外连: 查询所有的部门信息，以及该部门中所有的员工，如果该部门没有员工，提示“无员工”
@Test
public void testQuery16() {
Session session = HibernateSessionUtils.getSession();
String hql = "select distinct d from Dept d left outer join fetch d.emps";
Query query = session.createQuery(hql);
List<Dept> list = query.list();
for (Dept dept : list) {
System.out.println(dept.getDname());
Set<Emp> emps = dept.getEmps();
if (emps!=null && emps.size()>0) {
for (Emp e : emps) {
System.out.println("\t"+e.getEname()+","+e.getJob()+","+e.getSal());
}
} else {
System.out.println("\t无员工");
}
}
HibernateSessionUtils.closeSession();
}
Criteria查询
特点：
- 以完全面向对象的编程风格
- 无需写语句
- 多条件的组合查询使用较多
- 也可以做分组，统计查询，多对象的连接查询，但是比较少用。（投影查询）
public class TestQBC {
@Test
public void testQuery1() {
Session session = HibernateSessionUtils.getSession();
Criteria criteria = session.createCriteria(Emp.class);
int pageNum = 3;
int pageSize=5;
criteria.setFirstResult((pageNum - 1)*pageSize);
criteria.setMaxResults(pageSize);
List<Emp> list = criteria.list();
for (Emp emp : list) {
System.out.println(emp.getEname()+"," + emp.getJob());
}
HibernateSessionUtils.closeSession();
}
@Test
public void testQuery2() {
Session session = HibernateSessionUtils.getSession();
Criteria criteria = session.createCriteria(Emp.class);
//拼接查询条件
criteria.add(Restrictions.like("ename", "%n%"))
.add(Restrictions.gt("sal", 2000D))
.add(Restrictions.eq("job", "CLERK"));
List<Emp> list = criteria.list();
for (Emp emp : list) {
System.out.println(emp.getEname()+"," + emp.getJob());
}
HibernateSessionUtils.closeSession();
}
//实例查询
@Test
public void testQuery3() {
Session session = HibernateSessionUtils.getSession();
Criteria criteria = session.createCriteria(Emp.class);
Emp emp = new Emp();
emp.setEname("zhansan");
//emp.setSal(5000D);
criteria.add(Example.create(emp)); // select * from emp where ename='zhangsan' and sal=5000
List<Emp> list = criteria.list();
for (Emp e : list) {
System.out.println(e.getEname()+"," + e.getJob());
}
HibernateSessionUtils.closeSession();
}
}
原生sql
@Test
public void testSql() {
Session session = HibernateSessionUtils.getSession();
String sql = "select * from dept";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(Dept.class);
// List<Object[]> list = query.list();
// for (Object[] row : list) {
// System.out.println(row[0]+","+row[1]+","+row[2]);
// }
List<Dept> list = query.list();
for (Dept dept : list) {
System.out.println(dept.getDname());
}
}
DAO封装
- UserDao接口定义
public interface UserDao {
//增删改
public boolean addUser(Users user);
public boolean updateUser(Users user);
public boolean deleteUser(Users user);
//查询
//根据唯一标识查询单个实体
public Users findUsersById(Integer id);
//根据实体的条件查询多个实体
public List<Users> findUsersByExample(Users user);
}
- UserDaoImpl实现类
package com.gec.hiber.dao.impl;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Example;
import com.gec.hiber.dao.UserDao;
import com.gec.hiber.entity.Users;
import com.gec.hiber.utils.HibernateSessionUtils;
public class UserDaoImpl implements UserDao {
@Override
public boolean addUser(Users user) {
try {
Session session = HibernateSessionUtils.getSession();
Transaction tx = session.beginTransaction();
session.save(user);
tx.commit();
return true;
} catch (HibernateException e) {
e.printStackTrace();
return false;
} finally {
HibernateSessionUtils.closeSession();
}
}
@Override
public boolean updateUser(Users user) {
try {
Session session = HibernateSessionUtils.getSession();
Transaction tx = session.beginTransaction();
session.update(user);
tx.commit();
return true;
} catch (HibernateException e) {
e.printStackTrace();
return false;
} finally {
HibernateSessionUtils.closeSession();
}
}
@Override
public boolean deleteUser(Users user) {
try {
Session session = HibernateSessionUtils.getSession();
Transaction tx = session.beginTransaction();
session.delete(user);
tx.commit();
return true;
} catch (HibernateException e) {
e.printStackTrace();
return false;
} finally {
HibernateSessionUtils.closeSession();
}
}
@Override
public Users findUsersById(Integer id) {
Session session = HibernateSessionUtils.getSession();
Users user = (Users) session.get(Users.class, id);
HibernateSessionUtils.closeSession();
return user;
}
@Override
public List<Users> findUsersByExample(Users user) {
Session session = HibernateSessionUtils.getSession();
Criteria criteria = session.createCriteria(Users.class);
criteria.add(Example.create(user));
List<Users> list = criteria.list();
HibernateSessionUtils.closeSession();
return list;
}
}
对hibernate性能的调优：数据加载方式
Hibernate中数据的加载方式默认都是”懒加载“（延时加载）
- 类级别
<class name="com.gec.hiber.entity.Users" table="users" catalog="hiberdb" lazy="false">
仅对 session.load() 起作用，把load方法变成即时加载。
- 关联级别 双向一对多
一方<set lazy="true|false|extra">
true : 延时加载
false: 即时加载 关联数据
extra：增强的延时加载
例如：
结果：
多方 <many-to-one name="district" lazy="false|no-proxy|proxy">
- false: 即时加载
- no-proxy: 延时 非代理对象的方式
- proxy: 延时 生成代理对象
数据抓取策略
一方的配置：
<set>设置 fetch=join,select,subselect
-
fetch=join,select
仅对load()或get()起作用 但hql查询不起作用(sql有n+1条)
join 表示连表查询 -
fetch=subselect子查询
仅对hql查询起作用，load,get不起作用,
使用子查询来优化关联方数据的获取，否则会出现n+1条数据！ -
batch-size="" 对hql查询起作用
220
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
