1.Hibernate查询方式
Hibernate提供了以下几种检索对象的方式：
HQL检索方式
HQL（Hibernate Q是面向对象的查询语言，它具有以下功能：
在查询语句中设定各种查询条件；
支持投影查询，仅查询出对象的部分属性；
支持分页查询；
支持连接查询；
支持分组查询，允许使用having和group by关键字；
提供内置聚合函数，如sun()、min()和max()；
能够调用用户定义的SQL函数；
支持子查询，即嵌入式查询；
支持动态绑定参数。
Session类的find()方法及Query接口都支持HQL检索方式，前者只是执行一些简单HQL查询语句，它不具有动态绑定参数的功能，而在新Hibernate版本中，有可能淘汰find()方法；而Query接口才是真正的HQL查询接口。
|
1
2
3
4
|
Query query = session.createQuery(
"from User as u where u.name = :userName and u.age = :userAge"
);
query.setString(
"userName"
,
"Tom"
);
query.setInteger(
"userAge"
,
21
);
List result = query.list();
|
QBC检索方式
采用HQL检索方式时，在应用程序汇总需要定义基于字符串形式的HQL查询语句。QBC (Query By Criteria) API提供检索对象的另一种方式，它主要由Criteria接口、Criterion接口和Expression类组成，它支持在运行时动态生成查询语句。
|
1
2
3
4
5
6
|
Criteria criteria = session.createCriteria(User.
class
);
Criterion criterion1 = Expression.like(
"name"
,
"T%"
);
Criterion criterion2 = Expression.eq(
"age"
,
new
Integer(
21
));
criteria = criteria.add(criterion1);
criteria = criteria.add(criterion2);
List result = criteria.list();
|
SQL检索方式
采用HQL或QBC检索方式时，Hibernate会生成标准的SQL查询语句，适用于所有的数据库平台，因袭这两种方式都是跨平台的。
有的应用程序可能需要根据底层数据库的SQL方言，来生成一些特殊的查询语句。在这种情况下，Hibernate提供SQL检索方式。
|
1
2
3
4
|
Query query = session.createSQLQuery(
"select {u.*} from User u where u.NAME like : userName and u.AGE = :userAge"
);
query.setString(
"userName"
,
"Tom"
);
query.setInteger(
"userAge"
,
21
);
List result = query.list();
|
使用别名
|
1
|
from User as u where u.name = :username
|
as关键字用于设定别名，也可以将as关键字省略：
|
1
|
from User u where u.name = :username
|
对查询结果排序
|
1
|
Query query = session.createQuery(
"from User as u order by u.name"
);
|
分页查询
|
1
2
3
4
|
Query query = session.createQuery(
"from User as u order by u.name asc"
);
query.setFirstResult(
0
);
query.setMaxResults(
10
);
List result = query.list();
|
查询单个对象
|
1
|
User user = (User)session.createQuery(
"from User as u order by u.name asc"
).setMaxResults(
1
).uniqueResult();
|
在HQL查询语句中绑定参数
|
1
|
Query query = session.createQuery(
"from User as u where u.name = '"
+ name +
"' and u.age = "
+ age);
|
按参数名绑定
|
1
|
Query query = session.createQuery(
"from User as u where u.name = :userName and u.age = :userAge"
);
|
调用Query的setXXX()方法来绑定参数：
|
1
2
|
query.setString(
"userName"
, name);
query.setInteger(
"userAge"
, age);
|
按参数位置绑定
|
1
|
Query query = session.createQuery(
"from User as u where u.name = ? and u.age = ?"
);
|
调用Query的setXXX()方法来绑定参数：
|
1
2
|
query.setString(
0
, name);
query.setInteger(
1
, age);
|
2.设定查询条件
比较运算
|
1
2
3
4
5
6
|
session.createQuery(
"from User as u where u.age > 18"
);
session.createQuery(
"from User as u where u.age <> 18"
);
session.createQuery(
"from User as u where u.name is null"
);
session.createQuery(
"from User as u where lower(u.name) = 'tom'"
);
session.createQuery(
"from User as u where upper(u.name) = 'TOM'"
);
session.createQuery(
"from User as u where u.age*4-100>10"
);
|
范围运算
|
1
2
3
|
session.createQuery(
"from User as u where u.name in('Tom','Jack')"
);
session.createQuery(
"from User as u where u.age between 18 and 25"
);
session.createQuery(
"from User as u where u.age not between 18 and 25"
);
|
字符串模式匹配
|
1
2
3
|
session.createQuery(
"from User as u where u.name like 'T%'"
);
session.createQuery(
"from User as u where u.name like '%om%'"
);
session.createQuery(
"from User as u where u.name like 'T__'"
);
|
逻辑运算
|
1
2
|
session.createQuery(
"from User as u where u.name like 'T%' and u.name like '%m%'"
);
session.createQuery(
"from User as u where (u.name like 'T%' or u.name like '%m%')"
);
|
3.连接查询
检索策略
|
1
2
3
4
|
List result = session.createQuery(
"from User as u where u.name like 'T%'"
).list();
for
(Iterator it = result.iterator(); it.hasNext();) {
User user = (User)it.next();
}
|
左外连接
|
1
2
3
4
5
6
|
List result = session.createQuery(
"from User as u left join u.roles where u.name like 'T%'"
).list();
for
(Iterator pairs = result.iterator(); pairs.hasNext();) {
Object[] pair = (Object[])pairs.next();
User user = (User)pair[
0
];
Role role = (Role)pair[
1
];
}
|
内连接
|
1
2
3
4
5
6
|
List result = session.createQuery(
"from User as u inner join u.roles where u.name like 'T%'"
).list();
for
(Iterator pairs = result.iterator(); pairs.hasNext();) {
Object[] pair = (Object[])pairs.next();
User user = (User)pair[
0
];
Role role = (Role)pair[
1
];
}
|
右外连接
|
1
2
3
4
5
6
|
List result = session.createQuery(
"from User as u right outer join u.roles where u.name like 'T%'"
).list();
for
(Iterator pairs = result.iterator(); pairs.hasNext();) {
Object[] pair = (Object[])pairs.next();
User user = (User)pair[
0
];
Role role = (Role)pair[
1
];
}
|
4.报表查询
投影查询
投影查询是查询结果仅包含部分实体或实体的部分属性。投影是通过select关键字来实现的。
|
1
|
List result = session.createQuery(
"select u.id,u.name,r.rolename from User u join u.roles r where u.name like 'T%'"
).list();
|
使用聚集函数
|
1
2
3
4
|
Integer count = (Integer)session.createQuery(
"select count(*) from User u"
).uniqueResult();
Float age = (Float)session.createQuery(
"select avg(u.age) from User u"
).uniqueResult();
Object[] os = (Object[])session.createQuery(
"select max(u.age),min(u.age) from User u"
).uniqueResult();
Integer count = (Integer)session.createQuery(
"select count(sistinct u.name) from User u"
).uniqueResult();
|
分组查询
|
1
2
|
List result = session.createQuery(
"select u.name,count(u) from User u group by u.name"
).list();
List result = session.createQuery(
"select u.name,count(u) from User u group by u.name having (count(u)>1"
).list();
|
5.高级查询技巧
动态查询
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
public
List findUsers(String name,
int
age) {
StringBuffer hqlStr =
new
StringBuffer(
"from User u"
);
if
(name !=
null
)
hqlStr.append(
" where lower(c.name) like :name"
);
if
(age !=
0
&& name !=
null
)
hqlStr.append(
" and u.age = :age"
);
if
(age !=
0
&& name !=
null
)
hqlStr.append(
" where u.age = :age"
);
Query query = session.createQuery(hqlStr.toString());
if
(name !=
null
)
query.setString(
"name"
, name.toLowerCase());
if
(age !=
0
)
query.setInteger(
"age"
, age);
return
query.list();
}
|
子查询
|
1
|
List result = session.createQuery(
"from User u where 1<(select count(o) from u.roles r)"
).list();
|
说明：笔记内容摘自《精通Hibernate：Java对象持久化技术详解》
本文转自 LinkedKeeper 51CTO博客，原文链接：http://blog.51cto.com/sauron/1238614，如需转载请自行联系原作者
