Last time we learned how to use Criteria to perform related operations together, but since Criteria is not the query method officially recommended by Hibernate, we don’t use it much. Now let’s take a look at the officially recommended HQL and learn how powerful it is together.
What is the difference between HQL, or Hibernate query statement and SQL? The difference between one letter, haha.
Of course this is not the case. The difference between HQL and SQL lies in the different ideas. HQL uses an object-oriented direction to query, while SQL uses a query for two-dimensional database tables. What is included here is the difference in ideas. HQL is actually SQL, and it is made by Hibernate to convert and generate SQL internally.
1) Without further ado, let’s take a look at its power.
from User
This code is very familiar, because we often use from table names in SQL, but the difference here is that User is not the table name here, but the name of the entity class, and hibernate helps us map.
Associate SQL statements, if we want to find a certain attribute and conditionally limit it according to a certain attribute, we can get a similar statement:
select usr.name,usr.age from User where usr.age > 20 and usr.age < 60
In this way, we found the names and ages of Users older than 20 and younger than 60. It's easy to understand.
The and, or, like, <,>,= and so on in SQL statements can all be used in HQL.
It should be noted that when we query multiple attributes, the returned result is an Object[] array, and when only a single one, the Object is returned. This requires different parsing methods, so you need to pay attention to it when querying.
2) Of course, we said earlier that HQL is object-oriented, and if we do this, it is not an object-oriented idea. Let's change it:
select new User(usr.name,usr.age) from User usr where usr.age > 20In this way, we put the query result into the User object. Note that the User constructor is called here. There must be a User constructor that receives two parameters in the User class, otherwise an error will be reported. The error message is roughly as follows:
Unable to locate appropriate constructor on class [org.hibernate.tutorial.domain8.User]
It cannot find a suitable constructor. It's very clear that just add the constructor that receives the corresponding parameters.
Note that when we checked the corresponding ID, we did not find out. If we call the saveOrUpdate method at this time, it actually performed the saved operation.
Let's take a look at the test code:
After executing the above query statement, I perform the following operations:
while(iter.hasNext()) { User user = (User)iter.next(); user.setName("sun2"); session.saveOrUpdate(user); } At this time, the Hibernate statement is:
Hibernate: insert into USER (USER_NAME, age) values (?, ?)
It inserts a new one, not an update.
Then if we need it to update, we need to find out 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 constructor.
At this time, we execute our test code again and you will get:
Hibernate: update USER set USER_NAME=?, age=? where USER_ID=?
select usr.name from User usr where usr.age > (select avg(usr.age) from usr)
This HQL detects the name of User who is older than the average age.
4) In Hibernate 3, we can easily update and delete objects, without having to load first and then delete in 2. We can do it directly in one statement:
update User set name='123123' where name='sun33'
Delete statements are similar:
delete User where name='123123'
5) It is also easy to group and sort in Hibernate. Just use group by and order by, and I won’t talk about it more at this time.
6) We see that the above are written directly to query or update. If we need dynamic assignment, or there are too many assignments, we can't splice strings like JDBC. It is estimated that there are more than 5, and everyone in the project team wants to scold me, haha.
It is better to use a modern method, use placeholders instead and then 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 method is similar to the PreparedStatement we use directly. It is set through set***, but the difference is that the position here starts from 0, while the PreparedStatement starts from 1, so pay special attention to it here.
There is also a session.find method in Hibernate2, but since I use 3 now, I won’t say much about it.
The placeholder we used above is called the order placeholder, and there is another one called the reference placeholder. Let's take a look:
Query query = session.createQuery("select new User(usr.name,usr.age,usr.id) from User usr where usr.name=:name"); query.setParameter("name","shun"); I saw that there is a thing like: name in our HQL statement. This is a reference placeholder. We only need to set the value through setParameter later. Note that the first parameter here needs to correspond to the placeholder value in the HQL statement.
Of course, some people may say that this is not object-oriented, so let’s go to object-oriented again:
First, create a class to encapsulate the value we query
public class UserQuery { private String name; private int age; //Omit the 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 directly through this class. Very object-oriented.
Some project teams have some strange regulations that SQL statements are not allowed to appear in the code. If this is a specification, then all the codes of our company I have seen are unqualified. A lot of string splicing in the cup make it look depressed. It's really hard to be hurt to maintain existing projects.
SQL statements are not allowed in the code. This is a good suggestion, but it depends on the occasion. Let's take a look at how Hibernate configures HQL in mapping files.
Look directly at the configuration file:
<query name="queryByName"> <![CDATA[ from User usr where usr.name=:name ]]> </query>
We added a tag like this, which indicates that it is an HQL statement.
When we need to get this statement, we only need to add a sentence to the code:
Query query = session.getNamedQuery("queryByName"); This way, the HQL statement is obtained.
HQL can also be queried using combinations in SQL, such as inner join, left outer join, right outer join, full join.
Let's take a look at their usage:
Let’s take a look at the entity class first, what we need 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} Let's take a look at the mapping file:
<hibernate-mapping package="org.hibernate.tutorial.domain6"> <class name="TUser" table="t_user" dynamic-insert="true" dynamic-update="true"> <id name="id" column="id"> <generator /> </id> <property name="name" type="java.lang.String" column="name"/> <property name="age" type="java.lang.Integer" column="age"/> <set name="addresses" cascade="all" table="t_address" inverse="true"> <key column="user_id" /> <one-to-many/> </set> </class> </hibernate-mapping> <hibernate-mapping package="org.hibernate.tutorial.domain6"> <class name="Address" table="t_address" dynamic-insert="false" dynamic-update="false" dynamic-update="false" dynamic-update="id" type="java.lang.Integer"> <generator /> </id> <property name="address" column="address" type="java.lang.String" /> <many-to-one name="user" column="user_id" not-null="true"></many-to-one> </class> </hibernate-mapping>
You just need to modify the corresponding package name.
Let's conduct formal tests:
Before testing, let's look at the data in the table:
The data of the t_address table is as follows:
The t_user table data is as follows:
1) First, let’s take a look at the inner join, which is fetched in HQL by inner join fetch. Note that the meaning of fetching here is to fetch the required data. If fetching is not used, the data we fetched is of the Object[] data type.
Let's take a look first
from TUser usr inner join fetch usr.addresses
When we run it, we see that the hibernate output is:
Hibernate: select tuser0_.id as id1_0_, addresses1_.id as id0_1_, tuser0_.name as name1_0_, tuser0_.age as age1_0_, addresses1_.address as address0_1_, addresses1_.user_id as user3_0_1_, addresses1_.id as id0__ from t_user tuser0_ inner join t_address addresses1_ on tuser0_.id=addresses1_.user_id
We can see the results when running in mysql:
We can see that hibernate converts it into an inner join statement and finds out the address.
We see that there is no record of shun4 in the result, because it does not have the corresponding address and record.
When we use inner join instead of fetch, the statement it prints is:
Hibernate: select tuser0_.id as id1_0_, addresses1_.id as id0_1_, tuser0_.name as name1_0_, tuser0_.age as age1_0_, addresses1_.address as address0_1_, addresses1_.user_id as user3_0_1_ from t_user tuser0_ inner join t_address addresses1_ on tuser0_.id=addresses1_.user_id
It seems that the statement is not different, but when we find it out, it gets an Object[] array type, so you need to pay attention to this analysis.
When we don't use fetch but just inner join, we need to parse it like 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 print result:
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 the corresponding object.
2) left outer join, this is equivalent to the left connection of SQL. Let’s take a 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_, addresses1_.address as address0_1_, addresses1_.user_id as user3_0_1_, addresses1_.id as id0__ from t_user tuser0_ left outer join t_address addresses1_ on tuser0_.id=addresses1_.user_id
We checked it in mysql and saw:
We see that although shun4 does not have a corresponding adress, it is still found out. left outer join refers to finding out all records in the left table.
I won't talk about the situation without fetch here.
3) Next, let’s take a look at the right outer join. Judging from the name, it must be related to the left outer join. We can clearly see it by looking at the examples directly.
from TUser usr right outer join fetch usr.addresses
We execute it and get the result statement output from Hibernate is:
Hibernate: select tuser0_.id as id1_0_, addresses1_.id as id0_1_, tuser0_.name as name1_0_, tuser0_.age as age1_0_, addresses1_.address as address0_1_, addresses1_.user_id as user3_0_1_, addresses1_.id as id0__ from t_user tuser0_ right outer join t_address addresses1_ on tuser0_.id=addresses1_.user_idWe can see the results after executing in mysql:
Here we can see that the address is Test4 and does not have the corresponding user corresponding to it, but it is still found out. Right outer join refers to finding out all the records in the right table.
The situation of fetch is as above. If you don’t understand, you can take a look at inner join fetch.