hql(二)

一：BaseDao的作用

1：为了解决赋值操作

2：分页 a:查询符合条件的总记录数

                   b:查询符合条件的某一页的记录数

二： 原生sql

hql实现不了的功能，可以考虑使用原生sql
1、多表（5+）联查
2、未配置映射文件中关系
原生态查询sql
String sql=“select b.book_name,o.order_no from t_hibernate_book b,t_hibernate_order o where 1=1”;

    List list = session.createSQLQuery(sql).list();

三：视图映射

列如：

create view book_order
as
select b.book_name,o.order_no from t_hibernate_book b,t_hibernate_order o where 1=1

//创建视图
String sql=“select * from book_order”;

package com.zking.eight.dao;
 
import java.util.Collection;
import java.util.List;
import java.util.Map;
 
import org.hibernate.Session;
import org.hibernate.query.Query;
 
import com.zking.five.entity.Book;
 
/**
 * 作用：
 *   1；将赋值的操作交给basedao
 *   2:分页
 *      a:查询出符合条件的总记录数
 *      b:查询出符合某一页的记录数
 */
public class BaseDao {
 
	/**
	 * 赋值操作
	 * @param query 预定义对象
	 * @param map  前台传过来的参数
	 */
	
	private void setParameter(Query query,Map<String, Object> map) {
		//query.setParameter("bookName", "%"+book.getBookName()+"%");
		//如果页面没有传值就直接返回 不赋值
		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);
			}
		}
	}
	
	
	/**
	 * 分页
	 *  jdbc: 
	 *  sql=select * from t_hibernate_book where book_name like "%双世宠妃%"
	 *  countSql=select count(*) from (select * from t_hibernate_book where book_name like "%双世宠妃%")
	 *  pageSql=sql+"limt ?,?" hibernate中这一步被省略
	 *  
	 *  hql="from Book where bookName like '%双世宠妃%'"
	 *  
	 *  思路：
	 *      只需要截取from 后的sql语句  前面拼接 select count(*) 就可获取总记录数
	 *       
	 *       注意：from 可以有多种形式  所以要先转化为全大写
	 *       
	 */
	
	public String getCountHql(String hql) {
		//hql转为大写 获取FROM下标 获取临时的 所以 return 要转
		int index = hql.toUpperCase().indexOf("FROM");
		return hql.substring(index);
	}
	
	public List executeQuery(Session session,String hql,PageBean pageBean,Map<String, Object> map) {
		//判断是否需要分页
		if(pageBean !=null && pageBean.isPagination()) {
			//1拿到分页的总记录数
			//select count(*) from Book where bookName like '%双世宠妃%'
			String countHql ="select count(*)"+ getCountHql(hql);
			Query countQuery = session.createQuery(countHql);
			this.setParameter(countQuery, map);
			//pageBean设置总记录数 ，最后用于分页
			String total = countQuery.getSingleResult().toString();
			pageBean.setTotal(total);
			//或者:pageBean.setTotal(countQuery.getSingleResult().toString());
			
			//查询展示数据  
			Query pageQuery = session.createQuery(hql);
			pageQuery.setFirstResult(pageBean.getStartIndex());
			pageQuery.setMaxResults(pageBean.getRows());
			this.setParameter(pageQuery, map);
			
			return pageQuery.list();
			
		}else {//不分页
			//生成预定义对象
			Query query = session.createQuery(hql);
			this.setParameter(query, map);
			return query.list();
		}
		
	}
	}

package com.zking.eight.test;
 
import java.util.Arrays;
import java.util.List;
 
import org.junit.Test;
 
import com.zking.eight.dao.PageBean;
import com.zking.five.dao.BookDao;
import com.zking.five.entity.Book;
 
public class TestQuery {
 
	private BookDao bookDao=new BookDao();
	
	@Test
	public void testList1() {
		PageBean pageBean=new PageBean();
		Book book=new Book();
		//pageBean.setPage(2);
		book.setBookName("双世宠妃");
		List<Book> list1 = this.bookDao.list1(book, pageBean);
		list1.forEach(System.out::println);
		
	}
	
	/**
	 * 通用分页
	 */
	@Test
	public void testList2() {
		PageBean pageBean=new PageBean();
		Book book=new Book();
		//pageBean.setPage(2);
		book.setBookName("哈哈哈");
		List<Book> list1 = this.bookDao.list2(book, pageBean);
		list1.forEach(System.out::println);
		
	}
	
	@Test
	public void testList3() {
		PageBean pageBean=new PageBean();
		Book book=new Book();
		pageBean.setPage(2);
		//book.setBookName("哈哈哈");
		List<Object[]> list1 = this.bookDao.list3(book, pageBean);
		for (Object[] b : list1) {
			System.out.println(Arrays.toString(b));
		}
		
	}
}
 

//无BaseDao
	
	public List<Book> list1(Book book,PageBean pageBean){
		Session session = SessionFactoryUtil.getSession();
		Transaction transaction = session.beginTransaction();
		String hql="from Book where 1=1";
		if(StringUtils.isNotBlank(book.getBookName())) {
			hql+=" and bookName like :bookName";
		}
		Query query = session.createQuery(hql);
		//如果没有传入name就不需要加入  每一个就必须判断 太麻烦 所有需要一个通用的
		if(StringUtils.isNotBlank(book.getBookName())) {
			query.setParameter("bookName", "%"+book.getBookName()+"%");
		}
		
		if(pageBean!=null&&pageBean.isPagination()) {
			query.setFirstResult(pageBean.getStartIndex());
			query.setMaxResults(pageBean.getRows());
		}
		List list = query.list();
		transaction.commit();
		session.close();
		return list;
		
	}
	
	public List<Book> list2(Book book,PageBean pageBean){
		Session session = SessionFactoryUtil.getSession();
		Transaction transaction = session.beginTransaction();
		Map<String, Object> map=new HashMap<>();
		String hql="from Book where 1=1";
		if(StringUtils.isNotBlank(book.getBookName())) {
			hql+=" and bookName like :bookName";
			//实际开发需要传过来
			map.put("bookName",book.getBookName());
		}
		//调用baseDao
		List list = super.executeQuery(session, hql, pageBean, map);
		transaction.commit();
		session.close();
		return list;
		
	}
	
	public List<Object[]> list3(Book book,PageBean pageBean){
		Session session = SessionFactoryUtil.getSession();
		Transaction transaction = session.beginTransaction();
		Map<String, Object> map=new HashMap<>();
		//String hql="select b.bookName,o.orderNo from Book b,Order o where 1=1";
		//原生态的查询
		//String sql="select b.book_name,o.order_no from t_hibernate_book b,t_hibernate_order o where 1=1";
		//创建视图
		String sql="select * from book_order";
		
		//List list = session.createQuery(hql).list();
		List list = session.createSQLQuery(sql).list();
		
		
		transaction.commit();
		session.close();
		return list;
		
	}