HQL查詢依賴于Query類，每個Query實例對應一個查詢對象，使用HQL查詢按如下步驟進行：
1.獲取Hibernate Session對象
2.編寫HQL語句
3.以HQL語句作為參數，調用Session的createQuery方法創建查詢對象
4.如果HQL語句包含參數，則調用Query的setXxx方法為參數賦值
5.調用Query獨享的list()或uniqueResult()方法返回查詢結果列表
簡單的例子：
復制代碼 代碼如下:
@SuppressWarnings("deprecation")
public class HibernateUtil {
private static final SessionFactory sessionFactory;
static {
sessionFactory = new AnnotationConfiguration().configure().buildSessionFactory();
}
public static Session getOpenSession() {
return sessionFactory.openSession();
}
public static Session getCurrentSession() {
return sessionFactory.getCurrentSession();
}
}
@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) {
this.id = id;
}
@Basic
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Basic
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String toString() {
return "id:" + id + " " + "name:" + name + " " + "age:" + age;
}
}
@SuppressWarnings("all")
public class HQLDemo {
@Test
public void testHQL() {
Session session = HibernateUtil.getOpenSession();
ListEmployee> employeeList = session.createQuery("from Employee as e").list();
for(Employee e : employeeList)
System.out.println(e);
}
@Test
public void testHQLHasParameter() {
Session session = HibernateUtil.getOpenSession();
ListEmployee> employeeList = session.createQuery("from Employee as e where e.name = :personName").setString("personName", "xujianguo").list();
for(Employee e : employeeList)
System.out.println(e);
}
}
HQL查詢的from子句：
from是最簡單的HQL語句，也是最基本的HQL語句，from關鍵字后緊跟持久化類的類名，如：
from Employee表名從Employee類中選出全部的實例
不過我們常用的是這樣做：
from employee as e這個e就是Employee的別名，也就是實例名，推薦這么寫。
HQL查詢的select子句：
select子句用于選擇指定的屬性或直接選擇某個實體，當然select選擇的屬性必須是from后持久化類包含的屬性，如：
select e.name from Employee as e select可以選擇任意屬性，即不僅可以選擇持久化類的直接屬性，還可以選擇組件屬性包含的屬性，如：
select e.name.firstName from Employee as eHQL查詢的聚集函數：
聚集函數：
avg：計算屬性的平均值
count：統計選擇對象的數量
max：統計屬性值的最大值
min：統計屬性值的最小值
sum：計算屬性值的總和
如：
復制代碼 代碼如下:
select count(*) from Employee as e @Test
public void testHQLFunction() {
Session session = HibernateUtil.getOpenSession();
System.out.println(session.createQuery("select count(*) from Employee as e").uniqueResult());
}
多態查詢：
HQL不僅會查詢出該持久化類的全部實例，還會查詢出該類的子類的全部實例，前提是存在繼承映射。
HQL查詢的where子句：
where子句主要用于篩選選中的結果，縮小選擇的范圍，如：
復制代碼 代碼如下:
from employee as e where e.name like "xjg%" @Test
public void testHQLWhere() {
Session session = HibernateUtil.getOpenSession();
ListEmployee> employeeList = session.createQuery("from Employee as e where e.name like 'zhou%'").list();
for(Employee e : employeeList)
System.out.println(e);
}
order by子句：
查詢返回結合可以根據類或組件屬性的任何屬性進行排序，還可以使用asc或desc關鍵字指定升序或者降序，如：
from Employee as e order by e.name desc
子查詢：
子查詢中就是查詢語句中還有查詢語句，如：
復制代碼 代碼如下:
from Employee as e where e.age > (select p.age from Person as p) @Test
public void testHQLChildQuery() {
Session session = HibernateUtil.getOpenSession();
ListEmployee> employeeList = session.createQuery("from Employee as e where e.age > (select e1.age from Employee as e1 where e1.name = 'xujianguo')").list();
for(Employee e : employeeList)
System.out.println(e);
}
[code]
命名查詢：
HQL查詢還支持將查詢所用的HQL語句放入配置文件中，而不是代碼中，在Hibernate映射文件的hibernate-mapping>元素中使用query>子元素來定義命名查詢，這個query>元素只需指定一個name屬性，指定該命名查詢的名字 ，如：
[code]
query name="query">
from Employee as e
query />
Session里提供了一個getNamedQuery(String name)方法，該方法用于創建一個Query對象，一旦獲得Query對象，剩下的工作就跟前面的一樣了。
復制代碼 代碼如下:
@Test
public void testHQLNamedQuery() {
Session session = HibernateUtil.getOpenSession();
ListEmployee> employeeList = session.getNamedQuery("query").list();
for(Employee e : employeeList)
System.out.println(e);
}
HQL 查詢語句
/**
*
*/
package com.b510.example;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.Query;
import org.hibernate.Session;
/**
*
* @author XHW
*
* @date 2011-6-18
*
*/
public class HibernateTest {
/**
* @param args
*/
public static void main(String[] args) {
HibernateTest test = new HibernateTest();
test.where();
test.function();
test.update();
test.jiaoChaCheck();
test.innerJoin();
test.QBC();
test.leftOuterJoin();
test.rightOuterJoin();
}
public void where() {
// 使用where查詢
Session session = HibernateSessionFactoryUtil.getSessionFactory()
.openSession();
session.beginTransaction();
Query query = session
.createQuery("from User where id not between 200 and 2000");
ListUser> list = query.list();
for (User user : list) {
System.out.println(user.getId() + user.getUsername());
}
// 投影查詢 中使用where子句
query = session.createQuery("select username from User where id=2");
ListString> listname = query.list();
for (String name : listname) {
System.out.println(name);
}
// in查詢
query = session
.createQuery("from User where username in ('Hongten','Hanyuan','dfgd')");
ListUser> listin = query.list();
for (User user : listin) {
System.out.println(user.getId() + user.getUsername());
}
// like查詢
query = session.createQuery("from User where username not like 'Hon%'");
ListUser> listlike = query.list();
for (User user : listlike) {
System.out.println(user.getId() + user.getUsername());
}
// null查詢
query = session.createQuery("from User where password is null");
ListUser> listnull = query.list();
for (User user : listnull) {
System.out.println(user.getId() + user.getUsername());
}
// and查詢
query = session
.createQuery("from User where password is not null and id5");
ListUser> listand = query.list();
for (User user : listand) {
System.out.println(user.getId() + user.getUsername()
+ user.getPassword());
}
// order by
query = session.createQuery("from User order by username,id desc");
ListUser> listorderby = query.list();
for (User user : listorderby) {
System.out.println(user.getId() + user.getUsername());
}
// 使用"?"號 作為參數占位符，一條HQL語句中可以使用多個？
// query.setInteger(0,2)
// query.setString(0,"Hongten")
query = session
.createQuery("select username from User where username=?");
query.setString(0, "Hongten");
ListString> listwenhao = query.list();
for (String name : listwenhao) {
System.out.println(name);
}
session.getTransaction().commit();
}
public void function() {// 把大寫字母轉化為小寫字母
// 作用可以用在：比如在一個用戶注冊的程序中，大小寫不容易區分，但是全部轉化為小寫后就可以很容易進行比較
Session session = HibernateSessionFactoryUtil.getSessionFactory()
.openSession();
session.beginTransaction();
// 輸出原始的數據
Query query = session.createQuery("select username from User");
ListString> list = query.list();
for (String name : list) {
System.out.println(name);
}
System.out.println("-------------------------------------------");
// 輸出的數據全部轉化為小寫
query = session.createQuery("select lower(username) from User");
ListString> listChange = query.list();
for (String name : listChange) {
System.out.println(name);
}
session.getTransaction().commit();
}
public void update() {
Session session = HibernateSessionFactoryUtil.getSessionFactory()
.openSession();
session.beginTransaction();
Query query = session
.createQuery("update User set username='洪偉1231' where id=?");
query.setInteger(0, 3);
int rowCount = query.executeUpdate();
System.out.println(rowCount);
session.getTransaction().commit();
}
public void operateProfile() {// 對profile這個類執行HQL語句操作
Session session = HibernateSessionFactoryUtil.getSessionFactory()
.openSession();
session.beginTransaction();
// 執行查詢操作
Query query = session.createQuery("from Profile");
ListProfile> list = query.list();
for (Profile profile : list) {
System.out.println(profile.getId() + profile.getEmail()
+ profile.getAddress() + profile.getMobile()
+ profile.getPostcode());
}
// 執行刪除操作
query = session.createQuery("delete from Profile where id=?");
query.setInteger(0, 3);
int rowCount = query.executeUpdate();
System.out.println(rowCount);
session.getTransaction().commit();
}
public void jiaoChaCheck() {//交叉查詢
//這種方法查詢出來的結果是笛卡爾積，對于我們開發中沒有多大用處
Session session = HibernateSessionFactoryUtil.getSessionFactory()
.openSession();
session.beginTransaction();
Query query=session.createQuery("from User,Profile");
ListObject[]> list=query.list();
for(Object[] values:list){
User user=(User)values[0];
System.out.print("ID :"+user.getId()+",UserName:"+user.getUsername()+",Password:"+user.getPassword());
Profile profile=(Profile)values[1];
System.out.println(profile.getEmail()+profile.getMobile()+profile.getAddress()+profile.getPostcode());
}
session.getTransaction().commit();
}
public void innerJoin(){//內連接查詢
/**
* 下面三種hql語句都是可以得到相同的結果
* String hql="select p from Profile as p inner join p.user";
* 在下面的hql語句中加入"fetch"后，此hql語句變為了"迫切HQL"語句，這樣的查詢效率要比上面的hql語句要高
* String hql="select p from Profile as p inner join fetch p.user";
*
* String hql="select p from Profile p,User u where p.user=u";
* String hql="select p from Profile p,User u where p.user.id=u.id";
*
*/
Session session = HibernateSessionFactoryUtil.getSessionFactory()
.openSession();
session.beginTransaction();
String hql="select p from Profile as p inner join fetch p.user";
//String hql="select p from Profile p,User u where p.user=u";
//String hql="select p from Profile p,User u where p.user.id=u.id";
Query query=session.createQuery(hql);
ListProfile> list=query.list();
for(Profile p:list){
System.out.println("ID:"+p.getUser().getId()+" Username: "+p.getUser().getUsername()+" Email: "+p.getEmail()+", Address: "+p.getAddress());
}
session.getTransaction().commit();
}
public void QBC(){//QBC中實現內連接查詢
Session session=HibernateSessionFactoryUtil.getSessionFactory().openSession();
session.beginTransaction();
Criteria criteria=session.createCriteria(Profile.class).createCriteria("user");
ListProfile> list=criteria.list();
for(Profile p:list){
System.out.println("ID:"+p.getUser().getId()+" Username: "+p.getUser().getUsername()+" Email: "+p.getEmail()+", Address: "+p.getAddress());
}
//QBC中實現外連接
System.out.println("##################################################");
criteria=session.createCriteria(Profile.class).setFetchMode("user", FetchMode.JOIN);
ListProfile> listp=criteria.list();
for(Profile p:list){
System.out.println("ID:"+p.getUser().getId()+" Username: "+p.getUser().getUsername()+" Email: "+p.getEmail()+", Address: "+p.getAddress());
}
session.getTransaction().commit();
}
public void leftOuterJoin(){//左外連接
/**
* String hql="select p from Profile p left outer join p.user order by p.user.id";
* 在下面的hql語句中加入"fetch"后，此hql語句變為了"迫切HQL"語句，這樣的查詢效率要比上面的hql語句要高
* String hql="select p from Profile p left outer join fetch p.user order by p.user.id";
*
* String hqlu="select u from User u left outer join u.profiles";
* 在下面的hql語句中加入"fetch"后，此hql語句變為了"迫切HQL"語句，這樣的查詢效率要比上面的hql語句要高
* String hqlu="select u from User u left outer join fetch u.profiles";
*/
Session session=HibernateSessionFactoryUtil.getSessionFactory().getCurrentSession();
session.beginTransaction();
String hql="select p from Profile p left outer join fetch p.user order by p.user.id";
Query query=session.createQuery(hql);
ListProfile> list=query.list();
for(Profile p:list){
System.out.println("ID:"+p.getUser().getId()+" Username: "+p.getUser().getUsername()+" Email: "+p.getEmail()+", Address: "+p.getAddress());
}
System.out.println("-------------------------------------");
String hqlu="select u from User u left outer join fetch u.profiles";
query=session.createQuery(hqlu);
ListUser> listu=query.list();
for(User u:listu){
System.out.println(u.getId()+u.getUsername()+u.getProfiles());
}
session.getTransaction().commit();
}
public void rightOuterJoin(){//右外連接
Session session=HibernateSessionFactoryUtil.getSessionFactory().getCurrentSession();
session.beginTransaction();
String hql="select u from User u right outer join u.profiles order by u.id";
Query query=session.createQuery(hql);
ListUser> listu=query.list();
for(User user:listu){
System.out.println(user.getId()+user.getUsername()+user.getProfiles());
}
session.getTransaction().commit();
}
}
結果：
log4j:WARN No appenders could be found for logger (org.hibernate.cfg.Environment).
log4j:WARN Please initialize the log4j system properly.
Hibernate:
select
user0_.id as id0_,
user0_.username as username0_,
user0_.password as password0_
from
users.user user0_
where
user0_.id not between 200 and 2000
1hongten
2hanyuan
3hongwei
4mingliu
5shouzhang
Hibernate:
select
user0_.username as col_0_0_
from
users.user user0_
where
user0_.id=2
hanyuan
Hibernate:
select
user0_.id as id0_,
user0_.username as username0_,
user0_.password as password0_
from
users.user 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
users.user 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
users.user user0_
where
user0_.password is null
Hibernate:
select
user0_.id as id0_,
user0_.username as username0_,
user0_.password as password0_
from
users.user user0_
where
(
user0_.password is not null
)
and user0_.id5
1hongten123
2hanyuan5645645
3hongwei5645645
4mingliu5645645
Hibernate:
select
user0_.id as id0_,
user0_.username as username0_,
user0_.password as password0_
from
users.user user0_
order by
user0_.username,
user0_.id desc
2hanyuan
1hongten
3hongwei
4mingliu
5shouzhang
Hibernate:
select
user0_.username as col_0_0_
from
users.user user0_
where
user0_.username=?
hongten
Hibernate:
select
user0_.username as col_0_0_
from
users.user user0_
hongten
hanyuan
hongwei
mingliu
shouzhang
-------------------------------------------
Hibernate:
select
lower(user0_.username) as col_0_0_
from
users.user user0_
hongten
hanyuan
hongwei
mingliu
shouzhang
Hibernate:
update
users.user
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
users.user user0_,
users.profile profile1_
ID :1,UserName:hongten,Password:123hongtenzone@foxmail.com45464Guangzhoushi65465
ID :1,UserName:hongten,Password:123hanyuan@foxmail.com45648255GuangzhoushiDianbian65465
ID :1,UserName:hongten,Password:123hanyuan@foxmail.com45648255GuangzhoushiDianbian65465
ID :2,UserName:hanyuan,Password:5645645hongtenzone@foxmail.com45464Guangzhoushi65465
ID :2,UserName:hanyuan,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465
ID :2,UserName:hanyuan,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465
ID :3,UserName:Hongwei1231,Password:5645645hongtenzone@foxmail.com45464Guangzhoushi65465
ID :3,UserName:Hongwei1231,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465
ID :3,UserName:Hongwei1231,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465
ID :4,UserName:mingliu,Password:5645645hongtenzone@foxmail.com45464Guangzhoushi65465
ID :4,UserName:mingliu,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465
ID :4,UserName:mingliu,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465
ID :5,UserName:shouzhang,Password:5645645hongtenzone@foxmail.com45464Guangzhoushi65465
ID :5,UserName:shouzhang,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465
ID :5,UserName:shouzhang,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465
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
users.profile profile0_
inner join
users.user user1_
on profile0_.user_id=user1_.id
ID:1 Username: hongten Email: hongtenzone@foxmail.com, Address: Guangzhoushi
ID:2 Username: hanyuan Email: hanyuan@foxmail.com, Address: GuangzhoushiDianbian
ID:3 Username:Hongwei1231 Email: hanyuan@foxmail.com, 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
users.profile this_
inner join
users.user user1_
on this_.user_id=user1_.id
ID:1 Username: hongten Email: hongtenzone@foxmail.com, Address: Guangzhoushi
ID:2 Username: hanyuan Email: hanyuan@foxmail.com, Address: GuangzhoushiDianbian
ID:3 Username: Hongwei1231 Email: hanyuan@foxmail.com, 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
users.profile this_
left outer join
users.user user2_
on this_.user_id=user2_.id
ID:1 Username: hongten Email: hongtenzone@foxmail.com, Address: Guangzhoushi
ID:2 Username: hanyuan Email: hanyuan@foxmail.com, Address: GuangzhoushiDianbian
ID:3 Username: 洪偉1231 Email: hanyuan@foxmail.com, 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
users.profile profile0_
left outer join
users.user user1_
on profile0_.user_id=user1_.id
order by
profile0_.user_id
ID:1 Username: hongten Email: hongtenzone@foxmail.com, Address: Guangzhoushi
ID:2 Username: hanyuan Email: hanyuan@foxmail.com, Address: GuangzhoushiDianbian
ID:3 Username: 洪偉1231 Email: hanyuan@foxmail.com, 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
users.user user0_
left outer join
users.profile profiles1_
on user0_.id=profiles1_.user_id
1hongten[com.b510.example.Profile@14eaec9]
2hanyuan[com.b510.example.Profile@569c60]
3Hongwei1231[com.b510.example.Profile@d67067]
4mingliu[]
5shouzhang[]
Hibernate:
select
user0_.id as id0_,
user0_.username as username0_,
user0_.password as password0_
from
users.user user0_
right outer join
users.profile 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
users.profile profiles0_
where
profiles0_.user_id=?
1hongten[com.b510.example.Profile@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
users.profile profiles0_
where
profiles0_.user_id=?
2hanyuan[com.b510.example.Profile@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
users.profile profiles0_
where
profiles0_.user_id=?
3Hongwei1231[com.b510.example.Profile@8997d1]
