Hibernate 的HQL,QBC 查询语言

1.HQL:(Hibernate Query Language) 是面向对象的查询语言 

1.实体查询 

Java代码
1.public void testQueryAllDept(){ 
2.String hql="from Employee"; 
3.//hql="from com.sh.Entity"; 
4.Query query=session.createQuery(hql); 
5.6.List<Employee> list= query.list(); 
7.8.//... 
9.} 


2.有条件的 实体查询 

Java代码
1.public void testQueryAllDept(){ 
2.String hql="from Deptas model where mode.deptName='人事部' "; 
3.//hql="from com.sh.Entity"; 
4.Query query=session.createQuery(hql); 
5.6.List<Employee> list= query.list(); 
7.8.//... 
9.} 
10.public void testQueryAllDept(){ 
11.String hql="from Deptas model where mode.deptName like '%部%' "; 
12.//hql="from com.sh.Entity"; 
13.Query query=session.createQuery(hql); 
14.15.List<Employee> list= query.list(); 
16.17.//... 
18.} 
19.20.public void testQueryDeptByGt(){ 
21.22.String hql="from Dept model where model.createDate> '2012-03-10'"; 
23.Query query=session.createQuery(hql); 
24.List<Employee> list= query.list(); 
25.} 



3.HQL 中的各种运算符 

运算符类型 HQL运算符 含义 
比较运算 = 等于 
 <> 不等于 
 > 大于 
 >= 大于等于 
 <= 小于等于 
 < 小于 
 is null 为空 
 is not null 不为空 
范围运算 in 等于列表中的某一值 
 not in 不等于列表中的任意一个值 
 between p1 and p2 大于等于值1 且 小于等于值2 
 not between p1 and p2 小于值1 或者 大于 值2 
字符串模式匹配 like 字符串模式匹配 
逻辑运算 and 与 
 or 或 
 not 非 



3.更新 删除 

Java代码
1.public void testModifyDate(){ 
2.Transaction tr=session.beginTransaction(); 
3.Dept detp=(Dept)session.get(Detp.class,new Integer(6)); 
4.detp.seteCreateDate("2000-03-10"); 
5.tr.commit(); 
6.7.} 
8.9.// delete 删除 
10.public void testDelDeptByHql(){ 
11.Transaction tr=session.beginTransaction(); 
12.String hql="delete Dept as model where model.deptId=6"; 
13.Query query=session.createQuery(hql); 
14.query.executeUpdate(); 
15.tr.commit(); 
16.} 


4.属性查询 

Java代码
1.public void testQueryDeptName(){ 
2.String hql="select model.deptName from Dept as model"; 
3.Query query=session.createQuery(hql); 
4.List<String> deptNameList=query.list(); 
5.//... 
6.} 
7.public void testQueryDeptName(){ 
8.String hql="select model.deptName,model.createDate from Dept as model"; 
9.Query query=session.createQuery(hql); 
10.List<Object[]> prsList=query.list(); 
11.for(){ 
12.} 
13.//... 
14.} 
15.16.//采用对象进行 保存 查询的字段(要添加 对应字段的构造方法) 
17.public void testQueryDeptName(){ 
18.String hql="select new Dapt(model.deptName,model.createDate) from Dept as model"; 
19.Query query=session.createQuery(hql); 
20.List<Dept> deptList=query.list(); 
21.for(Dept d:deptList){ 
22.23.d.setDeptName("test"); 
24.session.saveOrUpdate(dept); 
25.} 
26.//... 
27.} 
28.29.30.//如果采用对象 保存上面查询出来的对象 如果对其进行更新的操作 是不会成功的 
31.32.public void testSaveDept(){ 
33.Transction tr=session.beginTransactino(); 
34.String hql="select new Dept(model.deptName,model.createDate) from Dept as model"; 
35.Query query=session.createQuery(); 
36.List<Dept> deptList=query.list(); 
37.for(Dept dept:deptList){ 
38.System.out.println(dept.deptName); 
39.dept.setDetpName("test"); 
40.session.saveOrUpdate(dept); 
41.} 
42.tr.commit(); 
43.// 最后 根本就没有更新 而是 在数据库中 插入 新的记录 
44.} 


5.使用函数 

Java代码
1.public void testQuyeryFunction(){ 
2.String hql="select count(*),min(model.createDate) from Dept as model"; 
3.Query query=session.createQuery(hql); 
4.List<Object[]> paramsList=query.list(); 
5.for(){ 
6.7.} 
8.//... 
9.} 


6.分组 与 排序 

Java代码
1.//排序 
2.public void testQueryOrderBy(){ 
3.String hql="from Dept as mode order by model.createDate,model.deptName desc"; 
4.Query query=session.createQuery(); 
5.List<Dept> deptList=query.list(); 
6.for(){} 
7.// 
8.9.} 
10.11.//分组 并且 使用 having 筛选 
12.public void testQueryGroupBy(){ 
13.String hql="select count(model),model.deptName from Dept as model group by model.deptName having count(model)>1"; 
14.Query query=session.createQuery(); 
15.List<Object[]> list=query.list(); 
16.//... 
17.} 


7.查询单个对象 

Java代码
1.public void testQueryUn(){ 
2.String hql="select model.deptName from Dept as model where model.deptId=2"; 
3.Query query=session.createQuery(hql); 
4.query.setMaxResults(1); 
5.String deptName=(String)query.uniqueResult(); 
6.System.out.println(deptNamae); 
7.} 



8.绑定参数 
释义:Hibernate中也采用了preparedStatement作为底层数据库的的访问方式,与之对应的SQL语句 可以重用preparedStatement对象, 首先会预编译,然后缓存起来,提高性能. 

1.使用 ":"加参数名 绑定参数 

Java代码
1.public void testQueryByParamsName(){ 
2.//使用 ":参数名" 来传递参数 
3.String hql="from Dept as model where 
4.odel.deptName=:deptName and model.createDate=:createDate"; 
5.6.Query query=session.createQuery(hql); 
7.query.setString("deptName","test"); 
8.query.setString("ceateDate","2000-02-10"); 
9.List<Dept> deptList=query.list(); 
10.for(){} 
11.12.//... 
13.14.} 


2.使用 "?" 按照位置来绑定参数 

Java代码
1.public void testQueryByParamsID(){ 
2.String hql="from Dept as model where model.deptName=? and model.createDate=?"; 
3.Query query=session.createQuery(hql); 
4.query.setString(0,"test"); 
5.query.setString(1,"2000-02-02"); 
6.List<Dept> list=query.list(); 
7.for(){} 
8.//.. 
9.} 


3.采用setEntity() 将参数和一个持久化对象进行绑定 

Java代码
1.public void testQueryByDept(){ 
2.Dept dept=(Dept)Session.get(Dept.clas,new Integer(2)); 
3.String hql="select Employee as model where model.dept=?" 
4.Query query=session.createQuery(hql); 
5.query.setEntity(0,dept); 
6.List<Employee> empList=query.list(); 
7.for(){} 
8.//.. 
9.10.11.} 


4.setParameter() 用于绑定任意类型的参数 

Java代码
1.public void testQueryBySetParams(){ 
2.String hql="from Dept as model where model.deptName=?"; 
3.Query query=session.createQuery(hql); 
4.query.setParameter(0,"test"); 
5.List<Dept> deptList=query.list(); 
6.for(){} 
7.//.. 
8.} 


5.setProperties() 将一个参数名 于一个对象的属性值绑定 

Java代码
1.public void testQueryBySetPro(){ 
2.Dept deptNew=new Dept(); 
3.deptNew.setDeptName("test"); 
4.5.String hql="from Dept as model where model.deptName=:deptName"; 
6.Query query=session.createQuery(); 
7.query.setProperties(deptNew); 
8.List<Dept> deptList=query.list(); 
9.for(){ 
10.11.} 
12.//.. 
13.14.} 


10. 联合查询 
inner join 

Java代码
1.public void testQueryInnerJoin(){ 
2.String hql="from Dept as model inner join model1.employees as model2"; 
3.Query query=session.createQuery(hql); 
4.List<Object[]> objList=query.list(); 
5.for(Object[] obj:objList){ 
6.Dept dept=(Dept)obj[0]; 
7.Employee emp=(Employee)obj[1]; 
8.System.out.println("xxxx"); 
9.} 
10.} 


left join 

Java代码
1.public void testQueryLeftJoin(){ 
2.String hql="from Dept as model left join model1.employees as model2"; 
3.Query query=session.createQuery(hql); 
4.List<Object[]> objList=query.list(); 
5.for(Object[] obj:objList){ 
6.Dept dept=(Dept)obj[0]; 
7.Employee emp=(Employee)obj[1]; //有可能为空 所以需要判断 
8.if(mep!=null){ 
9.System.out.println("xxxx"); 
10.}else{ 
11.System.out.println(null); 
12.} 
13.System.out.println(dept.getDeptName()); 
14.} 
15.} 


right join 

Java代码
1.public void testQueryRightJoin(){ 
2.String hql="from Dept as model right join model1.employees as model2"; 
3.Query query=session.createQuery(hql); 
4.List<Object[]> objList=query.list(); 
5.for(Object[] obj:objList){ 
6.Dept dept=(Dept)obj[0]; 
7.Employee emp=(Employee)obj[1]; //有可能为空 所以需要判断 
8.if(dept!=null){ 
9.System.out.println(dept.getDeptName()); 
10.}else{ 
11.System.out.println(null); 
12.} 
13.System.out.println("dd"+emp.getEmployeeName()); 
14.} 
15.} 


11.子查询 

Java代码
1.public void testQueryChild(){ 
2.String hql="from Dept as model where (select count(*) from model.employee) >2"; 
3.Query query=session.createQuery(hql); 
4.List<Dept> objList=query.list(); 
5.for(){} 
6.//.. 
7.} 


12 Criteria 查询 
释义:通过面向对象的设计将数据查询条件封装在一个对象 

Java代码
1.//无条件 
2.public void testQueryDept(){ 
3.Criteria criteria=session.createCriteria(Dept.class); 
4.List<Dept> deptList=criteria.list(); 
5.for(){} 
6.//.. 
7.} 
8.9.//添加条件 
10.public void testQueryDeptByName(){ 
11.Criteria criteria=session.createCriteria(Dept.class); 
12.criteria.add(Expression.eq("deptName", "人事部")); 
13.List<Dept> deptList=criteria.list(); 
14.for(){} 
15.//.. 
16.} 


SQL语法查询机制 

方法 描述 
Expression.eq 对应sql中的field =value 
Expression.gt 对应sql中的field>value 
Expression.ge 对应sql中的field>=value 
Expression.lt 对应sql中的field<value 
Expression.le 对应sql中的field<=value 
Expression.between 对应sql中的between 
Expression.like 对应 like 
Expression.in 对应 in 
Expression.eqProperty 用于比较两个属性之间的值,对应的Sql条件中field=field 
Expression.gtProperty 用于比较两个属性之间的值,对Sqlfiled>field 
Expression.geProperty 用于比较两个属性之间的值,对应sql field>=field 
Expression.ltProperty 用于比较两个属性之间的值,对应field<field 
Expression.leProperty 用于比较 两个属性之间的值,对应field<=field 




Hibernate3中采用Restrictions类代替Expression 



Java代码
1.public void testQueryDeptByNameRe(){ 
2.Criteria criteria=session.createCriteria(Dept.class); 
3.criteria.add(Restrictions.eq("deptName", "人事部")); 
4.List<Dept> deptList=criteria.list(); 
5.for(){} 
6.//.. 
7.8.} 


Criteria中限定返回的行数 

Java代码
1.public void testQueryDept(){ 
2.Criteria criteria=session.createCriteria(Dept.class); 
3.criteria.setFirstResult(2); 
4.criteria.setMaxResults(2); 
5.List<Dept> deptList=criteria.list(); 
6.for(){} 
7.//.. 
8.} 


Criteria排序 

Java代码
1.public void testQueryDept(){ 
2.Criteria criteria=session.createCriteria(Dept.class); 
3.criteria.addOrder(Order.asc("createDate")); 
4.List<Dept> deptList=criteria.list(); 
5.for(){} 
6.//.. 
7.}