Hql
Hql query:
Criteria queries encapsulate the query conditions in an object-oriented manner and conform to the programming mindset. However, hql (Hibernate query language) queries provide richer and more flexible query features, therefore, Hibernate sets the hql query method as the standard query method officially recommended. hql queries provide a query method similar to standard SQL statements on the premise that covering all functions of criteria queries, it also provides more object-oriented encapsulation. The complete hql statement is as follows:
Select/update/delete ...... From ...... Where ...... Group ...... Having ...... Order ...... ASC/DESC
Update/delete is a new feature in hibernate3. It can be seen that hql queries are very similar to standard SQL queries. As hql queries play a core role in the entire hibernate entity operating system, I will explain the specific technical details of hql operations in this section.
1. Object Query:
The object query technology has already been involved many times before, for example, the following example:
String hql = "from user ";
List list = session. createquery (hql). List ();
The code above is executed to query all the data corresponding to the user object, encapsulate the data into the user object, and put it in the list to return. It should be noted that there is a judgment on the inheritance relationship in the hibernate object query. For example, we have discussed the employee object in the ing object inheritance relationship, which has two subclasses: hourlyemployee, salariedemployee, if there is such an hql statement: "From employee", Hibernate will retrieve the data (including its subclass hourlyemployee, salariedemployee ).
Because hql statements are similar to standard SQL statements, we can also use the WHERE clause in hql statements, and use various expressions, comparison operators, and "and" in the WHERE clause ", or connects a combination of different query conditions. Let's take a look at some simple examples below:
From user where user. Age = 20;
From user where user. Age between 20 and 30;
From user where user. Age in (20, 30 );
From user where user. Name is null;
From user where user. name like '% ZX % ';
From user where (user. Age % 2) = 1;
From user where user. Age = 20 and user. name like '% ZX % ';
2. Update and delete objects:
Before continuing to explain other more powerful query functions of hql, we will explain the following techniques for entity update and deletion using hql. This technical feature is newly added 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-year-old, we need to retrieve the 18-Year-Old users first, then they changed their age to 20 and finally called the session. update () statement. Hibernate3 provides a more flexible and efficient solution to this problem, as shown in the following code:
Transaction trans = session. begintransaction ();
String hql = "update user set user. Age = 20 where user. Age = 18 ";
Query queryupdate = session. createquery (hql );
Int retaskqueryupdate.exe cuteupdate ();
Trans. Commit ();
In this way, we can update the batch data at one time in hibernate3, which improves the performance. You can also perform the delete operation in a similar way, as shown in the following code:
Transaction trans = session. begintransaction ();
String hql = "delete from user where user. Age = 18 ";
Query queryupdate = session. createquery (hql );
Int retaskqueryupdate.exe cuteupdate ();
Trans. Commit ();
If you read them one by one, you will surely remember that I have discussed this operation method in the second section on batch data operations, this operation method is called bulk delete/update in hibernate3. This method can greatly improve operation flexibility and efficiency, however, this method is very likely to cause cache synchronization problems (please refer to the relevant discussion ).
3. query attributes:
In most cases, when retrieving data, we do not need to retrieve all the data corresponding to the object. Instead, we only need to retrieve the data corresponding to some attributes of the object. At this time, you can use the hql attribute query technology, as shown in the following program example:
List list = session. createquery ("Select User. name from user"). List ();
For (INT I = 0; I
System. Out. println (list. Get (I ));
}
We only retrieve the data corresponding to the name attribute of the user object. At this time, each entry in the list containing the result set returned is the data corresponding to the name attribute of the string type. We can also retrieve multiple attributes at a time, as shown in the following program:
List list = session. createquery ("Select User. Name, user. Age from 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 returned result set list, each entry is of the object [] type, which contains the corresponding attribute data value. As our current generation of developers deeply influenced by Object-Oriented ideas, they may feel that the returned object [] is not in line with the object-oriented style, in this case, we can use the dynamic instance construction function provided by hql to encapsulate the flat data, as shown in the following code:
List list = session. createquery ("select new user (user. Name, user. Age) from 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 dynamically construct instance objects and encapsulate the returned results to make our programs more in line with the object-oriented style. However, there is a problem that must be noted here, that is, the returned user object is only a common Java object. Except for the query result value, all other attribute values are null (including the primary key value ID ), that is to say, the session object cannot be used to perform a persistent update operation on this object. The following code:
List list = session. createquery ("select new user (user. Name, user. Age) from user"). List ();
For (INT I = 0; I
User user = (User) list. Get (I );
User. setname ("Gam ");
Session. saveorupdate (User); // a save operation is actually executed here, instead of an update operation, because the ID attribute of this user object is null, hibernate uses it as a free object (see the description of the Persistent Object state), so it will perform the 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 using the order by clause, and specify the sorting method using the ASC or DESC keyword, as shown in the following code:
From user order by user. Name ASC, user. Age DESC;
In the preceding hql query statement, the name attribute is sorted in ascending order and the age attribute is sorted in descending order. In addition, like the SQL statement, the default sorting method is ASC, that is, ascending order.
B. Group by clause and statistical query:
Hql statements also support group query using group by clauses and group by clauses combined with grouping statistics query of Aggregate functions. Most standard SQL Aggregate functions can be used in hql statements, for example, count (), sum (), max (), min (), AVG (), etc. The following program code:
String hql = "select count (user), user. Age from user group by user. Age having count (User)> 10 ";
List list = session. createquery (hql). List ();
C. Optimize statistics query:
Assume that we have two database tables: the customer table and the order table. Their structures are as follows:
Customer
Id varchar2 (14)
Age number (10)
Name varchar2 (20)
Order
Id varchar2 (14)
Order_number number (10)
Customer_id varchar2 (14)
There are two hql query statements:
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 internal connection query of the hql Statement (we will discuss this in the connection query section of the hql Statement ), now we can see that the results returned by these two query statements are the same at the end, but they are actually significantly different. The results retrieved by the statement (1) will return the customer and order persistence objects, they will be placed in the hibernate session cache, and the session will be responsible for their uniqueness in the cache and synchronization with the background database data, transactions are cleared from the cache only after they are committed. Statement (2) returns relational data instead of persistent objects, so they do not occupy the session cache of hibernate, as long as applications do not access them after retrieval, the memory they occupy may be reclaimed by the JVM garbage collector, and hibernate will not modify them synchronously.
In our system development, especially the MIS system, statistical query development is inevitable. This type of function has two features: first, a large amount of data; in general, read-only operations do not involve modifying the statistical data. If the first query method is used, a large number of persistent objects are stored in the hibernate session cache, in addition, the session cache of Hibernate is also responsible for synchronizing them with the database data. If the second query method is used, the query performance is obviously improved, because the management overhead of the hibernate session cache is not required, and as long as the application does not use the data, the memory space they occupy will be recycled and released.
Therefore, when developing a statistical query system, try to use the SELECT statement to write the attributes to be queried to return the relational data, avoid using the first query method to return persistent objects (this method is suitable for use when there is a need to modify), which can improve the running efficiency and reduce memory consumption. The real master of NLP is not proficient in everything, but proficient in using the appropriate means on the right occasions.
5. Parameter binding:
Hibernate provides rich support for dynamic query parameter binding. What is dynamic query parameter binding? In fact, if we are familiar with traditional JDBC programming, it is not difficult to understand the dynamic binding of query parameters. The following code binds parameters of traditional JDBC:
Preparestatement pre = connection. Prepare ("select * from user where user. Name = ?");
Pre. setstring (1, "zhaoxin ");
Resultset rs1_pre.exe cutequery ();
In hibernate, the query parameter binding function similar to this type is also provided. In hibernate, this function also provides many features that are richer than traditional JDBC operations, there are four parameter binding methods in hibernate. We will introduce them as follows:
A. Bind by parameter name:
To define a name parameter in an hql statement, start with ":". The format is as follows:
Query query = session. createquery ("from user where user. Name =: customername and user: customerage =: Age ");
Query. setstring ("mermername", name );
Query. setinteger ("mermerage", age );
In the above Code, mername and: customerage are used to define the customername and customerage respectively, and then the setxxx () method of the query interface is used to set the name parameter value. The setxxx () method contains two parameters, they are the name of the named parameter and the actual value of the named parameter.
B. According to the parameter location:
Use "?" In the hql query statement "?" Parameter location, in the following format:
Query query = session. createquery ("from user where user. Name =? And user. Age =? ");
Query. setstring (0, name );
Query. setinteger (1, age );
Similarly, the setxxx () method is used to set the binding parameter, except that the first parameter of the setxxx () method represents the position number of the bonding parameter in the hql Statement (starting from 0 ), the second parameter still represents the actual value of the parameter.
Note: In actual development, naming parameters by name are recommended, because this not only provides good program readability, but also improves the maintainability of the program, because when the location of the query parameter changes, you do not need to adjust the program code in the naming method.
C. setparameter () method:
In the hql query of hibernate, you can use the setparameter () method to specify any type of parameters, as shown in the following code:
String hql = "from user where user. Name =: customername ";
Query query = session. createquery (hql );
Query. setparameter ("mermername", name, hibernate. String );
As shown in the code above, the setparameter () method contains three parameters: the named parameter name, the actual value of the named parameter, and the ing type of the named parameter. The setparameter () method of some parameter types can guess the corresponding ing type based on the Java type of the parameter value. Therefore, you do not need to display the ing type, as shown in the preceding example, you can directly write it like this:
Query. setparameter ("mermername", name); however, for some types, you must specify the ing type, such as Java. util. date type, because it corresponds to multiple hibernate ing types, such as hibernate. data or hibernate. timestamp.
D. setproperties () method:
In hibernate, you can use the setproperties () method to bind the named parameter with the property value of an object. 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 instance of the customer object to the name parameter, but requires that the name of the name parameter must be the same as the property of the object.
There is also a special setentity () method, which associates the named parameter with a persistent object, as shown in the following code:
Customer customer = (customer) Session. Load (customer. Class, "1 ");
Query query = session. createquery ("from order where order. Customer =: customer ");
Query. setproperties ("customer", customer );
List list = query. List ();
The above code generates an SQL statement similar to the following:
Select * from order where customer_id = '1 ';
E. advantages of using bound parameters:
Why do we need to bind a named parameter? The existence of any thing has its own value. Specifically, binding parameters has the following two main advantages for hql queries:
① You can use the database to implement performance optimization. For hibernate, preparestatement is used at the underlying layer to complete the query. Therefore, for SQL statements with the same syntax and parameters, you can make full use of the pre-compiled SQL statement cache to improve query efficiency.
② Prevent SQL Injection security vulnerabilities:
SQL injection is an attack method specially designed for SQL statement Assembly. For example, for common user logon, you can enter the user name and password on the logon interface, at this time, the login verification program may generate the following hql statement:
"From user where user. Name = '" + name + "' and user. Password = '" + password + "'"
This hql statement is logically correct, and the logon verification function is generally completed correctly, however, if you enter "zhaoxin or 'X' = 'x" in the user name during logon, the following hql statement is generated if the string of a simple hql statement is assembled:
"From user where user. Name = 'zhaoxin' or 'X' = 'X' and user. Password = 'admin '";
Obviously, the where clause of this hql statement will always be true, making the role of the user's password meaningless. This is the basic principle of SQL injection attacks.
Using the parameter binding method, you can properly handle this problem. When using the parameter binding method, you will get the following hql statement:
From user where user. name = ''zhaoxin' or ''x = ''x' and user. password = 'admin'; therefore, using the binding parameter will resolve the single quotation marks entered in the user name to a string (if you want to include single quotation marks in the string, you should use repeated single quotation marks ), therefore, parameter binding can effectively prevent SQL Injection security vulnerabilities. |
