MyBatis implements instances of physical paging

The paging implemented by MyBatis using RowBounds is logical paging, that is, it queries all data records first, and then truncates the records according to offset and limit

In order to achieve physical paging at the database level without changing MyBatis's functional logic, you can write plugin to intercept MyBatis Executor's statementhandler and rewrite SQL to execute queries

The plug-in code below is for MySQL only

plugin code

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;

/**
 *  By intercepting <code>StatementHandler</code> the <code>prepare</code> Method, override sql The statement implements physical paging. 
 *  As usual, the type to be intercepted in the signature must be the interface. 
 *
 */
@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$"; //  To intercept ID( Regular match )

  @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");
    //  Detach the proxy object chain ( Since the target class may be intercepted by multiple interceptors, resulting in multiple proxies, the original target class can be separated by the following two loops )
    while (metaStatementHandler.hasGetter("h")) {
      Object object = metaStatementHandler.getValue("h");
      metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
    }
    //  Separation of the last 1 The target class of the proxy object 
    while (metaStatementHandler.hasGetter("target")) {
      Object object = metaStatementHandler.getValue("target");
      metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
    }

    // property in mybatis settings In-file configuration 
    Configuration configuration = (Configuration) metaStatementHandler.getValue("delegate.configuration");

    //  Set up the 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");
    //  Override only those that require paging sql Statements. through MappedStatement the ID Match, default override to Page At the end of the MappedStatement the 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();
        //  rewrite sql
        String pageSql = sql + " LIMIT " + rowBounds.getOffset() + "," + rowBounds.getLimit();
        metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);
        //  With physical paging, you don't need it mybatis Is paging out of memory, so reset the following two parameters 
        metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);
        metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);
      }
    }
    //  Hand over the power of execution 1 An interceptor 
    return invocation.proceed();
  }

  @Override
  public Object plugin(Object target) {
    //  When the target class is StatementHandler Type, the target class is wrapped, or the target itself is returned , Reduces the number of times the target is proxied 
    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.
  }

}

Configuration plugin

  <plugins>
    <plugin interceptor="plugin.PaginationInterceptor" />
  </plugins>

Query SQL

  <!--  Test paging query  -->
  <select id="selectUserByPage" resultMap="dao.base.userResultMap">
    <![CDATA[
    SELECT * FROM user
    ]]>
  </select>

Invoke the sample

  @Override
  public List<User> selectUserByPage(int offset, int limit) {
    RowBounds rowBounds = new RowBounds(offset, limit);
    return getSqlSession().selectList("dao.userdao.selectUserByPage", new Object(), rowBounds);
  }

In addition, in combination with Spring MVC, write page turning and generate page code

The page number class

package util;

/**
 * Created with IntelliJ IDEA.
 * User: zhenwei.liu
 * Date: 13-8-7
 * Time:  In the morning 10:29
 * To change this template use File | Settings | File Templates.
 */
public class Pagination {
  private String url; //  The page number url
  private int pageSize = 10; //  Displays the number of records per page 
  private int currentPage = 1;  //  The current page number 
  private int maxPage = Integer.MAX_VALUE;  //  The largest number of pages 

  //  To obtain offset
  public int getOffset() {
    return (currentPage - 1) * pageSize;
  }

  //  To obtain 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;
  }
}

To calculate the maximum page number, you need to know the total number of records in the data table, query SQL below

  <!--  The total number of records  -->
  <select id="countUser" resultType="Integer">
    <![CDATA[
    SELECT COUNT(*) FROM user
    ]]>
  </select>

 @Override
  public Integer countTable() {
    return getSqlSession().selectOne("dao.userdao.countUser");
  }

Used in 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";
  }