HQL(Hibernate Query Language):面向对象的查询语言。
1.实体查询
Java示例代码:
{
String hql="from Employee"; //hql="from cn.wangm.Entity";Query query=session.createQuery(hql);
List<Employee> list= query.list();
}
2.有条件的实体查询
Java示例代码1:
public void testQuery()
{
String hql="from Deptas model where mode.deptName='人事部' "; //hql="from cn.wangm.Entity";Query query=session.createQuery(hql);
List<Employee> list= query.list();
}
Java示例代码2:
public void testQuery()
{
String hql="from Deptas model where mode.deptName like '%部%' "; //hql="from cn.wangm.Entity";Query query=session.createQuery(hql);
List<Employee> list= query.list();
}
Java示例代码3:
public void testQuery()
{
String hql="from Dept model where model.createDate> '2015-12-19'";Query query=session.createQuery(hql);
List<Employee> list= query.list();
}
3.HQL 中的各种运算符
运算符类型 | HQL运算符 | 含义 |
比较运算 | = | 等于 |
<> | 不等于 | |
> | 大于 | |
< | 小于 | |
>= | 大于等于 | |
<= | 小于等于 | |
is null | 为空 | |
is not null | 不为空 | |
范围运算 | in | 等于列表中的某一个值 |
not in | 不等于列表中的任意一个值 | |
between 1 and 2 | 大于等于 1 并且小于等于2 | |
not between 1 and 2 | 小于 1 或者大于 2 | |
字符创模式匹配 | like | 字符串模式匹配 |
逻辑运算 | and | 与 |
or | 或 | |
not | 非 |
Java示例代码1::
public void testModifyDate()
{
Transaction tr=session.beginTransaction();Dept detp=(Dept)session.get(Detp.class,new Integer(6));
detp.seteCreateDate("2015-12-19");
tr.commit();
}
Java示例代码2:
public void testDelDeptByHql()
{
Transaction tr=session.beginTransaction();String hql="delete Dept as model where model.deptId=6";
Query query=session.createQuery(hql);
query.executeUpdate();
tr.commit();
}
5.属性查询
Java示例代码1:
public void testQueryDeptName()
{
String hql="select model.deptName from Dept as model";Query query=session.createQuery(hql);
List<String> deptNameList=query.list();
}
Java示例代码2:
public void testQueryDeptName()
{
String hql="select model.deptName,model.createDate from Dept as model";Query query=session.createQuery(hql);
List<Object[]> prsList=query.list();
for()
{
...
}
6.采用对象进 保存, 查询的字段(要添加对应字段的构造方法)
Java示例代码:
public void testQueryDeptName()
{
String hql="select new Dapt(model.deptName,model.createDate) from Dept as model";Query query=session.createQuery(hql);
List<Dept> deptList=query.list();
for(Dept d:deptList)
{
session.saveOrUpdate(dept);
}
}
7.如果采用对象 保存 6查询出来的对象,如果对其进行更新的操作,是不会成功的
public void testSaveDept()
{
Transction tr=session.beginTransactino();String hql="select new Dept(model.deptName,model.createDate) from Dept as model";
Query query=session.createQuery();
List<Dept> deptList=query.list();
for(Dept dept:deptList)
{
System.out.println(dept.deptName);dept.setDetpName("test");
session.saveOrUpdate(dept);
}
tr.commit(); // 最后 根本就没有更新 而是 在数据库中 插入 新的记录
}
8.使用函数 count()
Java示例代码:
public void testQuyeryFunction()
{
String hql="select count(*),min(model.createDate) from Dept as model";Query query=session.createQuery(hql);
List<Object[]> paramsList=query.list();
for()
{
...}
}
9.分组与排序
Java示例代码:
//排序
public void testQueryOrderBy()
{
Query query=session.createQuery();
List<Dept> deptList=query.list();
for(){...}
}
//分组 并且 使用 having 筛选
public void testQueryGroupBy()
{
String hql="select count(model),model.deptName from Dept as model group by model.deptName having count(model)>1";Query query=session.createQuery();
List<Object[]> list=query.list();
}
10.查询单个对象
Java示例代码:
public void testQueryUn()
{
String hql="select model.deptName from Dept as model where model.deptId=2";Query query=session.createQuery(hql);
query.setMaxResults(1);
String deptName=(String)query.uniqueResult();
System.out.println(deptNamae);
}
11.绑定参数
释义:Hibernate中也采用了preparedStatement作为底层数据库的的访问方式,与之对应的SQL语句 可以重用preparedStatement对象, 首先会预编译,然后缓存起来,提高性能.
(1).使用 ":"加参数名 绑定参数
Java示例代码:
public void testQueryByParamsName()
{
//使用 ":参数名" 来传递参数String hql="from Dept as model where odel.deptName=:deptName and model.createDate=:createDate";
Query query=session.createQuery(hql);
query.setString("deptName","test");
query.setString("ceateDate","2015-12-19");
List<Dept> deptList=query.list();
for(){...}
}
(2).使用 "?" 按照位置来绑定参数
Java示例代码:
public void testQueryByParamsID()
{
String hql="from Dept as model where model.deptName=? and model.createDate=?";Query query=session.createQuery(hql);
query.setString(0,"test");
query.setString(1,"2015-12-19");
List<Dept> list=query.list();
for(){}
}
(3).采用 setEntity() 将参数和 一个持久化对象进行绑定
Java示例代码:
public void testQueryByDept()
{
Dept dept=(Dept)Session.get(Dept.class,new Integer(2));String hql="select Employee as model where model.dept=?"
Query query=session.createQuery(hql);
query.setEntity(0,dept);
List<Employee> empList=query.list();
for(){}
}
(4). setParameter() 用于绑定任意类型的参数
Java示例代码:
public void testQueryBySetParams()
{
String hql="from Dept as model where model.deptName=?";Query query=session.createQuery(hql);
query.setParameter(0,"test");
List<Dept> deptList=query.list();
for(){}
}
(5). setProperties() 将一个参数名 于一个对象的属性值绑定
Java示例代码:
public void testQueryBySetPro()
{
Dept deptNew=new Dept();deptNew.setDeptName("test");
String hql="from Dept as model where model.deptName=:deptName";
Query query=session.createQuery();
query.setProperties(deptNew);
List<Dept> deptList=query.list();
for(){}
}
11. 联合查询
inner join
Java示例代码:
public void testQueryInnerJoin()
{
String hql="from Dept as model inner join model1.employees as model2";Query query=session.createQuery(hql);
List<Object[]> objList=query.list();
for(Object[] obj:objList)
{
Dept dept=(Dept)obj[0];Employee emp=(Employee)obj[1];
}
}
left join
Java示例代码:
public void testQueryLeftJoin()
{
String hql="from Dept as model left join model1.employees as model2";Query query=session.createQuery(hql);
List<Object[]> objList=query.list();
for(Object[] obj:objList)
{
Dept dept=(Dept)obj[0];Employee emp=(Employee)obj[1]; //有可能为空 所以需要判断
if(mep!=null)
{
System.out.println("xxxx");}
else
{
System.out.println(null);}
System.out.println(dept.getDeptName());
}
}
right join
Java示例代码:
public void testQueryRightJoin()
{
String hql="from Dept as model right join model1.employees as model2";Query query=session.createQuery(hql);
List<Object[]> objList=query.list();
for(Object[] obj:objList)
{
Dept dept=(Dept)obj[0];Employee emp=(Employee)obj[1]; //有可能为空 所以需要判断
if(dept!=null)
{
System.out.println(dept.getDeptName());}
else
{
System.out.println(null);}
System.out.println("dd"+emp.getEmployeeName());
}
}
12.子查询
Java示例代码:
public void testQueryChild()
{
String hql="from Dept as model where (select count(*) from model.employee) >2";Query query=session.createQuery(hql);
List<Dept> objList=query.list();
for(){}
}
13.Criteria 查询
释义:通过面向对象的设计将数据查询条件封装在一个对象
Java示例代码:
//无条件
public void testQueryDept()
{
Criteria criteria=session.createCriteria(Dept.class);List<Dept> deptList=criteria.list();
for(){}
}
//添加条件
public void testQueryDeptByName()
{
Criteria criteria=session.createCriteria(Dept.class);criteria.add(Expression.eq("deptName", "人事部"));
List<Dept> deptList=criteria.list();
for(){}
}
注:SQL语法查询机制,Hibernate3中采用Restrictions类代替Expression。
方法 | 描述 |
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 | 用于比较两个属性之间的值,对Sql条件中filed>field |
Expression.geProperty | 用于比较两个属性之间的值,对应sql条件中 field>=field |
Expression.ltProperty | 用于比较两个属性之间的值,对应field<field |
Expression.leProperty | 用于比较 两个属性之间的值,对应field<=field |
public void testQueryDeptByNameRe()
{
Criteria criteria=session.createCriteria(Dept.class);criteria.add(Restrictions.eq("deptName", "人事部"));
List<Dept> deptList=criteria.list();
for(){}
}
14.Criteria中限定返回的行数
Java示例代码:
public void testQueryDept()
{
Criteria criteria=session.createCriteria(Dept.class);criteria.setFirstResult(2);
criteria.setMaxResults(2);
List<Dept> deptList=criteria.list();
for(){}
}
15.Criteria排序
Java示例代码:
public void testQueryDept()
{
Criteria criteria=session.createCriteria(Dept.class);criteria.addOrder(Order.asc("createDate"));
List<Dept> deptList=criteria.list();
for(){}
}