1.HQL:(Hibernate Query Language) 是面向对象的查询语言
1.实体查询
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.有条件的 实体查询
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.更新 删除
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.属性查询
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.使用函数
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.分组 与 排序
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.查询单个对象
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.使用 ":"加参数名 绑定参数
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.使用 "?" 按照位置来绑定参数
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() 将参数和一个持久化对象进行绑定
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() 用于绑定任意类型的参数
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() 将一个参数名 于一个对象的属性值绑定
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
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
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
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.子查询
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 查询
释义:通过面向对象的设计将数据查询条件封装在一个对象
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
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中限定返回的行数
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排序
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. }
此文章由 http://www.ositren.com 收集整理 ,地址为:
http://www.ositren.com/htmls/68544.html