mybatis实战教程之七实现mybatis分页

mybatis实战教程(mybatis in action)之七:实现mybatis分页(源码下载)上一篇文章里已经讲到了mybatis与spring MVC的集成，并且做了一个列表展示，显示出所有article 列表，但没有用到分页，在实际的项目中，分页是肯定需要的。而且是物理分页，不是内存分页。对于物理分页方案，不同的数据库，有不同的实现方法,对于mysql 来说 就是利用 limit offset,pagesize 方式来实现的。oracle 是通过rownum 来实现的，如果你熟悉相关数据库的操作，是一样的很好扩展，本文以mysql 为例子来讲述.先看一下效果图(源代码在文章最后提供下载):实现mybatis 物理分页，一个最简单的方式是，是在你的mapper的SQL语句中直接写类似如下方式 :程序代码 select user.id,user.userName,user.userAddress,article.id aid,article.title,article.content from user,article where user.id=article.userid and user.id=#id limit #offset,#pagesize 请注意这里的 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(); 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; 然后运行程序，进入分页页面，你就可以看到结果了: