Mybatis分页实现

一、引言

Mybatis提供了强大的分页拦截实现，可以完美的实现分功能

二、普通的分页实现

普通分页实现即使直接在mapper文件中写分页查询语句

Messsage.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.imooc.bean.Message">

  <resultMap type="com.imooc.bean.Message" id="MessageResult">
    <id column="id" jdbcType="INTEGER" property="id"/>
    <result column="command" jdbcType="VARCHAR" property="command"/>
    <result column="description" jdbcType="VARCHAR" property="description"/>
    <result column="content" jdbcType="VARCHAR" property="content"/>
  </resultMap>

  <!-- 
	普通分页实现		
	使用sql实现分页查询，不是使用拦截器 
  	oracle分页使用rownum,子查询呢需要使用别名不能使用rownum，row等关键字
  -->
  <select id="queryMessageListByPage" parameterType="java.util.Map" resultMap="MessageResult">
  	select * from ( select rownum r, id,command,description,content from message
  	 	<where>
  			<if test="command != null and command.trim() != ''">
  				and command=#{command}
  			</if>
  			<if test="descriptioin != null and description.trim() != '' ">
  				and descriptioin=#{descriptioin}
  			</if>
  			<if test="page != null">
  			 	and rownum <= #{page.dbNumber}
  			</if>
 		 </where>
  		)
  		<where>
  			 and r > #{page.dbIndex}
 		 </where>
 		 order by id
  </select>
  <select id="count" parameterType="com.imooc.bean.Message"  resultType="int">
    select count(*) from message
    <where>
    	<if test="command != null and !"".equals(command.trim())">
	    	and command=#{command}
	    </if>
	    <if test="description != null and ''!=description.trim()">
	    	and description like concat(concat('%',#{description}),'%')
	    </if>
    </where>
  </select>
</mapper>

Page.java分页相关的参数都通过该类对象设置

package com.imooc.common;

/**
 * 分页对应的实体类
 */
public class Page {
	/**
	 * 总条数
	 * */
	private int totalNumber;

	/**
	 * 总页数
	 * */
	private int totalPage;

	/**
	 * 当前页
	 * */
	private int currentPage;

	/**
	 * 每页显示的数目
	 * */
	private int pageNumber = 5;

	/**
	 * 数据库中limit的参数，从第几条开始取
	 * */
	private int dbIndex;

	/**
	 * 数据库中limit的参数，一共取多少条,适用于mysql, 如果是oracle则表示最大取到的条数
	 * */
	private int dbNumber;

	/**
	 * 根据当前对象中的属性值计算并设置相关属性的值
	 * */
	
	public void count() {
		// 计算总页数
		int totalPageTemp = this.totalNumber / this.pageNumber;
		int plus = (this.totalNumber % this.pageNumber) == 0 ? 0 : 1;
		totalPageTemp += plus;
		// 如果总页数小于0显示第一页
		if (totalPageTemp <= 0) {
			totalPageTemp += 1;
		}
		this.totalPage = totalPageTemp;	
		//设置limit参数
		this.dbIndex = (this.currentPage -1 ) * this.pageNumber;
		this.dbNumber = this.pageNumber;
	}

	public int getTotalNumber() {
		return totalNumber;
	}

	public void setTotalNumber(int totalNumber) {
		this.totalNumber = totalNumber;
		count();
	}

	public int gettotalPage() {
		return totalPage;
	}

	public void settotalPage(int totalPage) {
		this.totalPage = totalPage;
	}

	public int getCurrentPage() {
		return currentPage;
	}

	public void setCurrentPage(int currentPage) {
		this.currentPage = currentPage;
		count();
	}

	public int getPageNumber() {
		return pageNumber;
	}

	public void setPageNumber(int pageNumber) {
		this.pageNumber = pageNumber;
	}

	public int getDbIndex() {
		return dbIndex;
	}

	public void setDbIndex(int dbIndex) {
		this.dbIndex = dbIndex;
	}

	public int getDbNumber() {
		return dbNumber;
	}

	public void setDbNumber(int dbNumber) {
		this.dbNumber = dbNumber;
	}
	
}

Dao层实现

package com.imooc.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;
import com.imooc.bean.Message;
import com.imooc.common.Page;
import com.imooc.db.DBAccess;

public class MessageDao {

	DBAccess dbAccess = new DBAccess();
 
	//计算查询的数据的总数
	
         public int getMessageCount(String command,String description) throws Exception{
		int count = 0;
		SqlSession session = null;
		try {
			session = dbAccess.getSqlSession();
			Message message = new Message();
			message.setCommand(command);
			message.setDescription(description);
			count = session.selectOne(Message.class.getName()+".count", message);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			if (session != null) {
				session.close();
			}
		}
		return count;
	} 
    
	//分页实现主要的方法
	public List<Message> queryMessageListByPage(String command, String description,Page page) throws Exception {
		List<Message> messagesList = null;
		SqlSession session = null;
		try {
			session = dbAccess.getSqlSession();
			Map<String, Object> paramater = new HashMap<String, Object>();
			paramater.put("command", command);
			paramater.put("description", description);
			paramater.put("page", page);
			messagesList = session.selectList(Message.class.getName()+".queryMessageListByPage2", paramater);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		} finally {
			if (session != null) {
				session.close();
			}
		}
		return messagesList;
	}
	

}

Service层实现

调用Dao层的方法，返回本次查询的数据

package com.imooc.service;

import java.util.ArrayList;
import java.util.List;
import com.imooc.bean.Message;
import com.imooc.common.Page;
import com.imooc.dao.MessageDao;

public class MessageService {

	MessageDao dao = new MessageDao();

	public int getCount(String command, String description) throws Exception {
		int count = 0;
		try {
			count= dao.getMessageCount(command, description);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		}
		return count;
	}
	

	public List<Message> queryMessageListByPage(String command, String description,Page page) throws Exception {
		List<Message> messageList = null;
		try {
			messageList = dao.queryMessageListByPage(command, description, page);
		} catch (Exception e) {
						throw new Exception(e.getMessage());

		}
		return messageList;
	}
}

ListServet.java 

用于接收前台页面的参数，以及传回数据给jsp页面

package com.imooc.servlet;

import java.io.IOException;
import java.util.List;
import java.util.regex.Pattern;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.imooc.bean.Message;
import com.imooc.common.Page;
import com.imooc.service.MessageService;

public class ListServlet extends HttpServlet {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	MessageService messageService = new MessageService();

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) {
		try {
			// 设置编码
			req.setCharacterEncoding("utf-8");
			// 接受参数
			String command = req.getParameter("command");
			String description = req.getParameter("description");
			String currentPage = req.getParameter("currentPage");
			//创建分页对象
			Page page = new Page();
			//设置总条数
			page.setTotalNumber(messageService.getCount(command, description));
			Pattern patttern = Pattern.compile("[0-9]{1,9}"); 
			if(currentPage == null || !patttern.matcher(currentPage).matches()){
				page.setCurrentPage(1);
			}else {
				page.setCurrentPage(Integer.parseInt(currentPage));
			}
			//oracle数据库分页，dbNumber表示最大能取到的条数
			page.setDbNumber(page.getDbIndex()+page.getPageNumber());
			List<Message> messageList = messageService.queryMessageListByPage(command, description, page);
			req.setAttribute("command", command);
			req.setAttribute("description", description);
			req.setAttribute("messages", messageList);
			req.setAttribute("page", page);
			// 向页面跳转
			req.getRequestDispatcher("/WEB-INF/jsp/back/list.jsp").forward(req,resp);
		} catch (Exception e) {
			e.printStackTrace();
			req.setAttribute("retMsg", "查询失败");
		}
	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		doGet(req, resp);
	}

}

jsp页面实现

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
		<meta http-equiv="X-UA-Compatible"content="IE=9; IE=8; IE=7; IE=EDGE" />
		<title>内容列表页面</title>
		<link href="<%= basePath %>css/all.css" rel="stylesheet" type="text/css" />
		<script src="<%= basePath %>js/common/jquery-1.8.0.min.js"></script>
		<script src="<%= basePath %>js/back/list.js"></script>
	</head>
	<body style="background: #e1e9eb;">
		<form action="<%= basePath %>List.action" id="mainForm" method="post">
			<input type="hidden" name="currentPage" id="currentPage" value="${page.currentPage}"/>
			
			<div class="right">
				<div class="current">当前位置：<a href="javascript:void(0)" style="color:#6E6E6E;">内容管理</a> > 内容列表</div>
				<div class="rightCont">
					<p class="g_title fix">内容列表 <a class="btn03" href="#">新 增</a>    <a class="btn03" href="javascript:deleteBatch('<%=basePath%>');">删 除</a></p>
					<table class="tab1">
						<tbody>
							<tr>
								<td width="90" align="right">指令名称：</td>
								<td>
									<input name="command" type="text" class="allInput" value="${command}"/>
								</td>
								<td width="90" align="right">描述：</td>
								<td>
									<input name="description" type="text" class="allInput" value="${description}"/>
								</td>
	                            <td width="85" align="right"><input type="submit" class="tabSub" value="查 询" /></td>
	       					</tr>
						</tbody>
					</table>
					<div class="zixun fix">
						<table class="tab2" width="100%">
							<tbody>
								<tr>
								    <th><input type="checkbox" id="all" οnclick="javascript:selAllCheckbox('id')"/></th>
								    <th>序号</th>
								    <th>指令名称</th>
								    <th>描述</th>
								    <th>操作</th>
								</tr>
								<c:forEach items="${requestScope.messages}" var="message" varStatus="status">
									<tr  <c:if test="${status.index % 2 != 0}">style='background-color:#ECF6EE;'</c:if>>
										<td><input type="checkbox"  name="id" value="${message.id}"/></td>
										<td>${status.index + 1}</td>
										<td>${message.command}</td>
										<td>${message.description}</td>
										<td>
											<a href="#">修改</a>   
											<a href="${basePath}DeleteOneServlet.action?id=${message.id}">删除</a>
										</td>
									</tr>
								</c:forEach>
							</tbody>
						</table>
						<div class='page fix'>
							共 <b>${page.totalNumber}</b> 条
							共<b>${page.totalPage }</b>页
							<c:if test="${page.currentPage != 1}">
								<a href="javascript:changeCurrentPage('1')" class='first'>首页</a>
								<a href="javascript:changeCurrentPage('${page.currentPage-1}')" class='pre'>上一页</a>
							</c:if>
							当前第<span>${page.currentPage}/${page.totalPage}</span>页
							<c:if test="${page.currentPage != page.totalPage}">
								<a href="javascript:changeCurrentPage('${page.currentPage+1}')" class='next'>下一页</a>
								<a href="javascript:changeCurrentPage('${page.totalPage}')" class='last'>末页</a>
							</c:if>
							跳至 <input id="currentPageText" type='text' value='${page.currentPage}' class='allInput w28' /> 页 
							<a href="javascript:changeCurrentPage($('#currentPageText').val())" class='go'>GO</a>
						</div>
					</div>
				</div>
			</div>
	    </form>
	</body>
</html>

使用jquery提交分页参数

/*
 * 实现翻页功能
 */
function changeCurrentPage(currentPage){
$("#currentPage").val(currentPage).val();
$("#mainForm").submit();
}

三、使用Mybatis分页拦截器实现

 使用Mybatis分页拦截器，我们可以不用在Mapper配置文件中写分页查询语句，我们只需要写非分页查询语句就行，然后通过分页拦截器，拦截到需要分页查询的普通sql

将普通的sql替换成分页sql，非常巧妙的实现分页查询

1.实现拦截器我们需实现Interceptor

我们假如将拦截器形象的比喻成够票与代理商

a.@Intercepts注解声明了代够员取哪里拦截需要买票的顾客（去哪里拦截顾客）

b.setProperties获取Configuration.xml中plugins--》plugin--->propertis中的固定资本（公司固定资本）

c.plugin方法中判断是否需要拦截（顾客是否需要购票）

d.intercept方法中可以获取原始的sql(非分页查询的sql),和分页参数   通过取到这些参数替换掉原来的sql即编程分页sql(代购员从顾客哪里获取信息)

package com.imooc.interceptor;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Map;
import java.util.Properties;

import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.Configuration;

import com.imooc.common.Page;

/**
 * myBatis分页拦截器实现
 * 拦截器在配置文件Configuration.xml中注册后，通过下边的注解声明拦截的类，方法，方法参数
 * */
//type指向需要拦截的接口，method指向需要拦截的方法，args指向方法中的参数
//StatementHandler是一个接口，实现类是BaseStatementHanlder，实现了其中的prepare方法，实现方法中instantiateStatement(connection)
//返回Statement
@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})})
public class PageInterceptor implements Interceptor{
	private Object object;
	private static String defualtSqlId = ".+ByPage$";  //需要拦截的配置文件中的id的正则表达式
	private static String defaultDialect = "oracle";   //默认数据库类型
	private String dialect;     			//数据库类型
	
	/**
	 * 3.代购
	 * 可以指定拦截映射文件中那种id的值，
	 * invocation中可以获取想要的数据
	 * */
	@Override
	public Object intercept(Invocation invocation) throws Throwable {
		//代理业务员获取代理对象的携带的信息（需要买票的人哪里获取信息）
		StatementHandler statementHandler =	(StatementHandler)invocation.getTarget();
		//使用metaObject中获取statementHandler属性的值
		MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY);
		//BaseStatementHandler-->mappedStatement(delegate为<span style="font-family: Arial, Helvetica, sans-serif;">mappedStatement属性名</span><span style="font-family: Arial, Helvetica, sans-serif;">)</span>
		MappedStatement mappedStatement=(MappedStatement) metaObject.getValue("delegate.mappedStatement");
	       Configuration configuration = (Configuration) metaObject.getValue("delegate.configuration");
		//获取数据库类型，获取Configuration.xml properties标签中声明的变量
	    dialect = configuration.getVariables().getProperty("dialect");
	    if (null == dialect || "".equals(dialect)) {
            dialect = defaultDialect;
        }
		//获取mapper文件中sql语句的id
		String pageSqlId = mappedStatement.getId();
		if(pageSqlId.matches(defualtSqlId)){//获取已Bypage结尾的id中的sql语句    		拦截谁
			BoundSql boundSql = statementHandler.getBoundSql();
			//获取原始的sql
			String sql = boundSql.getSql();
			//查询总条数
			String countSql = "select count(*) from ("+sql+") a";
			Connection connection = (Connection) invocation.getArgs()[0];
			PreparedStatement countStatement = connection.prepareStatement(countSql);
			//获取原始的sql参数信息
			ParameterHandler parameterHandler =  (ParameterHandler) metaObject.getValue("delegate.parameterHandler");
			//设置参数值
			parameterHandler.setParameters(countStatement);
			//执行获取总条数
			ResultSet rs = countStatement.executeQuery();
			
			//改造成带分页查询的sql，获取传给配置文件的参数,就是MessageDao中queryMessageListByPage中方法执行中传递进去的map参数，所以需要强转
			Map<String, Object> parameter = (Map<String, Object>) boundSql.getParameterObject();
			//paramter(map)中传入的page对象的参数
			Page page = (Page)parameter.get("page"); 
			//设置page的总条数
			if(rs.next()){
				page.setTotalNumber(rs.getInt(1));
			}
			//改造成带分页查询的sql
			String pageSql = buildPageSql(sql,page);
			//替换员来的sql
			//第一参数：找到源码中的sql
			//第二个参数：改造后的sql
			metaObject.setValue("delegate.boundSql.sql", pageSql);   		//买票
		}
		return invocation.proceed();   //交回主权，买票后送回
	}

	/**
	 * 2.定位客户  @Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})})
	 * 如果拦截成功，会返回一个代理类（业务员），然后会执行intercept方法
	 * 就好比拦截到买票的人，需不需要买票，不需要放行，return就是放行
	 * 需要就代表一种协议Plugin.wrap(target, this);
	 * target就好比拦截住的需要买票的人,this就是公司的业务员，返回后this就变成可以代理target去买票的业务员
	 * */
	@Override
	public Object plugin(Object target) {
		return Plugin.wrap(target, this);
	}

	/**
	 * 1.获取固定资本
	 * 获取Configuration.xml中的plugin中的property中的属性值
	 * */
	@Override
	public void setProperties(Properties properties) {
		this.object = properties.get("test"); 
	}
	
	/**
	 * 改造成带分页查询的sql
	 * sql原始sql(不带分页功能)
	 * Page page分页实体类对象
	 **/
	public String buildPageSql(String sql,Page page){
		StringBuilder builder = new StringBuilder();
		if(dialect.equals("oracle")){
			builder = pageSqlForOracle(sql, page);
		}
		if(dialect.equals("mysql")){
			pageSqlForMysql(sql, page);
		}
		return builder.toString();
	}
	//mysql分页查询
	public StringBuilder pageSqlForMysql(String sql,Page page){
		StringBuilder builder = new StringBuilder();
		builder.append(sql);
		builder.append(" limit "+page.getDbIndex()+","+page.getDbNumber());
		builder.append(" order by id");
		return builder;
	}

	/**
	 * 实现oracle分页
	 * */
	public StringBuilder pageSqlForOracle(String sql,Page page){
		StringBuilder builder = new StringBuilder();
		builder.append("select * from ( select rownum r, id,command,description,content from (");
		builder.append(sql);
		builder.append(") where rownum <= ").append(page.getDbNumber()+page.getDbIndex());
		builder.append(") where r > ").append(page.getDbIndex());
		builder.append(" order by id");
		System.out.println(builder.toString());
		return builder;
	}
}

Configuration.xml中注册分页拦截器插件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
    PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
	<properties>
		<property name="dialect" value="oracle" />
	</properties>
	<!-- 拦截器可以有很多，就像代购公司有很多每一个公司都需要注册，下边就是注册 -->
	<plugins>
		<plugin interceptor="com.imooc.interceptor.PageInterceptor">
			<property name="test" value="interceptor" /><!-- property中的值可以在拦截器中的setProperties中拿到 -->
		</plugin>
	</plugins>

	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC">
				<property name="" value="" />
			</transactionManager>
			<dataSource type="UNPOOLED">
				<property name="driver" value="oracle.jdbc.driver.OracleDriver" />
				<!-- <property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"/> -->
				<property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" />
				<property name="username" value="caojx" />
				<property name="password" value="caojx" />
			</dataSource>
		</environment>
	</environments>

	<mappers>
		<mapper resource="com/imooc/config/sqlxml/Message.xml" />
	</mappers>

</configuration>

Message.xml变化

不需要写分语句

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

<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.imooc.bean.Message">

  <resultMap type="com.imooc.bean.Message" id="MessageResult">
    <id column="id" jdbcType="INTEGER" property="id"/>
    <result column="command" jdbcType="VARCHAR" property="command"/>
    <result column="description" jdbcType="VARCHAR" property="description"/>
    <result column="content" jdbcType="VARCHAR" property="content"/>
  </resultMap>

<!-- select标签用于书写查询语句-->
  <select id="queryMessageListByPage" parameterType="java.util.Map" resultMap="MessageResult">
    select id,command,description,content from message
    <where>
    	<if test="command != null and !"".equals(command.trim())">
	    	and command=#{command}
	    </if>
	    <!-- like 查询一般会拼接concat()拼接两个字符串 -->
	    <if test="description != null and ''!=description.trim()">
	    	and description like concat(concat('%',#{description}),'%')
	    </if>
    </where>
  </select>
</mapper>

ListServlet.java中的一小点变化

package com.imooc.servlet;

import java.io.IOException;
import java.util.List;
import java.util.regex.Pattern;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.imooc.bean.Message;
import com.imooc.common.Page;
import com.imooc.service.MessageService;

public class ListServlet extends HttpServlet {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	MessageService messageService = new MessageService();

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) {
		try {
			// 设置编码
			req.setCharacterEncoding("utf-8");
			// 接受参数
			String command = req.getParameter("command");
			String description = req.getParameter("description");
			String currentPage = req.getParameter("currentPage");
			//创建分页对象
			Page page = new Page();	
			Pattern patttern = Pattern.compile("[0-9]{1,9}"); 
			if(currentPage == null || !patttern.matcher(currentPage).matches()){
				page.setCurrentPage(1);
			}else {
				page.setCurrentPage(Integer.parseInt(currentPage));
			}
			List<Message> messageList = messageService.queryMessageListByPage(command, description, page);
			req.setAttribute("command", command);
			req.setAttribute("description", description);
			req.setAttribute("messages", messageList);
			req.setAttribute("page", page);
			// 向页面跳转
			req.getRequestDispatcher("/WEB-INF/jsp/back/list.jsp").forward(req,resp);
		} catch (Exception e) {
			e.printStackTrace();
			req.setAttribute("retMsg", "查询失败");
		}
	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		doGet(req, resp);
	}

}

结果图

注：本案例思想有慕课网提供指导