mybatis3.1分页自动添加总数

问题

1.mybatis默认分页是内存分页的，谁用谁崩溃啊！
  类似于下面的DAO签名方法，只要有RowBounds入参，Mybatis即会自动内存分页：

    @Select("SELECT * FROM cartan_common.t_app s WHERE s.author = #{param.author}")
    ArrayList<App> queryList(@Param("param")AppQueryParam appQueryParam,RowBounds rowBounds);

   我们必须将其转换为物理分页，也即数据库分页。

2.分页一般都需要自动计算出总行数，而在mybatis中，你必须手动发起两次请求，烦人。

解决思路

1.Mybatis的拦截器是我们动手动脚的地方

  Mybatis的架构是非常漂亮的，它允许对多个接口的多个方法定义拦截器（Interceptor）,以下是Mybatis的调用粗线：


    我们不但可以对Excutor的方法编写插件，还可以对StatementHandler或ResultSetHandler的方法编写插件。以下是一个Mybatis的插件：

package com.ridge.dao.mybatis;

import org.apache.commons.lang.reflect.FieldUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.ibatis.executor.statement.PreparedStatementHandler;
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.ParameterMapping;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.RowBounds;
import org.hibernate.dialect.Dialect;

import java.sql.Connection;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 默认情况下
 * 数据库的类型
 *
 * @author : chenxh
 * @date: 13-7-5
 */
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})
public class PaginationInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
       ...
    }
}

    注意PaginationInterceptor类的@Intercepts注解，如上所示，它将对StatementHandler的prepare(Connection connection)方法进行拦截。

2.怎样将mybatis的语句转换为分页的语句呢
  
    这得求助Hibernate的org.hibernate.dialect.Dialect接口及其实现类。我们知道不同数据库分页的方法是不一样的。mysql是limit x,y,而Oracle要用一个子查询，使用rownum来做到。Hibernater的org.hibernate.dialect.Dialect的以下方法可以非常方便地让我们做到这点：

getLimitString(originalSql, rowBounds.getOffset(), rowBounds.getLimit())

   以下就是使用该方法完成的转换：

引用

SELECT * FROM cartan_common.t_app s WHERE s.author = ?
对应的分页SQL:
SELECT * FROM cartan_common.t_app s WHERE s.author = ? limit ?, ?

3.怎样生成SQL对应的总条数SQL呢？  

   通过以下的类的MybatisHepler.getCountSql(originalSql)方法即可：

package com.ridge.dao.mybatis;


import com.ridge.dao.Paging;
import org.apache.ibatis.session.RowBounds;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class MybatisHepler {

    /**
     * 获取查询总数对应的SQL
     * @param querySelect
     * @return
     */
    public static String getCountSql(String querySelect) {

        querySelect = compress(querySelect);
        int orderIndex = getLastOrderInsertPoint(querySelect);

        int formIndex = getAfterFormInsertPoint(querySelect);
        String select = querySelect.substring(0, formIndex);

        //如果SELECT 中包含 DISTINCT 只能在外层包含COUNT
        if (select.toLowerCase().indexOf("select distinct") != -1 || querySelect.toLowerCase().indexOf("group by") != -1) {
            return new StringBuffer(querySelect.length()).append(
                    "select count(1) count from (").append(
                    querySelect.substring(0, orderIndex)).append(" ) t")
                    .toString();
        } else {
            return new StringBuffer(querySelect.length()).append(
                    "select count(1) count ").append(
                    querySelect.substring(formIndex, orderIndex)).toString();
        }
    }

    /**
     * 得到最后一个Order By的插入点位置
     *
     * @return 返回最后一个Order By插入点的位置
     */
    private static int getLastOrderInsertPoint(String querySelect) {
        int orderIndex = querySelect.toLowerCase().lastIndexOf("order by");
        if (orderIndex == -1) {
            orderIndex = querySelect.length();
        }else{
           if(!isBracketCanPartnership(querySelect.substring(orderIndex, querySelect.length()))){
               throw new RuntimeException("My SQL 分页必须要有Order by 语句!");
           }
        }
        return orderIndex;
    }

    /**
     * 将{@code paging}转换为{@link org.apache.ibatis.session.RowBounds}对象
     * @param paging
     * @return
     */
    public static final RowBounds toRowBounds(Paging paging){
        return new RowBounds(paging.getRowOffset(),paging.getPageSize());
    }

    /**
     * 得到分页的SQL
     *
     * @param offset 偏移量
     * @param limit  位置
     * @return 分页SQL
     */
    public static String getPagingSql(String querySelect, int offset, int limit) {

        querySelect = compress(querySelect);

        String sql = querySelect.replaceAll("[^\\s,]+\\.", "") + " limit " + offset + " ," + limit;

        return sql;

    }

    /**
     * 将SQL语句压缩成一条语句，并且每个单词的间隔都是1个空格
     * @param sql SQL语句
     * @return 如果sql是NULL返回空，否则返回转化后的SQL
     */
    private static String compress(String sql) {
        return sql.replaceAll("[\r\n]", " ").replaceAll("\\s{2,}", " ");
    }

    /**
     * 得到SQL第一个正确的FROM的的插入点
     */
    private static int getAfterFormInsertPoint(String querySelect) {
        String regex = "\\s+FROM\\s+";
        Pattern pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(querySelect);
        while (matcher.find()) {
            int fromStartIndex = matcher.start(0);
            String text = querySelect.substring(0, fromStartIndex);
            if (isBracketCanPartnership(text)) {
                return fromStartIndex;
            }
        }
        return 0;
    }

    /**
     * 判断括号"()"是否匹配,并不会判断排列顺序是否正确
     *
     * @param text 要判断的文本
     * @return 如果匹配返回TRUE, 否则返回FALSE
     */
    private static boolean isBracketCanPartnership(String text) {
        if (text == null
                || (getIndexOfCount(text, '(') != getIndexOfCount(text, ')'))) {
            return false;
        }
        return true;
    }

    /**
     * 得到一个字符在另一个字符串中出现的次数
     *
     * @param text 文本
     * @param ch   字符
     */
    private static int getIndexOfCount(String text, char ch) {
        int count = 0;
        for (int i = 0; i < text.length(); i++) {
            count = (text.charAt(i) == ch) ? count + 1 : count;
        }
        return count;
    }
}

  好了，下面给出PaginationInterceptor的完整代码：

package com.ridge.dao.mybatis;

import org.apache.commons.lang.reflect.FieldUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.ibatis.executor.statement.PreparedStatementHandler;
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.ParameterMapping;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.RowBounds;
import org.hibernate.dialect.Dialect;

import java.sql.Connection;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 默认情况下
 * 数据库的类型
 *
 * @author : chenxh
 * @date: 13-7-5
 */
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})
public class PaginationInterceptor implements Interceptor {

    private final static Log logger = LogFactory.getLog(PaginationInterceptor.class);
    public static final String CONFIGURATION = "configuration";
    private static Dialect dialect = null;
    private static final String ROW_BOUNDS = "rowBounds";
    private static final String BOUND_SQL = "boundSql";
    private static final String DIALECT = "dialect";
    private static final String SQL = "sql";
    private static final String OFFSET = "offset";
    private static final String LIMIT = "limit";
    public static final String DELEGATE = "delegate";
    private static final int CONNECTION_INDEX = 0;

    private static final String INTERCEPTOR_CONF = "<plugins>\n" +
            "<plugin interceptor=\"" + PaginationInterceptor.class.getCanonicalName() + "\">\n" +
            "<property name=\"dialect\" value=\"" + DialetHelper.getSupportDatabaseTypes() + "\"/>\n" +
            "</plugin>\n" +
            "</plugins>";

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        RoutingStatementHandler statementHandler = (RoutingStatementHandler) invocation.getTarget();
        PreparedStatementHandler preparedStatHandler =
                (PreparedStatementHandler) FieldUtils.readField(statementHandler, DELEGATE, true);
        final Object[] queryArgs = invocation.getArgs();
        Connection connection = (Connection) queryArgs[CONNECTION_INDEX];

        RowBounds rowBounds = (RowBounds) FieldUtils.readField(preparedStatHandler, ROW_BOUNDS, true);
        BoundSql boundSql = (BoundSql) FieldUtils.readField(preparedStatHandler, BOUND_SQL, true);

        Configuration configuration = (Configuration) FieldUtils.readField(preparedStatHandler, CONFIGURATION, true);

        //没有分页，直接返回原调用
        if (rowBounds == null || rowBounds == RowBounds.DEFAULT) {
            return invocation.proceed();
        }

        //有分页
        String originalSql = boundSql.getSql();

        //1.获取总行数，将行数绑定到当前线程中
        String countSql = MybatisHepler.getCountSql(originalSql);
        CountHelper.getCount(countSql, preparedStatHandler, configuration, boundSql, connection);


        //2.获取分页的结果集
        //////////////////////////////////////////////////////////////////////////////////////////////
        String pagingSql = dialect.getLimitString(originalSql, rowBounds.getOffset(), rowBounds.getLimit());
        FieldUtils.writeField(boundSql, SQL, pagingSql, true);


        int size = 0;
        size = getPageParamNum(originalSql, pagingSql);

        if (size == 1) {
            ParameterMapping.Builder builder = new ParameterMapping.Builder(configuration, LIMIT, Integer.class);
            boundSql.getParameterMappings().add(builder.build());
            boundSql.setAdditionalParameter(LIMIT, rowBounds.getLimit());
        }
        if (size == 2) {

            ParameterMapping.Builder builder = new ParameterMapping.Builder(
                    configuration, OFFSET, Integer.class);
            boundSql.getParameterMappings().add(builder.build());
            boundSql.setAdditionalParameter(OFFSET, rowBounds.getOffset());

            builder = new ParameterMapping.Builder(configuration, LIMIT,
                    Integer.class);
            boundSql.getParameterMappings().add(builder.build());
            boundSql.setAdditionalParameter(LIMIT, rowBounds.getLimit());
        }
        FieldUtils.writeField(rowBounds, OFFSET, RowBounds.NO_ROW_OFFSET, true);
        FieldUtils.writeField(rowBounds, LIMIT, RowBounds.NO_ROW_LIMIT, true);

        if (logger.isDebugEnabled()) {
            logger.debug("\n" + originalSql +
                    "\n对应的分页SQL:\n" +
                    boundSql.getSql() +
                    "\n对应的count SQL:\n" +
                    countSql);
        }
        return invocation.proceed();
    }

    /**
     * 获取用于控制分页的问号的个数
     *
     * @param originalSql
     * @param pagingSql
     * @return
     */
    private int getPageParamNum(String originalSql, String pagingSql) {
        int size = 0;
        String addSql = pagingSql.replace(originalSql, "");

        Pattern pattern = Pattern.compile("[?]");
        Matcher matcher = pattern.matcher(addSql);
        while (matcher.find()) {
            size++;
        }
        return size;
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {
        if (PaginationInterceptor.dialect == null) {
            String dialect = properties.getProperty(DIALECT);
            if (dialect == null) {
                throw new RuntimeException("拦截器未提供dialect的配置，正确配置参见：\n" + INTERCEPTOR_CONF);
            }
            PaginationInterceptor.dialect = DialetHelper.getDialect(dialect);
        }
    }
}

如何在保证分页接口签名不变的情况下，将总行数传回去呢？

   下面是一个Service层的方法：

    public Page<App> queryAppList(AppQueryParam queryParam,Paging paging){
        List<App> apps = appMapper.queryList(queryParam, MybatisHepler.toRowBounds(paging));
        Page<App> appPage = new Page<App>();
        appPage.setResult(apps);
        appPage.setPageSize(paging.getPageSize());
        return appPage;
    }
   

由于DAO appMapper按正常签名只返回一个List，对应的总行数我怎么获取呢？这里我用到了ThreadLocal，因为它让我们可以跨类访问，毕竟Service调用DAO，它们都位于同一个Thread中：

package com.ridge.dao.mybatis;

import org.apache.commons.lang.reflect.FieldUtils;
import org.apache.ibatis.executor.statement.PreparedStatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import org.apache.ibatis.session.Configuration;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;


/**
 * @author : chenxh
 * @date: 13-7-8
 */
public class CountHelper {


    private static final String MAPPED_STATEMENT = "mappedStatement";
    private static Logger logger = LoggerFactory.getLogger(CountHelper.class);

    /**
     * 保存计算总行数的值
     */
    private static ThreadLocal<Integer> totalRowCountHolder = new ThreadLocal<Integer>();

    /**
     * 获取查询对象的总行数
     * @param sql  获取总行数的SQL
     * @param statementHandler
     * @param configuration
     * @param boundSql
     * @param connection
     * @throws Throwable
     */
    static void getCount(String sql, PreparedStatementHandler statementHandler,
                                Configuration configuration,  BoundSql boundSql,
                                Connection connection)
            throws Throwable{
        Object parameterObject = statementHandler.getParameterHandler().getParameterObject();
        if (logger.isDebugEnabled()) {
            logger.debug("Total count SQL [{}] ", sql);
            logger.debug("Total count Parameters: {} ", parameterObject);
        }


        PreparedStatement countStmt = null;
        ResultSet rs = null;
        try {
            countStmt = connection.prepareStatement(sql);
            final BoundSql countBS = new BoundSql(configuration, sql,
                    boundSql.getParameterMappings(), parameterObject);

            MappedStatement mappedStatement = (MappedStatement)FieldUtils.readField(statementHandler, MAPPED_STATEMENT, true);

            DefaultParameterHandler handler =
                    new DefaultParameterHandler(mappedStatement, parameterObject, countBS);
            handler.setParameters(countStmt);

            rs = countStmt.executeQuery();
            int count = 0;
            if (rs.next()) {
                count = rs.getInt(1);
            }
            if (logger.isDebugEnabled()) {
                logger.debug("Total count: {}", count);
            }
            totalRowCountHolder.set(count);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } finally {
                if (countStmt != null) {
                    countStmt.close();
                }
            }
        }
    }

    /**
     * 获取当前线程对应的分页查询的总行数
     *
     * @return
     */
    public static int getTotalRowCount() {
        return totalRowCountHolder.get();
    }
}

这样，以上Service的方法就可以改成：

    public Page<App> queryAppList(AppQueryParam queryParam,Paging paging){
        List<App> apps = appMapper.queryList(queryParam, MybatisHepler.toRowBounds(paging));
        Page<App> appPage = new Page<App>();
        appPage.setResult(apps);
        appPage.setPageSize(paging.getPageSize());
        appPage.setTotalRows(CountHelper.getTotalRowCount());//①注意这里！！
        return appPage;
    }
   

改进
 
   但是每个Service都要手工调用setTotalRows(CountHelper.getTotalRowCount())是不是有点多余呢？
  
   这里我们可以使用Spring AOP自动做这个事，这样①处的代码就可以不用手工写了。
为些，我写了一个Advice：

package com.ridge.dao.mybatis;

import com.ridge.dao.Page;

/**
 * @author : chenxh
 * @date: 13-7-8
 */
public class TotalRowValueMount {

    public void setTotalRows(Page page){
       page.setTotalRows(CountHelper.getTotalRowCount());
    }
}

   在Spring配置文件中，将TotalRowValueMount#setTotalRows(Page page)方法植入到所有返回值类型为Page的方法上，在方法返回时，自动调用setTotalRows(CountHelper.getTotalRowCount());

    <!-- 所有分页查询的方法自动设置总行数 -->
    <aop:config>
        <aop:aspect id="pagingQueryAspect" ref="totalRowCounter">
           <aop:pointcut id="pagingQueryMethods" expression="execution(public com.ridge.dao.Page *(..))"/>
            <aop:after-returning  pointcut-ref="pagingQueryMethods"  returning="page"  method="setTotalRows"/>
        </aop:aspect>
    </aop:config>
    <bean id="totalRowCounter" class="com.ridge.dao.mybatis.TotalRowValueMount"/>

怎么配置mybatis以应用拦截器呢

  在mybatis配置文件中，添加拦截器即可：

   <?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>
    <settings>
        <!-- changes from the defaults -->
        <setting name="lazyLoadingEnabled" value="false" />
    </settings>
    <plugins>
        <plugin interceptor="com.ridge.dao.mybatis.PaginationInterceptor">
            <property name="dialect" value="MYSQL"/>
        </plugin>
    </plugins>
    <mappers>
        <package name="com.xxx.yyy"/>
    </mappers>
</configuration>

  
   稍后再提交整个代码。

评论

1 楼 dpcheung 2014-08-13  

怎么没有完整代码贴出来呢？