MyBatis实现物理分页的实例

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

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

下面的插件代码只针对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";
  }

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。