Hibernate_查询_HQL详解（三）_命名的查询、更新与删除的HQL语法
来源：互联网 发布：小程序个人中心源码 编辑：程序博客网 时间：2024/05/29 07:37
1，聚集函数2，分组3，连接查询 / HQL是面向对象的查询数据库中查询：select e.id_, e.name_, d.name_ from employee e inner join department d on e.departmentId=d.id_;(内连接)select e.id_, e.name_, d.name_ from employee e left outer join department d on e.departmentId=d.id_;(左外连接)select e.id_, e.name_, d.name_ from employee e right outer join department d on e.departmentId=d.id_;(右外连接)4，查询时使用参数>> 方式一：使用'?'占位>> 方式二：使用变量名// 使用HQL查询@Testpublic void testHql2() throws Exception {Session session = sessionFactory.openSession();Transaction tx = null;try {tx = session.beginTransaction();// ------------------------------------String hql = null;//// 1，聚集函数：count(),max(),min(),avg(),sum()// 总数、最大、最小、平均、求和// hql = "select count(e) from Employee e";// 返回的结果是Long型的// hql = "select min(e.id) from Employee e";// 返回的结果是id的属性的类型// Long result = (Long) session.createQuery(hql).uniqueResult();// Number result = (Number) session.createQuery(hql).uniqueResult();// System.out.println("总记录数：" + result.intValue());// 2，分组：Group By ... Having// hql = "select e.name,count(*) from Employee e group by e.name";// hql = "select e.name,count(*) from Employee e group by e.name Having count(e.id)>1";// hql = "select e.name,count(*) from Employee e where e.id<9 group by e.name Having count(e.id)>1";// hql = "select e.name,count(*) " + //// ---// "from Employee e " + //// "where e.id<9 " + //// "group by e.name " + //// "Having count(e.id)>1 " + //// "order by count(e.id) asc";// ---// hql = "select e.name,count(*) as c " + //// "from Employee e " + //// "where e.id<9 " + //// "group by e.name " + //// "Having count(e.id)>1 " + // 在Having子句中不能使用列别名// "Order by c asc";// 在Order by中可以使用列别名// 3，连接查询 / HQL是面向对象的查询// >> 内连接(inner关键字可以省略)// hql = "select e.id,e.name,d.name from Employee e join e.department d";// hql = "select e.id,e.name,d.name from Employee e inner join e.department d";// >> 左外连接(outer关键可以省略)// hql = "select e.id,e.name,d.name from Employee e left outer join e.department d";// >> 右外连接(outer关键可以省略)// hql = "select e.id,e.name,d.name from Employee e right outer join e.department d";// 可以使过更方便的方法// hql = "select e.id,e.name,e.department.name from Employee e";// 4，查询时使用参数// >> 方式一：使用'?'占位// hql = "from Employee e where e.id between ? And ?";// List1，聚集函数2，分组3，连接查询 / HQL是面向对象的查询数据库中查询：select e.id_, e.name_, d.name_ from employee e inner join department d on e.departmentId=d.id_;(内连接)select e.id_, e.name_, d.name_ from employee e left outer join department d on e.departmentId=d.id_;(左外连接)select e.id_, e.name_, d.name_ from employee e right outer join department d on e.departmentId=d.id_;(右外连接)4，查询时使用参数>> 方式一：使用'?'占位>> 方式二：使用变量名5，使用命名查询6，update与delete，不会通知Session缓存在update或delete后，需要refresh(obj)一下以获取最新的状态隔离级别中文隔离级别英文对应的整数表示读未提交READ UNCOMMITED1读已提交READ COMMITED2可重复读REPEATABLE READ4串行化(不可并发)SERIALIZEABLE8
<?xml version="1.0"?><!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"><!-- 导入包 auto-import属性：默认为true使用非全限定名,如果为false在使用HQL查询时得写上全限定名。 表示在HQL中写类的简单名称时,是否导入当前这个包。 --><hibernate-mapping package="cn.itcast.k_query_hql"auto-import="true"><!-- 类名 --><class name="Employee" table="employee"><id name="id" type="integer" column="id_"><generator class="native" /></id><property name="name" type="string" column="name_" /><!-- department属性,表达的是本类与Department的多对一的关系 --><many-to-one name="department" class="Department" column="departmentId"></many-to-one></class><!-- 定义命明的查询 --><query name="queryByIdRange"><![CDATA[from Employee e where e.id>=:idMin and e.id<=:idMax]]></query></hibernate-mapping>
// 使用HQL查询@Testpublic void testHql2() throws Exception {Session session = sessionFactory.openSession();Transaction tx = null;try {tx = session.beginTransaction();// ------------------------------------String hql = null;//// 1，聚集函数：count(),max(),min(),avg(),sum()// 总数、最大、最小、平均、求和// hql = "select count(e) from Employee e";// 返回的结果是Long型的// hql = "select min(e.id) from Employee e";// 返回的结果是id的属性的类型// Long result = (Long) session.createQuery(hql).uniqueResult();// Number result = (Number) session.createQuery(hql).uniqueResult();// System.out.println("总记录数：" + result.intValue());// 2，分组：Group By ... Having// hql = "select e.name,count(*) from Employee e group by e.name";// hql = "select e.name,count(*) from Employee e group by e.name Having count(e.id)>1";// hql = "select e.name,count(*) from Employee e where e.id<9 group by e.name Having count(e.id)>1";// hql = "select e.name,count(*) " + //// ---// "from Employee e " + //// "where e.id<9 " + //// "group by e.name " + //// "Having count(e.id)>1 " + //// "order by count(e.id) asc";// ---// hql = "select e.name,count(*) as c " + //// "from Employee e " + //// "where e.id<9 " + //// "group by e.name " + //// "Having count(e.id)>1 " + // 在Having子句中不能使用列别名// "Order by c asc";// 在Order by中可以使用列别名// 3，连接查询 / HQL是面向对象的查询// >> 内连接(inner关键字可以省略)// hql = "select e.id,e.name,d.name from Employee e join e.department d";// hql = "select e.id,e.name,d.name from Employee e inner join e.department d";// >> 左外连接(outer关键可以省略)// hql = "select e.id,e.name,d.name from Employee e left outer join e.department d";// >> 右外连接(outer关键可以省略)// hql = "select e.id,e.name,d.name from Employee e right outer join e.department d";// 可以使过更方便的方法// hql = "select e.id,e.name,e.department.name from Employee e";// 4，查询时使用参数// >> 方式一：使用'?'占位// hql = "from Employee e where e.id between ? And ?";// List<Object> list = session.createQuery(hql)//// .setParameter(0, 5)// 设置参数,第1个参数的索引为0。// .setParameter(1, 15)//// .list();// >> 方式二：使用变量名// hql = "from Employee e where e.id between :idMin And :idMax";// List<Object> list = session.createQuery(hql)//// .setParameter("idMin", 5)//// .setParameter("idMax", 15)//// .list();// >> 方式三：当参数是集合时,一定要使用ParameterList()设置参数值// hql = "from Employee e where id in (:ids)";// List<Object> list = session.createQuery(hql)//// .setParameterList("ids", new Object[] { 1, 2, 3, 5, 8, 100 })//// .list();// 5，使用命名查询// Query query = session.getNamedQuery("queryByIdRange");// query.setParameter("idMin", 3);// query.setParameter("idMax", 10);// List<Object> list = query.list();// 6，update与delete，不会通知Session缓存// >> 在update或delete后，需要refresh(obj)一下以获取最新的状态// >> Update// Integer result = session.createQuery(//// "update Employee e set e.name=? where id>15")//// .setParameter(0, "无名氏")//// .executeUpdate();// 返回int型结果,表示影响了几行。// if (result != 0) {// System.out.println("更新成功 result = " + result);// } else {// System.out.println("更新失败 result = " + result);// }// >> DeleteInteger result = session.createQuery(//"delete Employee e where id=?")//.setParameter(0, 20)//.executeUpdate();// 返回int型结果,表示影响了几行。if (result != 0) {System.out.println("删除成功 result = " + result);} else {System.out.println("删除失败 result = " + result);}// ------------------------------------// ------ 查询结果并 显示结果// List<Object> list = session.createQuery(hql).list();// for (Object obj : list) {// if (obj.getClass().isArray()) {// System.out.println(Arrays.toString((Object[]) obj));// } else {// System.out.println(obj);// }// }tx.commit();} catch (RuntimeException e) {tx.rollback();throw e;} finally {session.close();}}// 使用HQL查询@Testpublic void testHql_DML() throws Exception {Session session = sessionFactory.openSession();Transaction tx = null;try {tx = session.beginTransaction();// ------------------------------------String hql = null;//// 第一次显示名称Employee employee = (Employee) session.get(Employee.class, 17);System.out.println(employee);// update与delete，不会通知Session缓存// >> UpdateInteger result = session.createQuery(//"update Employee e set e.name=? where id>15")//.setParameter(0, "无名氏1")//.executeUpdate();// 返回int型结果,表示影响了几行。if (result != 0) {System.out.println("更新成功 result = " + result);} else {System.out.println("更新失败 result = " + result);}// 第二次显示名称// >> 在update或delete后，需要refresh(obj)一下以获取最新的状态session.refresh(employee);System.out.println(employee.getName());// ------------------------------------tx.commit();} catch (RuntimeException e) {tx.rollback();throw e;} finally {session.close();}} 0 0
- Hibernate_查询_HQL详解（三）_命名的查询、更新与删除的HQL语法
- Hibernate_查询_HQL详解（二）_聚集函数、分组、连接查询、查询时使用参数的HQL语法
- 08_传智播客hibernate教程_hql的命名参数与Query接口的分页查询
- Hibernate_查询_多种查询方式介绍、HQL详解（一）
- Hibernate_查询_QueryByCriteria的方式
- 自己总结的hql新增、删除、查询与sql的新增、删除、查询区别
- hql语句查询（命名查询）
- 89-----hibernate的hql查询，原生SQL查询，命名查询,投影查询
- hibernate_三种查询方式
- Hibernate的查询 HQL查询
- Hibernate-命名HQL查询
- 常用的一些HQL查询语句详解（2）
- 常用的一些HQL查询语句详解（3）
- HQL的简单查询
- hql的日期查询
- hql查询的使用：
- Hibernate的Hql查询
- HQL查询的使用
- inline函数
- pcre3的源代码
- Mongodb 基于分布式文件存储的数据库
- 在myeclipse快捷生成setget方法
- spring整合jdbc;使用properties文件获取mysql数据库连接参数
- Hibernate_查询_HQL详解（三）_命名的查询、更新与删除的HQL语法
- java 消息机制 ActiveMQ入门实例
- 量化交易——传统技术分析随机震荡指标STO的原理及实现
- 375. Guess Number Higher or Lower II
- Lua-闭包
- Java用队列优化递归
- HTTP API 设计指南
- 制作 wordpress 博客静态化到本地
- 函数的瞬时python 多端元光谱混合分解<七>
