HQL2:通用分页

一：普通查询分页：

1.dao方法：

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;
	}
	

2.测试：

public void testList1() {
		//pageBean.setPage(2);
		book.setBookName("三国");
		List<Book> list1 = this.bookDao.list1(book, pageBean);
		for (Book book : list1) {
			System.out.println(book);
		}
	}

 

二：封装工具类，实现简便查询分页

1.封装工具类：BaseDao

作用：1.将赋值的操作交给BaseDao
           2.通过调用方法实现分页

package com.crud.eight.dao;

import java.util.Collection;
import java.util.List;
import java.util.Map;

import org.hibernate.query.Query;
import org.hibernate.Session;

import com.crud.eight.test.PageBean;


/*
 * 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()+"%");
		//如果map没值，即前台没传值过来
		if(map==null||map.size()==0) {
			return;
		}
		//如果有值，就遍历
		Object value=null;    //值为object类
		for (Map.Entry<String, Object> entry : map.entrySet()) {
			//赋值操作
			value=entry.getValue();  //获取值
			if(value instanceof Collection) {  //如果value是集合
				query.setParameterList(entry.getKey(), (Collection)value);
			}else if(value instanceof Object[]) {  //如果value是数组
				query.setParameterList(entry.getKey(), (Object[])value);
			}else {  //其他情况
				query.setParameter(entry.getKey(), value);
			}
		}
	}
	
	/**
	 * sql=select * from t_hibernate_book where book_name=?
	 * countSql=select count(*) from (select * from t_hibernate_book where book_name=?)
	 * pageSql=sql+" limit ?,?"   hibernate中这一步省略
	 * 
	 * 
	 * 思路：
	 *   截取from后的SQL语句，前面拼接select count(*) 即整个查询语句
	 *   因为from关键字不区分大小写，所以截取时有几种情况：
	 *   From
	 *   fRom
	 *   frOm
	 *   froM
	 *   解决方法：
	 *   	全部转为大写   FROM
	 */
	
	public String getCountSql(String hql) {
		int index = hql.toUpperCase().indexOf("FROM");  //from后面的SQL语句
		return "select count(*)"+hql.substring(index);
	}
	
	/**
	 * 
	 * @param hql   hql语句
	 * @param pageBean   分页
	 * @param map   传过来的参数集合  ：bookName
	 * @return
	 */
	public List executeQuery(Session session,String hql,PageBean pageBean,Map<String, Object> map) {
		if(pageBean!=null||pageBean.isPagination()) {  //分页
			String countSql = getCountSql(hql);  //拿到分页的总记录数
			Query countQuery = session.createQuery(countSql);
			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);
			List list = query.list();
			return list;
		}
		
	}
	

}

2.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.executeQuery(session, hql, pageBean, map);
		transaction.commit();
		session.close();
		return list;
	}

3.测试：

public void testList2() {
		//pageBean.setPage(1);
		book.setBookName("红");
		List<Book> list1 = this.bookDao.list2(book, pageBean);
		for (Book book : list1) {
			System.out.println(book);
		}
	}

三：原生SQL 

1.使用原生SQL的场景：

      1.1、多表（5+）联查
      1. 2、未配置映射文件中关系

2.案例：

2.1：dao方法：

public List<Object[]> list3(){
		Session session = SessionFactoryUtils.getSession();
		Transaction transaction = session.beginTransaction();
		Map<String, Object> map=new HashMap<String, Object>();

		//原生态查询
		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();
				
		transaction.commit();
		session.close();
		return list;
	}

2.3：测试：

public void testList3() {
		pageBean.setPage(1);
		List<Object[]> list1 = this.bookDao.list3();
		for (Object[] book : list1) {
			System.out.println(Arrays.toString(book));
		}
	}

四：视图映射

适用场景：
        select * from 3表联查

第一种：用SQL语句进行视图映射 

新建视图：

 

dao方法：

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

测试：

public void testList3() {
		pageBean.setPage(1);
		List<Object[]> list1 = this.bookDao.list3();
		for (Object[] book : list1) {
			System.out.println(Arrays.toString(book));
		}
	}

 

第二种：用hql进行视图映射（把视图变为实体类）

1：新建视图：

2：视图转为实体类（OrderBook）：

package com.crud.eight.dao;

import java.io.Serializable;

public class OrderBook implements Serializable{
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private Integer bookId;
	private String bookName;
	private Integer orderId;
	private String orderNo;
	
	
	public Integer getBookId() {
		return bookId;
	}
	public void setBookId(Integer bookId) {
		this.bookId = bookId;
	}
	public Integer getOrderId() {
		return orderId;
	}
	public void setOrderId(Integer orderId) {
		this.orderId = orderId;
	}
	public String getBookName() {
		return bookName;
	}
	public void setBookName(String bookName) {
		this.bookName = bookName;
	}
	public String getOrderNo() {
		return orderNo;
	}
	public void setOrderNo(String orderNo) {
		this.orderNo = orderNo;
	}
	@Override
	public String toString() {
		return "OrderBook [bookId=" + bookId + ", bookName=" + bookName + ", orderId=" + orderId + ", orderNo="
				+ orderNo + "]";
	}
	
	
	
	

}

3.配置文件：

OrderBook.hbm.xml

 

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-configuration PUBLIC
	"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
	"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
	//order_book:视图名
<hibernate-mapping>
	<class name="com.crud.eight.dao.OrderBook" table="order_book">	
		<composite-id>		
			<key-property name="bookId" column="book_id" type="java.lang.Integer" />	
			<key-property name="orderId" column="order_id" type="java.lang.Integer" />		
		</composite-id>
		<property name="bookName" column="book_name" type="java.lang.String"></property>	
		<property name="orderNo" column="order_no" type="java.lang.String"></property>
	</class>

</hibernate-mapping>

hibernate.cfg.xml:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-configuration PUBLIC
	"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
	"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
	
<hibernate-configuration>
	<session-factory>
		<!-- 1. 数据库相关 -->
		<property name="connection.username">root</property>
		<property name="connection.password">123</property>
		<property name="connection.url">jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=UTF-8</property>
		<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
		<!-- 连接MySQL数据库 -->
		<property name="dialect">org.hibernate.dialect.MySQLDialect</property> 
		
		<!-- 2.配置本地事务（No CurrentSessionContext configured!） -->
		<property name="hibernate.current_session_context_class">thread</property>
		
		<!-- 3. 调试相关 -->
		<property name="show_sql">true</property>
		<property name="format_sql">true</property>
		
		<!-- 4.hibernate需要管理的数据库表对应的实体类映射文件 -->
		<mapping resource="com/crud/one/entity/User.hbm.xml"></mapping>
		
				
		<mapping resource="com/crud/eight/dao/OrderBook.hbm.xml"></mapping>
	</session-factory>
</hibernate-configuration>

4.测试方法：

	public void testList4() {	
		session = SessionFactoryUtils.getSession();
	    transaction = session.beginTransaction();
		String hql = "from OrderBook";	
		Query query = session.createQuery(hql);	
		List<OrderBook> scList = query.list();	
		for (OrderBook sc : scList) {
			System.out.print(sc.getBookId()+","+sc.getBookName());
		}	
		transaction.commit();
		session.close();
	}