Hibernate学习笔记（七）查询总结

Hibernate查询分类

1. get/load 根据OID检索
2. 对象视图检索 c.getOrders
3. Sql语句 createSqlQuery
4. Hql语句 createQuery
5. Criteria查询 createCriteria

HQL语句详解

1. 简介：
   • 描写对象操作的一种查询语句，Hibernate特有的
   • 与SQL语句大致一致，不同的是HQL语句是面向对象查询，查询的是对象和对象中的属性
2. 查询所有

	//HQL语句查询
	//查询所有
	@Test
	public void test2(){
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		
		Query query = session.createQuery("from Customer");
		
		List<Customer> list = query.list();
		
		System.out.println(list);
		
		session.getTransaction().commit();
		session.close();
	}

3. 查询几个属性

	//HQL语句查询
	//选择查询
	@Test
	public void test3(){
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		
		Query query = session.createQuery("select c.id,c.name from Customer c");
		
		List<Object[]> list = query.list();
		
		for(Object[] o : list){
			System.out.println(Arrays.toString(o));
		}
		//System.out.println(list);
		
		session.getTransaction().commit();
		session.close();
	}

4. 投影查询：选择查询的基础上，想把查询结果封装到对象中
   • 前提需要类有构造函数
     

	//HQL语句查询
	//投影查询
	@Test
	public void test4(){
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		
		Query query = session.createQuery("select new Customer(c.id,c.name) from Customer c");
		
		List<Customer> list = query.list();
		
//		for(Object[] o : list){
//			System.out.println(Arrays.toString(o));
//		}
		System.out.println(list);
		
		session.getTransaction().commit();
		session.close();
	}

5. 排序

//HQL语句查询
	//排序查询
	@Test
	public void test5(){
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		//降序
		Query query = session.createQuery("select new Customer(c.id,c.name) from Customer c Order by c.id desc");
		//升序
		Query query = session.createQuery("select new Customer(c.id,c.name) from Customer c Order by c.id asc");
		List<Customer> list = query.list();
		
//		for(Object[] o : list){
//			System.out.println(Arrays.toString(o));
//		}
		System.out.println(list);
		
		session.getTransaction().commit();
		session.close();
	}

6. 分页

//HQL语句查询
		//分页查询
		@Test
		public void test5(){
			Session session = HibernateUtils.openSession();
			session.beginTransaction();
			//降序
			Query query = session.createQuery("select new Customer(c.id,c.name) from Customer c Order by c.id desc");
			//升序
			//Query query = session.createQuery("select new Customer(c.id,c.name) from Customer c Order by c.id asc");
			
			//分页 limit ?,?(setFirstResult,setMaxResults)
			//(当前页数-1)*每页最大记录数
			query.setFirstResult(1);//从哪个索引开始取数据，包括索引
			query.setMaxResults(1);//取几个
			List<Customer> list = query.list();
			
//			for(Object[] o : list){
//				System.out.println(Arrays.toString(o));
//			}
			System.out.println(list);
			
			session.getTransaction().commit();
			session.close();
		}

7. 绑定参数查询

//HQL语句查询
		//绑定查询
		@Test
		public void test7(){
			Session session = HibernateUtils.openSession();
			session.beginTransaction();
			
			//第一种方式：
			/*Query query = session.createQuery("from Customer c where id = ?");
			//(?,?)参数1：？占位符的索引，第一个问号索引为0
			query.setInteger(0, 1);*/
			
			//第二种方式：
			Query query = session.createQuery("from Customer c where id = :h");
			//(?,?)参数1：？占位符的名称
			query.setInteger("h", 1);
			
			
			//List<Customer> list = query.list();
			//System.out.println(list);
			
			
			Customer c = (Customer) query.uniqueResult();
			System.out.println(c);
			
//			for(Object[] o : list){
//				System.out.println(Arrays.toString(o));
//			}

			
			session.getTransaction().commit();
			session.close();
		}

8. 聚合函数

		//HQL语句查询
		//聚合函数查询
		@Test
		public void test8(){
			Session session = HibernateUtils.openSession();
			session.beginTransaction();
			//count函数
			//Query query = session.createQuery("select count(*) from Customer c");
			//avg函数
			//Query query = session.createQuery("select avg(c.id) from Customer c");
			//sum函数
			//Query query = session.createQuery("select sum(c.id) from Customer c");
			//Max函数
			//Query query = session.createQuery("select max(c.id) from Customer c");
			//Min函数
			Query query = session.createQuery("select min(c.id) from Customer c");
			
			
			Object o = query.uniqueResult();
			
			System.out.println(o);

			
			session.getTransaction().commit();
			session.close();
		}

9. 分组查询

		//HQL语句查询
		//分组查询
		@Test
		public void test9(){
			Session session = HibernateUtils.openSession();
			session.beginTransaction();
			
			Query query = session.createQuery("select o.customer,count(o) from Order o group by o.customer");
			List<Object[]> list = query.list();
			
			for(Object[] o : list){
				System.out.println(Arrays.toString(o));
			}
			
			

			
			session.getTransaction().commit();
			session.close();
		}

10. 命名查询
    • 将HQL从java源码中，提取到配置文件中。

      • 全局：在任何位置都能获得到，在**.hbm.xml中class元素后面创建query元素

        • Query query = session.getNamedQuery("abc");
          

      • 局部：在特定的包下才能获得到，在**.hbm.xml中class元素里面创建query元素

        • Query query = session.getNamedQuery("com.itheima.domain.Customer.bcd");
          
11. 表连接

//HQL详解2-表连接

//内连接	=>
//左外连接 =>
//右外连接 =>
public class Demo2 {
	@Test
	//交叉连接 => 笛卡尔积
	//开发时要避免出现笛卡尔积
	public void fun1(){
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		//------------------------------------------------
		
		Query query = session.createQuery("from Customer c,Order o");
		
		
		List<Object[]> list = query.list();
		
		for(Object[] objs : list){
			System.out.println(Arrays.toString(objs));
		}
		
		
		//------------------------------------------------
		session.getTransaction().commit();
		session.close(); // 游离状态
	}
	@Test
	//内连接 
	//隐式内连接 => 在笛卡尔积基础上过滤无效数据
	public void fun2(){
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		//------------------------------------------------
		
		Query query = session.createQuery("from Customer c,Order o where o.customer = c");
		
		
		List<Object[]> list = query.list();
		
		for(Object[] objs : list){
			System.out.println(Arrays.toString(objs));
		}
		
		
		//------------------------------------------------
		session.getTransaction().commit();
		session.close(); // 游离状态
	}
	@Test
	//内连接
	//显式内连接( 非迫切)=> inner join
	// List<Object[]>
	// Object[] => [Customer,Order]
	// 将父 与 子 对象装入数组中分别 返回
	public void fun3(){
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		//------------------------------------------------
		
		//Query query = session.createQuery("from Customer c  inner join c.orders ");
		
		Query query = session.createQuery("from Customer c  inner join   c.orders ");
		
		List<Object[]> list = query.list();
		
		for(Object[] objs : list){
			System.out.println(Arrays.toString(objs));
		}
		
		
		//------------------------------------------------
		session.getTransaction().commit();
		session.close(); // 游离状态
	}
	
	@Test
	//内连接
	//显式内连接(迫切)=> inner join
	// List<Customer> 
	// 迫切连接会将 子装入父中,组装成一个对象
	public void fun4(){
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		//------------------------------------------------
		
		
		Query query = session.createQuery("from Customer c  inner join fetch  c.orders ");
		
		List<Object> list = query.list();
		
		for(Object obj : list){
			System.out.println(obj);
		}
		
		
		//------------------------------------------------
		session.getTransaction().commit();
		session.close(); // 游离状态
	}
	
	
	@Test
	//左外连接
	//left [outer] join
	public void fun5(){
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		//------------------------------------------------
		
		
		Query query = session.createQuery("from Customer c  left outer join   c.orders ");
		
		List<Object[]> list = query.list();
		
		for(Object[] objs : list){
			System.out.println(Arrays.toString(objs));
		}
		
		//------------------------------------------------
		session.getTransaction().commit();
		session.close(); // 游离状态
	}
	
	@Test
	//左外连接 迫切
	//left [outer] join fetch
	public void fun6(){
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		//------------------------------------------------
		
		
		Query query = session.createQuery("from Customer c  left outer join fetch  c.orders ");
		
		
		List<Object> list = query.list();
		
		for(Object obj : list){
			System.out.println(obj);
		}
		
		//------------------------------------------------
		session.getTransaction().commit();
		session.close(); // 游离状态
	}
	
	

	@Test
	//右外连接
	//right [outer] join
	public void fun7(){
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		//------------------------------------------------
		
		
		Query query = session.createQuery("from Customer c  right outer join   c.orders ");
		
		List<Object[]> list = query.list();
		
		for(Object[] objs : list){
			System.out.println(Arrays.toString(objs));
		}
		
		//------------------------------------------------
		session.getTransaction().commit();
		session.close(); // 游离状态
	}
	
	@Test
	//右外连接 迫切
	//right [outer] join fetch
	public void fun8(){
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		//------------------------------------------------
		
		
		Query query = session.createQuery("from Customer c  right outer join fetch  c.orders ");
		
		
		List<Object> list = query.list();
		
		for(Object obj : list){
			System.out.println(obj);
		}
		
		//------------------------------------------------
		session.getTransaction().commit();
		session.close(); // 游离状态
	}