HQL query depends on the Query class. Each Query instance corresponds to a query object. Use HQL query to proceed as follows:
1. Get the Hibernate Session object
2. Write HQL statements
3. Use the HQL statement as a parameter and call the createQuery method of Session to create a query object
4. If the HQL statement contains parameters, then the setXxx method of Query is called assigning values to the parameter.
5. Call Query's exclusive list() or uniqueResult() method to return the query result list
Simple example:
@SuppressWarnings("deprecation")
public class HibernateUtil {
private static final SessionFactory sessionFactory;
static {
sessionFactory = new AnnotationConfiguration().configure().buildSessionFactory();
}
public static Session getOpenSession() {
return ();
}
public static Session getCurrentSession() {
return ();
}
}
@Entity
public class Employee {
private Integer id;
private String name;
private Integer age;
@Id
@GeneratedValue
public Integer getId() {
return id;
}
public void setId(Integer id) {
= id;
}
@Basic
public String getName() {
return name;
}
public void setName(String name) {
= name;
}
@Basic
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
= age;
}
public String toString() {
return "id:" + id + " " + "name:" + name + " " + "age:" + age;
}
}
@SuppressWarnings("all")
public class HQLDemo {
@Test
public void testHQL() {
Session session = ();
List<Employee> employeeList = ("from Employee as e").list();
for(Employee e : employeeList)
(e);
}
@Test
public void testHQLHasParameter() {
Session session = ();
List<Employee> employeeList = ("from Employee as e where = :personName").setString("personName", "xujianguo").list();
for(Employee e : employeeList)
(e);
}
}
From clause of HQL query:
from is the simplest HQL statement and the most basic HQL statement. The from keyword is followed by the class name of the persistent class, such as:
From Employee table name selects all instances from Employee class
But what we often use is to do this:
From employee as e, this e is the alias of Employee, which is the instance name. It is recommended to write this way.
The select clause of HQL query:
The select clause is used to select the specified attribute or directly select an entity. Of course, the attribute selected by select must be attributes contained in the persistent class after from, such as:
select from Employee as select can select any attribute, that is, you can not only select the direct attribute of the persistent class, but also the attributes contained in the component attributes, such as:
Select from Employee as eHQL query aggregation function:
Aggregation function:
avg: calculate the average value of attributes
count: count the number of selected objects
max: The maximum value of the statistical attribute value
min: The minimum value of the statistical attribute value
sum: calculates the sum of attribute values
like:
select count(*) from Employee as e @Test
public void testHQLFunction() {
Session session = ();
(("select count(*) from Employee as e").uniqueResult());
}
Polymorphic query:
HQL will not only query all instances of the persistent class, but also query all instances of the subclass of the class, provided that there is an inheritance map.
Where clause of HQL query:
Where clause is mainly used to filter selected results and narrow the scope of selection, such as:
from employee as e where like "xjg%" @Test
public void testHQLWhere() {
Session session = ();
List<Employee> employeeList = ("from Employee as e where like 'zhou%'").list();
for(Employee e : employeeList)
(e);
}
order by clause:
Query return combinations can be sorted according to any attribute of the class or component attributes, and can also specify ascending or descending order using the asc or desc keywords, such as:
from Employee as e order by desc
Subquery:
In subquery, there are also query statements in the query statement, such as:
from Employee as e where > (select from Person as p) @Test
public void testHQLChildQuery() {
Session session = ();
List<Employee> employeeList = ("from Employee as e where > (select from Employee as e1 where = 'xujianguo')").list();
for(Employee e : employeeList)
(e);
}
[code]
Naming query:
HQL query also supports putting the HQL statements used by the query into the configuration file instead of in the code. Use the <query> child element in the <hibernate-mapping> element of the Hibernate mapping file to define a named query. This <query> element only needs to specify a name attribute and specify the name of the named query, such as:
[code]
<query name="query">
from Employee as e
<query />
The Session provides a getNamedQuery(String name) method, which is used to create a Query object. Once the Query object is obtained, the rest of the work will be the same as before.
@Test
public void testHQLNamedQuery() {
Session session = ();
List<Employee> employeeList = ("query").list();
for(Employee e : employeeList)
(e);
}
HQL query statement
/**
*
*/
package com.;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
/**
*
* @author XHW
*
* @date 2011-6-18
*
*/
public class HibernateTest {
/**
* @param args
*/
public static void main(String[] args) {
HibernateTest test = new HibernateTest();
();
();
();
();
();
();
();
();
}
public void where() {
// Use where to query
Session session = ()
.openSession();
();
Query query = session
.createQuery("from User where id not between 200 and 2000");
List<User> list = ();
for (User user : list) {
(() + ());
}
// Use where clause in projection query
query = ("select username from User where id=2");
List<String> listname = ();
for (String name : listname) {
(name);
}
// inQuery
query = session
.createQuery("from User where username in ('Hongten','Hanyuan','dfgd')");
List<User> listin = ();
for (User user : listin) {
(() + ());
}
// Like query
query = ("from User where username not like 'Hon%'");
List<User> listlike = ();
for (User user : listlike) {
(() + ());
}
// null query
query = ("from User where password is null");
List<User> listnull = ();
for (User user : listnull) {
(() + ());
}
// and query
query = session
.createQuery("from User where password is not null and id<5");
List<User> listand = ();
for (User user : listand) {
(() + ()
+ ());
}
// order by
query = ("from User order by username,id desc");
List<User> listorderby = ();
for (User user : listorderby) {
(() + ());
}
// Use the "?" number as the parameter placeholder, can multiple be used in a HQL statement?
// (0,2)
// (0,"Hongten")
query = session
.createQuery("select username from User where username=?");
(0, "Hongten");
List<String> listwenhao = ();
for (String name : listwenhao) {
(name);
}
().commit();
}
public void function() {// Convert uppercase letters to lowercase letters
// It can be used in: for example, in a user-registered program, it is not easy to distinguish between upper and lowercase cases, but it can be easily compared after all converted to lowercase.
Session session = ()
.openSession();
();
// Output the original data
Query query = ("select username from User");
List<String> list = ();
for (String name : list) {
(name);
}
("-------------------------------------------");
// All output data is converted to lowercase
query = ("select lower(username) from User");
List<String> listChange = ();
for (String name : listChange) {
(name);
}
().commit();
}
public void update() {
Session session = ()
.openSession();
();
Query query = session
.createQuery("update User set username='Hong Wei1231' where id=?");
(0, 3);
int rowCount = ();
(rowCount);
().commit();
}
public void operateProfile() {// Perform HQL statement operations on the profile class
Session session = ()
.openSession();
();
// Perform query operations
Query query = ("from Profile");
List<Profile> list = ();
for (Profile profile : list) {
(() + ()
+ () + ()
+ ());
}
// Perform the delete operation
query = ("delete from Profile where id=?");
(0, 3);
int rowCount = ();
(rowCount);
().commit();
}
public void jiaoChaCheck() {//Cross-query
//The result of this method is Cartesian product, which is not very useful for our development.
Session session = ()
.openSession();
();
Query query=("from User,Profile");
List<Object[]> list=();
for(Object[] values:list){
User user=(User)values[0];
("ID :"+()+",UserName:"+()+",Password:"+());
Profile profile=(Profile)values[1];
(()+()+()+());
}
().commit();
}
public void innerJoin(){//Inner connection query
/**
* The following three hql statements can all get the same results
* String hql="select p from Profile as p inner join ";
* After adding "fetch" to the following hql statement, this hql statement becomes an "urgent HQL" statement. Such query efficiency is higher than that of the above hql statement.
* String hql="select p from Profile as p inner join fetch ";
*
* String hql="select p from Profile p,User u where =u";
* String hql="select p from Profile p,User u where =";
*
*/
Session session = ()
.openSession();
();
String hql="select p from Profile as p inner join fetch ";
//String hql="select p from Profile p,User u where =u";
//String hql="select p from Profile p,User u where =";
Query query=(hql);
List<Profile> list=();
for(Profile p:list){
("ID:"+().getId()+" Username: "+().getUsername()+" Email: "+()+", Address: "+());
}
().commit();
}
public void QBC(){//Implement intra-connection query in QBC
Session session=().openSession();
();
Criteria criteria=().createCriteria("user");
List<Profile> list=();
for(Profile p:list){
("ID:"+().getId()+" Username: "+().getUsername()+" Email: "+()+", Address: "+());
}
//Implement external connection in QBC
("##################################################");
criteria=().setFetchMode("user", );
List<Profile> listp=();
for(Profile p:list){
("ID:"+().getId()+" Username: "+().getUsername()+" Email: "+()+", Address: "+());
}
().commit();
}
public void leftOuterJoin(){//Left outer connection
/**
* String hql="select p from Profile p left outer join order by ";
* After adding "fetch" to the following hql statement, this hql statement becomes an "urgent HQL" statement. Such query efficiency is higher than that of the above hql statement.
* String hql="select p from Profile p left outer join fetch order by ";
*
* String hqlu="select u from User u left outer join ";
* After adding "fetch" to the following hql statement, this hql statement becomes an "urgent HQL" statement. Such query efficiency is higher than that of the above hql statement.
* String hqlu="select u from User u left outer join fetch ";
*/
Session session=().getCurrentSession();
();
String hql="select p from Profile p left outer join fetch order by ";
Query query=(hql);
List<Profile> list=();
for(Profile p:list){
("ID:"+().getId()+" Username: "+().getUsername()+" Email: "+()+", Address: "+());
}
("-------------------------------------");
String hqlu="select u from User u left outer join fetch ";
query=(hqlu);
List<User> listu=();
for(User u:listu){
(()+()+());
}
().commit();
}
public void rightOuterJoin(){//Right Outer Connection
Session session=().getCurrentSession();
();
String hql="select u from User u right outer join order by ";
Query query=(hql);
List<User> listu=();
for(User user:listu){
(()+()+());
}
().commit();
}
}
result:
log4j:WARN No appenders could be found for logger ().
log4j:WARN Please initialize the log4j system properly.
Hibernate:
select
user0_.id as id0_,
user0_.username as username0_,
user0_.password as password0_
from
user0_
where
user0_.id not between 200 and 2000
1hongten
2hanyuan
3hongwei
4mingliu
5shouzhang
Hibernate:
select
user0_.username as col_0_0_
from
user0_
where
user0_.id=2
hanyuan
Hibernate:
select
user0_.id as id0_,
user0_.username as username0_,
user0_.password as password0_
from
user0_
where
user0_.username in (
'Hongten' , 'Hanyuan' , 'dfgd'
)
1hongten
2hanyuan
Hibernate:
select
user0_.id as id0_,
user0_.username as username0_,
user0_.password as password0_
from
user0_
where
user0_.username not like 'Hon%'
2hanyuan
4mingliu
5shouzhang
Hibernate:
select
user0_.id as id0_,
user0_.username as username0_,
user0_.password as password0_
from
user0_
where
user0_.password is null
Hibernate:
select
user0_.id as id0_,
user0_.username as username0_,
user0_.password as password0_
from
user0_
where
(
user0_.password is not null
)
and user0_.id<5
1hongten123
2hanyuan5645645
3hongwei5645645
4mingliu5645645
Hibernate:
select
user0_.id as id0_,
user0_.username as username0_,
user0_.password as password0_
from
user0_
order by
user0_.username,
user0_.id desc
2hanyuan
1hongten
3hongwei
4mingliu
5shouzhang
Hibernate:
select
user0_.username as col_0_0_
from
user0_
where
user0_.username=?
hongten
Hibernate:
select
user0_.username as col_0_0_
from
user0_
hongten
hanyuan
hongwei
mingliu
shouzhang
-------------------------------------------
Hibernate:
select
lower(user0_.username) as col_0_0_
from
user0_
hongten
hanyuan
hongwei
mingliu
shouzhang
Hibernate:
update
set
username='Hongwei1231'
where
id=?
1
Hibernate:
select
user0_.id as id0_0_,
profile1_.id as id1_1_,
user0_.username as username0_0_,
user0_.password as password0_0_,
profile1_.user_id as user2_1_1_,
profile1_.email as email1_1_,
profile1_.phone as phone1_1_,
profile1_.mobile as mobile1_1_,
profile1_.address as address1_1_,
profile1_.postcode as postcode1_1_
from
user0_,
profile1_
ID :1,UserName:hongten,Password:[email protected]
ID :1,UserName:hongten,Password:[email protected]
ID :1,UserName:hongten,Password:[email protected]
ID :2,UserName:hanyuan,Password:[email protected]
ID :2,UserName:hanyuan,Password:[email protected]
ID :2,UserName:hanyuan,Password:[email protected]
ID :3,UserName:Hongwei1231,Password:[email protected]
ID :3,UserName:Hongwei1231,Password:[email protected]
ID :3,UserName:Hongwei1231,Password:[email protected]
ID :4,UserName:mingliu,Password:[email protected]
ID :4,UserName:mingliu,Password:[email protected]
ID :4,UserName:mingliu,Password:[email protected]
ID :5,UserName:shouzhang,Password:[email protected]
ID :5,UserName:shouzhang,Password:[email protected]
ID :5,UserName:shouzhang,Password:[email protected]
Hibernate:
select
profile0_.id as id1_0_,
user1_.id as id0_1_,
profile0_.user_id as user2_1_0_,
profile0_.email as email1_0_,
profile0_.phone as phone1_0_,
profile0_.mobile as mobile1_0_,
profile0_.address as address1_0_,
profile0_.postcode as postcode1_0_,
user1_.username as username0_1_,
user1_.password as password0_1_
from
profile0_
inner join
user1_
on profile0_.user_id=user1_.id
ID:1 Username: hongten Email: hongtenzone@, Address: Guangzhoushi
ID:2 Username: hanyuan Email: hanyuan@, Address: GuangzhoushiDianbian
ID:3 Username:Hongwei1231 Email: hanyuan@, Address: GuangzhoushiDianbian
Hibernate:
select
this_.id as id1_1_,
this_.user_id as user2_1_1_,
this_.email as email1_1_,
this_.phone as phone1_1_,
this_.mobile as mobile1_1_,
this_.address as address1_1_,
this_.postcode as postcode1_1_,
user1_.id as id0_0_,
user1_.username as username0_0_,
user1_.password as password0_0_
from
this_
inner join
user1_
on this_.user_id=user1_.id
ID:1 Username: hongten Email: hongtenzone@, Address: Guangzhoushi
ID:2 Username: hanyuan Email: hanyuan@, Address: GuangzhoushiDianbian
ID:3 Username: Hongwei1231 Email: hanyuan@, Address: GuangzhoushiDianbian
##################################################
Hibernate:
select
this_.id as id1_1_,
this_.user_id as user2_1_1_,
this_.email as email1_1_,
this_.phone as phone1_1_,
this_.mobile as mobile1_1_,
this_.address as address1_1_,
this_.postcode as postcode1_1_,
user2_.id as id0_0_,
user2_.username as username0_0_,
user2_.password as password0_0_
from
this_
left outer join
user2_
on this_.user_id=user2_.id
ID:1 Username: hongten Email: hongtenzone@, Address: Guangzhoushi
ID:2 Username: hanyuan Email: hanyuan@, Address: GuangzhoushiDianbian
ID:3 Username: Hong Wei1231 Email: hanyuan@, Address: GuangzhoushiDianbian
Hibernate:
select
profile0_.id as id1_0_,
user1_.id as id0_1_,
profile0_.user_id as user2_1_0_,
profile0_.email as email1_0_,
profile0_.phone as phone1_0_,
profile0_.mobile as mobile1_0_,
profile0_.address as address1_0_,
profile0_.postcode as postcode1_0_,
user1_.username as username0_1_,
user1_.password as password0_1_
from
profile0_
left outer join
user1_
on profile0_.user_id=user1_.id
order by
profile0_.user_id
ID:1 Username: hongten Email: hongtenzone@, Address: Guangzhoushi
ID:2 Username: hanyuan Email: hanyuan@, Address: GuangzhoushiDianbian
ID:3 Username: Hong Wei1231 Email: hanyuan@, Address: GuangzhoushiDianbian
-------------------------------------
Hibernate:
select
user0_.id as id0_0_,
profiles1_.id as id1_1_,
user0_.username as username0_0_,
user0_.password as password0_0_,
profiles1_.user_id as user2_1_1_,
profiles1_.email as email1_1_,
profiles1_.phone as phone1_1_,
profiles1_.mobile as mobile1_1_,
profiles1_.address as address1_1_,
profiles1_.postcode as postcode1_1_,
profiles1_.user_id as user2_0__,
profiles1_.id as id0__
from
user0_
left outer join
profiles1_
on user0_.id=profiles1_.user_id
1hongten[com.@14eaec9]
2hanyuan[com.@569c60]
3Hongwei1231[com.@d67067]
4mingliu[]
5shouzhang[]
Hibernate:
select
user0_.id as id0_,
user0_.username as username0_,
user0_.password as password0_
from
user0_
right outer join
profiles1_
on user0_.id=profiles1_.user_id
order by
user0_.id
Hibernate:
select
profiles0_.user_id as user2_1_,
profiles0_.id as id1_,
profiles0_.id as id1_0_,
profiles0_.user_id as user2_1_0_,
profiles0_.email as email1_0_,
profiles0_.phone as phone1_0_,
profiles0_.mobile as mobile1_0_,
profiles0_.address as address1_0_,
profiles0_.postcode as postcode1_0_
from
profiles0_
where
profiles0_.user_id=?
1hongten[com.@10c0f66]
Hibernate:
select
profiles0_.user_id as user2_1_,
profiles0_.id as id1_,
profiles0_.id as id1_0_,
profiles0_.user_id as user2_1_0_,
profiles0_.email as email1_0_,
profiles0_.phone as phone1_0_,
profiles0_.mobile as mobile1_0_,
profiles0_.address as address1_0_,
profiles0_.postcode as postcode1_0_
from
profiles0_
where
profiles0_.user_id=?
2hanyuan[com.@e265d0]
Hibernate:
select
profiles0_.user_id as user2_1_,
profiles0_.id as id1_,
profiles0_.id as id1_0_,
profiles0_.user_id as user2_1_0_,
profiles0_.email as email1_0_,
profiles0_.phone as phone1_0_,
profiles0_.mobile as mobile1_0_,
profiles0_.address as address1_0_,
profiles0_.postcode as postcode1_0_
from
profiles0_
where
profiles0_.user_id=?
3Hongwei1231[com.@8997d1]