HQL查询语言的使用介绍

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();}}@Entitypublic class Employee {

private Integer id;private String name;private Integer age;@Id@GeneratedValuepublic Integer getId() {return id;}public void setId(Integer id) {this.id = id;}@Basicpublic String getName() {return name;}public void setName(String name) {this.name = name;}@Basicpublic 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 {

@Testpublic void testHQL() {Session session = HibernateUtil.getOpenSession();List employeeList = session.createQuery("from Employee as e").list();for(Employee e : employeeList)System.out.println(e);}@Testpublic void testHQLHasParameter() {Session session = HibernateUtil.getOpenSession();List 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 eselect可以选择任意属性，即不仅可以选择持久化类的直接属性，还可以选择组件属性包含的属性，如：

select e.name.firstName from Employee as eHQL查询的聚集函数：

聚集函数：

avg：计算属性的平均值

count：统计选择对象的数量

max：统计属性值的最大值

min：统计属性值的最小值

sum：计算属性值的总和

如：

复制代码代码如下:select count(*) from Employee as e @Testpublic 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%" @Testpublic void testHQLWhere() {Session session = HibernateUtil.getOpenSession();List 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) @Testpublic void testHQLChildQuery() {Session session = HibernateUtil.getOpenSession();List 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映射文件的元素中使用子元素来定义命名查询，这个元素只需指定一个name属性，指定该命名查询的名字，如：

[code]from Employee as e

Session里提供了一个getNamedQuery(String name)方法，该方法用于创建一个Query对象，一旦获得Query对象，剩下的工作就跟前面的一样了。

复制代码代码如下:@Testpublic void testHQLNamedQuery() {Session session = HibernateUtil.getOpenSession();List 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");List 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");List listname = query.list();

for (String name : listname) {System.out.println(name);}// in查询query = session.createQuery("from User where username in ('Hongten','Hanyuan','dfgd')");List 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%'");List listlike = query.list();

for (User user : listlike) {System.out.println(user.getId() + user.getUsername());}// null查询query = session.createQuery("from User where password is null");List 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 id<5");List listand = query.list();

for (User user : listand) {System.out.println(user.getId() + user.getUsername()+ user.getPassword());}// order byquery = session.createQuery("from User order by username,id desc");List 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");List 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");List list = query.list();

for (String name : list) {System.out.println(name);}System.out.println("-------------------------------------------");// 输出的数据全部转化为小写query = session.createQuery("select lower(username) from User");List 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");List 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");List 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);List 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");List 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);List 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);List 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);List 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);List 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:selectuser0_.id as id0_,user0_.username as username0_,user0_.password as password0_fromusers.user user0_whereuser0_.id not between 200 and 20001hongten2hanyuan3hongwei4mingliu5shouzhangHibernate:selectuser0_.username as col_0_0_fromusers.user user0_whereuser0_.id=2hanyuanHibernate:selectuser0_.id as id0_,user0_.username as username0_,user0_.password as password0_fromusers.user user0_whereuser0_.username in ('Hongten' , 'Hanyuan' , 'dfgd')1hongten2hanyuanHibernate:selectuser0_.id as id0_,user0_.username as username0_,user0_.password as password0_fromusers.user user0_whereuser0_.username not like 'Hon%'2hanyuan4mingliu5shouzhangHibernate:selectuser0_.id as id0_,user0_.username as username0_,user0_.password as password0_fromusers.user user0_whereuser0_.password is nullHibernate:selectuser0_.id as id0_,user0_.username as username0_,user0_.password as password0_fromusers.user user0_where(user0_.password is not null)and user0_.id<51hongten1232hanyuan56456453hongwei56456454mingliu5645645Hibernate:selectuser0_.id as id0_,user0_.username as username0_,user0_.password as password0_fromusers.user user0_order byuser0_.username,user0_.id desc2hanyuan1hongten3hongwei4mingliu5shouzhangHibernate:selectuser0_.username as col_0_0_fromusers.user user0_whereuser0_.username=?hongtenHibernate:selectuser0_.username as col_0_0_fromusers.user user0_hongtenhanyuanhongweimingliushouzhang-------------------------------------------Hibernate:selectlower(user0_.username) as col_0_0_fromusers.user user0_hongtenhanyuanhongweimingliushouzhangHibernate:updateusers.usersetusername='Hongwei1231'whereid=?1Hibernate:selectuser0_.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_fromusers.user user0_,users.profile profile1_ID :1,UserName:hongten,Password:123hongtenzone@foxmail.com45464Guangzhoushi65465ID :1,UserName:hongten,Password:123hanyuan@foxmail.com45648255GuangzhoushiDianbian65465ID :1,UserName:hongten,Password:123hanyuan@foxmail.com45648255GuangzhoushiDianbian65465ID :2,UserName:hanyuan,Password:5645645hongtenzone@foxmail.com45464Guangzhoushi65465ID :2,UserName:hanyuan,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465ID :2,UserName:hanyuan,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465ID :3,UserName:Hongwei1231,Password:5645645hongtenzone@foxmail.com45464Guangzhoushi65465ID :3,UserName:Hongwei1231,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465ID :3,UserName:Hongwei1231,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465ID :4,UserName:mingliu,Password:5645645hongtenzone@foxmail.com45464Guangzhoushi65465ID :4,UserName:mingliu,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465ID :4,UserName:mingliu,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465ID :5,UserName:shouzhang,Password:5645645hongtenzone@foxmail.com45464Guangzhoushi65465ID :5,UserName:shouzhang,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465ID :5,UserName:shouzhang,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465Hibernate:selectprofile0_.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_fromusers.profile profile0_inner joinusers.user user1_on profile0_.user_id=user1_.idID:1 Username: hongten Email: hongtenzone@foxmail.com, Address: GuangzhoushiID:2 Username: hanyuan Email: hanyuan@foxmail.com, Address: GuangzhoushiDianbianID:3 Username:Hongwei1231 Email: hanyuan@foxmail.com, Address: GuangzhoushiDianbianHibernate:selectthis_.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_fromusers.profile this_inner joinusers.user user1_on this_.user_id=user1_.idID:1 Username: hongten Email: hongtenzone@foxmail.com, Address: GuangzhoushiID:2 Username: hanyuan Email: hanyuan@foxmail.com, Address: GuangzhoushiDianbianID:3 Username: Hongwei1231 Email: hanyuan@foxmail.com, Address: GuangzhoushiDianbian##################################################Hibernate:selectthis_.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_fromusers.profile this_left outer joinusers.user user2_on this_.user_id=user2_.idID:1 Username: hongten Email: hongtenzone@foxmail.com, Address: GuangzhoushiID:2 Username: hanyuan Email: hanyuan@foxmail.com, Address: GuangzhoushiDianbianID:3 Username: 洪伟1231 Email: hanyuan@foxmail.com, Address: GuangzhoushiDianbianHibernate:selectprofile0_.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_fromusers.profile profile0_left outer joinusers.user user1_on profile0_.user_id=user1_.idorder byprofile0_.user_idID:1 Username: hongten Email: hongtenzone@foxmail.com, Address: GuangzhoushiID:2 Username: hanyuan Email: hanyuan@foxmail.com, Address: GuangzhoushiDianbianID:3 Username: 洪伟1231 Email: hanyuan@foxmail.com, Address: GuangzhoushiDianbian-------------------------------------Hibernate:selectuser0_.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__fromusers.user user0_left outer joinusers.profile profiles1_on user0_.id=profiles1_.user_id1hongten[com.b510.example.Profile@14eaec9]2hanyuan[com.b510.example.Profile@569c60]3Hongwei1231[com.b510.example.Profile@d67067]4mingliu[]5shouzhang[]Hibernate:selectuser0_.id as id0_,user0_.username as username0_,user0_.password as password0_fromusers.user user0_right outer joinusers.profile profiles1_on user0_.id=profiles1_.user_idorder byuser0_.idHibernate:selectprofiles0_.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_fromusers.profile profiles0_whereprofiles0_.user_id=?1hongten[com.b510.example.Profile@10c0f66]Hibernate:selectprofiles0_.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_fromusers.profile profiles0_whereprofiles0_.user_id=?2hanyuan[com.b510.example.Profile@e265d0]Hibernate:selectprofiles0_.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_fromusers.profile profiles0_whereprofiles0_.user_id=?3Hongwei1231[com.b510.example.Profile@8997d1]