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();
    }
}
@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();
        list<employee> employeelist = session.createquery("from employee as e").list();
        for(employee e : employeelist)
            system.out.println(e);
    }

    @test
    public void testhqlhasparameter() {
        session session = hibernateutil.getopensession();
        list<employee> 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();
        list<employee> 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();
        list<employee> 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();
        list<employee> 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<user> 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<string> listname = query.list();

  for (string name : listname) {
   system.out.println(name);
  }
  // in查询
  query = session
    .createquery("from user where username in ('hongten','hanyuan','dfgd')");
  list<user> 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<user> 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<user> 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<user> 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");
  list<user> 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<string> 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<string> list = query.list();

  for (string name : list) {
   system.out.println(name);
  }
  system.out.println("-------------------------------------------");
  // 输出的数据全部转化为小写
  query = session.createquery("select lower(username) from user");
  list<string> 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<profile> 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<object[]> 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<profile> 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<profile> 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<profile> 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<profile> 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<user> 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<user> 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_.id<5
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:[e-mail]45464guangzhoushi65465
id :1,username:hongten,password:[e-mail]45648255guangzhoushidianbian65465
id :1,username:hongten,password:[e-mail]45648255guangzhoushidianbian65465
id :2,username:hanyuan,password:[e-mail]45464guangzhoushi65465
id :2,username:hanyuan,password:[e-mail]45648255guangzhoushidianbian65465
id :2,username:hanyuan,password:[e-mail]45648255guangzhoushidianbian65465
id :3,username:hongwei1231,password:[e-mail]45464guangzhoushi65465
id :3,username:hongwei1231,password:[e-mail]45648255guangzhoushidianbian65465
id :3,username:hongwei1231,password:[e-mail]45648255guangzhoushidianbian65465
id :4,username:mingliu,password:[e-mail]45464guangzhoushi65465
id :4,username:mingliu,password:[e-mail]45648255guangzhoushidianbian65465
id :4,username:mingliu,password:[e-mail]45648255guangzhoushidianbian65465
id :5,username:shouzhang,password:[e-mail]45464guangzhoushi65465
id :5,username:shouzhang,password:[e-mail]45648255guangzhoushidianbian65465
id :5,username:shouzhang,password:[e-mail]45648255guangzhoushidianbian65465
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: [e-mail],   address: guangzhoushi
id:2   username: hanyuan   email: [e-mail],   address: guangzhoushidianbian
id:3   username:hongwei1231   email: [e-mail],   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: [e-mail],   address: guangzhoushi
id:2   username: hanyuan   email: [e-mail],   address: guangzhoushidianbian
id:3   username: hongwei1231   email: [e-mail],   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: [e-mail],   address: guangzhoushi
id:2   username: hanyuan   email: [e-mail],   address: guangzhoushidianbian
id:3   username: 洪伟1231   email: [e-mail],   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: [e-mail],   address: guangzhoushi
id:2   username: hanyuan   email: [e-mail],   address: guangzhoushidianbian
id:3   username: 洪伟1231   email: [e-mail],   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]