hql statement examples and detailed explanations (Reprinted)

HQL query
HQL query:
Criteria queries conducted on the query object-oriented package with programmers way of thinking, but the HQL (Hibernate Query Lanaguage) queries to provide a more rich and flexible query characteristics,

Hibernate HQL query approach will be established as the official recommended standard query methods, HQL query in Query Criteria include all the features of the premise, provides a standard SQL statement similar to queries, while also providing a more

Plus object-oriented package. The complete situation HQL statement as follows:
Select / update / delete ... ... from ... ... where ... ... group by ... ... having ... ... order by ... ... asc / desc
One of the update / delete for the Hibernate3 in the newly added features, see HQL query is very similar to standard SQL queries. Hibernate HQL query as a whole entity in the core operating system status, this section I

Will be dedicated to operations around the HQL specific technical details to explain.
1, entity query:
The entity query technology, in fact, we have had in the previous involved, such as the following example:
String hql = "from User user";
List list = session.CreateQuery (hql). List ();
Result of the above code execution, query the User entity object corresponding to all the data, and data encapsulated in User entity object, and returned into the List. It should be noted that, Hibernate entities check

There is consultation on the inheritance of the judge, such as we discussed earlier, mapping entity inheritance relations Employee entity object, it has two sub-classes are HourlyEmployee, SalariedEmployee, if there is such

HQL statement: "from Employee", the time when the implementation of the search will retrieve all Employee Hibernate entity object type corresponding to the data (including its subclasses HourlyEmployee, SalariedEmployee corresponding

Because HQL statement similar to the standard SQL statements, so we can use in the HQL statement where the words and where words can be used in a variety of expressions, comparison operators and the use of "and", "or" connection

Different query combinations. See the following simple example:
from User user where user.age = 20;
from User user where user.age between 20 and 30;
from User user where user.age in (20,30);
from User user where user.name is null;
from User user where user.name like '% zx%';
from User user where (user.age% 2) = 1;
from User user where user.age = 20 and user.name like '% zx%';
2, update and delete entities:
Continues to explain other, more powerful query HQL function, we first carried out to explain the following entities using HQL update and delete technology. The technology function is Hibernate3 new features added in Hibernate2

Is not available. For example, in Hibernate2, if we want the database to all users of the age of 18 years to 20 years old all, then we have to first 18 years of age out of the user search and then their

Modify the age of 20 years, the last call Session.update () statement is updated. On this issue in Hibernate3 provides a more flexible and more efficient solutions, 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 Hibernate3, the one-time bulk data to complete the update, the performance improvement is quite substantial. Also can delete a similar manner to complete the operation, such as 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 of chapters one by one, then you will remember me in the second part of the bulk data manipulation of discourses, the discussion of this mode of operation, this mode of operation referred to as bulk in Hibernate3

delete / update, this approach can significantly improve the operation of the flexibility and efficiency, but using this approach is likely to cause a buffer synchronization of Problem (please see discussion).
3, attribute query:
Very often, we retrieve the data, does not need physical objects corresponding to all the data, but only part of the object needs to retrieve physical properties of the corresponding data. This time we can use HQL query technology attributes

, Such as the following program example:
List list = session.createQuery ("select user.name from User user"). List ();
for (int i = 0; i <list.size (); i + +) (
System.out.println (list.get (i));
We only retrieve the User entity name attribute corresponds to the data, then return to the list containing the result set for each entry is of type String name attribute corresponds to the data. We can also search more than one property,

If the following procedures:
List list = session.createQuery ("select user.name, user.age from User user"). List ();
for (int i = 0; i <list.size (); i + +) (
Object [] obj = (Object []) list.get (i);
System.out.println (obj [0]);
System.out.println (obj [1]);
At this point in the returned result set list, each entry is contained in an Object [] type, which contains the corresponding attribute data values. Today our generation as well the ideological influence of object-oriented developers, may

Will find the above to return Object [] not meet the object-oriented style, then we can use HQL to provide examples of the dynamic structure function data on these flat packages, such as the following code:
List list = session.createQuery ("select new User (user.name, user.age) from User user"). List ();
for (int i = 0; i <list.size (); i + +) (
User user = (User) list.get (i);
System.out.println (user.getName ());
System.out.println (user.getAge ());
Here we construct an instance of an object through dynamic, on a package to return the results to make our program more in line with the object-oriented style, but there is a problem here, is important to note that the User object returned by this time,

Merely an ordinary Java object to, in addition to the value of query results, the other attribute values are null (including the primary key id), that can not implement this object Session object persistence update parade

For. Such as the following code:
List list = session.createQuery ("select new User (user.name, user.age) from User user"). List ();
for (int i = 0; i <list.size (); i + +) (
User user = (User) list.get (i);
user.setName ("gam");
session.saveOrUpdate (user); / / here will be the practical implementation of a save operation, but does not perform update operations, because the User object's id property to null, Hibernate will it as a freedom

Like (please refer to the persistent object state part of the paper), so would save operation it performs.
4, grouping and sorting
A, Order by clause:
And the SQL statement similar to, HQL queries can also order by clause to sort the query result set, and can be sorted asc or desc keywords specified ways, such as the following code:
from User user order by user.name asc, user.age desc;
HQL query above, will name attribute in ascending order, descending sort attributes to age, but with the same SQL statement, the default sort is asc, or ascending order.
B, Group by clause and statistical inquiry:
HQL statement in the same group by clause groups support the use of inquiry, also supports the group by clause combined with aggregate functions with statistical queries, most of the standard SQL aggregate functions can be used in the HQL statement, such as:

count (), sum (), max (), min (), avg () and so on. Such as the following code:
String hql = "select count (user), user.age from User user group by user.age having count (user)> 10";
List list = session.createQuery (hql). List ();
C, optimization of statistical inquiry:
Suppose we now have two database tables, each table is the customer and order table, which is structured as follows:
ID varchar2 (14)
age number (10)
name varchar2 (20)

ID varchar2 (14)
order_number number (10)
customer_ID varchar2 (14)

There are two HQL query, 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 using the HQL statement in connection query (HQL statement that we will be devoted to the query part of the connection), now we can see the last two query results returned are the same, but they

In fact, there is a clear distinction between statements (1) search results will be returned to Customer and Order objects persistent, and they will be placed into Hibernate's Session cache, and the Session will be responsible for them in the cache

The uniqueness and data synchronization with back-end database, and only after the submission of their affairs will be removed from the cache; the statement (2) returns the relational data rather than persistent objects, so they will not take up

Hibernate's Session cache, as long as the search after the application is not access them, they are occupied by JVM's memory is likely to be garbage collected, and the Hibernate does not synchronize their changes.
In our system development, especially Mis system is inevitable to the development of statistical inquiry, such function has two features: the first volume of data; the second operation under normal circumstances are read-only and will not involve to the right system

Meter data changes, then if a query using the first approach will inevitably lead to a large number of persistent objects in Hibernate's Session cache, and Hibernate's Session cache and data but also for their

Database data synchronization. If the second query by way of, obviously it will improve query performance, because no Hibernate, Session cache management overhead, and long as the application Chengxubuzai use these Shuju, it

Were occupied by the release of memory space will be recovered.
Therefore, development of statistical query system, try to use the written statement required by select query to return the way the properties of relational data, and ways to avoid using the first query to return persistent objects (in this way is in a

More suitable for use when demand changes), so you can improve operational efficiency and reduce memory consumption. Body of the real master is not fluent in all, but proficient in the right places with the appropriate means.
5, parameter binding:
Hibernate dynamic query parameter binding on providing a wealth of support, then what is the query parameters dynamically bind it? In fact, if we are familiar with the traditional JDBC programming, we not difficult to understand the query parameters dynamic binding,

The following code in the traditional JDBC bind parameters:
PrepareStatement pre = connection.prepare ("select * from User where user.name =?");
pre.setString (1, "zhaoxin");
ResultSet rs = pre.executeQuery ();
Also provided in the Hibernate query parameters like this binding function, but also on the Hibernate features than the traditional JDBC Operation provides rich multi-features, the existence of four kinds of parameters Hibernate bind the CPC

In the manner, we will introduce the following:
A, binding by parameter name:
Defined in the HQL statement named parameters use ":" at the beginning of the form as follows:
Query query = session.createQuery ("from User user where user.name =: customername and user.customerage =: age");
query.setString ("customername", name);
query.setInteger ("customerage", age);
The code above: customername and: customerage customername named parameters are defined and customerage, then use the Query interface setXXX () method to set name of the parameter values, setXXX () Method package

With two parameters, namely, the name of named parameters and named parameters of the actual value.
B, position by bonding parameters:
Used in the HQL query "?" To define the parameters of location, format is as follows:
Query query = session.createQuery ("from User user where user.name =? And user.age =?");
query.setString (0, name);
query.setInteger (1, age);
Use the same setXXX () method to set bind parameters, but this time setXXX () method of the first argument on behalf of bonding parameters appear in the HQL statement, the position number (from 0 starting number), the second argument still behalf of the Senate

Actual value.
Note: In the actual development, the state will promote the use of named parameters by name, because it not only provides a very good program readability, but also to improve the program's ease of maintenance, because when the query parameters of the position is changed

, By name naming parameters in the way state does not require adjustment of program code.
C, setParameter () method:
In Hibernate's HQL query by setParameter () method of bonding parameters of any type, the following code:
String hql = "from User user where user.name =: customername";
Query query = session.createQuery (hql);
query.setParameter ("customername", name, Hibernate.STRING);
Code as shown above, setParameter () method contains three parameters, namely the name of named parameters, named parameters of the actual value, and name the parameter mapping type. For some parameter type setParameter () method

More parameter values to Java types, guess the corresponding mapping type, so this time do not need to write a map type display, such as the above example, can write:
query.setParameter ("customername", name); but for some types of maps must indicate the type, such as java.util.Date type, because it corresponds to a variety of Hibernate mapping types, such as

Hibernate.DATA or Hibernate.TIMESTAMP.
D, setProperties () method:
In Hibernate you can use the setProperties () method, named parameters with an object tied to property values, as follows 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);
setProperties () method will automatically be customer object instance property value to the named parameters match, but the requested named parameter name must be the appropriate entity object attributes with the same name.
There is also a special setEntity () method, which will name the parameters associated with a persistent 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 above code will be generated similar to the following SQL statement:
Select * from order where customer_ID = '1 ';
E, the advantages of using the binding parameters:
Why do we use the binding named parameters? The existence of any one thing all have their value, specific to the binding parameters for the HQL query, the main there are two main advantages:
① , can be implemented using database performance optimization, because Hibernate is using a PrepareStatement at the bottom to complete the query, so the same parameters for different SQL syntax statements, can be

To take advantage of pre-compiled SQL statement cache, so as to enhance query efficiency.
② , can prevent SQL Injection vulnerabilities arise:
SQL Injection is a specially assembled for the attack SQL statement, such as our common user login, the login screen, users enter a user name and password, then login verification process may be generated as

Under the HQL statement:
"From User user where user.name = '" + name + "' and user.password = '" + password + "'"
The HQL statement is logically no problems, this login authentication in general is done correctly, but if the user name when logging in, type "zhaoxin or 'x' = 'x",

Then using a simple HQL statement if the string assembly, it will generate the following HQL statement:
"From User user where user.name = 'zhaoxin' or 'x' = 'x' and user.password = 'admin'";
Obviously this HQL statement of where the words will always be true, Er Shi meaningless role of the user password, which is the basic principle of SQL Injection attacks.
Way to use bound parameters, we can properly deal with this problem, when using the binding parameters, will be the following HQL statement:
from User user where user.name =''zhaoxin''or''x =''x'''and user.password =' admin '; can be seen using the binding parameters will enter the user name in single quotes Solutions

Analysis of a string (if you want to include single quotes in the string should be used to repeat the form of single quotation marks), so the parameter binding to prevent SQL Injection vulnerabilities.

分类:Java 时间:2010-08-26 人气:267
blog comments powered by Disqus


  • oracle restore accidentally deleted data, unlock the other sql statements 2010-04-27

    1. Have accidentally developed delete all on database table, then threatened to death. We found the following statement to one hour to restore the data before! Very simple. Note that using the administrator login system: select * from table name as o

  • [Summary] oracle restore accidentally deleted data, unlock the other sql statements (switch) 2010-04-28

    Transfer from: http://www.javaeye.com/topic/648858 1. Who do not care development on database table to delete all, was scared to death. We found the following statement to one hour to restore the data before! Very simple. Note that using the administ

  • In-depth performance tuning SQL statements 2011-05-17

    This example sqlserver Some programmers to write database applications, often focusing on the use of OOP and a variety of framework, but it ignores the basic SQL statement and its "performance (performance) optimization" problem. Had heard of a

  • Linux operating system upstart MeeGo 2010-03-25

    March 13, 2010 In the just-concluded World Mobile Congress 2010 in Barcelona, Linux operating system family of intelligent merging the two forces, that Intel's Moblin and Nokia's Maemo, the merger enabled the new name MeeGo, and management by the Lin

  • Write high-performance methods of SQL statements 35 2010-09-07

    (1) integration of simple, non-associated database access: If you have a few simple database queries, you can put them into a query (even if no relationship between them) (2) to remove duplicate records: The most efficient way to delete duplicate rec

  • Linux Learning - the first day - what is the operating system? 2011-06-30

    Chapter What is Linux What is Linux 1.1 1.1.1 Computer: calculation aids Computer must have the components: Input unit: such as a mouse, keyboard, card reader machine, and so on. Central processing unit (CPU): contains the arithmetic logic, control,

  • SQL statements to manipulate the collection 2011-04-22

    // Keep a small number of decimal places select cast( Column name as a numeric type , Such as decimal,numeric, ( Length, number of digits after the decimal point )) Column name from the table name Case study :select cast(Sid as decimal(18,2)) Sid fro

  • Google Tablet PC operating system, a prototype map interface 2010-03-31

    Google Chromium official website of the Tablet PC operating system interface diagram U.S. technology blog site TechCrunch that, Chrome OS Tablet PC operating system interface map from Google Chrome seems to be the designer Glen Murphy (Glen Murphy) h

  • Hibernate query object Expression package introduced 2009-08-20

    Criteria Query is provided by the Hibernate object-oriented SQL statement expressed in a way. When we approach the use of JDBC to query the data will be written like this: select NAME FROM EMPLOYEE where ID ='001 ' Criteria Query using Hibernate mode

  • Study concluded nine Lucene: Lucene query object (1) transfer 2010-06-08

    In addition to supporting other Lucene query syntax, you can construct your search query object. From the previous section of the Lucene's syntax chapter can know, with the corresponding query object query are: BooleanQuery, FuzzyQuery, MatchAllDocsQ

iOS 开发

Android 开发

Python 开发



PHP 开发

Ruby 开发






Javascript 开发

.NET 开发



Copyright (C) codeweblog.com, All Rights Reserved.

CodeWeblog.com 版权所有 闽ICP备15018612号

processed in 0.398 (s). 12 q(s)