Introduction to criteria in hibernate

Introduction to criteria in hibernate:To operate the database management system, the most basic thing is to use SQL (standard query language) statements. Most databases support standard SQL statements, however, there are also some database-specific SQL statements. When the application uses SQL statements for database queries, if the database-specific SQL statements are used, the program itself may be dependent on a specific database.

When using hibernate, you can use the APIS provided by hibernate to query SQL statements, even if you do not know how to use and write SQL statements. hibernate. criteria encapsulates SQL statements. You can combine various query conditions from the perspective of Java objects. hibernate automatically generates SQL statements for you without having to manage the dependency between SQL statements and databases.

For the most basic query, if you want to query all the content in the data table corresponding to an object, you can query it as follows:

Criteria criteria = session.createCriteria(User.class);List users = criteria.list();for(Iterator it = users.iterator(); it.hasNext(); ) {User user = (User) it.next();System.out.println(user.getId() +" "t " + user.getName() +"/" + user.getAge());}

After criteria is created, if no conditions are given, all the data in the table corresponding to the object is queried by default. If you execute the preceding program snippet, the "show_ SQL" attribute of Hibernate is set in the configuration file. The following SQL statements are generated under the master:

Hibernate: select this _. ID as id0_0 _, this _. Name as name0_0 _, this _. Age as age0_0 _ from t_user this _

Criteria basic query condition settings

Org. hibernate. criteria is actually a condition-appended container. If you want to set query conditions, use Org. hibernate. criterion. various static methods of restrictions are returned to Org. hibernate. criterion. criteria instance, each Org. hibernate. criterion. the criteria instance represents a condition. You must use Org. hibernate. the add () method of criteria adds these condition instances. For example, you can query information with the "Age" greater than 20 and less than 40:

Criteria criteria = session.createCriteria(User.class);criteria.add(Restrictions.gt("age", new Integer(20)));criteria.add(Restrictions.lt("age", new Integer(40)));List users = criteria.list();for(Iterator it = users.iterator(); it.hasNext(); ) {User user = (User) it.next();System.out.println(user.getId() +" "t " + user.getName() +"/" + user.getAge());}

The GT () method of restrictions indicates that the condition is greater than (great than), and lt indicates that the condition is less than (less than). Execute the preceding program fragment and observe the generated SQL statement, the where and clause will be used 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 _ where this _. age>? And this _. Age

When conditions are added using the add () method, and conditions are combined by default. If conditions are combined using the or method, restrictions can be used. or () method, for example, conditions that combine age equal to (EQ) 20 or (OR) age is null (isnull:

Criteria criteria = session.createCriteria(User.class);criteria.add(Restrictions.or(Restrictions.eq("age", new Integer(20)),Restrictions.isNull("age")));List users = criteria.list();

Observe the generated SQL statement, and use the where and or clauses 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 _ Where (this _. Age =? Or this _. Age is null)

You can also use the restrictions. Like () method to perform the like clause function in SQL. For example, you can query information starting with "name" and "just:

Criteria criteria = session.createCriteria(User.class);criteria.add(Restrictions.like("name", "just%"));List users = criteria.list();

Observe 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 _ where this _. name like?

The following table describes several methods for querying restrictions:

Method description
Restrictions. EQ equals
Restrictions. alleq uses map and key/value to compare multiple equal values.
Restrictions. gt is greater than>
Restrictions. ge greater than or equal to> =
Restrictions. Lt less than <
Restrictions. Le is less than or equal to <=
Restrictions. Between: SQL's between clause
Restrictions. Like corresponds to the like clause of SQL
The in clause of the SQL statement corresponding to restrictions. In
Restrictions. And and off Conditions
Restrictions. Or or off limit

Criteria advanced query condition settings

When using criteria for query, you can not only combine the WHERE clause functions in SQL, but also combine query functions such as sorting, statistics, and grouping.

Sort

You can use criteria to query and use Org. hibernate. criterion. order sorts the results, for example, using Oder. ASC (), specify to sort by "Age" from small to large (otherwise DESC ()):

Criteria 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 sorting:

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 queries

The setmaxresults () method of criteria can limit the number of records returned from the query. If setfirstresult () is used to set the position of the first data in the query result, simple paging can be implemented, for example, 50 pieces of data (if any) after 51st pieces are returned ):

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

Based on the database you specified, Hibernate will automatically generate a limit number query clause that is dependent on the database. For example, in MySQL, limit will generate the following SQL statement:

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 operations on the query results by using Org. hibernate. criterion. the AVG (), rowcount (), count (), max (), min (), countdistinct () and other methods of projections are added with the setprojection () method of criteria, for example, average the "Age" of the query result:

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

The above program will automatically generate SQL AVG functions by hibernate for average calculation:

Hibernate: Select AVG (this _. Age) as y0 _ from t_user this _

Group

You can also use the groupproperty () of projections to group the results. For example, you can use "Age" to group the results. That is, if "Age" contains 20, 20, 25, or 30, 20, 25, and 30 are displayed as follows:

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

The above program will automatically generate the SQL group by clause by hibernate for grouping calculation:

Hibernate: select this _. Age as y0 _ from t_user this _ group by this _. Age

If you want to combine 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 of 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();

Observe the generated SQL statement, group by will be used to group first, and then count the count function for each group

Hibernate: select this _. Age as y0 _, count (*) as Y1 _ from t_user this _ group by this _. Age

Query Based on known objects

You do not have to use restrictions to set query conditions. If there are many attribute conditions, it is not convenient to use restrictions. If there is a known object, you can use this object as the basis for query, check whether there are attributes and similar objects, such:

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

You can use Org. hibernate. criterion. the create () method of example is used to create the example instance. example implements the criteria interface. Therefore, you can add the add () method to the criteria condition setting. hibernate automatically filters out null attributes, based on the property set on the known object, determine whether the object is generated 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 = ?)

Set an SQL Template

If you know how to write SQL statements and want to set some templates when hibernate generates SQL statements, you can also use the sqlrestriction () method of restrictions to provide SQL syntax templates for limited queries, for example, query information whose name starts with cater:

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

Here, alias will be replaced with the name related to the user category, while? It will be replaced with cater %, that is, the value provided by the second parameter. The first parameter of sqlrestriction () method sets the part of the WHERE clause. Therefore, when writing SQL statements, you do not need to write where again. Observe the generated SQL statements and use the SQL template 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 _ where this _. name like (?)

If you have multiple query conditions, such as the query of the between clause, you can:

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 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 _ where this _. Age (?) And (?)

Use detchedcriteria

Criteria is bound to a session. the lifecycle of the session ends with the end of the session. When criteria is used for query, objects must be dynamically created each time during execution and various query conditions must be added, as the session is recycled, criteria is also recycled.

To reuse the criteria object, org. hibernate. criterion. detchedcriteria: You can first create a detchedcriteria instance, add various query conditions, and bind it with the session when you need to query to obtain a criteria object bound to the session. For example:

// Create a detchedcriteria object first
Detachedcriteria detchedcriteria = detachedcriteria. forclass (user. Class );
// Add query Conditions
Detchedcriteria. Add (restrictions. Ge ("Age", new INTEGER (25 )));

Session session = sessionfactory. opensession ();
// Bind the session and return a criteria instance
Criteria = detchedcriteria. getexecutablecriteria (session );

List users = criteria. List ();

Conclusion

Hibernate's criteria API allows you to combine the conditions for querying the database system using the object method. hibernate automatically generates SQL statements dynamically based on the database you are using, when your application accesses a database, it does not depend on a specific database for writing specific SQL statements. If your developers are not familiar with writing SQL statements, you can also try to use criteria to query the database.