Hibernate(十五):QBC检索、本地SQL检索和HQL删除
- QBC检索
QBC查询就是通过使用Hibernate提供的Query By Criteria API来查询对象,这种API封装了SQL语句的动态拼装,对查询提供了更加面向对象的功能接口。
1)通过Critera实现具有条件的查询
1 @Test 2 public void testCriteria00() { 3 // 1、创建criteria对象 4 Criteria criteria = session.createCriteria(Employee.class); 5 6 // 2、添加过滤条件可以用Criterion表,Criterion可以通过Restrictions的静态方法返回。 7 criteria.add(Restrictions.eq("email", "tommy10@dx.com")); 8 criteria.add(Restrictions.gt("salary", 1000F)); 9 10 System.out.println(criteria.uniqueResult()); 11 }
执行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=tommy10@dx.com]
2)通过Critera实现具有AND OR条件的查询
1 @Test 2 public void testCriteraAndOr() { 3 Criteria criteria = session.createCriteria(Employee.class); 4 5 // AND :使用Conjunction表示,Conjunction本身就是一个Criterion对象,且其中还可以添加Criterion对象 6 Conjunction conjunction = Restrictions.conjunction(); 7 conjunction.add(Restrictions.like("name", "2", MatchMode.ANYWHERE)); 8 Department depart = new Department(); 9 depart.setId(5); 10 conjunction.add(Restrictions.eq("department", depart)); 11 12 // OR 13 Disjunction disjunction = Restrictions.disjunction(); 14 disjunction.add(Restrictions.gt("salary", 10000F)); 15 disjunction.add(Restrictions.isNull("email")); 16 17 criteria.add(disjunction); 18 criteria.add(conjunction); 19 20 List<Employee> items = (List<Employee>) criteria.list(); 21 System.out.println(items.size()); 22 }
执行sql及结果:
1 Hibernate: 2 select 3 this_.ID as ID1_1_0_, 4 this_.NAME as NAME2_1_0_, 5 this_.SALARY as SALARY3_1_0_, 6 this_.EMAIL as EMAIL4_1_0_, 7 this_.DEPARTMENT_ID as DEPARTME5_1_0_ 8 from 9 DX_EMPLOYEE this_ 10 where 11 ( 12 this_.SALARY>? 13 or this_.EMAIL is null 14 ) 15 and ( 16 this_.NAME like ? 17 and this_.DEPARTMENT_ID=? 18 ) 19 2
3)通过Critera实现统计查询:使用Projection 来表示
1 @Test 2 public void testStatistics(){ 3 Criteria criteria=session.createCriteria(Employee.class); 4 5 // 统计查询:使用Projection 来表示 6 criteria.setProjection(Projections.max("salary")); 7 8 System.out.println(criteria.uniqueResult()); 9 }
执行sql及结果:
1 Hibernate: 2 select 3 max(this_.SALARY) as y0_ 4 from 5 DX_EMPLOYEE this_ 6 79000.0
4)通过Critera实现排序、分页查询
1 @Test 2 public void testOrderByAndPager() { 3 Criteria criteria = session.createCriteria(Employee.class); 4 5 // 1) Order By 6 criteria.addOrder(Order.desc("salary")); 7 criteria.addOrder(Order.desc("name")); 8 9 // 2) Pager 10 int pageSize = 5; 11 int pageNum = 2; 12 List<Employee> employees = (List<Employee>) criteria.setFirstResult((pageNum - 1) * pageSize).setMaxResults(pageSize).list(); 13 14 System.out.println(employees.size()); 15 }
执行sql及结果:
1 Hibernate: 2 select 3 this_.ID as ID1_1_0_, 4 this_.NAME as NAME2_1_0_, 5 this_.SALARY as SALARY3_1_0_, 6 this_.EMAIL as EMAIL4_1_0_, 7 this_.DEPARTMENT_ID as DEPARTME5_1_0_ 8 from 9 DX_EMPLOYEE this_ 10 order by 11 this_.SALARY desc, 12 this_.NAME desc limit ?, 13 ? 14 5
更多关于QBC的使用方式需要参考Hibernate官网实例。
- 本地SQL查询
本地SQL查询来完善HQL不能涵盖所有的查询特性。
1 @Test 2 public void testNativeSqlInsert() { 3 String sql = "insert into dx_department(id,name)values(?,?);"; 4 5 Query query = session.createSQLQuery(sql); 6 7 query.setInteger(0, 7).setString(1, "移动部门").executeUpdate(); 8 9 Department departs = session.get(Department.class, 7); 10 System.out.println(departs); 11 }
执行sql及结果:
1 Hibernate: 2 insert 3 into 4 dx_department 5 (id,name) 6 values 7 (?,?); 8 Hibernate: 9 select 10 department0_.ID as ID1_0_0_, 11 department0_.NAME as NAME2_0_0_ 12 from 13 DX_DEPARTMENT department0_ 14 where 15 department0_.ID=? 16 Department [id=7, name=移动部门]
- HQL删除操作:
1 @Test 2 public void testDeleteWithHQL() { 3 String hql = "DELETE FROM Department d WHERE d.id=:id"; 4 session.createQuery(hql).setInteger("id", 7).executeUpdate(); 5 }
执行sql及结果:
1 Hibernate: 2 delete 3 from 4 DX_DEPARTMENT 5 where 6 ID=?
基础才是编程人员应该深入研究的问题,比如:
1)List/Set/Map内部组成原理|区别
2)mysql索引存储结构&如何调优/b-tree特点、计算复杂度及影响复杂度的因素。。。
3)JVM运行组成与原理及调优
4)Java类加载器运行原理
5)Java中GC过程原理|使用的回收算法原理
6)Redis中hash一致性实现及与hash其他区别
7)Java多线程、线程池开发、管理Lock与Synchroined区别
8)Spring IOC/AOP 原理;加载过程的。。。
【+加关注】。