我要评论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: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]
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！
网友评论