8. mybatis实战教程(mybatis in action)之七:实现mybatis分页(源码下载)

转自：https://blog.csdn.net/tangruyi1992/article/details/52584012

上一篇文章里已经讲到了mybatis与spring MVC的集成，并且做了一个列表展示，显示出所有article 列表，但没有用到分页，在实际的项目中，分页是肯定需要的。而且是物理分页，不是内存分页。对于物理分页方案，不同的数据库，有不同的实现方法,对于 mysql 来说就是利用 limit offset,pagesize 方式来实现的。oracle 是通过rownum 来实现的，如果你熟悉相关数据库的操作，是一样的很好扩展，本文以mysql 为例子来讲述.先看一下效果图(源代码在文章最后提供下载):

实现mybatis 物理分页，一个最简单的方式是，是在你的mapper的SQL语句中直接写类似如下方式 :

1 <select id="getUserArticles" parameterType="Your_params" resultMap="resultUserArticleList">
2        select user.id,user.userName,user.userAddress,article.id aid,article.title,article.content from user,article 
3               where user.id=article.userid and user.id=#{id} limit #{offset},#{pagesize}
4     </select>

请注意这里的 parameterType 是你传入的参数类，或者map ，里面包含了offset,pagesize ,和其他你需要的参数，用这种方式，肯定可以实现分页。这是简单的一种方式。但更通用的一种方式是用 mybatis 插件的方式. 参考了网上的很多资料，mybatis plugin 方面的资料。写自己的插件.

  1 package com.yihaomen.util;
  2 
  3 import java.lang.reflect.Field;
  4 import java.sql.Connection;
  5 import java.sql.PreparedStatement;
  6 import java.sql.ResultSet;
  7 import java.sql.SQLException;
  8 import java.util.List;
  9 import java.util.Map;
 10 import java.util.Properties;
 11 
 12 import javax.xml.bind.PropertyException;
 13 
 14 import org.apache.ibatis.builder.xml.dynamic.ForEachSqlNode;
 15 import org.apache.ibatis.executor.ErrorContext;
 16 import org.apache.ibatis.executor.Executor;
 17 import org.apache.ibatis.executor.ExecutorException;
 18 import org.apache.ibatis.executor.statement.BaseStatementHandler;
 19 import org.apache.ibatis.executor.statement.RoutingStatementHandler;
 20 import org.apache.ibatis.executor.statement.StatementHandler;
 21 import org.apache.ibatis.mapping.BoundSql;
 22 import org.apache.ibatis.mapping.MappedStatement;
 23 import org.apache.ibatis.mapping.ParameterMapping;
 24 import org.apache.ibatis.mapping.ParameterMode;
 25 import org.apache.ibatis.plugin.Interceptor;
 26 import org.apache.ibatis.plugin.Intercepts;
 27 import org.apache.ibatis.plugin.Invocation;
 28 import org.apache.ibatis.plugin.Plugin;
 29 import org.apache.ibatis.plugin.Signature;
 30 import org.apache.ibatis.reflection.MetaObject;
 31 import org.apache.ibatis.reflection.property.PropertyTokenizer;
 32 import org.apache.ibatis.session.Configuration;
 33 import org.apache.ibatis.session.ResultHandler;
 34 import org.apache.ibatis.session.RowBounds;
 35 import org.apache.ibatis.type.TypeHandler;
 36 import org.apache.ibatis.type.TypeHandlerRegistry;
 37 
 38 @Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) })
 39 public class PagePlugin implements Interceptor {
 40 
 41     private static String dialect = "";
 42     private static String pageSqlId = "";
 43 
 44     @SuppressWarnings("unchecked")
 45     public Object intercept(Invocation ivk) throws Throwable {
 46 
 47         if (ivk.getTarget() instanceof RoutingStatementHandler) {
 48             RoutingStatementHandler statementHandler = (RoutingStatementHandler) ivk
 49                     .getTarget();
 50             BaseStatementHandler delegate = (BaseStatementHandler) ReflectHelper
 51                     .getValueByFieldName(statementHandler, "delegate");
 52             MappedStatement mappedStatement = (MappedStatement) ReflectHelper
 53                     .getValueByFieldName(delegate, "mappedStatement");
 54 
 55             if (mappedStatement.getId().matches(pageSqlId)) {
 56                 BoundSql boundSql = delegate.getBoundSql();
 57                 Object parameterObject = boundSql.getParameterObject();
 58                 if (parameterObject == null) {
 59                     throw new NullPointerException("parameterObject error");
 60                 } else {
 61                     Connection connection = (Connection) ivk.getArgs()[0];
 62                     String sql = boundSql.getSql();
 63                     String countSql = "select count(0) from (" + sql + ") myCount";
 64                     System.out.println("总数sql 语句:"+countSql);
 65                     PreparedStatement countStmt = connection
 66                             .prepareStatement(countSql);
 67                     BoundSql countBS = new BoundSql(
 68                             mappedStatement.getConfiguration(), countSql,
 69                             boundSql.getParameterMappings(), parameterObject);
 70                     setParameters(countStmt, mappedStatement, countBS,
 71                             parameterObject);
 72                     ResultSet rs = countStmt.executeQuery();
 73                     int count = 0;
 74                     if (rs.next()) {
 75                         count = rs.getInt(1);
 76                     }
 77                     rs.close();
 78                     countStmt.close();
 79 
 80                     PageInfo page = null;
 81                     if (parameterObject instanceof PageInfo) {
 82                         page = (PageInfo) parameterObject;
 83                         page.setTotalResult(count);
 84                     } else if(parameterObject instanceof Map){
 85                         Map<String, Object> map = (Map<String, Object>)parameterObject;
 86                         page = (PageInfo)map.get("page");
 87                         if(page == null)
 88                             page = new PageInfo();
 89                         page.setTotalResult(count);
 90                     }else {
 91                         Field pageField = ReflectHelper.getFieldByFieldName(
 92                                 parameterObject, "page");
 93                         if (pageField != null) {
 94                             page = (PageInfo) ReflectHelper.getValueByFieldName(
 95                                     parameterObject, "page");
 96                             if (page == null)
 97                                 page = new PageInfo();
 98                             page.setTotalResult(count);
 99                             ReflectHelper.setValueByFieldName(parameterObject,
100                                     "page", page);
101                         } else {
102                             throw new NoSuchFieldException(parameterObject
103                                     .getClass().getName());
104                         }
105                     }
106                     String pageSql = generatePageSql(sql, page);
107                     System.out.println("page sql:"+pageSql);
108                     ReflectHelper.setValueByFieldName(boundSql, "sql", pageSql);
109                 }
110             }
111         }
112         return ivk.proceed();
113     }
114 
115     private void setParameters(PreparedStatement ps,
116             MappedStatement mappedStatement, BoundSql boundSql,
117             Object parameterObject) throws SQLException {
118         ErrorContext.instance().activity("setting parameters")
119                 .object(mappedStatement.getParameterMap().getId());
120         List<ParameterMapping> parameterMappings = boundSql
121                 .getParameterMappings();
122         if (parameterMappings != null) {
123             Configuration configuration = mappedStatement.getConfiguration();
124             TypeHandlerRegistry typeHandlerRegistry = configuration
125                     .getTypeHandlerRegistry();
126             MetaObject metaObject = parameterObject == null ? null
127                     : configuration.newMetaObject(parameterObject);
128             for (int i = 0; i < parameterMappings.size(); i++) {
129                 ParameterMapping parameterMapping = parameterMappings.get(i);
130                 if (parameterMapping.getMode() != ParameterMode.OUT) {
131                     Object value;
132                     String propertyName = parameterMapping.getProperty();
133                     PropertyTokenizer prop = new PropertyTokenizer(propertyName);
134                     if (parameterObject == null) {
135                         value = null;
136                     } else if (typeHandlerRegistry
137                             .hasTypeHandler(parameterObject.getClass())) {
138                         value = parameterObject;
139                     } else if (boundSql.hasAdditionalParameter(propertyName)) {
140                         value = boundSql.getAdditionalParameter(propertyName);
141                     } else if (propertyName
142                             .startsWith(ForEachSqlNode.ITEM_PREFIX)
143                             && boundSql.hasAdditionalParameter(prop.getName())) {
144                         value = boundSql.getAdditionalParameter(prop.getName());
145                         if (value != null) {
146                             value = configuration.newMetaObject(value)
147                                     .getValue(
148                                             propertyName.substring(prop
149                                                     .getName().length()));
150                         }
151                     } else {
152                         value = metaObject == null ? null : metaObject
153                                 .getValue(propertyName);
154                     }
155                     TypeHandler typeHandler = parameterMapping.getTypeHandler();
156                     if (typeHandler == null) {
157                         throw new ExecutorException(
158                                 "There was no TypeHandler found for parameter "
159                                         + propertyName + " of statement "
160                                         + mappedStatement.getId());
161                     }
162                     typeHandler.setParameter(ps, i + 1, value,
163                             parameterMapping.getJdbcType());
164                 }
165             }
166         }
167     }
168 
169 
170     private String generatePageSql(String sql, PageInfo page) {
171         if (page != null && (dialect !=null || !dialect.equals(""))) {
172             StringBuffer pageSql = new StringBuffer();
173             if ("mysql".equals(dialect)) {
174                 pageSql.append(sql);
175                 pageSql.append(" limit " + page.getCurrentResult() + ","
176                         + page.getShowCount());
177             } else if ("oracle".equals(dialect)) {
178                 pageSql.append("select * from (select tmp_tb.*,ROWNUM row_id from (");
179                 pageSql.append(sql);
180                 pageSql.append(")  tmp_tb where ROWNUM<=");
181                 pageSql.append(page.getCurrentResult() + page.getShowCount());
182                 pageSql.append(") where row_id>");
183                 pageSql.append(page.getCurrentResult());
184             }
185             return pageSql.toString();
186         } else {
187             return sql;
188         }
189     }
190 
191     public Object plugin(Object arg0) {
192         // TODO Auto-generated method stub
193         return Plugin.wrap(arg0, this);
194     }
195 
196     public void setProperties(Properties p) {
197         dialect = p.getProperty("dialect");
198         if (dialect ==null || dialect.equals("")) {
199             try {
200                 throw new PropertyException("dialect property is not found!");
201             } catch (PropertyException e) {
202                 // TODO Auto-generated catch block
203                 e.printStackTrace();
204             }
205         }
206         pageSqlId = p.getProperty("pageSqlId");
207         if (dialect ==null || dialect.equals("")) {
208             try {
209                 throw new PropertyException("pageSqlId property is not found!");
210             } catch (PropertyException e) {
211                 // TODO Auto-generated catch block
212                 e.printStackTrace();
213             }
214         }
215     }
216 
217 }

此插件有两个辅助类:PageInfo,ReflectHelper,你可以下载源代码参考。
写了插件之后，当然需要在 mybatis 的配置文件Configuration.xml 里配置这个插件

1 <plugins>
2         <plugin interceptor="com.yihaomen.util.PagePlugin">
3             <property name="dialect" value="mysql" />
4             <property name="pageSqlId" value=".*ListPage.*" />
5         </plugin>
6     </plugins>

请注意，这个插件定义了一个规则，也就是在mapper中sql语句的id 必须包含ListPage才能被拦截。否则将不会分页处理.

插件写好了，现在就可以在 spring mvc 中的controller 层中写一个方法来测试这个分页:

 1 @RequestMapping("/pagelist")
 2     public ModelAndView pageList(HttpServletRequest request,HttpServletResponse response){
 3         int currentPage = request.getParameter("page")==null?1:Integer.parseInt(request.getParameter("page"));
 4         int pageSize = 3;
 5         if (currentPage<=0){
 6             currentPage =1;
 7         }
 8         int currentResult = (currentPage-1) * pageSize;
 9         
10         System.out.println(request.getRequestURI());
11         System.out.println(request.getQueryString());
12         
13         PageInfo page = new PageInfo();
14         page.setShowCount(pageSize);
15         page.setCurrentResult(currentResult);
16         List<Article> articles=iUserOperation.selectArticleListPage(page,1);
17         
18         System.out.println(page);
19         
20         int totalCount = page.getTotalResult();
21         
22         int lastPage=0;
23         if (totalCount % pageSize==0){
24             lastPage = totalCount % pageSize;
25         }
26         else{
27             lastPage =1+ totalCount / pageSize;
28         }
29         
30         if (currentPage>=lastPage){
31             currentPage =lastPage;
32         }
33         
34         String pageStr = "";
35 
36         pageStr=String.format("<a href="%s">上一页</a>    <a href="%s">下一页</a>",
37                         request.getRequestURI()+"?page="+(currentPage-1),request.getRequestURI()+"?page="+(currentPage+1) );
38 
39 
40         //制定视图，也就是list.jsp
41         ModelAndView mav=new ModelAndView("list");
42         mav.addObject("articles",articles);
43         mav.addObject("pageStr",pageStr);
44         return mav;
45     }

然后运行程序，进入分页页面，你就可以看到结果了: