1.Hibernate查询总结
(1)查询方法共有5种:
- oid查询(session.get(Customer.class, 23l))
- 对象属性导航查询(01.查询cid=3的客户 Customer customer = session.get(Customer.class, 3); 02.再查询cid=3的客户的所有联系人 :通过客户的联系人属性获取其所有的联系人 Set<LinkMan> linkMans = customer.getSetLinkMan();)
- HQL查询(面向对象的语句查询)
- Criteria查询(面向对象的无语句查询)
- 原生SQL
在Hibernate02中有记录,此处添加补充:
(1.1)HQL查询补充
排序查询:
@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
//统计查询
//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 ";//Customer要有相应的构造方法和空参构造方法
//new Customer(cust_id,cust_name) 把查询到的id和name封装成Customer,而不是一个数组
Query query = session.createQuery(hql3);
List list = query.list();
System.out.println(list);
//----------------------------------------------------
tx.commit();
session.close();
}
多表查询(不常用):
package cn.itcast.a_hql;
import java.util.Arrays;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.junit.Test;
import cn.itcast.domain.Customer;
import cn.itcast.utils.HibernateUtils;
//学习HQL语法(不常用) - 多表查询语法
public class Demo2 {
//回顾-原生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();
}
}
(1.2)Criteria查询补充
排序查询:
@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)Criteria离线查询。
在dao层给Criteria添加查询条件,那么相似的功能就需要写多个方法。而在web层创建一个离线的Criteria,组装好条件,传到dao层直接查询,可以减少dao层的重复代码。
非离线查询:
离线查询:
@Test
public void test2(){
//web层/service层
DetachedCriteria dc=DetachedCriteria.forClass(Customer.class);
dc.add(Restrictions.eq("cust_id", 24l));//拼装条件和criteria一样
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
//dao层
Criteria criteria = dc.getExecutableCriteria(session);//在dao层获取criteria
Object rs = criteria.uniqueResult();
System.out.println(rs);
tx.commit();
session.close();
}
2.查询优化(了解)
(1)类级别查询
get方法:没有任何策略.调用即立即查询数据库加载数据.
load方法: 调用时不查询,使用的时候才查询
(2)关联级别查询
<?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="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>
<?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>
结论:为了提高效率.fetch的选择上应选择select. lazy的取值应选择 true. 全部使用默认值.