Shulou(Shulou.com)06/02 Report--

This article mainly introduces "how to use Hibernate Criteria advanced query". In daily operation, I believe many people have doubts about how to use Hibernate Criteria advanced query. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to use Hibernate Criteria advanced query". Next, please follow the editor to study!

When querying with Criteria, we can combine not only the functions of where clause in SQL, but also the query functions such as sorting, statistics, grouping and so on. This is the Criteria advanced query.

Sort

You can use Criteria to query and use org.hibernate.criterion.Order to sort the results, such as using Oder.asc (), specifying the sort from smallest to largest according to "age" (and vice versa using desc ()):

Criteria criteria = session.createCriteria (User.class); criteria.addOrder (Order.asc ("age")); List users = criteria.list ()

Note that when you add the Order condition, you use the addOrder () method instead of the add () method, and when you generate the SQL statement, you use order by and asc (desc) to specify the sort:

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

Limit the number of query pens

The setMaxResults () method of Criteria can limit the number of entries returned from the query. If you cooperate with setFirstResult () to set the location of the query result *, you can achieve simple paging, such as returning 50 pieces of data after the 51st stroke (if any):

Criteria criteria = session.createCriteria (User.class); criteria.setFirstResult (51); criteria.setMaxResults (50); List users = criteria.list ()

Depending on the database you specify, Hibernate will automatically generate a limited number of query clauses that depend on the database. For example, in MySQL, the following SQL statement will be generated using limit:

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

Statistical action

You can perform statistical actions on the query results by using org.hibernate.criterion.Projections 's avg (), rowCount (), count (), max (), min (), countDistinct () and other methods, and then adding condition settings with Criteria's setProjection () method, such as averaging the "age" of the query results:

Criteria criteria = session.createCriteria (User.class); criteria.setProjection (Projections.avg ("age")); List users = criteria.list ()

The above program will automatically generate the avg function of SQL for average calculation by Hibernate:

Hibernate: select avg (this_.age) as y0 _ from T_USER this_

Grouping

You can also group the results with groupProperty () of Projections, for example, by "age", that is, if "age" in the data has 20, 20, 25, 30, the following displays 20, 25, 30:

Criteria criteria = session.createCriteria (User.class); criteria.setProjection (Projections.groupProperty ("age")); List users = criteria.list ()

The above program automatically generates the group by clause of SQL by Hibernate for grouping calculation:

Hibernate: select this_.age as y0_ from T_USER this_ group by this_.age

If you want to combine statistics and grouping at the same time, you can use org.hibernate.criterion.ProjectionList, for example, the following program calculates how many people are at each age:

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 ()

Observing the resulting SQL statement, we will use group by to group first, and then count the count function for each group.

Hibernate: select this_.age as y0mm, count (*) as y1 _ from T_USER this_ group by this_.age

Query based on known objects

It is not necessary to use Restrictions to set query conditions. If there are many attribute conditions, it is not convenient to use Restrictions. If you have a known object, you can use this object as the basis for query to see if there are any objects with similar attributes, such as:

User user = new User (); user.setAge (new Integer (30)); Criteria criteria = session.createCriteria (User.class); criteria.add (Example.create (user)); List users = criteria.list ()

In the advanced query of Criteria, you can create an Example instance through the create () method of org.hibernate.criterion.Example. Example implements the Criteria interface, so you can use the add () method to add to the Criteria condition setting. Hibernate will automatically filter out empty attributes and determine whether it is generated in the where clause based on the attributes set on the known object:

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

Set up a SQL template

If you know how to write SQL statements and want to set up some templates when Hibernate generates SQL, you can also use the sqlRestriction () method of Restrictions to provide SQL syntax templates for qualified queries, such as querying materials for which name begins with cater:

Criteria criteria = session.createCriteria (User.class); criteria.add (Restrictions.sqlRestriction ("{alias} .name LIKE (?)", "cater%", Hibernate.STRING); List users = criteria.list ()

Where alias will be replaced with the name associated with the User category, and? It will be replaced with the value provided by cater%, that is, the value provided by the second parameter. The parameters of the sqlRestriction () method * are set as part of the where clause, so when SQL is written, it is no longer necessary to write where, observe the resulting SQL statement, and use the SQL template you set as the basis to complete the conditional query of SQL:

Hibernate: select this_.id as id0_0_, this_.name as name0_0_, this_.age as age0_0_ from T_USER this_ where this_.name LIKE.

If you have multiple query conditions, such as a query for the between clause, you can do the following:

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 ()

Observe the resulting SQL statement as follows:

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

At this point, the study on "how to use Hibernate Criteria Advanced query" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.