mybatis分页的简单实现

最近的项目中使用到了mybatis，发现mybatis不支持物理分页，只支持内存分页。因此为了解决这个问题，在网上搜索了一番，不过都比较繁琐。最后使用正则表达过滤查询语句的方式简单解决了该问题.

mybatis物理分页的核心是使用mybatis的拦截器 org.apache.ibatis.plugin.Interceptor ,在mybatis准备好SQL的时候，对SQL字符串进行拦截，生成适合Oracle数据库的分页语句即可。废话不多讲了，直接上代码.

Notes: 该部分依赖commons-lang3.jar包进行反射写入，也可使用 mybatis 自带的反射类实现这部分功能

拦截器代码

package org.mybatis.test.interceptor;
import java.sql.Connection;
import java.util.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.RowBounds;
@Intercepts(@Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }))
public class PaginationInterceptor implements Interceptor {
    private final static String SQL_SELECT_REGEX = "(?is)^\\s*SELECT.*$";
    private final static String SQL_COUNT_REGEX = "(?is)^\\s*SELECT\\s+COUNT\\s*\\(\\s*(?:\\*|\\w+)\\s*\\).*$";
    //@Override
    public Object intercept(Invocation inv) throws Throwable {
        StatementHandler target = (StatementHandler) inv.getTarget();
        BoundSql boundSql = target.getBoundSql();
        String sql = boundSql.getSql();
        if (StringUtils.isBlank(sql)) {
            return inv.proceed();
        }
        System.out.println("origin sql>>>>>" + sql.replaceAll("\n", ""));
        // 只有为select查询语句时才进行下一步
        if (sql.matches(SQL_SELECT_REGEX)
                && !Pattern.matches(SQL_COUNT_REGEX, sql)) {
            Object obj = FieldUtils.readField(target, "delegate", true);
            // 反射获取 RowBounds 对象。
            RowBounds rowBounds = (RowBounds) FieldUtils.readField(obj,
                    "rowBounds", true);
            // 分页参数存在且不为默认值时进行分页SQL构造
            if (rowBounds != null && rowBounds != RowBounds.DEFAULT) {
                FieldUtils.writeField(boundSql, "sql", newSql(sql, rowBounds),
                        true);
                System.out.println("new sql>>>>>"
                        + boundSql.getSql().replaceAll("\n", ""));
                // 一定要还原否则将无法得到下一组数据(第一次的数据被缓存了)
                FieldUtils.writeField(rowBounds, "offset",
                        RowBounds.NO_ROW_OFFSET, true);
                FieldUtils.writeField(rowBounds, "limit",
                        RowBounds.NO_ROW_LIMIT, true);
            }
        }
        return inv.proceed();
    }
    public String newSql(String oldSql, RowBounds rowBounds) {
        String start = " SELECT * FROM   (SELECT   row_.*, ROWNUM rownum_ FROM ( ";
        String end = " ) row_ WHERE   ROWNUM <= " + rowBounds.getLimit()
                + ") WHERE   rownum_ > " + rowBounds.getOffset();
        return start + oldSql + end;
    }
    //@Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }
    //@Override
    public void setProperties(Properties arg0) {
        System.out.println(arg0);
    }
    //测试正则表达式是否能正常工作
    public static void main(String[] args) {
        String SQL_SELECT_REGEX = "^\\s*SELECT.*$";
        String SQL_COUNT_REGEX = "^\\s*SELECT\\s+COUNT\\s*\\(\\s*(?:\\*|\\w+)\\s*\\).*$";
        List<String> tests = new ArrayList<String>();
        tests.add("select count(*) from abc \n\t\t where\n abc");
        tests.add("SELECT   COUNT(*) from abc");
        tests.add(" select count  (*) from abc");
        tests.add("  select count(  *) from abc");
        tests.add("select count( *  ),id   from abc");
        tests.add("select * from abc");
        tests.add("select abc,test,fdas from abc");
        tests.add("select count(adb) from abc");
        tests.add("select count(0) from abc");
        tests.add("select min(count(*)) from abc");
        tests.add("update min(count(*)) from abc");
        tests.add("delete min(count(*)) from abc");
        Pattern p1 = Pattern.compile(SQL_SELECT_REGEX, Pattern.DOTALL
                | Pattern.CASE_INSENSITIVE);
        Pattern p2 = Pattern.compile(SQL_COUNT_REGEX, Pattern.DOTALL
                | Pattern.CASE_INSENSITIVE);
        for (String str : tests) {
            Matcher m1 = p1.matcher(str);
            Matcher m2 = p2.matcher(str);
            System.out.println("匹配字符串: " + str);
            System.out.println("    是select语句? " + m1.matches());
            System.out.println("    是count语句? " + m2.matches());
            System.out.println();
        }
    }
}

在spring中配置拦截器

<bean name="paginationInterceptor" class="org.mybatis.test.interceptor.PaginationInterceptor"></bean>
<!-- define the SqlSessionFactory -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="typeAliasesPackage" value="org.mybatis.test.domain" />
    <property name="plugins">
        <list>
            <ref bean="paginationInterceptor" />
        </list>
    </property>
</bean>

使用

public class Test(){
    private String name;
    private int age;
    // set/get省
}

1. 在TestMapper.java接口类中声明分页方法

    //统计总条数
    int countByVo(Test test)；
    //分页查询
    List<T> queryByVo(RowBounds rowBound, Test test);

2. TestMapper.xml中

    <select id="countByVo" parameterType="Test" resultType="int">
        select count(*) as count from table_test
        <where>
            <if test="name != null">name like '' || #{name} || '%'</if>
        </where>
    </select>
    <select id="queryByVo" parameterType="Test" resultType="Test">
        select * from table_test
        <where>
            <if test="name != null">name like '' || #{name} || '%'</if>
        </where>
    </select>   

3. 业务层代码

    @Autowired
    private TestMapper testMapper;  
    /**
      * @param offset 起始位置
      * @param limit 结束位置
      */
    public void queryByVo(int offset, int limit) {
        Test test = new Test();
        List<User> list = this.testMapper.queryByVo(new RowBounds(offset, limit), test);
    }