Hibernate QBC advanced Query

Hibernate QBC Query

QBC queries objects through the query by criteria API provided by hibernate. This API encapsulates the dynamic assembly of SQL statements and provides a more object-oriented interface for queries. Let's look at the following example.Program:

Criteria = session. createcriteria (user. Class );

Criteria. Add (expression. eq ("name", "zx "));

Criteria. Add (expression. eq ("Age", new INTEGER (27 ));

List list = criteria. List ();

 

When criteria. list () generates an SQL statement like this: Select * from user where name = 'zx' and age = 27; so here we can see that, criteria is actually a query container, which encapsulates the addition of the query condition expression. The specific query condition is added through the add () method, the expression operation for specific query conditions is specified by expression. At runtime, Hibernate adds query conditions based on the expression conditions specified by criteria and generates query statements. This method is very compatible with Java and all object-orientedProgramming LanguageSo most persistence layer frameworks provide support for this method of query. The following describes the technical details of this query method.

 

Cri1 and criteria Query expressions:

As we can see, expression encapsulates and limits the expression of the query statement. The following table lists all the methods of expression and the Query expressions corresponding to each method and their restrictions.

Method
Description
 
Expression. EQ
Corresponding SQL field = value expression

For example, expression. eq ("name", "zx ");

Expression. alleq
The method parameter is a map object that contains multiple name/value pairs, equivalent to the superposition of multiple expression. EQ

Expression. gt
Corresponding to the "field> value" expression of SQL

Expression. Ge
Corresponding to the SQL "field> = value" expression

Expression. lt
Corresponding SQL field <value "expression

Expression. Le
Corresponding SQL field <= value "expression

Expression.
Corresponding to the between expression of the SQL statement. For example, to query users aged between 21 and 27, you can write an expression. between ("age", new INTEGER (21), new INTEGER (27 ));

Expression. Like
The "field like value" expression corresponding to the SQL statement

Expression. In
Corresponding to "field in (...)" of the SQL statement (......)" Expression

Expression. eqproperty
Used to compare two property values, corresponding to the "field = field" SQL expression

Expression. gtproperty
Used to compare two property values, corresponding to the "field> Field" SQL expression

Expression. geproperty
Used to compare two property values, corresponding to the "field> = field" SQL expression

Expression. ltproperty
Used to compare two property values, corresponding to the "field <field" SQL expression

Expression. leproperty
Used to compare two property values, corresponding to the "field <= field" SQL expression

Expression. And
Corresponds to the and link combination of SQL statements, such as expression. and (expression. eq ("name", "ZX"), expression. eq ("sex", "1 "));

Expression. or
Corresponds to the or link combination of SQL statements, such as expression. or (expression. eq ("name", "ZX"), expression. eq ("name", "zhaoxin "));

Expression. SQL
This method provides support for native SQL statement queries. During execution, native SQL statements are used for restrictions, such as expression. SQL ("lower ({alias }. name) Like (?) ", "Zhao %", hibernate. String); At runtime, {alias} will be replaced by the object class name associated with the current query, in? It will be replaced by "Zhao %" and the type is specified by hibernate. String.

Note: The attribute name (such as name and sex) specified by the attribute parameter (the first parameter in each method) in each expression method is not the actual field name in the database table, it is the class property name mapped to the actual data table field in the object.

 

2. Example query:

The example query is completed by using the example class. The example class implements the criterion interface and can be used as the criteria for querying criteria. The example class is used based on existing objects, query other objects with the same property value. As follows:CodeAs shown in:

Criteria = session. createcriteria (user. Class );

User exampleuser = new user ("ZX ");

Criteria. Add (example. Create (exampleuser ));

List list = criteria. List ();

For (INT I = 0; I <list. Size (); I ++ ){

User user = (User) list. Get (I );

System. Out. println (user. getname () + "\ n ");

}

In the above Code, user exampleuser = new user ("ZX"); criteria. Add (example. Create (exampleuser); two sentences are equivalent

Criteria. Add (expression. eq ("name", "zx"); therefore, an SQL statement similar to the following is generated:

Select * from user where name = 'zx'; in the above Code, exampleuser is called a sample object.

 

For example queries in the hibernate squadron, attributes with null property values in the example object are excluded by default. You can also call example. excludenone (exclude empty string value)/excludezeros (exclude zero value), or call example. excludeproperty to specify to exclude specific properties.

The sample query is mainly used in combined queries. For example, the final query statement is dynamically generated based on the query conditions entered by the user, and the example query is used, you can avoid writing a large number of if judgment statements due to too many query conditions.

3. Compound query:

Composite queries are mainly about how to perform Association queries for two entities with associations. For example, if the user object and addres object have a one-to-many association relationship, we can construct a consortium query as follows:

Criteria = session. createcriteria (user. Class );

Criteria addcriteria = criteria. createcriteria ("addresses"); (1)

Addcriteria. Add (Express. Like ("Address", "% Tianjin % "));

List list = criteria. List ();

For (INT I = 0; I <list. Size (); I ++ ){

User user = (User) list. Get (I );

System. Out. println (user. getname () + "\ n ");

Set addresses = user. getaddresses ();

Iterator it = addresses. iterator ();

While (it. hasnext (){

Address = (Address) it. Next ();

System. Out. println (address. getaddress () + "\ n ");

}

}

 

When (1) is executed, it indicates that you want to add a new query condition for the addresses attribute of the user object. Therefore, when you execute criteria. in list (), Hibernate generates SQL statements similar to the following:

Select * from user inner join address on user. ID = address. ID where address. Address like '% Shanghai % ';

As we can see, we can construct a composite query by adding the set attribute to save the associated object to criteria (the addresses attribute stores the address object associated with the user object, at the database end, the query is performed through an internal connection.

 

433. advanced features of criteria:

A. Limit the number of returned records:

We can use the criteria. setfirstresult/setmaxresult method to limit the number of records returned for a query. The following code:

Criteria = session. createcriteria (user. Class );

Criteria. setfirstresult (100 );

Criteria. setmaxresult (200 );

 

The code above allows you to set the first record in the User table returned for this query from the first record to the end of the second record.

B. Sort the query results:

You can use the net. SF. hibernate. expression. Order class to sort the query result set, as shown in the following code:

Criteria = session. createcriteria (user. Class );

Criteria. Add (expression. eq ("groupid", "2 ");

Criteria. addorder (order. ASC ("name "));

Criteria. addorder (order. DESC ("groupid "));

List list = criteria. List ();

 

By using the ASC ()/DESC () method of the Order class, you can specify the sorting logic for a field. If you execute the above Code, an SQL statement similar to the following will be generated:

Select * from user where groupid = '2' order by name ASC, groupid DESC

 

C. Grouping and statistics:

In hibernate3, new features are added to criteria to support grouping and statistics. Projections and projectionlist are added to hibernate3, which encapsulate grouping and statistics functions, the following code:

Criteria = session. createcriteria (user. Class );

Criteria. setprojection (projections. groupproperty ("age"); (1)

List list = criteria. List ();

Iterator it = List. iterator ();

While (it. hasnext ()){

System. Out. println (it. Next ());

}

Through the code at (1), we specify the target attribute for grouping through the projections class. During retrieval, Hibernate will generate an SQL statement similar to the following:

Select age from user group by age;

You can also use projections AVG ()/rowcount ()/count ()/MAX ()/min ()/countdistinct () and other methods to achieve statistical functions, the following code example:

Criteria = session. createcriteria (user. Class );

Criteria. setprojection (projections. AVG ("age"); (1)

List list = criteria. List ();

Iterator it = List. iterator ();

While (it. hasnext ()){

System. Out. println (it. Next ());

}

Through the code at (1), we can calculate the average age of users. During retrieval, Hibernate will generate SQL statements similar to the following:

Select AVG (AGE) from user;

In addition, the multi-condition grouping and statistics function in SQL statements can be implemented using the projectionlist class, as shown in the following code:

Criteria = session. createcriteria (user. Class );

Projectionlist prolist = Projections. projectionlist ();

Prolist. Add (projections. groupproperty ("age "));

Prolist. Add (projections. rowcount ());

Criteria. setprojection (prolist );

List list = criteria. List ();

The code above enables grouping statistics on the number of people of different ages. During retrieval, Hibernate generates SQL statements similar to the following:

Select age, count (*) from user group by age;

5. detachedcriteria:

 

In hibernate2, the criteria instance has the same lifecycle as the session instance that created it. That is to say, the session instance is the host of the Criteria instance it created. When the session is closed, criteria parasitic on the session instance will be invalid. This makes it difficult to reuse criteria. To achieve the reuse of criteria instances, a detachedcriteria class is provided in hibernate3. the lifecycle of the detachedcriteria instance is irrelevant to the session instance, we can use detachedcriteria to detach some common criteria query conditions and associate it with the session instance to obtain the criteria instance at runtime. The following code is used:

Detachedcriteria Dc = detachedcriteria. forclass (user. Class );

DC. Add (expression. eq ("name", "zhaoxin "));

DC. Add (expression. eq ("sex", "1 "));

Criteria = Dc. getexecutablecriteria (session );

Iterator it = criteria. List (). iterator ();

While (it. hasnext ()){

User user = (User) it. Next ();

System. Out. println (user. getname ());

}

 

As we can see, the lifecycle of detachedcriteria has nothing to do with the session instance. When you need to search, you can use the getexecutablecriteria (Session) method to associate it with the current session instance and obtain the criteria instance at runtime, complete the search.

Detachedcriteria can also be used to complete the subquery function, as shown in the following code:

Detachedcriteria Dc = detachedcriteria. forclass (user. Class );

DC. setprojection (projections. AVG ("age "));

Criteria = session. createcriteria (user. Class );

Criteria. Add (subqueries. propertygt ("age", DC ));

List list = criteria. List ();

 

Subqueries class is used to add subqueries. we add the query conditions set by detachedcriteria as subqueries to the query conditions of the Criteria instance at runtime, when the search is executed, Hibernate generates an SQL statement similar to the following:

Select * from user where age> (select AVG (AGE) from user group by age );