Brief Introduction to hibernate compound Query

Hibernate composite query mainly deals with how two entities with associations perform Association queries. For example, the user object and addres object have one-to-multiple associations.

AD:
The 2013 global big data Technology Summit is selling tickets at a low price

Hibernate composite query mainly deals with how two entities with associations perform Association queries. For example, the user object and addres object have one-to-multiple associations.

Hibernate composite query:
1. hibernate
2. hibernate QBC Query
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 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 programming methods for object-oriented programming languages, so most persistence layer frameworks provide support for this method of query. The following describes the technical details of this query method.

1. Criteria query expression:

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 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 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. The following code is used:

 
 

  
  
Criteria criteria=session.createCriteria(User.class);  
  
  
User exampleuser=new User(“zx”);  
  
  
criteria.add(Example.create(exampleuser));  
  
  
List list=criteria.list();  
  
  
for(int i=0;i   
  
  
   User user=(User)list.get(i);  
  
  
   System.out.println(user.getName()+”\n”);  
  
  
}  
 
 

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 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   
  
  
     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=(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.

4. 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 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 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 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 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 an SQL statement 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 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 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 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: