Hibernate 之 Criteria 、 Query 查询

hibernate的Criteria Query（转）

当查询数据时，人们往往需要设置查询条件。在SQL或HQL语句中，查询条件常常放在where子句中。此外，Hibernate还支持Criteria查询（Criteria Query），这种查询方式把查询条件封装为一个Criteria对象。在实际应用中，使用Session的createCriteria()方法构建一个org.hibernate.Criteria实例，然后把具体的查询条件通过Criteria的add()方法加入到Criteria实例中。这样，程序员可以不使用SQL甚至HQL的情况下进行数据查询，如例程9-1所示。

例程9-1  Criteria应用实例  

代码

Criteria cr = session.createCriteria(Student.class); //生成一个Criteria对象
cr.add(Restrictions.eq("name", "Bill"));//等价于where name=’Bill’
List list = cr.list();
Student stu = (Student)list.get(0);
System.out.println(stu.getName());

 

1．常用的查询限制方法

在例程9-1中，Restrictions.eq()方法表示equal，即等于的情况。Restrictions类提供了查询限制机制。它提供了许多方法，以实现查询限制。这些方法及其他一些criteria常用查询限制方法列于表9-1中。

表9-1  Criteria Query常用的查询限制方法

Method Summary
static Criterion	allEq(Map propertyNameValues)            Apply an "equals" constraint to each property in the key set of a Map
static Conjunction	and(Criterion... predicates)            Return the conjuction of multiple expressions
static LogicalExpression	and(Criterion lhs, Criterion rhs)            Return the conjuction of two expressions
static Criterion	between(String propertyName, Object lo, Object hi)            Apply a "between" constraint to the named property
static Conjunction	conjunction()            Group expressions together in a single conjunction (A and B and C...)
static Disjunction	disjunction()            Group expressions together in a single disjunction (A or B or C...)
static SimpleExpression	eq(String propertyName, Object value)            Apply an "equal" constraint to the named property
static PropertyExpression	eqProperty(String propertyName, String otherPropertyName)            Apply an "equal" constraint to two properties
static SimpleExpression	ge(String propertyName, Object value)            Apply a "greater than or equal" constraint to the named property
static PropertyExpression	geProperty(String propertyName, String otherPropertyName)            Apply a "greater than or equal" constraint to two properties
static SimpleExpression	gt(String propertyName, Object value)            Apply a "greater than" constraint to the named property
static PropertyExpression	gtProperty(String propertyName, String otherPropertyName)            Apply a "greater than" constraint to two properties
static Criterion	idEq(Object value)            Apply an "equal" constraint to the identifier property
static Criterion	ilike(String propertyName, Object value)            A case-insensitive "like", similar to Postgres ilike operator
static Criterion	ilike(String propertyName, String value, MatchMode matchMode)            A case-insensitive "like", similar to Postgres ilike operator
static Criterion	in(String propertyName, Collection values)            Apply an "in" constraint to the named property
static Criterion	in(String propertyName, Object[] values)            Apply an "in" constraint to the named property
static Criterion	isEmpty(String propertyName)            Constrain a collection valued property to be empty
static Criterion	isNotEmpty(String propertyName)            Constrain a collection valued property to be non-empty
static Criterion	isNotNull(String propertyName)            Apply an "is not null" constraint to the named property
static Criterion	isNull(String propertyName)            Apply an "is null" constraint to the named property
static SimpleExpression	le(String propertyName, Object value)            Apply a "less than or equal" constraint to the named property
static PropertyExpression	leProperty(String propertyName, String otherPropertyName)            Apply a "less than or equal" constraint to two properties
static SimpleExpression	like(String propertyName, Object value)            Apply a "like" constraint to the named property
static SimpleExpression	like(String propertyName, String value, MatchMode matchMode)            Apply a "like" constraint to the named property
static SimpleExpression	lt(String propertyName, Object value)            Apply a "less than" constraint to the named property
static PropertyExpression	ltProperty(String propertyName, String otherPropertyName)            Apply a "less than" constraint to two properties
static NaturalIdentifier	naturalId()            Consider using any of the natural id based loading stuff from session instead, especially in cases where the restriction is the full set of natural id values.
static SimpleExpression	ne(String propertyName, Object value)            Apply a "not equal" constraint to the named property
static PropertyExpression	neProperty(String propertyName, String otherPropertyName)            Apply a "not equal" constraint to two properties
static Criterion	not(Criterion expression)            Return the negation of an expression
static Disjunction	or(Criterion... predicates)            Return the disjuction of multiple expressions
static LogicalExpression	or(Criterion lhs, Criterion rhs)            Return the disjuction of two expressions
static Criterion	sizeEq(String propertyName, int size)            Constrain a collection valued property by size
static Criterion	sizeGe(String propertyName, int size)            Constrain a collection valued property by size
static Criterion	sizeGt(String propertyName, int size)            Constrain a collection valued property by size
static Criterion	sizeLe(String propertyName, int size)            Constrain a collection valued property by size
static Criterion	sizeLt(String propertyName, int size)            Constrain a collection valued property by size
static Criterion	sizeNe(String propertyName, int size)            Constrain a collection valued property by size
static Criterion	sqlRestriction(String sql)            Apply a constraint expressed in SQL.
static Criterion	sqlRestriction(String sql, Object[] values, Type[] types)            Apply a constraint expressed in SQL, with the given JDBC parameters.
static Criterion	sqlRestriction(String sql, Object value, Type type)            Apply a constraint expressed in SQL, with the given JDBC parameter.

例1：查询学生名字以t开头的所有Student对象。

Criteria cr = session.createCriteria(Student.class); cr.add(Restrictions.like(“name”, “t%”)) List list = cr.list(); Student stu = (Student)list.get(0);

或者使用另一种方式：

Criteria cr = session.createCriteria(Student.class); cr.add(Restrictions.like(“name”, “t”, MatchMode.START)) List list = cr.list(); Student stu = (Student)list.get(0);

例2：查询学生姓名在Bill, Jack和Tom之间的所有Student对象。

String[] names = {“Bill”, “Jack”, “Tom”} Criteria cr = session.createCriteria(Student.class); cr.add(Restrictions.in(“name”, names)) List list = cr.list(); Student stu = (Student)list.get(0);

例3：查询学生的年龄age等于22或age为空（null）的所有Student对象。

Criteria cr = session.createCriteria(Student.class); cr.add(Restrictions.eq(“age”, new Integer(22)); cr.add(Restrictions.isNull(“age”)); List list = cr.list(); Student stu = (Student)list.get(0);

例4：查询学生姓名以字母F开头的所有Student对象，并按姓名升序排序。

Criteria cr = session.createCriteria(Student.class); cr.add(Restrictions.like(“name”, “F%”); cr.addOrder(Order.asc(“name”)); List list = cr.list(); Student stu = (Student)list.get(0);

调用Order.asc的方法应是Criteria的addOrder()方法。

 

 

使用add()方法加入条件时，预设是使用and来组合条件，如果要用or的方式来组合条件，则可以使用Restrictions.or()方法，例如结合age等于（eq）20或（or）age为空（isNull）的条件：

Criteria criteria = session.createCriteria(User.class);  

criteria.add(Restrictions.or( 

                   Restrictions.eq("age", new Integer(20)),  

                   Restrictions.isNull("age")  

               )); 

List users = criteria.list(); 

 

观察所产生的SQL语句，将使用where与or子句完成SQL的条件查询：

Hibernate: select this_.id as id0_0_,this_.name as name0_0_, this_.age as age0_0_ from T_USER this_ where(this_.age=? or this_.age is null)

 

 

使用Criteria进行查询时，不仅仅能组合出SQL中where子句的功能，还可以组合出如排序、统计、分组等的查询功能。这就是Criteria进阶查询。

排序 
您可以使用Criteria进行查询，并使用org.hibernate.criterion.Order对结果进行排序，例如使用Oder.asc()，指定根据”age”由小到大排序（反之则使用desc()）：

Criteria criteria = session.createCriteria(User.class); 

criteria.addOrder(Order.asc("age")); 

List users = criteria.list(); 

注意在加入Order条件时，使用的是addOrder()方法，而不是add()方法，在产生SQL语句时，会使用order by与asc（desc）来进行排序指定：

Hibernate: select this_.id as id0_0_,this_.name as name0_0_, this_.age as age0_0_ from T_USER this_ order bythis_.age asc

 

限定查询笔数 
Criteria的setMaxResults()方法可以限定查询回来的笔数，如果配合setFirstResult()设定传回查询结果第一笔资料的位置，就可以实现简单的分页，例如传回第51笔之后的50笔资料（如果有的话）：

Criteria criteria = session.createCriteria(User.class); 

criteria.setFirstResult(51); 

criteria.setMaxResults(50); 

List users = criteria.list(); 

根据您所指定得资料库，Hibernate将自动产生与资料库相依的限定笔数查询子句，例如在MySQL中，将使用limit产生以下的SQL语句：

Hibernate: select this_.id as id0_0_,this_.name as name0_0_, this_.age as age0_0_ from T_USER this_ limit ?, ?

 

统计动作 
您可以对查询结果进行统计动作，使用org.hibernate.criterion.Projections的avg()、rowCount()、count()、max()、min ()、 countDistinct()等方法，再搭配Criteria的setProjection()方法加入条件设定，例如对查询结果的"age"作平均：

Criteria criteria = session.createCriteria(User.class); 

criteria.setProjection(Projections.avg("age")); 

List users = criteria.list(); 

上面的程式将由Hibernate自动产生SQL的avg函数进行平均计算：

Hibernate: select avg(this_.age) as y0_from T_USER this_

 

分组 
还可以配合Projections的groupProperty()来对结果进行分组，例如以"age"进行分组，也就是如果资料中"age"如果有 20、20、25、30，则以下会显示20、25、30：

Criteria criteria = session.createCriteria(User.class); 

criteria.setProjection(Projections.groupProperty("age")); 

List users = criteria.list(); 

上面的程式将由Hibernate自动产生SQL的group by子句进行分组计算：

Hibernate: select this_.age as y0_ fromT_USER this_ group by this_.age

如果想同时结合统计与分组功能，则可以使用org.hibernate.criterion.ProjectionList，例如下面的程式会计算每个年龄各有多少个人：

ProjectionList projectionList = Projections.projectionList(); 

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

projectionList.add(Projections.rowCount()); 

 

 

Criteria criteria = session.createCriteria(User.class); 

criteria.setProjection(projectionList); 

List users = criteria.list(); 

观察所产生的SQL语句，将使用group by先进行分组，再针对每个分组进行count函数的计数，

Hibernate: select this_.age as y0_,count(*) as y1_ from T_USER this_ group by this_.age

 

根据已知物件进行查询 
设定查询条件并非一定要使用Restrictions，如果属性条件很多，使用Restrictions也不方便，如果有一个已知的物件，则可以根据这个物件作为查询的依据，看看是否有属性与之类似的物件，例如：

User user = new User(); 

user.setAge(new Integer(30)); 

 

Criteria criteria = session.createCriteria(User.class); 

criteria.add(Example.create(user)); 

 

List users = criteria.list(); 

Criteria进阶查询中，您可以透过org.hibernate.criterion.Example的create()方法来建立Example实例，Example实作了 Criteria介面，因此可以使用add()方法加入至Criteria条件设定之中，Hibernate将自动过滤掉空属性，根据已知物件上已设定的属性，判定是否产生于where子句之中：

Hibernate: select this_.id as id0_0_,this_.name as name0_0_, this_.age as age0_0_ from T_USER this_ where(this_.age=?)

 

设定SQL范本 
如果您了解如何撰写SQL语句，想要设定一些Hibernate产生SQL时的范本，您也可以使用Restrictions的sqlRestriction()方法，提供SQL语法范本作限定查询，例如查询name以cater开头的资料：

Criteria criteria = session.createCriteria(User.class); 

criteria.add(Restrictions.sqlRestriction( 

"{alias}.name LIKE (?)", "cater%", Hibernate.STRING)); 

List users = criteria.list(); 

其中alias将被替换为与User类别相关的名称，而? 将被替换为cater%，也就是第二个参数所提供的值，sqlRestriction()方法第一个参数所设定的是where子句的部份，所以在SQL撰写时，不必再写where，观察所产生的SQL语句，将使用您所设定的SQL范本作为基础，来完成SQL的条件查询：

Hibernate: select this_.id as id0_0_,this_.name as name0_0_, this_.age as age0_0_ from T_USER this_ where this_.nameLIKE (?)

如果有多个查询条件，例如between子句的查询，则可以如下：

Criteria criteria = session.createCriteria(User.class); 

Integer[] ages = {new Integer(20), new Integer(40)}; 

Type[] types = {Hibernate.INTEGER, Hibernate.INTEGER}; 

criteria.add(Restrictions.sqlRestriction( 

"{alias}.age BETWEEN (?) AND (?)", ages, types)); 

List users = criteria.list(); 

观察所产生的SQL语句如下：

Hibernate: select this_.id as id0_0_, this_.nameas name0_0_, this_.age as age0_0_ from T_USER this_ where this_.age BETWEEN (?)AND (?)

 

 

2．连接限制

在Criteria 查询中使用FetchMode来实现连接限制。在HQL语句中，可以通过fetch关键字来表示预先抓取（Eager fetching），如下所示：

from Group g left join fetch g.students s where g.name like '%2005'

可以使用Criteria的API完成同样的功能，如下所示：

Criteria cr = session.createCriteria(Group.class); cr.setFetchMode(“students”, FetchMode.EAGER); cr.add(Restrictions.like(“name”, “2005”, MatchMode.END)) List list = cr.list();

以上两种方式编写的代码，都使用相同的SQL语句完成它们的功能，如下所示：

select g.*, s.* from Group g left outer join Student s on g.id = s.group_id where g.name like '%2005'

 

 

 

 

 

Query :

常用方法:

Method Summary
int	executeUpdate()            Execute the update or delete statement.
String[]	getNamedParameters()            Return the names of all named parameters of the query.
String	getQueryString()            Get the query string.
String[]	getReturnAliases()            Return the HQL select clause aliases (if any)
Type[]	getReturnTypes()            Return the Hibernate types of the query result set.
boolean	isReadOnly()            Should entities and proxies loaded by this Query be put in read-only mode? If the read-only/modifiable setting was not initialized, then the default read-only/modifiable setting for the persistence context is returned instead.
Iterator	iterate()            Return the query results as an Iterator.
List	list()            Return the query results as a List.
ScrollableResults	scroll()            Return the query results as ScrollableResults.
ScrollableResults	scroll(ScrollMode scrollMode)            Return the query results as ScrollableResults.
Query	setBigDecimal(int position, BigDecimal number)
Query	setBigDecimal(String name, BigDecimal number)
Query	setBigInteger(int position, BigInteger number)
Query	setBigInteger(String name, BigInteger number)
Query	setBinary(int position, byte[] val)
Query	setBinary(String name, byte[] val)
Query	setBoolean(int position, boolean val)
Query	setBoolean(String name, boolean val)
Query	setByte(int position, byte val)
Query	setByte(String name, byte val)
Query	setCacheable(boolean cacheable)            Enable caching of this query result set.
Query	setCacheMode(CacheMode cacheMode)            Override the current session cache mode, just for this query.
Query	setCacheRegion(String cacheRegion)            Set the name of the cache region.
Query	setCalendar(int position, Calendar calendar)
Query	setCalendar(String name, Calendar calendar)
Query	setCalendarDate(int position, Calendar calendar)
Query	setCalendarDate(String name, Calendar calendar)
Query	setCharacter(int position, char val)
Query	setCharacter(String name, char val)
Query	setComment(String comment)            Add a comment to the generated SQL.
Query	setDate(int position, Date date)
Query	setDate(String name, Date date)            Bind the date (time is truncated) of a given Date object to a named query parameter.
Query	setDouble(int position, double val)
Query	setDouble(String name, double val)
Query	setEntity(int position, Object val)            Bind an instance of a mapped persistent class to a JDBC-style query parameter.
Query	setEntity(String name, Object val)            Bind an instance of a mapped persistent class to a named query parameter.
Query	setFetchSize(int fetchSize)            Set a fetch size for the underlying JDBC query.
Query	setFirstResult(int firstResult)            Set the first row to retrieve.
Query	setFloat(int position, float val)
Query	setFloat(String name, float val)
Query	setFlushMode(FlushMode flushMode)            Override the current session flush mode, just for this query.
Query	setInteger(int position, int val)
Query	setInteger(String name, int val)
Query	setLocale(int position, Locale locale)
Query	setLocale(String name, Locale locale)
Query	setLockMode(String alias, LockMode lockMode)            Set the lockmode for the objects idententified by the given alias that appears in the FROM clause.
Query	setLockOptions(LockOptions lockOptions)            Set the lock options for the objects idententified by the given alias that appears in the FROM clause.
Query	setLong(int position, long val)
Query	setLong(String name, long val)
Query	setMaxResults(int maxResults)            Set the maximum number of rows to retrieve.
Query	setParameter(int position, Object val)            Bind a value to a JDBC-style query parameter.
Query	setParameter(int position, Object val, Type type)            Bind a value to a JDBC-style query parameter.
Query	setParameter(String name, Object val)            Bind a value to a named query parameter.
Query	setParameter(String name, Object val, Type type)            Bind a value to a named query parameter.
Query	setParameterList(String name, Collection vals)            Bind multiple values to a named query parameter.
Query	setParameterList(String name, Collection vals, Type type)            Bind multiple values to a named query parameter.
Query	setParameterList(String name, Object[] vals)            Bind multiple values to a named query parameter.
Query	setParameterList(String name, Object[] vals, Type type)            Bind multiple values to a named query parameter.
Query	setParameters(Object[] values, Type[] types)            Bind values and types to positional parameters.
Query	setProperties(Map bean)            Bind the values of the given Map for each named parameters of the query, matching key names with parameter names and mapping value types to Hibernate types using hueristics.
Query	setProperties(Object bean)            Bind the property values of the given bean to named parameters of the query, matching property names with parameter names and mapping property types to Hibernate types using hueristics.
Query	setReadOnly(boolean readOnly)            Set the read-only/modifiable mode for entities and proxies loaded by this Query.
Query	setResultTransformer(ResultTransformer transformer)            Set a strategy for handling the query results.
Query	setSerializable(int position, Serializable val)
Query	setSerializable(String name, Serializable val)
Query	setShort(int position, short val)
Query	setShort(String name, short val)
Query	setString(int position, String val)
Query	setString(String name, String val)
Query	setText(int position, String val)
Query	setText(String name, String val)
Query	setTime(int position, Date date)
Query	setTime(String name, Date date)            Bind the time (date is truncated) of a given Date object to a named query parameter.
Query	setTimeout(int timeout)            Set a timeout for the underlying JDBC query.
Query	setTimestamp(int position, Date date)
Query	setTimestamp(String name, Date date)            Bind the date and the time of a given Date object to a named query parameter.
Object	uniqueResult()            Convenience method to return a single instance that matches the query, or null if the query returns no results

 

 

Query-----Hibernate查询(Query Language)

 参考：http://blog.sina.com.cn/s/blog_aaa756740101fncl.html  （讲的很好）