hibernate（八）HQL(2)

BaseDAO需求

• 作用：

• 1、将赋值的操作交给basedao
• 2、分页
  a、查询出符合条件的总记录数
  b、查询符合条件的某一页记录

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.eight.util.PageBean;

/**
 * 作用：
 * 1、将赋值的操作交给basedao
 * 2、分页
 * 	a、查询出符合条件的总记录数
 * 	b、查询符合条件的某一页记录
 * @author Administrator
 *
 */
public class BaseDao {

	/**
	 * 赋值的操作
	 * @param query 预定义对象
	 * @param map	前台传递过来的参数
	 */
	public void setParameter(Query query,Map<String, Object> map) {
//		query.setParameter("bookname", "%"+book.getBookname()+"%");
		if(map == null || map.size()==0) {
			return;
		}
		Object values = null;
		for (Map.Entry<String, Object> entry : map.entrySet()) {
			values = entry.getValue();
			if(values instanceof Collection) {
				query.setParameterList(entry.getKey(), (Collection) values);
			}
			else if(values instanceof Object[]) {
				query.setParameterList(entry.getKey(), (Object[]) values);
			}
			else {
				query.setParameter(entry.getKey(), values);
			}
		}
	}
	
	
	/**
	 * 思路：
	 * 	截取from后的sql语句，前面拼接select count(*)
	 * 
	 * 转成大写
	 */
	
	//查询出符合条件的总记录数
	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()) {
			String countHql = getCountHql(hql);			
			Query countQuery = session.createQuery(countHql);
			this.setParameter(countQuery, map);		
			//pagebean设置总记录数，最后用于分页
			pageBean.setTotal(countQuery.getSingleResult().toString());
			//查询展示的数据
			Query pageQuery = session.createQuery(hql);
			this.setParameter(pageQuery, map);
			pageQuery.setFetchSize(pageBean.getStartIndex());
			pageQuery.setMaxResults(pageBean.getRows());
		
			return pageQuery.list();	
		}
		else {
			Query query = session.createQuery(hql);
			this.setParameter(query, map);
			return query.list();	
		}
			
	}
	
	
}

dao方法：
没有BaseDao方法：

public List<Book> list1(Book book,PageBean pageBean){
		
		Session session = SessionFactoryUtils.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);
		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;
		
	}

继承BaseDao方法：

	public List<Book> list2(Book book,PageBean pageBean){
		
		Session session = SessionFactoryUtils.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());
		}		
		List list = super.executeQuery(session, hql, pageBean, map);		
		transaction.commit();
		session.close();		
		return list;
		
	}

原生态SQL

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

	public List<Object[]> list3(Book book,PageBean pageBean){
		
		Session session = SessionFactoryUtils.getSession();
		Transaction transaction = session.beginTransaction();		
		Map<String, Object> map = new HashMap<>();
//		String hql = " select b.bookname,o.orderId from Book b,Order o where 1=1";				
//		 List list = session.createQuery(hql).list();
		//原生态SQL
		 String sql = " select b.book_name,o.order_id from t_hibernate_book b,t_hibernate_order o where 1=1";
		 List list = session.createSQLQuery(sql).list();
		transaction.commit();
		session.close();		
		return list;
		
	}

视图映射

hibernate支持视图映射

	public List<Object[]> list3(Book book,PageBean pageBean){
		
		Session session = SessionFactoryUtils.getSession();
		Transaction transaction = session.beginTransaction();		
		Map<String, Object> map = new HashMap<>();
		//视图映射
		 String sql = " select * from book_order";
		 List list = session.createSQLQuery(sql).list();
		transaction.commit();
		session.close();		
		return list;
		
	}