------------------hql语法
- 基础
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();
- 排序
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();
- 条件
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();
- 分页
String hql1 = " from cn.itcast.domain.Customer ";//完整写法
Query query = session.createQuery(hql1);
//limit ?,?
// (当前页数-1)*每页条数
query.setFirstResult(2);
query.setMaxResults(2);
List list = query.list();
- 聚合
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();
- 投影
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();
- 多表查询
//回顾-原生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的多表查询
//内连接(迫切)
//外连接
// |-左外(迫切)
// |-右外(迫切)
- hql多表语法
//HQL 内连接 => 将连接的两端对象分别返回.放到数组中.
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));
}
//HQL 迫切内连接 => 帮我们进行封装.返回值就是一个对象
String hql = " from Customer c inner join fetch c.linkMens ";
Query query = session.createQuery(hql);
List<Customer> list = query.list();
//HQL 左外连接 => 将连接的两端对象分别返回.放到数组中.
String hql = " from Customer c left join c.linkMens ";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
//HQL 右外连接 => 将连接的两端对象分别返回.放到数组中.
String hql = " from Customer c right join c.linkMens ";
-------------------criteria
- 基本
Criteria c = session.createCriteria(Customer.class);
List<Customer> list = c.list();
- 条件
Criteria c = session.createCriteria(Customer.class);
// c.add(Restrictions.idEq(2l));
c.add(Restrictions.eq("cust_id",2l));
List<Customer> list = c.list();
- 分页
Criteria c = session.createCriteria(Customer.class);
//limit ?,?
c.setFirstResult(0);
c.setMaxResults(2);
List<Customer> list = c.list();
- 排序
Criteria c = session.createCriteria(Customer.class);
c.addOrder(Order.asc("cust_id"));
//c.addOrder(Order.desc("cust_id"));
List<Customer> list = c.list();
- 统计
Criteria c = session.createCriteria(Customer.class);
//设置查询目标
c.setProjection(Projections.rowCount());
List list = c.list();
----------------离线与非离线区别在于是否是有session创建的criteria
@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();
}
----------------查询优化
- 类级别优化是使用代理方式使之实体对象可以在使用时候才进行查询操作（类似将connection类的close方法重写）
- get方法:没有任何策略.调用即立即查询数据库加载数据.
- load方法: 应用类级别的加载策略
// load方法(默认):是在执行时,不发送任何sql语句.返回一个对象.使用该对象时,才执行查询.
// 延迟加载: 仅仅获得没有使用.不会查询.在使用时才进行查询.
// 是否对类进行延迟加载: 可以通过在class元素上配置lazy属性来控制.
//lazy:true 加载时,不查询.使用时才查询b
//lazy:false 加载时立即查询.
--------
<class name="Customer" table="cst_customer" lazy="false" >
-------------
Customer c = session.load(Customer.class, 2l);
- 集合策略
<!--
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>
- 关联属性策略
<!--
fetch 决定加载的sql语句
select: 使用单表查询
join : 多表查询
lazy 决定加载时机
false: 立即加载
proxy: 由customer的类级别加载策略决定.
-->
- 结论:为了提高效率.fetch的选择上应选择select. lazy的取值应选择 true. 全部使用默认值.
-
no-session问题解决: 扩大session的作用范围.
image.png
