上一篇文章里已經(jīng)講到了 Mybatis與 Spring MVC 的集成，并且做了一個列表展示，顯示出所有 article 列表，但沒有用到分頁，在實際的項目中，分頁是肯定需要的。而且是物理分頁，不是內存分頁。對于物理分頁方案，不同的數(shù)據(jù)庫，有不同的實現(xiàn)方法,對于 Mysql 來說 就是利用 limit offset,pagesize 方式來實現(xiàn)的。oracle 是通過 rownum 來實現(xiàn)的，如果你熟悉相關數(shù)據(jù)庫的操作，是一樣的很好擴展，本文以 Mysql 為例子來講述。先看一下效果圖(源代碼在文章最后提供下載):
http://wiki.jikexueyuan.com/project/mybatis-in-action/images/mybatis8.jpg" alt="" />
實現(xiàn) Mybatis 物理分頁，一個最簡單的方式是，是在你的 mapper 的 SQL 語句中直接寫類似如下方式 :
<select id="getUserArticles" parameterType="Your_params" resultMap="resultUserArticleList">
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}
</select>請注意這里的 parameterType 是你傳入的參數(shù)類，或者map ，里面包含了 offset,pagesize ,和其他你需要的參數(shù)，用這種方式，肯定可以實現(xiàn)分頁。這是簡單的一種方式。但更通用的一種方式是用 mybatis 插件的方式. 參考了網(wǎng)上的很多資料 ，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("總數(shù)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<String, Object> map = (Map<String, Object>)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<ParameterMapping> 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 里配置這個插件
<plugins>
<plugin interceptor="com.yihaomen.util.PagePlugin">
<property name="dialect" value="mysql" />
<property name="pageSqlId" value=".*ListPage.*" />
</plugin>
</plugins>請注意，這個插件定義了一個規(guī)則，也就是在 mapper 中 sql 語句的 id 必須包含 ListPage 才能被攔截。否則將不會分頁處理。
插件寫好了，現(xiàn)在就可以在 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<Article> 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("<a href=\"%s\">上一頁</a> <a href=\"%s\">下一頁</a>",
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;
}然后運行程序，進入分頁頁面，你就可以看到結果了:
http://wiki.jikexueyuan.com/project/mybatis-in-action/images/mybatis9.jpg" alt="" />
掃碼關注鍍金池微信號
手機訂課，優(yōu)惠多多