一、HQL
1、基本语法
public class Demo {
@Test
//基本语法
public void fun1() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
/*String hql ="from com.sh.domain.Customer";*///完整写法
String hql = "from Customer";//简单写法
Query query = session.createQuery(hql);
List<Customer> list = query.list();
System.out.println(list);
tx.commit();
session.close();
}
@Test
//排序
public void fun2() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
String hql = "from Customer order by cust_id desc";//简单写法 asc desc
Query query = session.createQuery(hql);
List<Customer> list = query.list();
System.out.println(list);
tx.commit();
session.close();
}
@Test
//条件查询
public void fun3() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
String hql = "from Customer where cust_id=?";
Query query = session.createQuery(hql);
query.setParameter(0, 2l);
Customer c1 = (Customer) query.getSingleResult();
Customer c2 = (Customer) query.uniqueResult();
System.out.println(c1+"--"+c2);
tx.commit();
session.close();
}
@Test
//分页查询
public void fun4() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
String hql = "from Customer";
Query query = session.createQuery(hql);
query.setFirstResult(0);
query.setMaxResults(2);
List<Customer> 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 Customer";
String hql2 = "select sum(cust_id) from Customer";
String hql3 = "select avg(cust_id) from Customer";
String hql4 = "select min(cust_id) from Customer";
String hql5 = "select max(cust_id) from Customer";
Query query = session.createQuery(hql5);
Number result = (Number) query.uniqueResult();
System.out.println(result);
tx.commit();
session.close();
}
@Test
//投影查询
public void fun6() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
String hql1 = "select cust_name from Customer";
String hql2 = "select cust_name,cust_id from Customer";
String hql3 = "select new Customer(cust_id,cust_name) from Customer";
Query query = session.createQuery(hql3);
List list = query.list();
System.out.println(list);
tx.commit();
session.close();
}
}
2、多表查询语法
public class Demo2 {
/**
* 原生SQL
* 交叉连接-笛卡尔积(避免)
* select * from A,B;
* 内连接
* - 隐式内连接
* select * from where b.aid = a,bid;
* - 显式内连接
* select * from A inner join B on b.aid = a.bid;
* 外连接
* - 左外
* select * from A left[outer] join B on b.aid = a.bid;
* - 右外
* select * from A right[outer] join B on b.aid = a.bid;
*
* 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<Object[]> list = query.list();
for (Object[] arr : list) {
System.out.println(Arrays.toString(arr));
}
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();
}
}
二、Criteria查询
1、基本语法
public class Demo {
@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(3l));
c.add(Restrictions.eq("cust_id",2l));
List<Customer> list = c.list();
System.out.println(list);
tx.commit();
session.close();
}
@Test
//分页查询
public void fun3() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
Criteria c = session.createCriteria(Customer.class);
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<Customer> list = c.list();
System.out.println(list);
tx.commit();
session.close();
}
}
2、Criteria离线查询
public class Demo2 {
@Test
// Criteria离线查询
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<Customer> list = c.list();
System.out.println(list);
tx.commit();
session.close();
}
}
三、查询优化
1、类级别策略
延迟加载(懒加载)
public class Demo {
@Test
//get方法
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 加载时,不查询,使用时彩查询
//lazy:false 加载时立即查询
public void fun2() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
Customer c = session.load(Customer.class,2l);
System.out.println(c);
tx.commit();
session.close();
}
}
2、关联级别策略&抓取策略
/**
* @author Amaze_lee
* @date 2017年10月18日 下午8:00:21
* @version V1.0
* @Description: 关联级别延迟加载&抓取策略
*/
public class Demo {
@Test
// 集合级别的关联
// fetch:select 单表查询
// lazy:true 使用时才加载集合数据
public void fun1() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
Customer c = session.load(Customer.class, 2l);
Set<LinkMan> linkMens = c.getLinkMens();
System.out.println(linkMens);// 关联级别
tx.commit();
session.close();
}
@Test
// 集合级别的关联
// fetch:select 单表查询
// lazy:false 立即加载集合数据
public void fun2() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
Customer c = session.load(Customer.class, 2l);
Set<LinkMan> linkMens = c.getLinkMens();
System.out.println(linkMens);// 关联级别
tx.commit();
session.close();
}
@Test
// 集合级别的关联
// fetch:select 单表查询
// lazy:extra 及其懒惰 与懒加载基本一致,如果只获得集合的size,只查询集合的size(count语句)
public void fun3() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
Customer c = session.load(Customer.class, 2l);
Set<LinkMan> linkMens = c.getLinkMens();
System.out.println(linkMens.size());// 关联级别
tx.commit();
session.close();
}
// 集合级别的关联
// fetch:join 多表查询
// lazy:true|false|extra 失效.立即加载.
@Test
public void fun4() {
Session session = HibernateUtils.openSession();
Transaction tx = session.beginTransaction();
// ----------------------------------------------------
Customer c = session.get(Customer.class, 2l);
Set<LinkMan> linkMens = c.getLinkMens();// 关联级别
System.out.println(linkMens.size());
System.out.println(linkMens);
// ----------------------------------------------------
tx.commit();
session.close();
}
@Test
// fetch: subselect 子查询
// lazy: true 懒加载
public void fun5() {
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();
}
@Test
// fetch: subselect 子查询
// lazy: false 立即加载
public void fun6() {
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();
}
@Test
// fetch: subselect 子查询
// lazy: extra 极其懒惰
public void fun7() {
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();
}
}
//关联级别 延迟加载 & 抓取策略
public class Demo2 {
@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();
}
}