上一篇文章里已经讲到了mybatis与spring MVC的集成，并且做了一个列表展示，显示出所有article 列表，但没有用到分页，在实际的项目中，分页是肯定需要的。而且是物理分页，不是内存分页。对于物理分页方案，不同的数据库，有不同的实现方法,对于mysql 来说 就是利用 limit offset,pagesize 方式来实现的。oracle 是通过rownum 来实现的，如果你熟悉相关数据库的操作，是一样的很好扩展，本文以mysql 为例子来讲述.先看一下效果图(源代码在文章最后提供下载):
实现mybatis 物理分页，一个最简单的方式是，是在你的mapper的SQL语句中直接写类似如下方式 :
请注意这里的 parameterType 是你传入的参数类，或者map ，里面包含了offset,pagesize ,和其他你需要的参数，用这种方式，肯定可以实现分页。这是简单的一种方式。但更通用的一种方式是用 mybatis 插件的方式. 参考了网上的很多资料 ，mybatis plugin 方面的资料。写自己的插件.
package com.yihaomen.util;import java.lang.reflect.Field;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.List;import java.util.Map;import java.util.Properties;import javax.xml.bind.PropertyException;import org.apache.ibatis.builder.xml.dynamic.ForEachSqlNode;import org.apache.ibatis.executor.ErrorContext;import org.apache.ibatis.executor.Executor;import org.apache.ibatis.executor.ExecutorException;import org.apache.ibatis.executor.statement.BaseStatementHandler;import org.apache.ibatis.executor.statement.RoutingStatementHandler;import org.apache.ibatis.executor.statement.StatementHandler;import org.apache.ibatis.mapping.BoundSql;import org.apache.ibatis.mapping.MappedStatement;import org.apache.ibatis.mapping.ParameterMapping;import org.apache.ibatis.mapping.ParameterMode;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.property.PropertyTokenizer;import org.apache.ibatis.session.Configuration;import org.apache.ibatis.session.ResultHandler;import org.apache.ibatis.session.RowBounds;import org.apache.ibatis.type.TypeHandler;import org.apache.ibatis.type.TypeHandlerRegistry;@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) })public class PagePlugin implements Interceptor { private static String dialect = ""; private static String pageSqlId = ""; @SuppressWarnings("unchecked") public Object intercept(Invocation ivk) throws Throwable { if (ivk.getTarget() instanceof RoutingStatementHandler) { RoutingStatementHandler statementHandler = (RoutingStatementHandler) ivk .getTarget(); BaseStatementHandler delegate = (BaseStatementHandler) ReflectHelper .getValueByFieldName(statementHandler, "delegate"); MappedStatement mappedStatement = (MappedStatement) ReflectHelper .getValueByFieldName(delegate, "mappedStatement"); if (mappedStatement.getId().matches(pageSqlId)) { BoundSql boundSql = delegate.getBoundSql(); Object parameterObject = boundSql.getParameterObject(); if (parameterObject == null) { throw new NullPointerException("parameterObject error"); } else { Connection connection = (Connection) ivk.getArgs()[0]; String sql = boundSql.getSql(); String countSql = "select count(0) from (" + sql + ") myCount"; System.out.println("总数sql 语句:"+countSql); PreparedStatement countStmt = connection .prepareStatement(countSql); BoundSql countBS = new BoundSql( mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), parameterObject); setParameters(countStmt, mappedStatement, countBS, parameterObject); ResultSet rs = countStmt.executeQuery(); int count = 0; if (rs.next()) { count = rs.getInt(1); } rs.close(); countStmt.close(); PageInfo page = null; if (parameterObject instanceof PageInfo) { page = (PageInfo) parameterObject; page.setTotalResult(count); } else if(parameterObject instanceof Map){ Map map = (Map )parameterObject; page = (PageInfo)map.get("page"); if(page == null) page = new PageInfo(); page.setTotalResult(count); }else { Field pageField = ReflectHelper.getFieldByFieldName( parameterObject, "page"); if (pageField != null) { page = (PageInfo) ReflectHelper.getValueByFieldName( parameterObject, "page"); if (page == null) page = new PageInfo(); page.setTotalResult(count); ReflectHelper.setValueByFieldName(parameterObject, "page", page); } else { throw new NoSuchFieldException(parameterObject .getClass().getName()); } } String pageSql = generatePageSql(sql, page); System.out.println("page sql:"+pageSql); ReflectHelper.setValueByFieldName(boundSql, "sql", pageSql); } } } return ivk.proceed(); } private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws SQLException { ErrorContext.instance().activity("setting parameters") .object(mappedStatement.getParameterMap().getId()); List parameterMappings = boundSql .getParameterMappings(); if (parameterMappings != null) { Configuration configuration = mappedStatement.getConfiguration(); TypeHandlerRegistry typeHandlerRegistry = configuration .getTypeHandlerRegistry(); MetaObject metaObject = parameterObject == null ? null : configuration.newMetaObject(parameterObject); for (int i = 0; i < parameterMappings.size(); i++) { ParameterMapping parameterMapping = parameterMappings.get(i); if (parameterMapping.getMode() != ParameterMode.OUT) { Object value; String propertyName = parameterMapping.getProperty(); PropertyTokenizer prop = new PropertyTokenizer(propertyName); if (parameterObject == null) { value = null; } else if (typeHandlerRegistry .hasTypeHandler(parameterObject.getClass())) { value = parameterObject; } else if (boundSql.hasAdditionalParameter(propertyName)) { value = boundSql.getAdditionalParameter(propertyName); } else if (propertyName .startsWith(ForEachSqlNode.ITEM_PREFIX) && boundSql.hasAdditionalParameter(prop.getName())) { value = boundSql.getAdditionalParameter(prop.getName()); if (value != null) { value = configuration.newMetaObject(value) .getValue( propertyName.substring(prop .getName().length())); } } else { value = metaObject == null ? null : metaObject .getValue(propertyName); } TypeHandler typeHandler = parameterMapping.getTypeHandler(); if (typeHandler == null) { throw new ExecutorException( "There was no TypeHandler found for parameter " + propertyName + " of statement " + mappedStatement.getId()); } typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType()); } } } } private String generatePageSql(String sql, PageInfo page) { if (page != null && (dialect !=null || !dialect.equals(""))) { StringBuffer pageSql = new StringBuffer(); if ("mysql".equals(dialect)) { pageSql.append(sql); pageSql.append(" limit " + page.getCurrentResult() + "," + page.getShowCount()); } else if ("oracle".equals(dialect)) { pageSql.append("select * from (select tmp_tb.*,ROWNUM row_id from ("); pageSql.append(sql); pageSql.append(") tmp_tb where ROWNUM<="); pageSql.append(page.getCurrentResult() + page.getShowCount()); pageSql.append(") where row_id>"); pageSql.append(page.getCurrentResult()); } return pageSql.toString(); } else { return sql; } } public Object plugin(Object arg0) { // TODO Auto-generated method stub return Plugin.wrap(arg0, this); } public void setProperties(Properties p) { dialect = p.getProperty("dialect"); if (dialect ==null || dialect.equals("")) { try { throw new PropertyException("dialect property is not found!"); } catch (PropertyException e) { // TODO Auto-generated catch block e.printStackTrace(); } } pageSqlId = p.getProperty("pageSqlId"); if (dialect ==null || dialect.equals("")) { try { throw new PropertyException("pageSqlId property is not found!"); } catch (PropertyException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }} 此插件有两个辅助类:PageInfo,ReflectHelper,你可以下载源代码参考。
写了插件之后，当然需要在 mybatis 的配置文件Configuration.xml 里配置这个插件请注意，这个插件定义了一个规则，也就是在mapper中sql语句的id 必须包含ListPage才能被拦截。否则将不会分页处理.
插件写好了，现在就可以在 spring mvc 中的controller 层中写一个方法来测试这个分页:@RequestMapping("/pagelist") public ModelAndView pageList(HttpServletRequest request,HttpServletResponse response){ int currentPage = request.getParameter("page")==null?1:Integer.parseInt(request.getParameter("page")); int pageSize = 3; if (currentPage<=0){ currentPage =1; } int currentResult = (currentPage-1) * pageSize; System.out.println(request.getRequestURI()); System.out.println(request.getQueryString()); PageInfo page = new PageInfo(); page.setShowCount(pageSize); page.setCurrentResult(currentResult); List articles=iUserOperation.selectArticleListPage(page,1); System.out.println(page); int totalCount = page.getTotalResult(); int lastPage=0; if (totalCount % pageSize==0){ lastPage = totalCount % pageSize; } else{ lastPage =1+ totalCount / pageSize; } if (currentPage>=lastPage){ currentPage =lastPage; } String pageStr = ""; pageStr=String.format(" 上一页 下一页", request.getRequestURI()+"?page="+(currentPage-1),request.getRequestURI()+"?page="+(currentPage+1) ); //制定视图，也就是list.jsp ModelAndView mav=new ModelAndView("list"); mav.addObject("articles",articles); mav.addObject("pageStr",pageStr); return mav; } 
原创