MyBatis實(shí)現(xiàn)物理分頁(yè)的實(shí)例

更新時(shí)間：2017年01月17日 16:27:54 作者：ZimZz

這篇文章主要介紹了MyBatis實(shí)現(xiàn)物理分頁(yè)的實(shí)例,MyBatis使用RowBounds實(shí)現(xiàn)的分頁(yè)是邏輯分頁(yè)，有興趣的可以了解一下。

MyBatis使用RowBounds實(shí)現(xiàn)的分頁(yè)是邏輯分頁(yè),也就是先把數(shù)據(jù)記錄全部查詢出來(lái),然在再根據(jù)offset和limit截?cái)嘤涗浄祷?/p>

為了在數(shù)據(jù)庫(kù)層面上實(shí)現(xiàn)物理分頁(yè),又不改變?cè)瓉?lái)MyBatis的函數(shù)邏輯,可以編寫(xiě)plugin截獲MyBatis Executor的statementhandler,重寫(xiě)SQL來(lái)執(zhí)行查詢

下面的插件代碼只針對(duì)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;

/**
 * 通過(guò)攔截<code>StatementHandler</code>的<code>prepare</code>方法，重寫(xiě)sql語(yǔ)句實(shí)現(xiàn)物理分頁(yè)。
 * 老規(guī)矩，簽名里要攔截的類(lèi)型只能是接口。
 *
 */
@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");
    // 分離代理對(duì)象鏈(由于目標(biāo)類(lèi)可能被多個(gè)攔截器攔截，從而形成多次代理，通過(guò)下面的兩次循環(huán)可以分離出最原始的的目標(biāo)類(lèi))
    while (metaStatementHandler.hasGetter("h")) {
      Object object = metaStatementHandler.getValue("h");
      metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
    }
    // 分離最后一個(gè)代理對(duì)象的目標(biāo)類(lèi)
    while (metaStatementHandler.hasGetter("target")) {
      Object object = metaStatementHandler.getValue("target");
      metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
    }

    // property在mybatis settings文件內(nèi)配置
    Configuration configuration = (Configuration) metaStatementHandler.getValue("delegate.configuration");

    // 設(shè)置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");
    // 只重寫(xiě)需要分頁(yè)的sql語(yǔ)句。通過(guò)MappedStatement的ID匹配，默認(rèn)重寫(xiě)以Page結(jié)尾的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();
        // 重寫(xiě)sql
        String pageSql = sql + " LIMIT " + rowBounds.getOffset() + "," + rowBounds.getLimit();
        metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);
        // 采用物理分頁(yè)后，就不需要mybatis的內(nèi)存分頁(yè)了，所以重置下面的兩個(gè)參數(shù)
        metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);
        metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);
      }
    }
    // 將執(zhí)行權(quán)交給下一個(gè)攔截器
    return invocation.proceed();
  }

  @Override
  public Object plugin(Object target) {
    // 當(dāng)目標(biāo)類(lèi)是StatementHandler類(lèi)型時(shí)，才包裝目標(biāo)類(lèi)，否者直接返回目標(biāo)本身,減少目標(biāo)被代理的次數(shù)
    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

  <!-- 測(cè)試分頁(yè)查詢 -->
  <select id="selectUserByPage" resultMap="dao.base.userResultMap">
    <![CDATA[
    SELECT * FROM user
    ]]>
  </select>

調(diào)用示例

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

另外,結(jié)合Spring MVC,編寫(xiě)翻頁(yè)和生成頁(yè)碼代碼

頁(yè)碼類(lèi)

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; // 頁(yè)碼url
  private int pageSize = 10; // 每頁(yè)顯示記錄數(shù)
  private int currentPage = 1;  // 當(dāng)前頁(yè)碼
  private int maxPage = Integer.MAX_VALUE;  // 最大頁(yè)數(shù)

  // 獲取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;
  }
}

為了計(jì)算最大頁(yè)碼,需要知道數(shù)據(jù)表的總記錄數(shù),查詢SQL如下

  <!-- 記錄總數(shù) -->
  <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";
  }

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

您可能感興趣的文章: