一、引言
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);
}
}
结果图
注:本案例思想有慕课网提供指导