本博客的目的:①总结自己的学习过程,相当于学习笔记 ②将自己的经验分享给大家,相互学习,互相交流,不可商用
内容难免出现问题,欢迎指正,交流,探讨,可以留言,也可以通过以下方式联系。
本人互联网技术爱好者,互联网技术发烧友
微博:伊直都在0221
QQ:951226918
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1.QBC 检索和本地 SQL 检索
1)QBC 查询就是通过使用 Hibernate 提供的 Query By Criteria API 来查询对象,这种 API 封装了 SQL 语句的动态拼装,对查询提供了更加面向对象的功能接口
2)本地SQL查询来完善HQL不能涵盖所有的查询特性:添加操作
2.QBC 的具体实践和相关API
1)基本的QBC 查询
@Test
public void testQBC(){
//1.创建一个Criteria 对象
Criteria criteria = session.createCriteria(Employee.class); //2.添加查询条件: 在QBC 中查询条件使用 Criteria 表示
//Criteria 可以通过 Restrictions 的静态方法得到
criteria.add(Restrictions.eq("email", "SKUMAR"));
criteria.add(Restrictions.gt("salary", 5000F)); //3.执行查询
Employee employee = (Employee) criteria.uniqueResult();
System.out.println(employee);
}
2)带 AND 和 OR 的QBC: Restrictions 封装了一个常用的逻辑运算,算术等
@Test
public void testQBC2(){
Criteria criteria = session.createCriteria(Employee.class); //1.AND :使用Conjunction 表示
//conjuntion 本身就是一个criteria 对象,且其中还可以添加Criteria 对象
Conjunction conjunction = Restrictions.conjunction();
conjunction.add(Restrictions.like("name", "a",MatchMode.ANYWHERE));
Department dept = new Department();
dept.setId(80);
conjunction.add(Restrictions.eq("dept", dept));
System.out.println(conjunction); //(name like %a% and dept=Department [id=80]) //2.OR
Disjunction disjunction = Restrictions.disjunction();
disjunction.add(Restrictions.ge("salary", 6000F));
disjunction.add(Restrictions.isNotNull("email") ); System.out.println(disjunction);//(salary>=6000.0 or email is not null) //添加至 criteria
criteria.add(disjunction);
criteria.add(conjunction); //执行
criteria.list(); }
Hibernate:
select
this_.ID as ID1_1_1_,
this_.NAME as NAME2_1_1_,
this_.SALARY as SALARY3_1_1_,
this_.EMAIL as EMAIL4_1_1_,
this_.DEPT_ID as DEPT_ID5_1_1_,
department2_.ID as ID1_0_0_,
department2_.NAME as NAME2_0_0_
from
GG_EMPLOYEE this_
left outer join
GG_DEPARTMENT department2_
on this_.DEPT_ID=department2_.ID
where
(
this_.SALARY>=?
or this_.EMAIL is not null
)
and (
this_.NAME like ?
and this_.DEPT_ID=?
)
3)统计查询:Projections 封装了一些 sql的聚合函数
@Test
public void testQBC3(){
Criteria criteria = session.createCriteria(Employee.class); //统计查询:使用Projection 来表示 可以由Projections 静态方法得到
criteria.setProjection(Projections.max("salary"));
System.out.println(criteria.uniqueResult()); }
Hibernate:
select
max(this_.SALARY) as y0_
from
GG_EMPLOYEE this_
24000.0
4)排序,分页
@Test
public void testQBC4(){
Criteria criteria = session.createCriteria(Employee.class);
//1.添加排序
criteria.addOrder(Order.asc("salary"));
criteria.addOrder(Order.desc("email")); //2.添翻页方法
int pageSize = 5;
int pageNo = 3;
criteria.setFirstResult((pageNo -1 ) * pageSize)
.setMaxResults(pageSize)
.list(); }
Hibernate:
select
*
from
( select
row_.*,
rownum rownum_
from
( select
this_.ID as ID1_1_1_,
this_.NAME as NAME2_1_1_,
this_.SALARY as SALARY3_1_1_,
this_.EMAIL as EMAIL4_1_1_,
this_.DEPT_ID as DEPT_ID5_1_1_,
department2_.ID as ID1_0_0_,
department2_.NAME as NAME2_0_0_
from
GG_EMPLOYEE this_
left outer join
GG_DEPARTMENT department2_
on this_.DEPT_ID=department2_.ID
order by
this_.SALARY asc,
this_.EMAIL desc ) row_
where
rownum <= ?
)
where
rownum_ > ?
3.由于QBC 不支持插入操作,所以hibernate 支持使用原生的 sql 进行更新操作
@Test
public void testNaviteSQL(){
String sql = "INSERT INTO gg_department VALUES(?,?)";
Query query = session.createSQLQuery(sql); query.setInteger(0, 300)
.setString(1, "JASON")
.executeUpdate(); }
Hibernate:
INSERT
INTO
gg_department VALUES
(?,?)
4.QBC 支持删除操作
@Test
public void testUpdate(){
String hql = "DELETE FROM Department d WHERE d.id = :id";
session.createQuery(hql).setInteger("id", 280).executeUpdate();
}
Hibernate:
delete
from
GG_DEPARTMENT
where
ID=?