Hibernate_查询_HQL详解（三）_命名的查询、更新与删除的HQL语法

分手后的思念是犯贱 2022-06-16 08:42 207阅读 0赞

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查询
    	@Test
    	public 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 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 list = session.createQuery(hql)//
    			// .setParameter("idMin", 5)//
    			// .setParameter("idMax", 15)//
    			// .list();
    
    			// >> 方式三：当参数是集合时,一定要使用ParameterList()设置参数值
    			hql = "from Employee e where id in (:ids)";
    			List list = session.createQuery(hql)//
    					.setParameterList("ids", new Object[] { 1, 2, 3, 5, 8, 100 })//
    					.list();
    
    			// ------------------------------------
    			// ------ 查询结果并 显示结果
    			// List 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();
    		}
    	}
    
    
     
     		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，查询时使用参数
    			>> 方式一：使用'?'占位
    			>> 方式二：使用变量名
    		5，使用命名查询
    		6，update与delete，不会通知Session缓存
    			在update或delete后，需要refresh(obj)一下以获取最新的状态
    
    			隔离级别中文			隔离级别英文				对应的整数表示
    			读未提交				READ UNCOMMITED				1
    			读已提交				READ COMMITED				2
    			可重复读				REPEATABLE READ				4
    			串行化(不可并发)		SERIALIZEABLE				8 
     
     
     <?xml version="1.0"?>
    <!DOCTYPE hibernate-mapping PUBLIC
            "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
            "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
    
    <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_" />
    
    		
    		<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查询
    	@Test
    	public 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);
    			// }
    
    			// >> Delete
    			Integer 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查询
    	@Test
    	public 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缓存
    
    			// >> Update
    			Integer 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();
    		}
    	}