【1】QBC检索
QBC 查询就是通过使用 Hibernate 提供的 Query By Criteria API 来查询对象，这种 API 封装了 SQL 语句的动态拼装，对查询提供了更加面向对象的功能接口。即使用面向对象的思想实现动态SQL拼接一样的功能，可以理解一下HQL和标准SQL。
这里就需要了解几个接口和实现类。
① Criterion接口
An object-oriented representation of a query criterion that may be used as a restriction in a Criteria query.
查询准则的面向对象表示，可作为Criteria 查询中的规则。
Built-in criterion types are provided by the Restrictions factory class. This interface might be implemented by application classes that define custom restriction criteria.
内置的标准类型由Restrictions 工厂类提供。此接口可以由定义自定义约束标准的应用程序类实现。
其源码如下：
public interface Criterion extends Serializable { /** * Render the SQL fragment * * @param criteria The local criteria * @param criteriaQuery The overal criteria query * * @return The generated SQL fragment * @throws org.hibernate.HibernateException Problem during rendering. */ public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException; /** * Return typed values for all parameters in the rendered SQL fragment * * @param criteria The local criteria * @param criteriaQuery The overal criteria query * * @return The types values (for binding) * @throws HibernateException Problem determining types. */ public TypedValue[] getTypedValues(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException; }
其实现类如下图所示：
② Restrictions类
该类主要产生Criterion对象，Criteria 通过组合一系列的Criterion对象来检索实体。
方法如下图所示：
③ Criteria接口
继承自CriteriaSpecification，Criteria实现类为CriteriaImpl，Criteria的javadoc如下：
功能说明：Criteria是一个检索entities的简单API，通过组合Criterion对象实现检索功能 <tt>Criteria</tt> is a simplified API for retrieving entities * by composing <tt>Criterion</tt> objects. This is a very * convenient approach for functionality like "search" screens * where there is a variable number of conditions to be placed * upon the result set.<br> * <br> * The <tt>Session</tt> is a factory for <tt>Criteria</tt>. * <tt>Criterion</tt> instances are usually obtained via * the factory methods on <tt>Restrictions</tt>. eg. * <pre> * List cats = session.createCriteria(Cat.class) * .add( Restrictions.like("name", "Iz%") ) * .add( Restrictions.gt( "weight", new Float(minWeight) ) ) * .addOrder( Order.asc("age") ) * .list(); * </pre> * 导航对象图检索方式 * You may navigate associations using <tt>createAlias()</tt> or * <tt>createCriteria()</tt>. * <pre> * List cats = session.createCriteria(Cat.class) * .createCriteria("kittens") * .add( Restrictions.like("name", "Iz%") ) * .list(); * </pre> * <pre> * List cats = session.createCriteria(Cat.class) * .createAlias("kittens", "kit") * .add( Restrictions.like("kit.name", "Iz%") ) * .list(); * </pre> * 报表查询--聚集函数 * You may specify projection and aggregation using <tt>Projection</tt> * instances obtained via the factory methods on <tt>Projections</tt>. * <pre> * List cats = session.createCriteria(Cat.class) * .setProjection( Projections.projectionList() * .add( Projections.rowCount() ) * .add( Projections.avg("weight") ) * .add( Projections.max("weight") ) * .add( Projections.min("weight") ) * .add( Projections.groupProperty("color") ) * ) * .addOrder( Order.asc("color") ) * .list(); * </pre>
④ Projections
一系列聚集函数的工厂类，用来产生Projection(An object-oriented representation of a query resultset projection in a Criteria query.)对象，其方法如下图所示：
【2】代码测试
① 普通查询
测试代码如下：
@Test public void testQBC(){ //1. 创建一个 Criteria 对象 Criteria criteria = session.createCriteria(Employee.class); //2. 添加查询条件: 在 QBC 中查询条件使用 Criterion 来表示 //Criterion 可以通过 Restrictions 的静态方法得到 criteria.add(Restrictions.eq("email", "AA@QQ.COM")); criteria.add(Restrictions.gt("salary", 500F)); //3. 执行查询 Employee employee = (Employee) criteria.uniqueResult(); System.out.println(employee); }
测试结果如下：
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_.DEPT_ID as DEPT_ID5_1_0_ from GG_EMPLOYEE this_ where this_.EMAIL=? and this_.SALARY>? Employee [id=1]
上面这种方式与普通HQL查询是等价的。
String HQL = "FROM Employee e where e.email = :email and e.salary > :salary";
② And 和Or查询
And使用Conjunction表示，Or使用Disjunction表示，其均是Junction子类。
测试代码如下：
@Test public void testQBC2(){ Criteria criteria = session.createCriteria(Employee.class); //1. AND: 使用 Conjunction 表示 //Conjunction 本身就是一个 Criterion 对象 //且其中还可以添加 Criterion 对象 Conjunction conjunction = Restrictions.conjunction(); conjunction.add(Restrictions.like("name", "A", MatchMode.ANYWHERE)); Department dept = new Department(); dept.setId(1); conjunction.add(Restrictions.eq("dept", dept)); System.out.println(conjunction); //2. OR Disjunction disjunction = Restrictions.disjunction(); disjunction.add(Restrictions.ge("salary", 3000F)); disjunction.add(Restrictions.isNull("email")); System.out.println(disjunction); criteria.add(disjunction); criteria.add(conjunction); List<Employee> list = criteria.list(); for(Employee employee:list){ System.out.println(employee); } }
测试结果如下：
(name like %A% and dept=Department [id=1]) (salary>=3000.0 or email is null) 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_.DEPT_ID as DEPT_ID5_1_0_ from GG_EMPLOYEE this_ where ( this_.SALARY>=? or this_.EMAIL is null ) and ( this_.NAME like ? and this_.DEPT_ID=? ) Employee [id=1]
③ 对象导航检索测试
测试代码如下：
@Test public void testQBC1(){ List<Employee> list = session.createCriteria(Employee.class). // createCriteria("dept","d") // .add( Restrictions.like("d.name", "%A%") ) createCriteria("dept") .add( Restrictions.like("name", "%A%") ) .list(); for(Employee employee:list){ System.out.println(employee); } }
测试结果如下：
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_, department1_.ID as ID1_0_0_, department1_.NAME as NAME2_0_0_ from GG_EMPLOYEE this_ inner join GG_DEPARTMENT department1_ on this_.DEPT_ID=department1_.ID where department1_.NAME like ? Employee [id=1] Employee [id=2]
④ 聚集函数测试
测试代码如下：
@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_ 10000.0
⑤ 分页和排序
测试代码如下：
@Test public void testQBC4(){ Criteria criteria = session.createCriteria(Employee.class); //1. 添加排序 criteria.addOrder(Order.asc("salary")); criteria.addOrder(Order.desc("email")); //2. 添加翻页方法 int pageSize = 2; int pageNo = 2; List<Employee> list = criteria.setFirstResult((pageNo - 1) * pageSize) .setMaxResults(pageSize) .list(); for(Employee employee:list){ System.out.println(employee); } }
测试结果如下：
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_.DEPT_ID as DEPT_ID5_1_0_ from GG_EMPLOYEE this_ order by this_.SALARY asc, this_.EMAIL desc limit ?, ? Employee [id=8] Employee [id=2]
【3】本地SQL查询
本地SQL查询来完善HQL不能涵盖所有的查询特性，这里主要使用createSQLQuery或createNativeQuery来创建NativeQuery 对象。二者有所区别的是前者创建的query对象在设置参数时索引从0开始，后者从1开始。
测试代码如下：
@Test public void testNativeSQL(){ String sql = "INSERT INTO gg_department VALUES(?, ?)"; Query query = session.createSQLQuery(sql); int executeUpdate = query.setInteger(0, 15).setString(1, "JANE").executeUpdate(); // NativeQuery query = session.createNativeQuery(sql); // int executeUpdate = query.setInteger(1, 15).setString(2, "JANE").executeUpdate(); System.out.println(executeUpdate); }
测试结果如下：
Hibernate: INSERT INTO gg_department VALUES (?, ?) 1
【4】HQL的更新和删除操作
前面所讲的HQL均是各种查询操作，同样HQL也支持更新和删除(但是不支持插入操作)。
测试代码如下：
@Test public void testHQLUpdate(){ String hql = "DELETE FROM Department d WHERE d.id = :id"; session.createQuery(hql).setInteger("id", 280) .executeUpdate(); }
测试结果如下：
Hibernate: delete from GG_DEPARTMENT where ID=?
如果尝试使用HQL执行插入操作，则会抛出异常。
