mybatis3.1分页自动增添总数

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())

   以下就是使用该方法完成的转换：
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" name="code">   <?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>

  
   稍后再提交整个代码。

查看更多 下一篇