MyBatis 物理分页

2021-11-05 12:38:18

MyBatis使用RowBounds实现的分页是逻辑分页,也就是先把数据记录全部查询出来,然在再根据offset和limit截断记录返回

为了在数据库层面上实现物理分页,又不改变原来MyBatis的函数逻辑,可以编写plugin截获MyBatis Executor的statementhandler,重写SQL来执行查询

参考资料: http://blog.csdn.net/hupanfeng/article/details/9265341

下面的插件代码只针对MySQL

plugin代码

package plugin;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Properties;

import org.apache.ibatis.executor.parameter.ParameterHandler;

import org.apache.ibatis.executor.statement.StatementHandler;

import org.apache.ibatis.logging.Log;

import org.apache.ibatis.logging.LogFactory;

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.factory.DefaultObjectFactory;

import org.apache.ibatis.reflection.factory.ObjectFactory;

import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;

import org.apache.ibatis.reflection.wrapper.ObjectWrapperFactory;

import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;

import org.apache.ibatis.session.Configuration;

import org.apache.ibatis.session.RowBounds;

/**

 * 通过拦截<code>StatementHandler</code>的<code>prepare</code>方法，重写sql语句实现物理分页。

 * 老规矩，签名里要拦截的类型只能是接口。

 *

 */

@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})

public class PaginationInterceptor implements Interceptor {

    private static final Log logger = LogFactory.getLog(PaginationInterceptor.class);

    private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();

    private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();

    private static String DEFAULT_PAGE_SQL_ID = ".*Page$"; // 需要拦截的ID(正则匹配)

    @Override

    public Object intercept(Invocation invocation) throws Throwable {

        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();

        MetaObject metaStatementHandler = MetaObject.forObject(statementHandler, DEFAULT_OBJECT_FACTORY,

                DEFAULT_OBJECT_WRAPPER_FACTORY);

        RowBounds rowBounds = (RowBounds) metaStatementHandler.getValue("delegate.rowBounds");

        // 分离代理对象链(由于目标类可能被多个拦截器拦截，从而形成多次代理，通过下面的两次循环可以分离出最原始的的目标类)

        while (metaStatementHandler.hasGetter("h")) {

            Object object = metaStatementHandler.getValue("h");

            metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);

        }

        // 分离最后一个代理对象的目标类

        while (metaStatementHandler.hasGetter("target")) {

            Object object = metaStatementHandler.getValue("target");

            metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);

        }

        // property在mybatis settings文件内配置

        Configuration configuration = (Configuration) metaStatementHandler.getValue("delegate.configuration");

        // 设置pageSqlId

        String pageSqlId = configuration.getVariables().getProperty("pageSqlId");

        if (null == pageSqlId || "".equals(pageSqlId)) {

            logger.warn("Property pageSqlId is not setted,use default '.*Page$' ");

            pageSqlId = DEFAULT_PAGE_SQL_ID;

        }

        MappedStatement mappedStatement = (MappedStatement)

                metaStatementHandler.getValue("delegate.mappedStatement");

        // 只重写需要分页的sql语句。通过MappedStatement的ID匹配，默认重写以Page结尾的MappedStatement的sql

        if (mappedStatement.getId().matches(pageSqlId)) {

            BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");

            Object parameterObject = boundSql.getParameterObject();

            if (parameterObject == null) {

                throw new NullPointerException("parameterObject is null!");

            } else {

                String sql = boundSql.getSql();

                // 重写sql

                String pageSql = sql + " LIMIT " + rowBounds.getOffset() + "," + rowBounds.getLimit();

                metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);

                // 采用物理分页后，就不需要mybatis的内存分页了，所以重置下面的两个参数

                metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);

                metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);

            }

        }

        // 将执行权交给下一个拦截器

        return invocation.proceed();

    }

    @Override

    public Object plugin(Object target) {

        // 当目标类是StatementHandler类型时，才包装目标类，否者直接返回目标本身,减少目标被代理的次数

        if (target instanceof StatementHandler) {

            return Plugin.wrap(target, this);

        } else {

            return target;

        }

    }

    @Override

    public void setProperties(Properties properties) {

        //To change body of implemented methods use File | Settings | File Templates.

    }

}

配置plugin

    <plugins>

        <plugin interceptor="plugin.PaginationInterceptor" />

    </plugins>

查询SQL

<!-- 测试分页查询 -->

<select id="selectUserByPage" resultMap="dao.base.userResultMap">

        <![CDATA[

        SELECT * FROM user

        ]]>

</select>

调用示例

@Override

    public List<User> selectUserByPage(int offset, int limit) {

        RowBounds rowBounds = new RowBounds(offset, limit);

        return getSqlSession().selectList("dao.userdao.selectUserByPage", new Object(), rowBounds);

    }

另外,结合Spring MVC,编写翻页和生成页码代码

页码类

package util;

/**

 * Created with IntelliJ IDEA.

 * User: zhenwei.liu

 * Date: 13-8-7

 * Time: 上午10:29

 * To change this template use File | Settings | File Templates.

 */

public class Pagination {

    private String url; // 页码url

    private int pageSize = 10;  // 每页显示记录数

    private int currentPage = 1;    // 当前页码

    private int maxPage = Integer.MAX_VALUE;    // 最大页数

    // 获取offset

    public int getOffset() {

        return (currentPage - 1) * pageSize;

    }

    // 获取limit

    public int getLimit() {

        return getPageSize();

    }

    public String getUrl() {

        return url;

    }

    public void setUrl(String url) {

        this.url = url;

    }

    public int getPageSize() {

        return pageSize;

    }

    public void setPageSize(int pageSize) {

        this.pageSize = pageSize;

    }

    public int getCurrentPage() {

        return currentPage;

    }

    public void setCurrentPage(int currentPage) {

        if (currentPage < 1)

            currentPage = 1;

        if (currentPage > maxPage)

            currentPage = maxPage;

        this.currentPage = currentPage;

    }

    public int getMaxPage() {

        return maxPage;

    }

    public void setMaxPage(int maxPage) {

        this.maxPage = maxPage;

    }

}

为了计算最大页码,需要知道数据表的总记录数,查询SQL如下

<!-- 记录总数 -->

    <select id="countUser" resultType="Integer">

        <![CDATA[

        SELECT COUNT(*) FROM user

        ]]>

    </select>

@Override

    public Integer countTable() {

        return getSqlSession().selectOne("dao.userdao.countUser");

    }

Controller中的使用

@RequestMapping("/getUserByPage")

    public String getUserByPage(@RequestParam

                                    int page, Model model) {

        pagination.setCurrentPage(page);

        pagination.setUrl(getCurrentUrl());

        pagination.setMaxPage(userDao.countTable() / pagination.getPageSize() + 1);

        List<User> userList = userDao.selectUserByPage(

                pagination.getOffset(), pagination.getLimit());

        model.addAttribute(pagination);

        model.addAttribute(userList);

        return "index";

    }

码农公寓

相关文章