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

来源：http://www.cnblogs.com/zemliu/archive/2013/08/07/3242966.html