mybatis :实现mybatis分页

上一篇文章里已经讲到了mybatis与spring MVC的集成，并且做了一个列表展示，显示出所有article 列表，但没有用到分页，在实际的项目中，分页是肯定需要的。而且是物理分页，不是内存分页。对于物理分页方案，不同的数据库，有不同的实现方法,对于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才能被拦截。否则将不会分页处理.

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

    }

　　

点击下载此文件

一号门原创