HQL statements for manipulating databases in the Java Hibernate framework _java

Last time we learned to use the criteria for the relevant operations, but because the criteria is not hibernate the official recommendation of the query, we do not use much. Now let's take a look at the official hql and learn about its strength.
Say is HQL, namely Hibernate query statement, and SQL what is the difference? The difference of a letter, haha.
Of course not, the difference between hql and SQL lies in the idea of different, hql is in the direction of object-oriented query, and SQL is the database two-dimensional table query, which contains the idea of the difference. HQL is actually SQL, which is hibernate to help us transform internally and generate SQL.
1 nonsense not much said, we directly look at its strong.
From User
This code is very familiar, because we often use the from table name in SQL, but here is a bit different is that user is not a table name here, but the name of the entity class, by Hibernate to help us map.

Lenovo SQL statement, if we want to identify a property, and based on a certain attribute conditions, it is easy to get a similar statement:

This allows us to identify the name and age of user older than 20 and less than 60. It's easy to understand.
and,or,like,<,>,= in SQL statements can be used in HQL.
It should be noted that when we query multiple properties, the result returned is a object[] array, and only a single time is returned object, which requires different parsing methods, so you need to be aware of the query.

2 of course, we said earlier that HQL is object-oriented, and we do so, not object-oriented thinking. Let's make a change:

Select New User (usr.name,usr.age) from User usr where usr.age > 20 

So we put the results of the query into the user object, note that this is called the user's constructor, the user class must have received two parameters of the user constructor, or you will be wrong, the error message is probably as follows:

Unable to locate appropriate constructor on class [Org.hibernate.tutorial.domain8.User] 

It cannot find the appropriate constructor. Quite clear, plus the constructor that receives the corresponding parameter is OK.
Note that when we detect the above, we do not find the corresponding ID, if we call the Saveorupdate method at this time, it actually performs the saved operation.
Let's take a look at the test code:
After I finish executing the above query, I do the following:

while (Iter.hasnext ()) { 
 User user = (User) iter.next (); 
 User.setname ("sun2"); 
 Session.saveorupdate (user); 
} 

At this point the Hibernate statement is:

Hibernate:insert into USER (user_name, age) VALUES (?,?) 

It inserts a new one instead of an update.
So if we need it to be updated, we need to identify the ID together:

Select New User (usr.name,usr.age,usr.id) from user usr where usr.age > (select Avg. usr.age) from USR) 

Remember to modify the user construction method.
Then we execute our test code, and we get this:

Hibernate:update USER set user_name=?, age=? where user_id=? 

3 we can add SQL functions to the HQL statement:

Select Usr.name from User usr where usr.age > (select AVG (usr.age) from USR) 

This section hql the name of the user who is older than the average age.

4 in Hibernate 3 we can easily update and delete objects, rather than the need to be like 2 in the first load and then delete, we can directly a statement to fix:

Update User set name= ' 123123 ' where name= ' sun33 ' 

Delete statements are similar:

Delete User where name= ' 123123 ' 

5 Hibernate can also be easily grouped and sorted, as long as the use of group by and order by can, this time do not say more.

6 We see that the above are directly to the value of the query or update, if we need dynamic assignment, or too much of the assignment, the total can not be like JDBC with string stitching it, an estimated more than 5, the project group of people want to dozens, hehe.
Or in a modern way, replace it with placeholders and set specific values.
Our Direct code:

Query query = session.createquery ("Select New User (usr.name,usr.age,usr.id) from user usr where usr.name=?"); 
Query.setstring (0, "shun"); 

We see that this approach is similar to the preparedstatement we use directly, and is set by set***, but the difference is that the position here starts at 0, and PreparedStatement starts with 1, paying special attention here.
There are session.find this method in Hibernate2, but since it is now 3, it's not much to say.
The above placeholder we use is called the order placeholder, and there is also a reference placeholder, let's take a look at:

Query query = session.createquery ("Select New User (usr.name,usr.age,usr.id) from user usr where usr.name=:name"); 
Query.setparameter ("name", "Shun"); 

See our HQL statement there is a: name such a thing, this is the reference placeholder, we only need to set the value by Setparameter, note that the first parameter here needs to correspond to the HQL statement of the placeholder value.
Of course, some people will say, this is not object-oriented, then we have to object-oriented:
First, get a class to encapsulate the value of our query.

public class UserQuery { 
 
 private String name; 
 private int age; 
  Omit Get/set method 
  
} 
Query query = Session.createquery ("Select New User (usr.name,usr.age,usr.id) from user usr where" Usr.name=:name "); 
 
UserQuery UQ = new UserQuery (); 
Uq.setname ("Shun"); 
   
Query.setproperties (UQ); 

We encapsulate the values we need to query in code from directly through this class. It's very object oriented.
Some of the project team has some strange rules, not to appear in the code SQL statements, if this is a specification, then I have seen the code of our company, all are unqualified, cup with a large number of string stitching, look at the depressed AH. People who maintain existing projects really can't afford to hurt.
SQL statements are not allowed in the code, which is good advice, but it still depends on the occasion. Let's take a look at hibernate how to configure HQL in the mapping file.
Look directly at the configuration file:

<query name= "Querybyname" > 
 <![ cdata[from 
  User usr where usr.name=:name 
 ]]> 
</query> 

We added a label like this, which indicates that the inside is a HQL statement.
When we need to get to this statement, just add one sentence to the code:

Query query = session.getnamedquery ("Querybyname"); 

This also takes this HQL statement.

HQL can also use combinatorial queries in SQL, such as inner join,left outer join,right outer JOIN.
Let's take a look at their usage:
Let's take a look at the entity classes that we're going to use in our tests:

public class TUser implements serializable{ 
 
 private static final long serialversionuid = 1L; 
 
 private int id; 
 private int age; 
 private String name; 
 Private set<address> addresses = new hashset<address> (); 
  Omit Get/set method 
} Public 
class address implements serializable{ 
 
 private static final long Serialversionuid = 1L; 
 
 private int id; 
 Private String address; 
 Private TUser user; 
  Omit Get/set Method 
} 

Here's a look at the mapping file:

We just have to do the corresponding package name modification on it.
Here's a formal test:
Before testing, let's look at the data in the table:
T_address table data are as follows:

T_user table data are as follows:

1 First we look at the inner join, which is in the HQL by the inner join FETCH, note that the meaning of the fetch is to take out the required data, if not fetch, we take out the data is object[] data type.
Let's take a look first.

From TUser usr INNER join fetch usr.addresses 

When we run it, we see the hibernate output as:

Hibernate:select tuser0_.id as id1_0_, addresses1_.id as id0_1_, tuser0_.name as name1_0_, tuser0_.age as age1_0_, addres Ses1_.address as address0_1_, addresses1_.user_id as user3_0_1_, addresses1_.user_id as user3_0__, addresses1_.id as id0_ _ from T_user tuser0_ inner join t_address addresses1_ on tuser0_.id=addresses1_.user_id 

We run in MySQL to see the results:

We can see that hibernate converts it into a inner join statement and finds the address.
We see no shun4 this record in the result because he doesn't have the address and it record.

And when we use inner join instead of fetch, it prints the statement:

Hibernate:select tuser0_.id as id1_0_, addresses1_.id as id0_1_, tuser0_.name as name1_0_, tuser0_.age as age1_0_, addres Ses1_.address as address0_1_, addresses1_.user_id as user3_0_1_ from T_user tuser0_ inner join t_address addresses1_ on Tu ser0_.id=addresses1_.user_id 

There seems to be no difference in the statement, but when we look it up, it gets the object[] array type, which needs to be noted when parsing.

When we don't have to fetch and just inner join, we need to parse this:

Query query = Session.createquery ("From TUser usr inner join usr.addresses"); 
 
List List = Query.list (); 
Iterator iter = List.iterator (); 
   
while (Iter.hasnext ()) { 
  object[] results = (object[)) Iter.next (); 
  for (int i = 0; i < results.length i + +) { 
  System.out.println (results[i]); 
  } 
 

We see the printed results:

Org.hibernate.tutorial.domain6.tuser@16925b0 
org.hibernate.tutorial.domain6.address@914f6a 
ORG.HIBERNATE.TUTORIAL.DOMAIN6.TUSER@787D6A 
org.hibernate.tutorial.domain6.address@71dc3d 
org.hibernate.tutorial.domain6.tuser@1326484 
Org.hibernate.tutorial.domain6.address@16546ef 

Each of its results is a corresponding object to be traced.

2 left OUTER JOIN, which is the equivalent of a left-hand connection to SQL, let's look at the example directly:

From TUser usr left outer join fetch usr.addresses 

When we run the above statement, hibernate prints out:

Hibernate:select tuser0_.id as id1_0_, addresses1_.id as id0_1_, tuser0_.name as name1_0_, tuser0_.age as age1_0_, addres Ses1_.address as address0_1_, addresses1_.user_id as user3_0_1_, addresses1_.user_id as user3_0__, addresses1_.id as id0_ _ from T_user tuser0_ left outer join t_address addresses1_ on tuser0_.id=addresses1_.user_id 

We found in MySQL, see:

We see that although SHUN4 does not have a corresponding adress, it is found out that the left outer join refers to the record of the table on the left-hand side.
There is no case of a fetch.

3 Next we look at right outer join, see the name must be with the left outer join a bit of a relationship, we directly look at the example can be clearly seen.

From TUser usr right outer join fetch usr.addresses 

We execute it and get the result statement of the hibernate output as:

Hibernate:select tuser0_.id as id1_0_, addresses1_.id as id0_1_, tuser0_.name as name1_0_, tuser0_.age as age1_0_, addres Ses1_.address as address0_1_, addresses1_.user_id as user3_0_1_, addresses1_.user_id as user3_0__, addresses1_.id as id0_ _ from T_user tuser0_ right outer join t_address addresses1_ on tuser0_.id=addresses1_.user_id 

We can see the results when we execute in MySQL:

Here we can see the address for Test4 and no corresponding user corresponds to it, but it is still detected, right outer join refers to the table on the right-hand side of all the records found.
If you don't understand the status of the fetch, you can look at the inner join fetch.