数据库：Criteria与原生SQL查询

1 Criteria查询

       

Hibernate除了提供强大的HQL查询之外，还提供了一种称为Criteria的查询方法。HQL和SQL很相似，其特点是灵活和功能丰富，但缺点是使用者必须熟悉SQL的语法，而且在组合条件查询时，常常需要拼装Where条件，还得为条件提供参数。而Criteria查询更加面向对象，和Java代码结合得更好，在组合条件查询时往往更加方便。当然，Criteria也有其缺点，其可读性不如HQL高，功能也不如HQL多。

        Hibernate官方往往更推荐使用HQL去解决问题。

1.1Criteria的使用方法。

 

Criteria criteria = sess.createCriteria(Category.class);     //创建持久化类的查询对象Criteria

        criteria.add(规则);                                                             //设置查询规则criterion

        List list = query.list();                                                          //获取查询结果

 1.2规则－Criterion

Criterion 是Criteria的查询条件。Criteria 提供了add(Criterion criterion)方法来添加查询条件。Criterion 接口的主要实现包括：Example 、Junction和SimpleExpression。Junction 的实际使用是它的两个子类 conjunction 和 disjunction ，分别是使用 AND 和 OR 操作符进行来联结查询条件集合。

Criterion的实例可以通过Restrictions工厂类来提供，Restrictions 提供了大量的静态方法，如 eq（等于）、 ge（大于等于）、between等来方法的创建Criterion查询条件

（SimpleExpression实例）。除此之外，Restrictions还提供了方法来创建conjunction和

disjunction实例，通过往该实例的 add(Criteria) 方法来增加查询条件形成一个查询条件集合。

Restrictions中的静态方法条件

含义	Criteria	HQL
等于	Restrictions.eq()	=
不等于	Restrictions.not(Exprission.eq())	<>
大于	Restrictions.gt()	>
大于等于	Restrictions.ge()	>=
小于	Restrictions.lt()	<
小于等于	Restrictions.le()	<=
等于空	Restrictions.isnull()	is null
非空	Restrictions.isNotNull()	is not null
模糊查询	Restrictions.like()	like
逻辑与	Restrictions.and()	and
逻辑与	Restrictions.conjunction()	and
逻辑或	Restrictions.or()	or
逻辑或	Restrictions.disjunction()	or
逻辑非	Restrictions.not()	not
等于某一个值	Restrictions.in()	in( )
不等于任一个值	Restrictions.not(Restrictions.in())	not in()
区间	Restrictions.between()	between x and y
不在区间内	Restrictions.not(Restrictions..between())	not between x and y

（1）为Criteria添加简单类型属性限制（查询条件）。

        Criteria criteria = sess.createCriteria(Movie.class);

        criteria.add(Restrictions.like("title", "%狂%"));

        List<Movie> list = criteria.list();

（2）为Criteria添加关联类属性限制。

        直接使用criteria的add()方法，仅能添加简单类型属性限制和对于关联类的Id属性限制。若要添加关联类的其它属性限制（如为Movie实体添加关联类Category的name属性限制，必须重新createCriteria()并把关联属性名作为参数传入，然后就可以使用关联类Category的属性作为限制条件。

        Criteria criteria = sess.createCriteria(Movie.class);

        criteria = criteria.createCriteria("category");                   //重新执行createCriteria()

        criteria.add(Restrictions.eq("name","动漫"));

        List<Movie> list = criteria.list();

        上述的效果还可以通过criteria的createAlias()方法实现，与createCriteria不同，它只是给关联实体起一个别名，使用createAlias后依然可以使用被查询对象的其它属性作为限制。

        List<Movie> list = sess.createCriteria(Movie.class)

                                                  .createAlias("category", "c")

                                                  .add(Restrictions.eq("c.name", "战争"))

                                                  .add(Restrictions.like("title", "风%"))

                                                  .list();

 1.3用实体一次声明多个等于或者like规则的限制条件－Example

Example也是一种添加Criteria规则的方式，这种方式使用一个查询实体类的对象，一口气声明多个规则。Example 的创建有所不同，Example 本身提供了一个静态方法 create(Object entity)，参数是一个实体对象（实际使用中一般是映射好的实体对象）来创建。然后可以设置一些过滤条件：

        Example example = Example.create(entity)

                                                           .ignoreCase()   // 忽略大小写

                                                           .enableLike(MatchMode.ANYWHERE);  //使用like

        criteria.add(example);

        List list = criteria.list()

1.4实现排序 

你可以使用Criteria的addOrder(Order order) 控制查询结果的顺序。Order对象实例可以通过Order.asc("属性名") 和Order.desc("属性名")获取。

        List cats = sess.createCriteria(Cat.class)

                                  .add( Restrictions.like("name", "F%")

                                  .addOrder( Order.asc("name") )

                                  .addOrder( Order.desc("age") )

                                  .list() ;

1.5 实现分页

Criteria对象与Query对象一样可以通过setFirstResult() 和setMaxResults()方法实现分页

1.6 投影Projection—实现聚合函数和分组

Criteria可以通过setProjection(Projection projection)方法实现聚合统计和分组。

Projection主要是让Criteria能够进行统计查询，并可以实现分组。Projection主要有   SimpleProjection和ProjectionList实现。其中 SimpleProjection 和 ProjectionList 的创建是通过内建的 Projections静态方法来完成的，如提供的avg()、count()、max()、min()、sum()可以让开发者很容易对某个字段进行统计查询。Projections的groupProperty()方法还可以对结果进行分组。

        ProjectionList projectionList = Projections.projectionList();

        Projection prjCount = Projections.count("id");

        projectionList.add(prjCount);

        projectionList.add(Projections.groupProperty("category"));

        criteria.setProjection(projectionList);

        List<Object[]> list = criteria.list();

1.7 DetachedCriteria 

DetachedCriteria类和Criteria接口功能很类似，可以使用上述提到的方式（Criterion与Projection）设置查询条件，但两者的创建方式不同：Criteria必须由Session对象创建，而DetachedCriteria创建时不需要Session对象。因此DetachedCriteria可以在Session作用域之外构建，并添加一系列复杂条件，然后传递到具有Session环境的Dao方法中执行。DetachedCriteria的出现实现了“条件构建”和“查询执行”的分离。

public static void main(String[] args) {
		DetachedCriteria cri = DetachedCriteria.forClass(Movie.class);
		cri.add(Restrictions.eq("category.id", 1));
		List<Movie> list = get(cri);
		for(Movie m: list){
			System.out.println(m.getTitle()+","+m.getCategory().getName());
		}
	}
	static List get(DetachedCriteria cri){
		Session sess = null;
		try {
			sess = HibernateUtil.getSession();	
			return cri.getExecutableCriteria(sess).list();
		} finally {
			if(sess!=null)
				sess.close();
		}
	}

2 原生SQL操作 

2.1 原生SQL查询

虽然HQL已经足够强大，但由于不同的数据库系统对标准SQL有不同的扩展（如SQL Server的T-SQL、Oracle的PL/SQL，Hibernate中称作方言“Dialect”），因此HQL无法100%完成我们在本地SQL中可以实现的功能。而且HQL最终还是要转换到SQL执行的，这种自动转换总有不如人意的地方，过于复杂的HQL转换成SQL后，执行效率可能会较低。为此，Hibernate还保留了我们直接使用数据库本地SQL的权利，我们可以直接编写SQL语句，控制查询结果。值得注意的是，一旦使用了本地SQL，若将来为数据访问层切换另一种数据库系统时，就需要修改这些本地SQL，使之符合新的数据库方言。

（1）返回基本类型Object数组的本地SQL查询。

本地SQL查询与HQL查询的用法基本相似，不同的是SQL查询需要使用Session的createSQLQuery(String sql)方法，返回的查询对象为SQLQuery类型。

String sql = "select m.Title, c.name from Movie m inner join Category c on m.CategoryId=c.Id where c.name=:cname";

SQLQuery query = sess.createSQLQuery(sql);

query.setString("cname", "战争");

List<Object[]> list = query.list();

（2）直接返回映射实体的本地SQL查询。

我们常常希望通过本地SQL查询返回持久化实体对象，若用上述的方式，Hibernate的返回结果是基本类型的Object数组，要获取实体，还需要重新构建实体对象和设置属性。为了简化我们的工作，SQLQuery接口对象直接提供了addEntity(String alias, Class entityClass)方法，可以帮助我们直接把SQL结果填充到实体对象，返回实体对象数组和列表。

String sql = "select m.*, c.* from Movie m inner join Category c on m.CategoryId=c.Id where c.name=:cname";
SQLQuery query = sess.createSQLQuery(sql);
query.addEntity("m",Movie.class);
query.addEntity("c",Category.class);
query.setString("cname", "战争");
List<Object[]> list = query.list();
for(Object[] arr : list){
        Category c = (Category)arr[1];
        Movie m = (Movie)arr[0];
        System.out.println( c.getName() + "," + m.getTitle());
}

2.2 原生JDBC操作 

如果想更灵活的使用原生JDBC操作增删改，则可以使用Session对象提供的doWork方法，通过Work接口编写内部匿名类，我们可以调用JDBC的底层API来实现批量操作。

Session.doWork() 方法的执行方式如下所示。

Session session=HibernateUtil.openSession();
	Transaction transaction=session.beginTransaction();
	session.doWork(new Work() {
			public void execute(Connection conn) throws SQLException {
				//这里是SQL非HQL
				String sql = "delete from Movie where categoryId=?"; 
				PreparedStatement stmt = conn.prepareStatement(sql);
				stmt.setInt(1, 2);
				stmt.executeUpdate();
			}
	});
	transaction.commit();