hql：通用分页，原生sql，视图映射

1. BaseDAO（通用分页）

需求：
按名字分页查询对应书籍信息

package com.xxx.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.xxx.eight.test.PageBean;


/**
 *作用：
 *	1、将赋值的操作交给basedao
 *	2、分页
 *		2.1、查询出符合条件的总记录数
 *		2.2、查询符合条件的某一页记录
 * @author Administrator
 *
 */
public class BaseDao {
	/**
	 * 1、赋值的操作
	 * @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);
			}
		}
	}
	
	/**
	 * select * from t_hibernate_book where book_name like '圣墟';
	 * countSql=select count(*) from (select * from t_hibernate_book where book_name like '圣墟')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有很多种写法，那么它的截取办法：先转大写/小写，再去截取
	 */
	
	public String getCountHql(String hql) {
		int index = hql.toUpperCase().indexOf("FROM");//获取from的下标
		return "select count(*)"+hql.substring(index);
	}
	
	
	public List excuteQuery(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设置总记录数，最后用于分页
			pageBean.setTotal(countQuery.getSingleResult().toString());
			//查询展示数据
			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();
		}
	}
	
}

Dao

public List<Book> list2(Book book,PageBean pageBean){
		Session session = SessionFactoryUtils.getSession();
		Transaction transaction = session.beginTransaction();
		Map<String, Object> map=new HashMap<String, Object>();
		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.excuteQuery(session, hql, pageBean, map);
		
		
		transaction.commit();
		session.close();
		return list;
	}

test测试类

@Test
	public void testList2() {
		PageBean pageBean=new PageBean();
		pageBean.setPage(2);
		Book book=new Book();
//		book.setBookName("不死不灭");
		List<Book> list1=this.bookDao.list2(book, pageBean);
		for (Book b : list1) {
			System.out.println(b);
		}
	}

2. 原生sql

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

3. 视图映射

场景
select * from 3表联查

Dao

public List<Object[]> list3(){
		Session session = SessionFactoryUtils.getSession();
		Transaction transaction = session.beginTransaction();
		Map<String, Object> map=new HashMap<String, Object>();
//		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";//原生态sql
		String sql="select * from book_order";//视图映射
		
		
		
//		List list = session.createQuery(hql).list();
		List list = session.createSQLQuery(sql).list();
		
		transaction.commit();
		session.close();
		return list;
	}

test类

@Test
	public void testList3() {
		PageBean pageBean=new PageBean();
		pageBean.setPage(2);
		Book book=new Book();
//		book.setBookName("不死不灭");
		List<Object[]> list1=this.bookDao.list3();
		for (Object[] b : list1) {
			System.out.println(Arrays.toString(b));
		}
		
	

4、视图映射（hql）

1、创建视图
2、创建视图实体类（注：实体类要实现 Serializable接口）

package com.xxx.eight.entity;

import java.io.Serializable;
import java.util.HashSet;
import java.util.Set;

import com.xxx.five.entity.Category;

public class BookOrder implements Serializable{
	private Integer book_id;
	private Integer order_id;
	private String bookName;
	private String OrderNo;
	
	
	
	public Integer getBook_id() {
		return book_id;
	}
	public void setBook_id(Integer book_id) {
		this.book_id = book_id;
	}
	public Integer getOrder_id() {
		return order_id;
	}
	public void setOrder_id(Integer order_id) {
		this.order_id = order_id;
	}
	public String getOrderNo() {
		return OrderNo;
	}
	public void setOrderNo(String orderNo) {
		OrderNo = orderNo;
	}
	
	
	
	public String getBookName() {
		return bookName;
	}
	public void setBookName(String bookName) {
		this.bookName = bookName;
	}
	public BookOrder(Integer book_id, Integer order_id, String bookName, String orderNo) {
		super();
		this.book_id = book_id;
		this.order_id = order_id;
		this.bookName = bookName;
		OrderNo = orderNo;
	}
	public BookOrder() {
		super();
	}
	@Override
	public String toString() {
		return "BookOrder [book_id=" + book_id + ", order_id=" + order_id + ", bookName=" + bookName + ", OrderNo="
				+ OrderNo + "]";
	}
}

3、创建xxx.hbm.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC 
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
    <hibernate-mapping>
   
    	<class table="bookorder" name="com.zking.eight.entity.BookOrder">
    		<composite-id>
    			<key-property name="book_id" type="java.lang.Integer" column="book_id"></key-property>
    			<key-property name="order_id" type="java.lang.Integer" column="order_id"></key-property>
    		</composite-id>
    		<property name="bookName" type="java.lang.String" column="book_name"></property>
    		<property name="OrderNo" type="java.lang.String" column="order_no"></property>
    		
    	</class>
    </hibernate-mapping>

4、配置hibernate.cfg.xml文件

    <mapping resource="com/zking/eight/entity/BookOrder.hbm.xml"/>

5、Dao

public List<BookOrder> list4(){
		Session session = SessionFactoryUtils.getSession();
		Transaction transaction = session.beginTransaction();
//		Map<String, Object> map=new HashMap<String, Object>();
		String hql="from BookOrder";
		
		List list = session.createQuery(hql).list();
		
		transaction.commit();
		session.close();
		return list;
	}

6、Test

@Test
	public void testList4() {
		PageBean pageBean=new PageBean();
		BookOrder bookOrder=new BookOrder();
		List<BookOrder> list1=this.bookDao.list4();
		for (BookOrder b : list1) {
			System.out.println(b);
		}
	}