- QBC检索
QBC查询就是通过使用Hibernate提供的Query By Criteria API来查询对象，这种API封装了SQL语句的动态拼装，对查询提供了更加面向对象的功能接口。
1）通过Critera实现具有条件的查询
@Test
public void testCriteria00() {
// 1、创建criteria对象
Criteria criteria = session.createCriteria(Employee.class); // 2、添加过滤条件可以用Criterion表，Criterion可以通过Restrictions的静态方法返回。
criteria.add(Restrictions.eq("email", "[email protected]"));
criteria.add(Restrictions.gt("salary", 1000F)); System.out.println(criteria.uniqueResult());
}
执行sql及结果:
Hibernate:
select
this_.ID as ID1_1_0_,
this_.NAME as NAME2_1_0_,
this_.SALARY as SALARY3_1_0_,
this_.EMAIL as EMAIL4_1_0_,
this_.DEPARTMENT_ID as DEPARTME5_1_0_
from
DX_EMPLOYEE this_
where
this_.EMAIL=?
and this_.SALARY>?
Employee [id=11, name=tommy10, salary=10000.0, [email protected]]
2）通过Critera实现具有AND OR条件的查询
@Test
public void testCriteraAndOr() {
Criteria criteria = session.createCriteria(Employee.class); // AND :使用Conjunction表示，Conjunction本身就是一个Criterion对象，且其中还可以添加Criterion对象
Conjunction conjunction = Restrictions.conjunction();
conjunction.add(Restrictions.like("name", "2", MatchMode.ANYWHERE));
Department depart = new Department();
depart.setId(5);
conjunction.add(Restrictions.eq("department", depart)); // OR
Disjunction disjunction = Restrictions.disjunction();
disjunction.add(Restrictions.gt("salary", 10000F));
disjunction.add(Restrictions.isNull("email")); criteria.add(disjunction);
criteria.add(conjunction); List<Employee> items = (List<Employee>) criteria.list();
System.out.println(items.size());
}
执行sql及结果：
Hibernate:
select
this_.ID as ID1_1_0_,
this_.NAME as NAME2_1_0_,
this_.SALARY as SALARY3_1_0_,
this_.EMAIL as EMAIL4_1_0_,
this_.DEPARTMENT_ID as DEPARTME5_1_0_
from
DX_EMPLOYEE this_
where
(
this_.SALARY>?
or this_.EMAIL is null
)
and (
this_.NAME like ?
and this_.DEPARTMENT_ID=?
)
2
3）通过Critera实现统计查询：使用Projection 来表示
@Test
public void testStatistics(){
Criteria criteria=session.createCriteria(Employee.class); // 统计查询：使用Projection 来表示
criteria.setProjection(Projections.max("salary")); System.out.println(criteria.uniqueResult());
}
执行sql及结果：
Hibernate:
select
max(this_.SALARY) as y0_
from
DX_EMPLOYEE this_
79000.0
4）通过Critera实现排序、分页查询
@Test
public void testOrderByAndPager() {
Criteria criteria = session.createCriteria(Employee.class); // 1) Order By
criteria.addOrder(Order.desc("salary"));
criteria.addOrder(Order.desc("name")); // 2) Pager
int pageSize = 5;
int pageNum = 2;
List<Employee> employees = (List<Employee>) criteria.setFirstResult((pageNum - 1) * pageSize).setMaxResults(pageSize).list(); System.out.println(employees.size());
}
执行sql及结果：
Hibernate:
select
this_.ID as ID1_1_0_,
this_.NAME as NAME2_1_0_,
this_.SALARY as SALARY3_1_0_,
this_.EMAIL as EMAIL4_1_0_,
this_.DEPARTMENT_ID as DEPARTME5_1_0_
from
DX_EMPLOYEE this_
order by
this_.SALARY desc,
this_.NAME desc limit ?,
?
5
更多关于QBC的使用方式需要参考Hibernate官网实例。
- 本地SQL查询
本地SQL查询来完善HQL不能涵盖所有的查询特性。
@Test
public void testNativeSqlInsert() {
String sql = "insert into dx_department(id,name)values(?,?);"; Query query = session.createSQLQuery(sql); query.setInteger(0, 7).setString(1, "移动部门").executeUpdate(); Department departs = session.get(Department.class, 7);
System.out.println(departs);
}
执行sql及结果：
Hibernate:
insert
into
dx_department
(id,name)
values
(?,?);
Hibernate:
select
department0_.ID as ID1_0_0_,
department0_.NAME as NAME2_0_0_
from
DX_DEPARTMENT department0_
where
department0_.ID=?
Department [id=7, name=移动部门]
- HQL删除操作：
@Test
public void testDeleteWithHQL() {
String hql = "DELETE FROM Department d WHERE d.id=:id";
session.createQuery(hql).setInteger("id", 7).executeUpdate();
}
执行sql及结果：
Hibernate:
delete
from
DX_DEPARTMENT
where
ID=?