2018-10-29 hql的入门

1. 什么是hql

HQL是Hibernate Query Language的缩写

2. hql和sql区别/异同

SQL

1. 结构化查询语言，表与列
2. 不区分大小写
3. 可以通过空格与as给表和列起别名
4. 支持?占位符，从顺序1开始

HQL
5. 面向对象的查询语言，类与属性
6. 类和属性区分大小写，但关键字不区别大小写
7. 可以通过空格与as给类和属性起别名
8. 支持?占位符，从下标0开始(不推荐使用)
9. 支持:命名参数(:名字)

3. 处理返回的结果集

3.1 单个对象

@Test
	public void testList1() {
		String hql = "from Book";
//		Query-->preparestatement
		Query query = session.createQuery(hql);
		List<Book> list = query.list();
		for (Book book : list) {
			System.out.println(book);
		}
	}

/**
	 * 结果处理二：
	 * 查一列
	 * jdbc:查的是表中某列，列名不区分大小写
	 * 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);
		}
	}

3.2 Object[]

@Test
	public void testList3() {
		String hql = "select bookId,bookName from Book";
		List<Object[]> list = session.createQuery(hql).list();
		for (Object[] book : list) {
			System.out.println(Arrays.toString(book));
		}
	}

3.3 Map

/**
	 * 结果处理情况四：利用hibernate内置函数(不区分大小写)进行结果处理
	 * map在此不是接口，不可new
	 * 
	 */
	@Test
	public void testList4() {
		String hql = "select new map(bookId,bookName) from Book";
		List<Map> list = session.createQuery(hql).list();
		for (Map book : list) {
			System.out.println(book);
		}
	}

3.4 new 构造方法(attr1,attr2)

/**
	 * 结果处理情况五：利用构造方法进行结果处理
	 * 1、当你给某一个类提供有参构造器，顺便加上无参构造器
	 * 2、构造方法赋值的原理（完善mvc通用crud）
	 * 	1	西游记	
		2	红楼梦	
		resultset
		Class clz = Book.class
		clz.getDecarldconstruct();
		获取到有参构造器中的参数
		Field[] Fields = clz.getDecarlFields();
			for(Field f:Fields){
				f.setObj(b,rs.getObject(1));
			}
			list.add(c.newInstance(?,?));
	 */
	@Test
	public void testList5() {
		String hql = "select new Book(bookId,bookName) from Book";
		List<Book> list = session.createQuery(hql).list();
		for (Book book : list) {
			System.out.println(book);
		}
	}

4. hql中使用占位符

/**
	 * hql中使用占位符
	 * jdbc:select * from t_hibernate_book where book_id=?
	 * hql:from Book where bookId = ?/:bookId(随便取名)
	 */
	@Test
	public void testList6() {
	
		//查具体的哪本书
		//		String hql = "from Book where bookId = :bookId";
		//		Query query = session.createQuery(hql);
		//		query.setParameter("bookId", 2);//查第二本书
		//		Book book = (Book) query.getSingleResult();
		//		System.out.println(book);
		
		//价格区间查
	//		String hql = "from Book where price >:min and price <:max";
	//		Query query = session.createQuery(hql);
	//		query.setParameter("min", 44f);
	//		query.setParameter("max", 54f);
	//		List<Book> list = query.list();
	//		for (Book book : list) {
	//			System.out.println(book);
	//		}
			
	//查询书id2,4,5的书籍
	//		String hql = "from Book where bookId in (:bookIds)";
	//		Query query = session.createQuery(hql);
	//		query.setParameterList("bookIds", new Integer[] {2,4,5});
	//		List<Book> list = query.list();
	//		for (Book book : list) {
	//			System.out.println(book);
	//		}
		
		String hql = "from Book where bookId in (:bookIds)";
		Query query = session.createQuery(hql);
		List<Integer> params = new ArrayList<Integer>();
		params.add(1);
		params.add(2);
		params.add(4);
		query.setParameterList("bookIds", params);
		List<Book> list = query.list();
		for (Book book : list) {
			System.out.println(book);
		}
	}

5. 连接查询

/**
	 * 连表查询
	 * jdbc:select * from t_hibernate_order o,t_hibernate_order_item oi 
	 * 		where o.orderId = oi.oid
	 * hql:select * from Order o,OrderItem oi 
	 * 		where o.orderId = oi.order.orderId
	 */
	@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";
		Query query = session.createQuery(hql);
		List<Object[]> list = query.list();
		
		for (Object[] objects : list) {
			System.out.println(Arrays.toString(objects));
		}
	}

6. 聚合函数

sum
avg
max
min
count

BaseDao

/**
 * 作用
 * 1、将赋值的操作交给Basedao
 * 2、分页
 * 		a.查询出符合条件的总记录数
 * 		b.查询符合条件的某一页记录
 * @author Administrator
 *
 */
public class BaseDao {
	/**
	 * 赋值的操作
	 * @param query 预定义对象
	 * @param map 前台传过来的参数
	 */
	private void setParameter(Query query,Map<String, Object> map) {
		if(map==null||map.size()==0) {
			return;
		}
		Object value = null;
		for(Map.Entry<String, Object> entry:map.entrySet()) {
			value = entry.getValue();
			if(value instanceof Collection) {
				query.setParameterList(entry.getKey(), (Collection)value);
			}else if(value instanceof Object[]) {
				query.setParameterList(entry.getKey(), (Object[])value);
			}else {
				query.setParameter(entry.getKey(), value);
			}
			
		}
	}
	
	/**
	 * sql = select * from t_hibernate_book where boon_name like '%不死不灭%'
	 * countSql = select count(*) from () t
	 * pageSql = sql+"limit ?,?"	hibernate中省略
	 * 
	 * hql = from Book where bookName like '%不死不灭%'
	 * hql = select bookName,price from Book where bookName like '%不死不灭%'
	 * 
	 * 思路：
	 * 	截取from后的sql语句，前面拼接select count(*) 
	 * 
	 * from
	 * 
	 * 转成大写
	 */
	
	/**
	 * 查询出符合条件的总记录数
 * @param hql
 * @return
 */
public String getCountHql(String hql) {
	int index = hql.toUpperCase().indexOf("FROM");
	return "select count(*)"+hql.substring(index);
}


public List executeQuery(Session session,String hql,PageBean pageBean,Map<String, Object> map) {
	if(pageBean != null&&pageBean.isPagination()) {
		//总记录数
		//select count(*) from Book where bookName like '%不死不灭%'
		String countHql = getCountHql(hql);
		Query countQuery = session.createQuery(countHql);
		this.setParameter(countQuery, map);
		//pageBean设置总记录数，最好用于分页
		String total = countQuery.getSingleResult().toString();
		pageBean.setTotal(total);
		
		//查询展示数据
		Query pageQuery = session.createQuery(hql);
		this.setParameter(pageQuery, map);
		pageQuery.setFirstResult(pageBean.getStartIndex());
		pageQuery.setMaxResults(pageBean.getRows());
		
		return pageQuery.list();
	}
	else {
		Query query = session.createQuery(hql);
		this.setParameter(query, map);
		return query.list();
	}
}
}

2. 原生sql

hql实现不了的功能，可以考虑使用原生sql
1、多表（5+）联查
2、未配置映射文件中关系