Hibernate HQL Query Statement summary

Hibernate HQL Query Statement summary

1, Entity query:
As for the entity query technology, we have already dealt with it many times before, such as the following example:
String hql= "from user User";
List list=session. CreateQuery (HQL). List ();
The result of the above code execution is that all data corresponding to the user entity object is queried, and the data is encapsulated into a user entity object and returned in the list. It is important to note that the entity query for Hibernate has a decision on the inheritance relationship, such as the employee entity object in the mapping entity inheritance relationship that we discussed earlier, and it has two subclasses that are Hourlyemployee,salariedemployee, If you have such a HQL statement: "From Employee", hibernate retrieves the data for all the Employee type entity objects (including its subclass Hourlyemployee, when performing the retrieval). SalariedEmployee the corresponding data).
Because the HQL statement is similar to a standard SQL statement, we can also use the WHERE clause in the HQL statement, and you can use various expressions in the WHERE clause, compare operators, and use "and", "or" to concatenate a combination of different query conditions. Take a look at some simple examples below:
From user user where user.age=20;
From user user where user.age between and 30;
From the user user where user.age in (20,30);
From the user user where user.name is null;
From the user user where User.Name like '%zx% ';
From user user where (user.age%2) = 1;
From the user user where user.age=20 and user.name like '%zx% ';
2. Updates and deletions of entities:
Before continuing to explain HQL other more powerful query functions, let's start with the following techniques for entity update and deletion using HQL. This technical feature is a new addition to Hibernate3 and is not available in Hibernate2. For example, in Hibernate2, if we want to change the age of all 18-year-olds in the database to 20, then we will first retrieve the users aged 18 and then change their age to 20 and finally call the Session.update () statement to update. Provides a more flexible and efficient solution to this problem in Hibernate3, such as the following code:
Transaction trans=session.begintransaction ();
String hql= "Update user user set user.age=20 where user.age=18";
Query queryupdate=session.createquery (HQL);
int ret=queryupdate.executeupdate ();
Trans.commit ();
In this way we can in the Hibernate3, one-time to complete the batch data update, the performance of the improvement is considerable. It is also possible to complete the delete operation in a similar way, as in the following code:
Transaction trans=session.begintransaction ();
String hql= "Delete from user user where user.age=18";
Query queryupdate=session.createquery (HQL);
int ret=queryupdate.executeupdate ();
Trans.commit ();
If you are reading chapters by chapter, then you will remember me in the second part of the related discussion of bulk data operations, this mode of operation, which is called bulk delete/update in Hibernate3, This approach can greatly improve the flexibility and operational efficiency of operations, but in this way it is most likely to cause problems with cache synchronization (refer to the related discussion).
3. Attribute query:
Many times when we are retrieving data, we do not need to get all the data corresponding to the entity object, but we need to retrieve the data corresponding to some properties of the entity object. You can then use the Hql property query technique, such as the following program example:
List List=session.createquery ("Select User.Name from User User"). List ();
for (int i=0;i
System.out.println (List.get (i));
}
We only retrieved the data for the Name property of the user entity, and each entry in the list containing the result set is the data for the string type Name property. We can also retrieve multiple properties at once, such as the following program:
List List=session.createquery ("Select user.name,user.age from User User"). List ();
for (int i=0;i
Object[] obj= (object[]) list.get (i);
System.out.println (Obj[0]);
System.out.println (obj[1]);
}
In the result set list returned, each entry contained is a object[] type that contains the corresponding property data value. As a developer of our generation deeply influenced by object-oriented thinking, it may be felt that the above return object[] is not enough to conform to the object-oriented style, and we can encapsulate these flat data using the dynamic construction instance provided by HQL, such as the following program code:
List List=session.createquery ("Select New User (user.name,user.age) from user User"). List ();
for (int i=0;i
User user= (user) list.get (i);
System.out.println (User.getname ());
System.out.println (User.getage ());
}
Here we encapsulate the return result by dynamically constructing the instance object, making our program more conform to the object-oriented style, but here's a question to be aware that the user object returned at this point is simply a generic Java object, except for the query result value. Other property values are null (including the primary key value ID), which means that the object cannot be persisted by the session object for the update operation. As in the following code:
List List=session.createquery ("Select New User (user.name,user.age) from user User"). List ();
for (int i=0;i
User user= (user) list.get (i);
User.setname ("Gam");
Session.saveorupdate (user);//This will actually perform a save operation without performing the update operation because the ID property of the user object is null. Hibernate takes it as a free object (refer to the discussion of the persisted Object state section), so it performs a save operation on it.
}
4. Grouping and Sorting
A, Order by clause:
Similar to SQL statements, HQL queries can also sort the query result set through the ORDER BY clause, and you can specify the sort by either the ASC or the DESC keyword, such as the following code:
From the user user order by User.Name asc,user.age desc;
The HQL query statement above is sorted in ascending order by the Name property, sorted in descending order with the Age property, and, like the SQL statement, the default sort is ASC, which is sorted in ascending order.
B, Group by clause and statistical query:
The GROUP BY clause is also supported in the HQL statement to group queries, and the GROUP BY clause is combined with a grouped statistical query of aggregate functions, and most standard SQL aggregation functions can be used in HQL statements, such as count (), sum (), Max (), Min (), AVG () and so on. As in the following program code:
String hql= "SELECT count (user), user.age from the user user group by User.age have count (user) >10";
List List=session.createquery (HQL). List ();
C, optimize the statistical query:
Suppose we now have two database tables, the Customer table and the order table, which are structured as follows:
Customer
ID VARCHAR2 (14)
Age Number (10)
Name VARCHAR2 (20)

Order
ID VARCHAR2 (14)
Order_number Number (10)
customer_id VARCHAR2 (14)
Now there are two HQL query statements, respectively, as follows:
From Customer c INNER join c.orders o Group by C.age; (1)

Select c.id,c.name,c.age,o.id,o.order_number,o.customer_id
From Customer c INNER join c.orders C GROUP by C.age; (2)
These two statements use the INNER JOIN query of the HQL statement (which we will discuss in the Connection Query section of the HQL statement), and now we can see that the results returned by the two query statements are the same, but they are obviously different. The result of the query (1) returns the customer and order persisted objects, and they are placed in Hibernate's session cache, and the session is responsible for their uniqueness in the cache and the synchronization with the background database data. Only transactions are purged from the cache after they are committed, whereas statements (2) Return relational data rather than persisted objects, so they do not consume Hibernate's session cache, as long as the application does not access them after the retrieval. The memory they occupy may be reclaimed by the JVM's garbage collector, and Hibernate does not synchronize changes to them.
In our system development, especially MIS system, it is inevitable to carry out the development of statistical query, this kind of function has two characteristics: the first data is large; the second is generally read-only and does not involve the modification of statistical data, so if you use the first query method, Will inevitably result in a large number of persistent objects in Hibernate's session cache, and Hibernate's session cache is responsible for synchronizing them with database data. If you use the second query, you will obviously improve query performance because you do not need the management overhead of Hibernate's session cache, and as long as the application is not using this data, the memory space they occupy will be reclaimed and freed.
Therefore, when developing the statistical query system, try to return the relational data by using the SELECT statement to write the properties that need to be queried, instead of returning the persisted object using the first query, which is used when there are modification requirements, which can improve the efficiency and reduce memory consumption. ㊣ true Master is not proficient in everything, but proficient in the right place to use the right means.
5. Parameter binding:
Hibernate provides rich support for dynamic query parameter binding, so what is dynamic binding of query parameters? In fact, if we are familiar with the traditional JDBC programming, we will not be difficult to understand the query parameter dynamic binding, the following code traditional JDBC parameter binding:
Preparestatement pre=connection.prepare ("select * from User where user.name=?");
Pre.setstring (1, "zhaoxin");
ResultSet Rs=pre.executequery ();
This is also provided in hibernate in the query parameter binding function, and in Hibernate for this feature also provides more than the traditional JDBC operation Rich features, in hibernate there are 4 kinds of parameter binding method, the following we will describe separately:
A, by parameter name binding:
Define named parameters in the HQL statement to begin with ":" In the following form:
Query query=session.createquery ("From user user where User.name=:customername and user:customerage=:age");
Query.setstring ("CustomerName", name);
Query.setinteger ("Customerage", age);
In the code above: CustomerName and: customerage define named Parameters CustomerName and Customerage respectively, and then use the Setxxx () method of the query interface to set the name parameter value, the Setxxx () method contains two parameters , which are named parameter names and the actual values of named parameters, respectively.
B, according to the parameter location bonding:
In the HQL query statement, "?" To define the parameter position, in the following form:
Query query=session.createquery ("From user user where user.name=?") and User.age =? ”);
Query.setstring (0,name);
Query.setinteger (1,age);
Also use the Setxxx () method to set the binding parameters, except that the first parameter of the Setxxx () method represents the position number of the state parameter in the HQL statement (starting from 0), and the second parameter still represents the actual value of the parameter.
Note: In the actual development, the use of the name of the state named parameter, because this can not only provide a very good program readability, but also improve the ease of maintenance of the program, because when the location of the query parameter changes, the name of the state parameter is not necessary to adjust the program code.
C, Setparameter () method:
In Hibernate's HQL query, you can use the Setparameter () method to state any type of parameter, as follows:
String hql= "from user user where user.name=:customername";
Query query=session.createquery (HQL);
Query.setparameter ("CustomerName", name,hibernate.string);
As shown in the preceding code, the Setparameter () method contains three parameters, namely named parameter names, named parameter actuals, and named parameter mapping types. For some parameter types, the Setparameter () method can have a Java type with more parameter values, guess the corresponding mapping type, so there is no need to display the mapping type at this point, as the above example can write directly:
Query.setparameter ("CustomerName", name), but for some types it is necessary to specify the mapping type, such as the java.util.Date type, because it corresponds to many of Hibernate's mapping types, such as Hibernate.data or Hibernate.timestamp.
D, SetProperties () method:
In hibernate, you can use the SetProperties () method to bind a named parameter to a property value of an object, as in the following program code:
Customer Customer=new customer ();
Customer.setname ("Pansl");
Customer.setage (80);
Query query=session.createquery ("from Customer C where C.name=:name and C.age=:age");
Query.setproperties (customer);
The SetProperties () method automatically matches the property value of the Customer object instance to the named parameter, but requires that the named parameter name must have the same name as the property corresponding to the entity object.
There is also a special setentity () method that will correlate named parameters with a persisted object, as shown in the following code:
Customer customer= (Customer) Session.load (Customer.class, "1");
Query query=session.createquery ("From Order order where order.customer=:customer");
Query. SetProperties ("Customer", customer);
List list=query.list ();
The code above generates an SQL statement similar to the following:
Select * from order where customer_id= ' 1 ';
E, the advantages of using binding parameters:
Why do we use binding named parameters? The existence of any one thing has its value, specific to the binding parameters for HQL query, there are the following two main advantages:
①, you can use the database to implement performance optimization, because Hibernate is used at the bottom of the preparestatement to complete the query, so for the syntax of the same parameters different SQL statements, can take full advantage of the precompiled SQL statement cache, thus improving query efficiency.
② can prevent the generation of SQL injection security vulnerabilities:
SQL injection is a special attack on SQL statements, such as for our common user login, in the login interface, the user input user name and password, the login validator may generate the following HQL statement:
"From user user where user.name= '" name "' and user.password= '" Password ""
This HQL statement is logically not a problem, this login verification function is normally done correctly, but if you enter "Zhaoxin or ' x ' = ' x" in the user name at login, if you use a simple HQL statement to assemble the string, The following HQL statement is generated:
"From user user where user.name= ' zhaoxin ' or ' x ' = ' x ' and user.password= ' admin '";
Obviously the WHERE clause of the HQL statement will always be true, and the role of the user's password is meaningless, which is the basic principle of the SQL injection attack.
With the binding parameter, the problem can be handled properly, and when the binding parameter is used, the following HQL statement is obtained:
From user user where user.name= ' zhaoxin ' or ' x= ' x ' and user.password= ' admin '; This shows that using a binding parameter resolves the single quotation mark entered in the user name into a string (if you want to include single quotes in a string, you should use repeating single quotes), so parameter binding can effectively prevent SQL injection security vulnerabilities

Hibernate HQL Query Statement summary