hql statement examples and detailed explanations (Reprinted)

sponsored links
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

Data).
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:
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, 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.
  • del.icio.us
  • StumbleUpon
  • Digg
  • TwitThis
  • Mixx
  • Technorati
  • Facebook
  • NewsVine
  • Reddit
  • Google
  • LinkedIn
  • YahooMyWeb

Related Posts of hql statement examples and detailed explanations (Reprinted)

  • Hibernate pessimistic locking mechanism for locking and optimistic locking

    hibernate lock mechanism 1. Pessimistic lock It refers to the modification of data by outsiders hold a conservative attitude. The assumption that at any time access to data, may also have another client to access the same data, in order to maintain t ...

  • hibernate using c3p0 connection pooling

    Private http://www.lifevv.com/tenyo/doc/20070605102040991.html c3p0 for open source's JDBC connection pool, with the release hibernate. This article describes how to use the hibernate configuration in c3p0. c3p0 connection pool configuration is v ...

  • JAVA Class naming convention

    1. Entity Layer: Inheritance: All categories inherited from BasicEntity, one of BasicEntity implementation java.io.Serializable interface; Naming rules: Class Name = Object + type suffix, one of type suffix for Bean, such as: SalesOrderBean 2. Form l ...

  • Hibernate configuration parameters hibernate.hbm2ddl.auto

    Hibernate in the configuration file: <properties> <property name="hibernate.hbm2ddl.auto" value="create" /> </ properties> Parameter Description: validate load hibernate, the authentication to create a database t ...

  • Nan-Jing 5: When IBatis.Hibernate mixed affairs, pay attention to your SQL

    [Problem] Now, in the development of the so-called multi-storey JavaEE applications, data persistence layer is always essential, and "Automatic" of ORM - Hibernate, and "all-manual-type" of SqlMap - IBatis, equivalent data are Per ...

  • In the servlet use Bean

    According to Sun's definition, JavaBean is a reusable software components. In fact JavaBean is a Java class, through the package into a property and methods of treatment of a function or a business object, referred to as bean. Because JavaBean is ...

  • Hibernate secondary cache

    Hibernate cache: 2-bit cache, also known as process-level cache or SessionFactory level cache, secondary cache can be shared by all of the session Cache configuration and the use of: Will echcache.xml (the document code in hibernate package directory ...

  • The level Hibernate cache

    Hibernate cache level: (1) a cache is very short and the session life cycle consistent, also known as session-level cache-level cache or transaction-level cache (2) Ways of Supporting level cache: get (); load (); iterator (); only entity object cach ...

  • Great collection of java interview topics

    1, object-oriented features of what has 1. Abstract: Abstract is that it has overlooked a subject has nothing to do with the current goal of those aspects in order to more fully with the current objectives of the attention-related aspects. Abstract does n

blog comments powered by Disqus
Recent
Recent Entries
Tag Cloud
Random Entries