HQL查询
HQL基本查询
Transaction transaction = session.beginTransaction();
String hql1 = "from java.lang.Object";//获取所有数据
String hql2 = "from com.java.Customer";//完整写法
//String hql2 = "from Customer";//简写 在此项目中Customer不能重复
//把获得的数据封装在Customer中
String hql3 = "select new Customer(id,name) from com.java.Customer";
//聚合函数 count sum avg max min 用Number类接收不确定类型的数据
//String hql3 = "select sum(id) com.java.Customer";
//String hql3 = "select avg(id) com.java.Customer";
//String hql3 = "select max(id) com.java.Customer";
//String hql3 = "select min(*) com.java.Customer";
String hql4 = "select count(*) com.java.Customer";
//设置参数
String hq5 = "from Customer where id = ?";
//String hql4 = "form Customer where id = :id";
Query query = session.createQuery(hql6);
//query.setParameter(0, 3);
query.setParameter("id", 3l);
//设置参数分页给limit
query.setFirstResult(0);//从0条开始查
query.setMaxResults(3);//每页显示两条
List<Customer> customers = query.list();
System.out.println(customers);
transaction.commit();
session.close();
sessionFactory.close();
HQL内外连接查询(无笛卡尔积)
Transaction transaction = session.beginTransaction();
//相当于select * from A a inner join B b on a.id = b.id
String hql = "from Customer c inner join c.linkMans";
Query query = session.createQuery(hql);
//返回的每条数据都是两个对象 一条数据一个Object[]
List<Object[]> objects = query.list();
transaction.commit();
HQL迫切内连接查询
Transaction transaction = session.beginTransaction();
//相当于select * from A a inner join fetch B b on a.id = b.id
String hql = "from Customer c inner join c.linkMans";
Query query = session.createQuery(hql);
//返回的每条数据是一个对象 把linkMan放在Customer里面
List<Customer> objects = query.list();
transaction.commit();
HQL左外连接查询&HQL右外连接查询 与实例相同 inner 改为 left|right
迫切HQL左外连接查询&迫切HQL右外连接查询 与实例相同 封装对象
Criteria查询
Criteria基本查询
Transaction transaction = session.beginTransaction();
//查询所有Customer对象
Criteria criteria = session.createCriteria(Customer.class);
//为Criteria查询添加条件 查询id为3的数据
criteria.add(Restrictions.eq("id", 3l));
//设置参数分页给limit
criteria.setFirstResult(0);//从0条开始查
criteria.setMaxResults(3);//每页显示两条
//排序 按照id列的升序对数据排序
//criteria.addOrder(Order.asc("id"));
//排序 按照id列的降序对数据排序
criteria.addOrder(Order.desc("id"));
//统计查询 设置条件为最大的id的值
criteria.setProjection(Projections.max("id"));
List<Customer> customers = criteria.list();
transaction.commit();
Restrictions的函数实现设置条件
方法名 | 说明 |
---|---|
Restrictions.eq | = |
Restrictions.allEq | 利用Map来进行多个等于的限制 |
Restrictions.gt | > |
Restrictions.ge | >= |
Restrictions.lt | < |
Restrictions.le | <= |
Restrictions.between | BETWEEN |
Restrictions.like | LIKE |
Restrictions.in | IN |
Restrictions.and | AND |
Restrictions.or | OR |
Restrictions.sqlRestriction | 用SQL限定查询 |
Projections的函数实现聚合函数
方法名 | 说明 |
---|---|
avg(String propertyName) | 计算属性字段的平均值 |
count(String propertyName) | 统计一个属性在结果中出现次数 |
countDistinct(String propertyName) | 统计属性包含的不重复值的数量 |
max(String propertyName) | 计算属性值最大值 |
min(String propertyName) | 计算属性值最小值 |
sum(String propertyName) | 计算属性值总和 |
Criteria离线查询(脱离session创建)
/*
* 离线对象作用
* 离线对象可体现JavaEE三层架构
* 在控制层离线的对象 并组装数据
* 在业务层对数据进行处理
* 在持久层操作数据库
*/
//获得离线的Criteria对象
DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Customer.class);
//与Criteria对象使用相同 运行时需要关联Session对象
detachedCriteria.add(Restrictions.eq("id", 3l));
Session session = HibernateUtils.openSession();
Transaction transaction = session.beginTransaction();
//从离线Criteria对象上获得可被执行的Criteria对象
Criteria criteria = detachedCriteria.getExecutableCriteria(session);
Customer customer = (Customer) criteria.uniqueResult();
transaction.commit();
查询优化(懒加载|延迟加载)
类级别延迟加载
/*
* load方法:原理返回代理对象 使用此对象时加载
* 注意 使用此对象时加载时Session为开启状态
* 延迟加载 仅获得没有使用 不会查询 在使用时才进行查询
* 是否对类进行延迟加载 可以通过在class元素上配置lazy属性来控制
* <class name="User" table="user" lazy="true" ></class>
* lazy:true 加载时 不查询 使用时才查询
* lazy:false 加载时立即查询 此时等于get
*/
Transaction transaction = session.beginTransaction();
//传统获得数据方式 执行完此行立即执行select
//Customer customer = session.get(Customer.class, 3l);
//懒加载方式获得数据 执行完此行会返回一个对象 此对象被使用时执行select
Customer customer = session.load(Customer.class, 3l);
transaction.commit();
关联级别延迟加载
<!--
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>
lazy属性&fetch属性
//集合级别的关联
//fetch:select 单表查询
//lazy:true 使用时才加载集合数据
@Test
public void test1() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
Customer c = session.get(Customer.class, 3l);
Set<LinkMan> linkMens = c.getLinkMens();//关联级别
System.out.println(linkMens);
tx.commit();
session.close();
}
//集合级别的关联
//fetch:select 单表查询
//lazy:false 立即记载集合数据
@Test
public void test2() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
Customer c = session.get(Customer.class, 3l);
Set<LinkMan> linkMens = c.getLinkMens();// 关联级别
System.out.println(linkMens);
tx.commit();
session.close();
}
//集合级别的关联
//fetch:select 单表查询
//lazy:extra 极其懒惰 与懒加载效果基本一致 如果只获得集合的size 只查询集合的size(count语句)
@Test
public void test3() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
Customer c = session.get(Customer.class, 3l);
Set<LinkMan> linkMens = c.getLinkMens();// 关联级别
System.out.println(linkMens.size());
System.out.println(linkMens);
tx.commit();
session.close();
}
//集合级别的关联
//fetch:join 多表查询
//lazy:true|false|extra 失效 立即加载
@Test
public void test4() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
Customer c = session.get(Customer.class, 3l);
Set<LinkMan> linkMens = c.getLinkMens();// 关联级别
System.out.println(linkMens.size());
System.out.println(linkMens);
tx.commit();
session.close();
}
//fetch:subselect 子查询
//lazy:true 懒加载
@Test
public void test5() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
String hql = "from Customer";
Query query = session.createQuery(hql);
List<Customer> list = query.list();
for (Customer c : list) {
System.out.println(c);
System.out.println(c.getLinkMens().size());
System.out.println(c.getLinkMens());
}
tx.commit();
session.close();
}
//fetch:subselect 子查询
//lazy:false 立即加载
@Test
public void test6() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
String hql = "from Customer";
Query query = session.createQuery(hql);
List<Customer> list = query.list();
for (Customer c : list) {
System.out.println(c);
System.out.println(c.getLinkMens().size());
System.out.println(c.getLinkMens());
}
tx.commit();
session.close();
}
//fetch:subselect 子查询
//lazy:extra 极其懒惰
@Test
public void test7() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
String hql = "from Customer";
Query query = session.createQuery(hql);
List<Customer> list = query.list();
for (Customer c : list) {
System.out.println(c);
System.out.println(c.getLinkMens().size());
System.out.println(c.getLinkMens());
}
tx.commit();
session.close();
}
//fetch:select 单表查询
//lazy:proxy
//customer-true 懒加载
@Test
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: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();
}
@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();
}
结论:为了提高效率 fetch的选择上应选择select lazy的取值应选择true 全部使用默认值
no-session问题解决:扩大session的作用范围
使用JavaEE Filter
Filter 前处理 打开session&事务
chain.doFilter(request,response) 放行->servlet->service->dao
Filter 后处理 关闭session&事务