MyBatis分页插件的配置

MyBatis拦截器介绍

MyBatis提供了一种插件(plugin)的功能，虽然叫做插件，但其实这是拦截器功能。
拦截器的一个作用就是我们可以拦截某些方法的调用，可以在这些方法被调用前后加上某些自己的业务逻辑。
对于拦截器Mybatis为我们提供了一个Interceptor接口，通过实现该接口就可以定义我们自己的拦截器。

Mybatis拦截器Interceptor接口

官方源码如下：

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//

package org.apache.ibatis.plugin;

import java.util.Properties;
import org.apache.ibatis.plugin.Invocation;

public interface Interceptor {
    Object intercept(Invocation var1) throws Throwable;

    Object plugin(Object var1);

    void setProperties(Properties var1);
}

plugin方法是拦截器用于封装目标对象的，通过这个方法我们可以返回目标对象本身，也可以返回他的一个代理，当返回代理是我们可以对其中的方法进行拦截来调用intercept方法，setProperties方法是用于在Mybatis配置文件中指定一些属性的。

Interceptor的注解

@Intercepts，其值是一个@Signature数组。@Intercepts用于表明当前的对象是一个Interceptor，而@Signature则表明要拦截的接口、方法以及对应的参数类型。

Mybatis拦截器的实际应用

Mybatis拦截器通常会被用来进行分页处理，JDBC要对数据库操作就必须有一个对应的Statement对象，Mybatis在执行sql语句之前也会产生一个包含sql语句的Statement对象，并且sql语句是在statement之前产生的，所以我们就需要在他生成statement之前对sql语句加上自己的处理。在mybatis中statement语句是通过RoutingStatementHandler对象的prepare方法生成的。

所以利用拦截器思想MyBatis分页的思路就是拦截StatementHandler接口的prepare方法，然后在拦截的方法中把sql语句改成对应的分页查询sql语句，之后再调用StatementHandler接口的prepare方法，即调用即调用invocation.proceed()。

由于在更改sql语句时sql等其他属性都没有对应的方法可以直接获取，他们对外部都是封闭的，是对象的私有属性，所以就用用到反射机制来获取或更改私有属性的值。

在拦截器中我们还需要做的就是统计当前记录的总数，这是通过获取到原始的sql之后把他改为对应的统计语句在利用mybatis封装好的参数和设置参数的功能把sql语句中的sql语句进行替换。

1.封装page实体对象

package com.fh.entity;

import com.fh.util.Const;
import com.fh.util.PageData;
import com.fh.util.Tools;

public class Page {

    private int showCount;      //每页显示记录数
    private int totalPage;      //总页数
    private int totalResult;    //总记录数
    private int currentPage;    //当前页
    private int currentResult;  //当前记录起始索引
    private boolean entityOrField;  //true:需要分页的地方，传入的参数就是Page实体；false:需要分页的地方，传入的参数所代表的实体拥有Page属性
    private String pageStr;     //最终页面显示的底部翻页导航，详细见：getPageStr();
    private PageData pd = new PageData();



    public Page(){
        try {
            this.showCount = Integer.parseInt(Tools.readTxtFile(Const.PAGE));
        } catch (Exception e) {
            this.showCount = 15;
        }
    }

    public int getTotalPage() {
        if(totalResult%showCount==0)
            totalPage = totalResult/showCount;
        else
            totalPage = totalResult/showCount+1;
        return totalPage;
    }

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

    public int getTotalResult() {
        return totalResult;
    }

    public void setTotalResult(int totalResult) {
        this.totalResult = totalResult;
    }

    public int getCurrentPage() {
        if(currentPage<=0)
            currentPage = 1;
        if(currentPage>getTotalPage())
            currentPage = getTotalPage();
        return currentPage;
    }

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

    public String getPageStr() {
        StringBuffer sb = new StringBuffer();
        if(totalResult>0){
            sb.append(" <ul>\n");
            if(currentPage==1){
                sb.append(" <li><a>共<font color=red>"+totalResult+"</font>条</a></li>\n");
                sb.append(" <li><input type=\"number\" value=\"\" id=\"toGoPage\" style=\"width:50px;text-align:center;float:left\" placeholder=\"页码\"/></li>\n");
                sb.append(" <li style=\"cursor:pointer;\"><a onclick=\"toTZ();\"  class=\"btn btn-mini btn-success\">跳转</a></li>\n");
                sb.append(" <li><a>首页</a></li>\n");
                sb.append(" <li><a>上页</a></li>\n");
            }else{
                sb.append(" <li><a>共<font color=red>"+totalResult+"</font>条</a></li>\n");
                sb.append(" <li><input type=\"number\" value=\"\" id=\"toGoPage\" style=\"width:50px;text-align:center;float:left\" placeholder=\"页码\"/></li>\n");
                sb.append(" <li style=\"cursor:pointer;\"><a onclick=\"toTZ();\"  class=\"btn btn-mini btn-success\">跳转</a></li>\n");
                sb.append(" <li style=\"cursor:pointer;\"><a onclick=\"nextPage(1)\">首页</a></li>\n");
                sb.append(" <li style=\"cursor:pointer;\"><a onclick=\"nextPage("+(currentPage-1)+")\">上页</a></li>\n");
            }
            int showTag = 5;//分页标签显示数量
            int startTag = 1;
            if(currentPage>showTag){
                startTag = currentPage-1;
            }
            int endTag = startTag+showTag-1;
            for(int i=startTag; i<=totalPage && i<=endTag; i++){
                if(currentPage==i)
                    sb.append("<li><a><font color='#808080'>"+i+"</font></a></li>\n");
                else
                    sb.append(" <li style=\"cursor:pointer;\"><a onclick=\"nextPage("+i+")\">"+i+"</a></li>\n");
            }
            if(currentPage==totalPage){
                sb.append(" <li><a>下页</a></li>\n");
                sb.append(" <li><a>尾页</a></li>\n");
            }else{
                sb.append(" <li style=\"cursor:pointer;\"><a onclick=\"nextPage("+(currentPage+1)+")\">下页</a></li>\n");
                sb.append(" <li style=\"cursor:pointer;\"><a onclick=\"nextPage("+totalPage+")\">尾页</a></li>\n");
            }
            sb.append(" <li><a>第"+currentPage+"页</a></li>\n");
            sb.append(" <li><a>共"+totalPage+"页</a></li>\n");


            sb.append(" <li><select title='显示条数' style=\"width:55px;float:left;\" onchange=\"changeCount(this.value)\">\n");
            sb.append(" <option value='"+showCount+"'>"+showCount+"</option>\n");
            sb.append(" <option value='10'>10</option>\n");
            sb.append(" <option value='20'>20</option>\n");
            sb.append(" <option value='30'>30</option>\n");
            sb.append(" <option value='40'>40</option>\n");
            sb.append(" <option value='50'>50</option>\n");
            sb.append(" <option value='60'>60</option>\n");
            sb.append(" <option value='70'>70</option>\n");
            sb.append(" <option value='80'>80</option>\n");
            sb.append(" <option value='90'>90</option>\n");
            sb.append(" <option value='99'>99</option>\n");
            sb.append(" </select>\n");
            sb.append(" </li>\n");



            sb.append("</ul>\n");
            sb.append("<script type=\"text/javascript\">\n");

            //换页函数
            sb.append("function nextPage(page){");
            sb.append(" top.jzts();");
            sb.append(" if(true && document.forms[0]){\n");
            sb.append("     var url = document.forms[0].getAttribute(\"action\");\n");
            sb.append("     if(url.indexOf('?')>-1){url += \"&"+(entityOrField?"currentPage":"page.currentPage")+"=\";}\n");
            sb.append("     else{url += \"?"+(entityOrField?"currentPage":"page.currentPage")+"=\";}\n");
            sb.append("     url = url + page + \"&" +(entityOrField?"showCount":"page.showCount")+"="+showCount+"\";\n");
            sb.append("     document.forms[0].action = url;\n");
            sb.append("     document.forms[0].submit();\n");
            sb.append(" }else{\n");
            sb.append("     var url = document.location+'';\n");
            sb.append("     if(url.indexOf('?')>-1){\n");
            sb.append("         if(url.indexOf('currentPage')>-1){\n");
            sb.append("             var reg = /currentPage=\\d*/g;\n");
            sb.append("             url = url.replace(reg,'currentPage=');\n");
            sb.append("         }else{\n");
            sb.append("             url += \"&"+(entityOrField?"currentPage":"page.currentPage")+"=\";\n");
            sb.append("         }\n");
            sb.append("     }else{url += \"?"+(entityOrField?"currentPage":"page.currentPage")+"=\";}\n");
            sb.append("     url = url + page + \"&" +(entityOrField?"showCount":"page.showCount")+"="+showCount+"\";\n");
            sb.append("     document.location = url;\n");
            sb.append(" }\n");
            sb.append("}\n");

            //调整每页显示条数
            sb.append("function changeCount(value){");
            sb.append(" top.jzts();");
            sb.append(" if(true && document.forms[0]){\n");
            sb.append("     var url = document.forms[0].getAttribute(\"action\");\n");
            sb.append("     if(url.indexOf('?')>-1){url += \"&"+(entityOrField?"currentPage":"page.currentPage")+"=\";}\n");
            sb.append("     else{url += \"?"+(entityOrField?"currentPage":"page.currentPage")+"=\";}\n");
            sb.append("     url = url + \"1&" +(entityOrField?"showCount":"page.showCount")+"=\"+value;\n");
            sb.append("     document.forms[0].action = url;\n");
            sb.append("     document.forms[0].submit();\n");
            sb.append(" }else{\n");
            sb.append("     var url = document.location+'';\n");
            sb.append("     if(url.indexOf('?')>-1){\n");
            sb.append("         if(url.indexOf('currentPage')>-1){\n");
            sb.append("             var reg = /currentPage=\\d*/g;\n");
            sb.append("             url = url.replace(reg,'currentPage=');\n");
            sb.append("         }else{\n");
            sb.append("             url += \"1&"+(entityOrField?"currentPage":"page.currentPage")+"=\";\n");
            sb.append("         }\n");
            sb.append("     }else{url += \"?"+(entityOrField?"currentPage":"page.currentPage")+"=\";}\n");
            sb.append("     url = url + \"&" +(entityOrField?"showCount":"page.showCount")+"=\"+value;\n");
            sb.append("     document.location = url;\n");
            sb.append(" }\n");
            sb.append("}\n");

            //跳转函数 
            sb.append("function toTZ(){");
            sb.append("var toPaggeVlue = document.getElementById(\"toGoPage\").value;");
            sb.append("if(toPaggeVlue == ''){document.getElementById(\"toGoPage\").value=1;return;}");
            sb.append("if(isNaN(Number(toPaggeVlue))){document.getElementById(\"toGoPage\").value=1;return;}");
            sb.append("nextPage(toPaggeVlue);");
            sb.append("}\n");
            sb.append("</script>\n");
        }
        pageStr = sb.toString();
        return pageStr;
    }

    public void setPageStr(String pageStr) {
        this.pageStr = pageStr;
    }

    public int getShowCount() {
        return showCount;
    }

    public void setShowCount(int showCount) {

        this.showCount = showCount;
    }

    public int getCurrentResult() {
        currentResult = (getCurrentPage()-1)*getShowCount();
        if(currentResult<0)
            currentResult = 0;
        return currentResult;
    }

    public void setCurrentResult(int currentResult) {
        this.currentResult = currentResult;
    }

    public boolean isEntityOrField() {
        return entityOrField;
    }

    public void setEntityOrField(boolean entityOrField) {
        this.entityOrField = entityOrField;
    }

    public PageData getPd() {
        return pd;
    }

    public void setPd(PageData pd) {
        this.pd = pd;
    }

}

2.写一个反射工具类

package com.fh.util;

import java.lang.reflect.Field;

/**
 *  反射工具
 */
public class ReflectHelper {
    /**
     * 获取obj对象fieldName的Field
     * @param obj
     * @param fieldName
     * @return
     */
    public static Field getFieldByFieldName(Object obj, String fieldName) {
        for (Class<?> superClass = obj.getClass(); superClass != Object.class; superClass = superClass
                .getSuperclass()) {
            try {
                return superClass.getDeclaredField(fieldName);
            } catch (NoSuchFieldException e) {
            }
        }
        return null;
    }

    /**
     * 获取obj对象fieldName的属性值
     * @param obj
     * @param fieldName
     * @return
     * @throws SecurityException
     * @throws NoSuchFieldException
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    public static Object getValueByFieldName(Object obj, String fieldName)
            throws SecurityException, NoSuchFieldException,
            IllegalArgumentException, IllegalAccessException {
        Field field = getFieldByFieldName(obj, fieldName);
        Object value = null;
        if(field!=null){
            if (field.isAccessible()) {
                value = field.get(obj);
            } else {
                field.setAccessible(true);
                value = field.get(obj);
                field.setAccessible(false);
            }
        }
        return value;
    }

    /**
     * 设置obj对象fieldName的属性值
     * @param obj
     * @param fieldName
     * @param value
     * @throws SecurityException
     * @throws NoSuchFieldException
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    public static void setValueByFieldName(Object obj, String fieldName,
            Object value) throws SecurityException, NoSuchFieldException,
            IllegalArgumentException, IllegalAccessException {
        Field field = obj.getClass().getDeclaredField(fieldName);
        if (field.isAccessible()) {
            field.set(obj, value);
        } else {
            field.setAccessible(true);
            field.set(obj, value);
            field.setAccessible(false);
        }
    }
}

3.分页拦截器的实现 PagePlugin.java

在需要的分页的mapper中，我们传递一个page对象作为参数。

package com.fh.plugin;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

import javax.xml.bind.PropertyException;

import org.apache.ibatis.executor.ErrorContext;
import org.apache.ibatis.executor.ExecutorException;
import org.apache.ibatis.executor.statement.BaseStatementHandler;
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.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
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.property.PropertyTokenizer;
import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;

import com.fh.entity.Page;
import com.fh.util.ReflectHelper;
import com.fh.util.Tools;
/**
 * 
* 类名称：PagePlugin.java
* 类描述：分页拦截器
 *
 */
@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})})
public class PagePlugin implements Interceptor {

    private static String dialect = ""; //数据库方言，不同的数据库有不同的分页方法
    private static String pageSqlId = ""; //mapper.xml中需要拦截的ID(正则匹配)

    /**
     * 拦截后要执行的方法
     */
    public Object intercept(Invocation ivk) throws Throwable {
        if(ivk.getTarget() instanceof RoutingStatementHandler){
            //拦截到的目标对象：RoutingStatementHandler对象
            RoutingStatementHandler statementHandler = (RoutingStatementHandler)ivk.getTarget();
            //通过反射获取到当前RoutingStatementHandler对象的delegate属性
            BaseStatementHandler delegate = (BaseStatementHandler) ReflectHelper.getValueByFieldName(statementHandler, "delegate");
            //通过反射获取delegate父类BaseStatementHandler的mappedStatement属性
            MappedStatement mappedStatement = (MappedStatement) ReflectHelper.getValueByFieldName(delegate, "mappedStatement");

            if(mappedStatement.getId().matches(pageSqlId)){ //拦截需要分页的SQL
                //获取到当前StatementHandler的 boundSql，这里不管是调用handler.getBoundSql()还是直接调用delegate.getBoundSql()结果是一样的
                BoundSql boundSql = delegate.getBoundSql();
               //拿到当前绑定Sql的参数对象，就是我们在调用对应的Mapper映射语句时所传入的参数对象
                Object parameterObject = boundSql.getParameterObject();//分页SQL<select>中parameterType属性对应的实体参数，即Mapper接口中执行分页方法的参数,该参数不得为空
                if(parameterObject==null){
                    throw new NullPointerException("parameterObject尚未实例化！");
                }else{
                    //拦截到的prepare方法参数是一个Connection对象
                    Connection connection = (Connection) ivk.getArgs()[0];
                    //获取当前要执行的Sql语句，也就是我们直接在Mapper映射语句中写的Sql语句
                    String sql = boundSql.getSql();
                    //String countSql = "select count(0) from (" + sql+ ") as tmp_count"; //记录统计
                    String countSql = "select count(0) from (" + sql+ ")  tmp_count"; //记录统计 == oracle 加 as 报错(SQL command not properly ended)
                    //记录统计
                    PreparedStatement countStmt = connection.prepareStatement(countSql);
                    BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(),countSql,boundSql.getParameterMappings(),parameterObject);
                    setParameters(countStmt,mappedStatement,countBS,parameterObject);
                    ResultSet rs = countStmt.executeQuery();
                    int count = 0;
                    if (rs.next()) {
                        count = rs.getInt(1);
                    }
                    rs.close();
                    countStmt.close();
                    //System.out.println(count);
                    Page page = null;
                    if(parameterObject instanceof Page){    //参数就是Page实体
                         page = (Page) parameterObject;
                         page.setEntityOrField(true);
                        //给当前的page参数对象设置总记录数
                         page.setTotalResult(count);
                    }else{  //参数为某个实体，该实体拥有Page属性
                        Field pageField = ReflectHelper.getFieldByFieldName(parameterObject,"page");
                        if(pageField!=null){
                            page = (Page) ReflectHelper.getValueByFieldName(parameterObject,"page");
                            if(page==null)
                                page = new Page();
                            page.setEntityOrField(false);
                            //给当前的page参数对象设置总记录数
                            page.setTotalResult(count);
                            ReflectHelper.setValueByFieldName(parameterObject,"page", page); //通过反射，对实体对象设置分页对象
                        }else{
                            throw new NoSuchFieldException(parameterObject.getClass().getName()+"不存在 page 属性！");
                        }
                    }
                    //获取分页Sql语句
                    String pageSql = generatePageSql(sql,page);
                    //利用反射设置当前BoundSql对应的sql属性为我们建立好的分页Sql语句
                    ReflectHelper.setValueByFieldName(boundSql, "sql", pageSql); //将分页sql语句反射回BoundSql.
                }
            }
        }
        return ivk.proceed();
    }


    /**
     * 对SQL参数(?)设值,参考org.apache.ibatis.executor.parameter.DefaultParameterHandler
     * @param ps
     * @param mappedStatement
     * @param boundSql
     * @param parameterObject
     * @throws SQLException
     */
    private void setParameters(PreparedStatement ps,MappedStatement mappedStatement,BoundSql boundSql,Object parameterObject) throws SQLException {
        ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        if (parameterMappings != null) {
            Configuration configuration = mappedStatement.getConfiguration();
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            MetaObject metaObject = parameterObject == null ? null: configuration.newMetaObject(parameterObject);
            for (int i = 0; i < parameterMappings.size(); i++) {
                ParameterMapping parameterMapping = parameterMappings.get(i);
                if (parameterMapping.getMode() != ParameterMode.OUT) {
                    Object value;
                    String propertyName = parameterMapping.getProperty();
                    PropertyTokenizer prop = new PropertyTokenizer(propertyName);
                    if (parameterObject == null) {
                        value = null;
                    } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                        value = parameterObject;
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        value = boundSql.getAdditionalParameter(propertyName);
                    } else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX)&& boundSql.hasAdditionalParameter(prop.getName())) {
                        value = boundSql.getAdditionalParameter(prop.getName());
                        if (value != null) {
                            value = configuration.newMetaObject(value).getValue(propertyName.substring(prop.getName().length()));
                        }
                    } else {
                        value = metaObject == null ? null : metaObject.getValue(propertyName);
                    }
                    TypeHandler typeHandler = parameterMapping.getTypeHandler();
                    if (typeHandler == null) {
                        throw new ExecutorException("There was no TypeHandler found for parameter "+ propertyName + " of statement "+ mappedStatement.getId());
                    }
                    typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType());
                }
            }
        }
    }

    /**
     * 根据数据库方言，生成特定的分页sql
     * @param sql
     * @param page
     * @return
     */
    private String generatePageSql(String sql,Page page){
        if(page!=null && Tools.notEmpty(dialect)){
            StringBuffer pageSql = new StringBuffer();
            if("mysql".equals(dialect)){
                pageSql.append(sql);
                pageSql.append(" limit "+page.getCurrentResult()+","+page.getShowCount());
            }else if("oracle".equals(dialect)){
                pageSql.append("select * from (select tmp_tb.*,ROWNUM row_id from (");
                pageSql.append(sql);
                //pageSql.append(") as tmp_tb where ROWNUM<=");
                pageSql.append(") tmp_tb where ROWNUM<=");
                pageSql.append(page.getCurrentResult()+page.getShowCount());
                pageSql.append(") where row_id>");
                pageSql.append(page.getCurrentResult());
            }
            return pageSql.toString();
        }else{
            return sql;
        }
    }

    /**
     * 拦截器对应的封装原始对象的方法
     * @param arg0
     * @return
     */
    public Object plugin(Object arg0) {
        return Plugin.wrap(arg0, this);
    }

    /**
     * 设置注册拦截器时设定的属性
     * @param p
     */
    public void setProperties(Properties p) {
        dialect = p.getProperty("dialect");
        if (Tools.isEmpty(dialect)) {
            try {
                throw new PropertyException("dialect property is not found!");
            } catch (PropertyException e) {
                e.printStackTrace();
            }
        }
        pageSqlId = p.getProperty("pageSqlId");
        if (Tools.isEmpty(pageSqlId)) {
            try {
                throw new PropertyException("pageSqlId property is not found!");
            } catch (PropertyException e) {
                e.printStackTrace();
            }
        }
    }

}

4.Mybatis配置文件中配置数据库方言和拦截ID

    <!--分页插件-->
    <plugins>
        <plugin interceptor="com.fh.plugin.PagePlugin">
            <property name="dialect" value="mysql"/><!--数据库方言-->
            <property name="pageSqlId" value=".*listPage.*"/><!--*listPage这样格式的写法，会自动按分页处理-->
        </plugin>
    </plugins>