Hibernate_day04
1.今日任务
·Hibernate查询-HQL语法
·Hibernate查询-Criteria语法
·Hibernate查询优化
·Crm案例练习:为客户列表增加查询条件
2.相关知识
2.1Hibernate查询-HQL语法
2.1.1基本语法
public class TestHql {
// 基本语法
@Test
public void fun1() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
// ----------------------------------------------------
String hql = " from cn.itcast.domain.Customer ";// 完整写法
String hql2 = " from Customer "; // 简单写法
String hql3 = " from java.lang.Object ";
Query query = session.createQuery(hql3);
List list = query.list();
System.out.println(list);
// ----------------------------------------------------
tx.commit();
session.close();
}
@Test
// 排序
public void fun2() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
// ----------------------------------------------------
String hql1 = " from cn.itcast.domain.Customer order by cust_id asc ";// 完整写法
String hql2 = " from cn.itcast.domain.Customer order by cust_id desc ";// 完整写法
Query query = session.createQuery(hql2);
List list = query.list();
System.out.println(list);
// ----------------------------------------------------
tx.commit();
session.close();
}
@Test
// 条件查询
public void fun3() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
// ----------------------------------------------------
String hql1 = " from cn.itcast.domain.Customer where cust_id =? ";// 完整写法
String hql2 = " from cn.itcast.domain.Customer where cust_id = :id ";// 完整写法
Query query = session.createQuery(hql2);
// query.setParameter(0, 2l);
query.setParameter("id", 2l);
List list = query.list();
System.out.println(list);
// ----------------------------------------------------
tx.commit();
session.close();
}
@Test
// 分页查询
public void fun4() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
// ----------------------------------------------------
String hql1 = " from cn.itcast.domain.Customer ";// 完整写法
Query query = session.createQuery(hql1);
// limit ?,?
// (当前页数-1)*每页条数
query.setFirstResult(2);
query.setMaxResults(2);
List list = query.list();
System.out.println(list);
// ----------------------------------------------------
tx.commit();
session.close();
}
@Test
// 统计查询
// count 计数
// sum 求和
// avg 平均数
// max
// min
public void fun5() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
// ----------------------------------------------------
String hql1 = " select count(*) from cn.itcast.domain.Customer ";// 完整写法
String hql2 = " select sum(cust_id) from cn.itcast.domain.Customer ";// 完整写法
String hql3 = " select avg(cust_id) from cn.itcast.domain.Customer ";// 完整写法
String hql4 = " select max(cust_id) from cn.itcast.domain.Customer ";// 完整写法
String hql5 = " select min(cust_id) from cn.itcast.domain.Customer ";// 完整写法
Query query = session.createQuery(hql5);
Number number = (Number) query.uniqueResult();
System.out.println(number);
// ----------------------------------------------------
tx.commit();
session.close();
}
@Test
// 投影查询
public void fun6() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
// ----------------------------------------------------
String hql1 = " select cust_name from cn.itcast.domain.Customer ";
String hql2 = " select cust_name,cust_id from cn.itcast.domain.Customer ";
String hql3 = " select new Customer(cust_id,cust_name) from cn.itcast.domain.Customer ";
Query query = session.createQuery(hql3);
List list = query.list();
System.out.println(list);
// ----------------------------------------------------
tx.commit();
session.close();
}
}
2.1.2多表查询语法
//学习HQL语法(不常用) - 多表查询语法
public class TestHql2 {
// 回顾-原生SQL
// 交叉连接-笛卡尔积(避免)
// select * from A,B
// 内连接
// |-隐式内连接
// select * from A,B where b.aid = a.id
// |-显式内连接
// select * from A inner join B on b.aid = a.id
// 外连接
// |- 左外
// select * from A left [outer] join B on b.aid = a.id
// |- 右外
// select * from A right [outer] join B on b.aid = a.id
// ---------------------------------------------------------------------
// HQL的多表查询
// 内连接(迫切)
// 外连接
// |-左外(迫切)
// |-右外(迫切)
@Test
// HQL 内连接 => 将连接的两端对象分别返回.放到数组中.
public void fun1() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
// ----------------------------------------------------
String hql = " from Customer c inner join c.linkMens ";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
for (Object[] arr : list) {
System.out.println(Arrays.toString(arr));
}
// ----------------------------------------------------
tx.commit();
session.close();
}
@Test
// HQL 迫切内连接 => 帮我们进行封装.返回值就是一个对象
public void fun2() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
// ----------------------------------------------------
String hql = " from Customer c inner join fetch c.linkMens ";
Query query = session.createQuery(hql);
List<Customer> list = query.list();
System.out.println(list);
// ----------------------------------------------------
tx.commit();
session.close();
}
@Test
// HQL 左外连接 => 将连接的两端对象分别返回.放到数组中.
public void fun3() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
// ----------------------------------------------------
String hql = " from Customer c left join c.linkMens ";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
for (Object[] arr : list) {
System.out.println(Arrays.toString(arr));
}
// ----------------------------------------------------
tx.commit();
session.close();
}
@Test
// HQL 右外连接 => 将连接的两端对象分别返回.放到数组中.
public void fun4() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
// ----------------------------------------------------
String hql = " from Customer c right join c.linkMens ";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
for (Object[] arr : list) {
System.out.println(Arrays.toString(arr));
}
// ----------------------------------------------------
tx.commit();
session.close();
}
}
2.2Hibernate查询-Criteria语法
2.2.1基本语法
public class TestCriteria {
@Test
// 基本语法
public void fun1() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
// ----------------------------------------------------
Criteria c = session.createCriteria(Customer.class);
List<Customer> list = c.list();
System.out.println(list);
// ----------------------------------------------------
tx.commit();
session.close();
}
@Test
// 条件语法
public void fun2() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
// ----------------------------------------------------
Criteria c = session.createCriteria(Customer.class);
// c.add(Restrictions.idEq(2l));
c.add(Restrictions.eq("cust_id", 2l));
List<Customer> list = c.list();
System.out.println(list);
// ----------------------------------------------------
tx.commit();
session.close();
}
@Test
// 分页语法 - 与HQL一样
public void fun3() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
// ----------------------------------------------------
Criteria c = session.createCriteria(Customer.class);
// limit ?,?
c.setFirstResult(0);
c.setMaxResults(2);
List<Customer> list = c.list();
System.out.println(list);
// ----------------------------------------------------
tx.commit();
session.close();
}
@Test
// 排序语法
public void fun4() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
// ----------------------------------------------------
Criteria c = session.createCriteria(Customer.class);
c.addOrder(Order.asc("cust_id"));
// c.addOrder(Order.desc("cust_id"));
List<Customer> list = c.list();
System.out.println(list);
// ----------------------------------------------------
tx.commit();
session.close();
}
@Test
// 统计语法
public void fun5() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
// ----------------------------------------------------
Criteria c = session.createCriteria(Customer.class);
// 设置查询目标
c.setProjection(Projections.rowCount());
List list = c.list();
System.out.println(list);
// ----------------------------------------------------
tx.commit();
session.close();
}
}
2.2.2 离线Criteria
/*
* 离线Criteria
*/
public class TestCriteria2 {
@Test
public void fun1(){
//Service/web层
DetachedCriteria dc = DetachedCriteria.forClass(Customer.class);
dc.add(Restrictions.idEq(6l));//拼装条件(全部与普通Criteria一致)
//----------------------------------------------------
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
//----------------------------------------------------
Criteria c = dc.getExecutableCriteria(session);
List list = c.list();
System.out.println(list);
//----------------------------------------------------
tx.commit();
session.close();
}
}
2.3Hibernate查询优化
2.3.1 类级别查询
//懒加载|延迟加载
public class TestLazy {
@Test
// get方法 : 立即加载.执行方法时立即发送sql语句查询结果
public void fun1(){
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
//----------------------------------------------------
Customer c = session.get(Customer.class, 2l);
System.out.println(c);
//----------------------------------------------------
tx.commit();
session.close();
}
@Test
// load方法(默认):是在执行时,不发送任何sql语句.返回一个对象.使用该对象时,才执行查询.
// 延迟加载: 仅仅获得没有使用.不会查询.在使用时才进行查询.
// 是否对类进行延迟加载: 可以通过在class元素上配置lazy属性来控制.
//lazy:true 加载时,不查询.使用时才查询b
//lazy:false 加载时立即查询.
public void fun2(){
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
//----------------------------------------------------
Customer c = session.load(Customer.class, 2l);
//----------------------------------------------------
tx.commit();
session.close();
System.out.println(c);
}
}
lazy(默认值):true, 查询类时,会返回代理对象.会在使用属性时,根据关联的session查询数据库.加载数据.
lazy:false.load方法会与get方法没有任何区别.调用时即加载数据.
结论:为了提高效率.建议使用延迟加载(懒加载)
注意:使用懒加载时要确保,调用属性加载数据时,session还是打开的.不然会抛出No Session异常
2.3.2 关联级别查询
Customer.hbm.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.itheima.domain" >
<class name="Customer" table="cst_customer" lazy="false" >
<id name="cust_id" >
<generator class="native"></generator>
</id>
<property name="cust_name" column="cust_name" ></property>
<property name="cust_source" column="cust_source" ></property>
<property name="cust_industry" column="cust_industry" ></property>
<property name="cust_level" column="cust_level" ></property>
<property name="cust_linkman" column="cust_linkman" ></property>
<property name="cust_phone" column="cust_phone" ></property>
<property name="cust_mobile" column="cust_mobile" ></property>
<!--
lazy属性: 决定是否延迟加载
true(默认值): 延迟加载,懒加载
false: 立即加载
extra: 极其懒惰
fetch属性: 决定加载策略.使用什么类型的sql语句加载集合数据
select(默认值): 单表查询加载
join: 使用多表查询加载集合
subselect:使用子查询加载集合
-->
<!-- batch-size: 抓取集合的数量为3.
抓取客户的集合时,一次抓取几个客户的联系人集合.
-->
<set name="linkMens" batch-size="3" >
<key column="lkm_cust_id" ></key>
<one-to-many class="LinkMan" />
</set>
</class>
</hibernate-mapping>
LinkMan.hbm.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="cn.itcast.domain" >
<class name="LinkMan" table="cst_linkman" >
<id name="lkm_id" >
<generator class="native"></generator>
</id>
<property name="lkm_gender" ></property>
<property name="lkm_name" ></property>
<property name="lkm_phone" ></property>
<property name="lkm_email" ></property>
<property name="lkm_qq" ></property>
<property name="lkm_mobile" ></property>
<property name="lkm_memo" ></property>
<property name="lkm_position" ></property>
<!--
fetch 决定加载的sql语句
select: 使用单表查询
join : 多表查询
lazy 决定加载时机
false: 立即加载
proxy: 由customer的类级别加载策略决定.
-->
<many-to-one name="customer" column="lkm_cust_id" class="Customer" fetch="join" lazy="proxy" >
</many-to-one>
</class>
</hibernate-mapping>
测试代码:
//关联级别 延迟加载 & 抓取策略
public class TestLazyFetch {
@Test
//fetch:select 单表查询
//lazy:proxy
//customer-true 懒加载
public void fun1(){
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
//----------------------------------------------------
LinkMan lm = session.get(LinkMan.class, 3l);
Customer customer = lm.getCustomer();
System.out.println(customer);
//----------------------------------------------------
tx.commit();
session.close();
}
@Test
//fetch:join 多表
//lazy: 失效
public void fun3(){
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
//----------------------------------------------------
LinkMan lm = session.get(LinkMan.class, 3l);
Customer customer = lm.getCustomer();
System.out.println(customer);
//----------------------------------------------------
tx.commit();
session.close();
}
@Test
//fetch:select 单表查询
//lazy:proxy
//customer-false 立即加载
public void fun2(){
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
//----------------------------------------------------
LinkMan lm = session.get(LinkMan.class, 3l);
Customer customer = lm.getCustomer();
System.out.println(customer);
//----------------------------------------------------
tx.commit();
session.close();
}
}
结论:为了提高效率.fetch的选择上应选择select. lazy的取值应选择 true. 全部使用默认值.
no-session问题解决: 扩大session的作用范围.
2.3.3批量抓取
<!-- batch-size: 抓取集合的数量为3.
抓取客户的集合时,一次抓取几个客户的联系人集合.
-->
<set name="linkMens" batch-size="3" >
<key column="lkm_cust_id" ></key>
<one-to-many class="LinkMan" />
</set>
3.crm练习案例
练习:为客户列表增加查询条件
Servlet层方法:
public String list(HttpServletRequest request, HttpServletResponse response) throws Exception{
DetachedCriteria dc = DetachedCriteria.forClass(Customer.class);
//获取当期页
int currentPage = 1;
//如果参数为空 默认显示第一页
if (request.getParameter("currentPage")!=null && !request.getParameter("currentPage").equals("")) {
currentPage = Integer.parseInt(request.getParameter("currentPage"));
}
//获取筛选客户名称
String cust_name = request.getParameter("cust_name");
if (cust_name!=null) {
//如果筛选客户名称不为空添加模糊查询条件
dc.add(Restrictions.like("cust_name", "%"+cust_name+"%"));
}
//设置每一页显示几条记录
int pageSize = 10;
//调用业务层获取客户列表
PageBean<Customer> pb = customerService.getCustomerByPage(dc,currentPage,pageSize);
request.setAttribute("pb", pb);
return "/jsp/customer/list.jsp";
}
Service层方法:
public PageBean<Customer> getCustomerByPage(DetachedCriteria dc, int currentPage, int pageSize) throws Exception {
// 获取客户列表
List<Customer> list = customrDao.getCustomerListByPage(dc,currentPage,pageSize);
// 获取总数量
int count = customrDao.getCount();
// 封装到pagebean
PageBean<Customer> pb = new PageBean<>(list, currentPage, pageSize, count);
return pb;
}
Dao层方法:
public List<Customer> getCustomerListByPage(DetachedCriteria dc, int currentPage, int pageSize) {
// TODO Auto-generated method stub
// 打开会话
Session session = HibernateUtils.openSession();
Criteria criteria = null;
//判断 dc是否为空
if (dc==null) {
criteria = session.createCriteria(Customer.class);
}else{
//将dc转换为criteria
criteria = dc.getExecutableCriteria(session);
}
// 设置分页参数
criteria.setFirstResult((currentPage-1)*pageSize);
criteria.setMaxResults(pageSize);
// 获取客户列表
List<Customer> list = criteria.list();
// 关闭session
session.close();
return list;
}