Http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querycriteria.html
When using criteria for Row-based query, you can combine the WHERE clause functions in SQL, you can also combine the query functions such as sorting, planning, and grouping.
You can use criteria to check the vulnerability and use Org. hibernate. criterion. sort the results by order, for example, using Oder. ASC (), specify the root partition "Age" in ascending order (otherwise DESC ()):
Criteria = session. createcriteria (user. Class );
Criteria. addorder (order. ASC ("Age "));
List users = criteria. List ();
Note that when the order condition is added, the addorder () method is used instead of the add () method. When an SQL statement is generated, order by and ASC (DESC) are used) to specify the order:
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 rows to be queried
The setmaxresults () method of criteria can limit the number of returned records. If setfirstresult () is used to set the position of the first item, we can implement a simple score, for example, returning 50 pieces of data (if any) after 51st Bytes ):
Criteria = session. createcriteria (user. Class );
Criteria. setfirstresult (51 );
Criteria. setmaxresults (50 );
List users = criteria. List ();
According to the specified resource, Hibernate will automatically generate a limit data query clause that is dependent on the resource. 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?, ?
You can log on to the query result using Org. hibernate. criterion. projections AVG (), rowcount (), count (), max (), min (), countdistinct () and other methods, and then setprojection () of criteria () add a condition to the method. For example, average the "Age" of the result:
Criteria = session. createcriteria (user. Class );
Criteria. setprojection (projections. AVG ("Age "));
List users = criteria. List ();
The above program calculates the AVG function of the SQL statement automatically generated by hibernate on average:
Hibernate: selectAVG (this _. Age)As y0 _ from t_user this _
You can also combine the groupproperty () of projections to group the result. For example, you can group the result by "age, that is, if "Age" is 20, 20, 25, or 30 in the data, the following values are 20, 25, and 30:
Criteria = session. createcriteria (user. Class );
Criteria. setprojection (projections. groupproperty ("Age "));
List users = criteria. List ();
The above program will be calculated by the Group by clause of hibernate self-generated SQL:
Hibernate: select this _. Age as y0 _ from t_user this _GroupThis _. Age
If you want to integrate the statistics and grouping functions at the same time, you can use org. hibernate. criterion. projectionlist. For example, the following program calculates the number of people in each year:
Projectionlist = Projections. projectionlist ();
Projectionlist. Add (projections. groupproperty ("Age "));
Projectionlist. Add (projections. rowcount ());
Criteria = session. createcriteria (user. Class );
Criteria. setprojection (projectionlist );
List users = criteria. List ();
Check the SQL statement generated. Group by is used to group the statement first, and then calculate the count function for each statement,
Hibernate: select this _. Age as y0 _,Count (*)As Y1 _ from t_user this _GroupThis _. Age
- Check for known objects in the root region
You do not have to use restrictions when setting check condition items. If there are many condition items, it is not convenient to use restrictions. If there is a known object, you can check the dependency of this object to see if there are similar objects such:
User user = new user ();
User. setage (New INTEGER (30 ));
Criteria = session. createcriteria (user. Class );
Criteria. Add (Example. Create (User));
List users = criteria. List ();
You can go through Org. hibernate. criterion. the create () method of example is used to establish the example. example implements the criteria interface. Therefore, you can use the add () method to add it to the criteria configuration, hibernate automatically removes the null atomicity. The root cause is that the property set on the object is known to determine whether it is produced in the WHERE clause:
Hibernate: select this _. ID as id0_0 _, this _. Name as name0_0 _, this _. Age as age0_0 _ from t_user this _Where (this _. Age = ?)
If you know how to create an explain SQL statement and want to set up an explain version for the SQL statement generated by hibernate, you can also use the sqlrestriction () method of restrictions, the SQL statement is provided to limit queries. For example, you can query the information starting with a cater:
Criteria = session. createcriteria (user. Class);
Criteria. Add (restrictions. sqlrestriction (
"{Alias}. name like (?) "," Cater % ", hibernate. String ));
List users = criteria. List ();
In this example, alias will be replaced with a user-dependent name, while? It will be replaced with cater %, that is, the value provided by the second vertex. The first vertex of the sqlrestriction () method sets the part of the WHERE clause, therefore, when creating an SQL statement, you do not have to write where statements. Check the generated SQL statement and use the SQL statement you have set as the basis, to complete the SQL condition query:
Hibernate: select this _. ID as id0_0 _, this _. Name as name0_0 _, this _. Age as age0_0 _ from t_user this _ WhereThis _. name like (?)
If there are multiple query condition items, such as the query condition of the between clause, you can do the following:
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 (?) And (?) ", Ages, types ));
List users = criteria. List ();
Check the generated 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 _ WhereThis _. Age (?) And (?) From: http://caterpillar.onlyfun.net/Gossip/HibernateGossip/CriteriaAdvanced.html
