从hibernate 3版本开始才支持
//更新
String hql=“update Users set uname=‘王五’,upwd=‘321’ where uid=3”;
int res = session.createQuery(hql).executeUpdate();
//删除
String hql=“delete from Users where uid=3”;
int res = session.createQuery(hql).executeUpdate();
HQL语句不能做添加
1.5、分组与排序
- 排序
处理方式和SQL语句中的排序是一样的
String hql=“from Users order by uid”;
Query query = session.createQuery(hql);
List list = query.list();
for (Users user : list) {
System.out.println(user);
}
order by语句只能出现在语句的最后位置
- 分组
处理方式和SQL语句中分组查询相同
分组语句需要处理筛选,只能使用having语句,而不能使用where语句。
String hql = “select e.dept.deptno, count(e) from Emp e group by dept.deptno”;
String hql = “select e.dept.deptno, count(e) from Emp e group by dept.deptno having count(e)>=5”;
1.6、参数绑定
- 使用占位参数
String hql=“from Users where uid=? or uname=?”;
Query query = session.createQuery(hql);
//索引从0开始
query.setInteger(0, 3);//query.setParameter(0, 3);
query.setString(1, “张三”);//query.setParameter(1, “张三”);
List list = query.list();
for (Users user : list) {
System.out.println(user);
}
- 使用参数名称
String hql = “from Users where uid=:no1 or uid=:no2”;
Query query = session.createQuery(hql);
query.setInteger(“no1”, 1);
query.setInteger(“no2”, 3);
//…
- 可以使用点位参数和名称参数混合使用
String hql = “from User where uid=? or uid=:no2”;
Query query = session.createQuery(hql);
query.setInteger(0, 7788);
query.setInteger(“no2”, 7566);
//…
//使用点位参数和名称参数混合使用,所有点位参数必须放在前面,一旦有名称参数出现,其后将不能再出现占位参数
1.7、连接查询
–SQL语句:查询员工姓名、薪资、部门名称
SELECt ENAME,SAL,DNAME
FROM EMP e JOIN DEPT d ON e.DEPTNO=d.DEPTNO
SELECt ENAME,SAL,DNAME FROM EMP,DEPT WHERe EMP.DEPTNO=DEPT.DEPTNO
-
没有on语句
-
能被连接到查询中的必须是主查询对象的子属性
String hql=“SELECt e.ename, e.sal, e.dept.dname FROM Emp e”;
//HQL连接查询
String hql=“SELECt e.ename, e.sal, d.dname FROM Emp e JOIN e.dept d”;
String hql = “SELECt e FROM Emp e JOIN e.dept”; //JOIN将没有意义
String hql = “FROM Emp e JOIN e.dept”;
Query query = session.createQuery(hql);
List
//List集合中的数组中会保存两个元素:
//0:主数据(Emp)
//1:从数据(Dept)
//查询编号为7788的员工信息,同时将对应的dept信息和manager信息查询并保存在对应的子属性中
String hql = “FROM Emp e JOIN FETCH e.dept d JOIN FETCH e.manager m WHERe e.empno=7788”;
Query query = session.createQuery(hql);
Emp emp = (Emp) query.uniqueResult();
System.out.println(emp);
System.out.println(emp.getManager());
System.out.println(emp.getDept());
1.8、分页
String hql = “from Users”;
Query query = session.createQuery(hql);
query.setFirstResult(0);
query.setMaxResults(2);
2、QBC查询QBC(Query By Criteria)查询就是通过使用Hibernate提供的Query By Criteria API来查询对象,这种API封装了SQL语句的动态拼装,对查询提供了更加面向对象的功能接口。
QBC查询最重要的三个类:
-
Restrictions 条件限制
-
Projections 列设射
-
Order 排序
2.1、查询实现
Criteria criteria = session.createCriteria(Users.class);
//session.createCriteria(“entity.Users”);
//session.createCriteria(Users.class, “别名”);
List list = criteria.list();
//查询单行结果(如果结果有两行或更多,会报错)
Object uniqueResult = criteria.uniqueResult();
2.2、Projections 列投射
语法:
//查询uname属性
Criteria criteria = session.createCriteria(Users.class);
PropertyProjection property = Projections.property(“name”);
criteria.setProjection(property);
List result = criteria.list();
//查询uname, upwd属性
Criteria criteria = session.createCriteria(Users.class);
//1.创建投射列表
ProjectionList projectionList = Projections.projectionList();
//2.向投射列表中添加列投射
PropertyProjection property1 = Projections.property(“uname”);
PropertyProjection property2 = Projections.property(“upwd”);
projectionList.add(property1).add(property2);
//3.将投射列表设置到准则中
criteria.setProjection(projectionList);
List result = criteria.list();
| 返回值类型 | 方法名称 | 描述 |
| — |
【一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义】 浏览器打开:qq.cn.hn/FTf 免费领取
— | — |
| PropertyProjection | Projections.property | 指定某属性 |
| AggregateProjection | Projections.avg | 求平均值 |
| CountProjection | Projections.count | 统计某属性的数量 |
| CountProjection | Projections.countDistinct | 统计某属性不同值的数量 |
| PropertyProjection | Projections.groupProperty | 指定某个属性为分组属性 |
| AggregateProjection | Projections.max | 求最大值 |
| AggregateProjection | Projections.min | 求最小值 |
| ProjectionList | Projections.projectionList | 创建一个ProjectionList对象 |
| Projection | Projections.rowCount | 查询结果集中的记录条数 |
| AggregateProjection | Projections.sum | 求某属性的合计 |
2.3、Restrictions 条件限制
语法:https://www.jianshu.com/p/3d3d67663157
Criteria criteria = session.createCriteria(Users.class);
Criterion notNull = Restrictions.isNotNull(“comm”);
criteria.add(notNull); //添加一个条件(如果添加了多个条件,默认条件之间使用and连接)
List list = criteria.list();
| 返回值类型 | 方法名称 | 描述 |
| — | — | — |
| Simpleexpression | Restrictions.eq | 等于(equal) |
| Criterion | Restrictions.allEq | 使用Map,Key/Valu进行多个等于的比对 |
| Simpleexpression | Restrictions.gt | 大于(great than) |
| Simpleexpression | Restrictions.ge | 大于等于(great than or equal) |
| Simpleexpression | Restrictions.lt | 小于(less than) |
| Simpleexpression | Restrictions.le | 小于等于(less than or equal) |
| Criterion | Restrictions.between | 对应SQL的between |
| Simpleexpression | Restrictions.like | 对应SQL的like |
| Criterion | Restrictions.in | 对应SQL的in |
| Logicalexpression | Restrictions.and | and关系 |
| Logicalexpression | Restrictions.or | or关系 |
| Criterion | Restrictions.isNull | 为空 |
| Criterion | Restrictions.sqlRestriction | SQL限定查询 |
| Criterion | Restrictions.not | 取反 |
2.3、Order排序
语法:
Criteria criteria = session.createCriteria(Dept.class);
criteria.addOrder(Order.asc(“name”))
.addOrder(Order.desc(“loc”));
//SELECt * FROM DEPT ORDER BY name ASC, loc DESC
| 返回值类型 | 方法名称 | 描述 |
| — | — | — |
| Order | Order.asc(String propertyName) | 升序 |
| Order | Order.desc(String propertyName) | 降序 |
2.4、分页查询
Criteria criteria = session.createCriteria(Dept.class);
int pageNum = 2, pageSize = 5;
criteria.setFirstResult((pageNum-1)*pageSize); //查询起始行下标
criteria.setMaxResults(pageSize); //查询的最大行数
List list = criteria.list();