[Mybatis]分页(基于插件)

转载自：http://www.yihaomen.com/article/java/326.htm

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

在实际的项目中，分页是肯定需要的。而且是物理分页，不是内存分页。对于物理分页方案，不同的数据库，有不同的实现方法,对于mysql 来说 就是利用 limit offset,pagesize 方式来实现的。oracle 是通过rownum 来实现的，如果你熟悉相关数据库的操作，是一样的很好扩展，本文以mysql 为例子来讲述.先看一下效果图(源代码在文章最后提供下载):

实现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 是你传入的参数类，或者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<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>

请注意，这个插件定义了一个规则，也就是在mapper中sql语句的id 必须包含ListPage才能被拦截。否则将不会分页处理。还有一点就是如果你用的oracle，那就相应的更改方言。

插件写好了，现在就可以在 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;}

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

源代码下载: 点击下载此文件

我复用崔希凡老师的分页导航，仿照咱们CSDN的分页导航写了一个分页导航，效果如下:

源代码下载:http://download.csdn.net/detail/gsf2451013894/8847763

转载自：http://www.yihaomen.com/article/java/326.htm