Hql面向对象的查询语言

1. 什么是hql
   HQL是Hibernate Query Language的缩写

   查全部

2. hql和sql区别/异同
   HQL
   类名/属性
   区分大小写,关键字不区分大小写
   别名
   ?，从下标0开始计算位置(hibernate5之后不支持)
   :命名参数
   面向对象的查询语言

   SQL
   表名/列名
   不区分大小写
   别名
   ?，从顺序1开始计算位置
   不支持:命名参数
   面向结构查询语言

private Session session;
	private Transaction transaction;
	@Before
	public void before() {
		session = SessionFactoryUtil.getSession();
		transaction = session.beginTransaction();
	}
	@After
	public void after() {
		transaction.commit();
		session.close();
	}
	/**
	 * 结果返回对象
	 * 查询所有
	 */
	@Test
	public void testList1() {
		String hql = "from Book";
		Query query = session.createQuery(hql);
		List<Book> list = query.list();
		for (Book book : list) {
			System.out.println(book);
		}
	}
	/**
	 * 结果处理二：查数据库表中的一列
	 * 数据库：在数据库中查询，是不区分大小写
	 * hql:由于你查询的是实体类的属性，那么久区分大小写
	 */
	@Test
	public void testList2() {
		String hql = "select bookName from Book";
		List<String> list = session.createQuery(hql).list();
		for (String bname : list) {
			System.out.println(bname);
		}
	}
	/**
	 * 结果方式三：Object[]
	 * 自定义mvc中的EntityBaseDao
	 * 数据库   8列
	 * 实体类   8个属性
	 * insert
	 *     Book  b
	 *  如果查询的不是一张完整的表，那么最好用map
	 */
	@Test
	public void testList3() {
		String hql = "select bookId,bookName from Book";
		List<Object[]> list= session.createQuery(hql).list();
		for (Object[] object : list) {
			System.out.println(Arrays.toString(object));
		}
	}
	/**
	 * 结果处理情况4：通过hibernate函数Map去处理
	 * 函数这个词源自数据库
	 * 函数这个词不区分大小写
	 */
	@Test
	public void testList4() {
		String hql = "select new map(bookId,bookName) from Book";
		List<Map> list= session.createQuery(hql).list();
		for (Map object : list) {
			System.out.println(object);
		}
	}
	/**
	 * 结果处理情况5：通过构造方法
	 */
	@Test
	public void testList5() {
		String hql = "select new Book(bookId,bookName) from Book";
		List<Book> list= session.createQuery(hql).list();
		for (Book object : list) {
			System.out.println(object);
		}
	}
	/**
	 * 讲解hibernate中占位符问题
	 * 5版本以后弃用“？”
	 */
	@Test
	public void testList6() {
		//第一种
//		String hql = "from Book where bookId = :bookId";
//		Query query = session.createQuery(hql);
//		query.setParameter("bookId", 1);
//		Book book = (Book) query.getSingleResult();
//		System.out.println(book);
		
		//第二种
		String hql = "from Book where bookId in (:bookId)";
		Query query = session.createQuery(hql);
		List<Integer> bookId = new ArrayList<>();
		bookId.add(2);
		bookId.add(3);
		List list = query.setParameter("bookId", bookId).list();
		Book book = (Book) query.getSingleResult();
		System.out.println(book);
		for (Object object : list) {
			System.out.println(object);
		}
		//第三种
//		String hql = "from Book where bookId in (:bookId)";
//		Query query = session.createQuery(hql);
//		List list = query.setParameterList("bookId", new Integer[] {1,2,3}).list();
//		for (Object object : list) {
//			System.out.println(object);
//		}	
	}
	/**
	 * 联表查询
	 */
	@Test
	public void testList7() {
//		String hql = "select o.orderNo,oi.productId from Order o,OrderItem oi where o.orderId = oi.order.orderId";
		String hql = "select o.orderNo,oi.productId from Order o,OrderItem oi where o = oi.order";
		List<Object[]> list = session.createQuery(hql).list();
		for (Object[] objects : list) {
			System.out.println(Arrays.toString(objects));
		}
	}
	/**
	 * 讲解聚合函数
	 */
	@Test
	public void testList8() {
		String hql = "select count(*) from Book";
		Long count = (Long)session.createQuery(hql).getSingleResult();
		System.out.println("count:"+count);
	}
	/**
	 * 讲解分页
	 * 要求一页3条数据
	 * limit 起始下标，偏移量
	 * int page = 2
	 * int ofset = 3
	 * select * from t_hibernate_book limit (page-1)* ofset,ofset
	 */
	@Test
	public void testList9() {
		String hql = "from Book";
		int page = 2;
		int ofset = 2;
		List<Book> list = session.createQuery(hql).setFirstResult((page-1)*ofset).setMaxResults(ofset).list();
	    for (Book book : list) {
			System.out.println(book);
		}
	}