什么是hql以及hql和sql的区别

一、hql的定义

Hibernate 查询语言（HQL）是一种面向对象的查询语言，类似于 SQL，但不是去对表和列进行操作，而是面向对象和它们的属性。

二、HQL

1.HQL里面的类名和属性名区分大小写，但关键字不区分大小写
2. ? 从下标0开始计算位置
3. HQL支持:命名参数
4. HQL是面向对象的查询语言

三、SQL

1.SQL里面的表名和列名不区分大小写
2.? 从顺序1开始计算位置
3.SQL不支持:命名参数
4.SQL是面向结构的查询语言

四、处理返回的结果集

4.1 单个对象：通过查询语句得到这个对象的所有属性值
示例代码：

@Test
	public void list() {
		String hql="from User";
		Session session = SessionFactoryUtil.getSession();
		Transaction transaction = session.beginTransaction();
		//创建批量对象
		Query query = session.createQuery(hql);
		//运行这个批量对象
		List<User> list = query.list();
		for (User user : list) {
			System.out.println(user);
		}
		transaction.commit();
		SessionFactoryUtil.closeSession();
	}

4.2 Object[]:通过查询语句得到指定的几个属性的值
示例代码：

@Test
	public void list2() {
		String hql="select userName,userPwd from User";
		Session session = SessionFactoryUtil.getSession();
		Transaction transaction = session.beginTransaction();
		//创建批量对象
		Query query = session.createQuery(hql);
		//运行这个批量对象
		List<Object[]> list = query.list();
		for (Object[] user : list) {
			System.out.println(Arrays.toString(user));
		}
		transaction.commit();
		SessionFactoryUtil.closeSession();
	}

4.3 Map
示例代码：

@Test
	public void list5() {
//		第二种hql语句
		String hql="select new map(u.userName,u.userPwd) from User u";
		Session session = SessionFactoryUtil.getSession();
		Transaction transaction = session.beginTransaction();
		//创建批量对象
		Query query = session.createQuery(hql);
		//运行这个批量对象
		List<Map<Object, Object>> list = query.list();
		for (Map<Object, Object> user : list) {
			System.out.println(user);
		}
		transaction.commit();
		SessionFactoryUtil.closeSession();
	}

4.4 new 构造方法(attr1,attr2)
示例代码：

@Test
	public void list6() {
		String hql="select new User(u.userName,u.userPwd) from User u";
		Session session = SessionFactoryUtil.getSession();
		Transaction transaction = session.beginTransaction();
		//创建批量对象
		Query query = session.createQuery(hql);
		//运行这个批量对象
		List<User> list = query.list();
		for (User user : list) {
			System.out.println(user);
		}
		transaction.commit();
		SessionFactoryUtil.closeSession();
	}
	
	/**

五.hql中使用占位符

5.1 ?占位符
从下标0开始计算位置
示例代码：

@Test
	public void list7() {
		String hql="select u.userName,u.userPwd from User u where u.userName=?";
		Session session = SessionFactoryUtil.getSession();
		Transaction transaction = session.beginTransaction();
		//创建批量对象
		Query query = session.createQuery(hql);
		query.setString(0, "zs");
		//运行这个批量对象
		List<Object[]> list = query.list();
		for (Object[] user : list) {
			System.out.println(Arrays.toString(user));
		}
		transaction.commit();
		SessionFactoryUtil.closeSession();
	}

5.2 :命名参数
示例代码：

@Test
	public void list8() {
		String hql="select u.userName,u.userPwd from User u where u.userName=:uname";
		Session session = SessionFactoryUtil.getSession();
		Transaction transaction = session.beginTransaction();
		//创建批量对象
		Query query = session.createQuery(hql);
		query.setString("uname", "zs");
		//运行这个批量对象
		List<Object[]> list = query.list();
		for (Object[] user : list) {
			System.out.println(Arrays.toString(user));
		}
		transaction.commit();
		SessionFactoryUtil.closeSession();
	}

六.连接查询

示例代码：

@Test
	public void list9() {
		String hql="select o.orderNo,oi.quantity from Order o,OrderItem oi " + 
				" where o=oi.order and o.orderId=:orderid";
		Session session = SessionFactoryUtil.getSession();
		Transaction transaction = session.beginTransaction();
		//创建批量对象
		Query query = session.createQuery(hql);
		query.setInteger("orderid", 3);
		//运行这个批量对象
		List<Object[]> list = query.list();
		for (Object[] objects : list) {
			System.out.println(Arrays.toString(objects));
		}
		transaction.commit();
		SessionFactoryUtil.closeSession();
	}

七.聚合函数

示例代码：

@Test
	public void list12() {
		String hql="select count(*),max(b.price),min(b.price),avg(round(b.price)),sum(b.price) from Book b";
		Session session = SessionFactoryUtil.getSession();
		Transaction transaction = session.beginTransaction();
		//创建批量对象
		Query query = session.createQuery(hql);
		//运行这个批量对象
		List<Object[]> list = query.list();
		for (Object[] objects : list) {
			System.out.println(Arrays.toString(objects));
		}
		transaction.commit();
		SessionFactoryUtil.closeSession();
	}

八.hql分页

示例代码：

@Test
	public void list13() {
		int page=1;
		int row=3;
		String hql="from User";
		Session session = SessionFactoryUtil.getSession();
		Transaction transaction = session.beginTransaction();
		//创建批量对象
		Query query = session.createQuery(hql);
		query.setFirstResult((page-1)*row);//设置起始记录下标
		query.setMaxResults(row);//设置返回的最大结果集 
		//运行这个批量对象
		List<User> list = query.list();
		for (User user : list) {
			System.out.println(user);
		}
		transaction.commit();
		SessionFactoryUtil.closeSession();
	}