【SSM】MyBatis分页处理

参考大牛文章：http://blog.csdn.net/isea533/article/details/23831273

分页插件示例：http://blog.csdn.net/isea533/article/details/24700339

最新版分页插件：http://blog.csdn.net/isea533/article/details/25505413

项目地址：http://git.oschina.net/free/Mybatis_PageHelper

大牛中的文章已经说明很清楚了，这里就说一下使用方法吧，我也是刚接触Mybatis不久，记录下来，便于以后调用和复习

第一步：

在包中创建一个PageHelper工具类，实现拦截器Interceptor，下面代码可以直接拷贝（对于Oracle用户可以不用修改任何内容，对于其他数据库，可根据说明进行修改）

我这里用的SpringMVC和MyBatis方式的例子，核心代码如下

package com.founder.utils;  
  
import org.apache.ibatis.executor.parameter.ParameterHandler;  
import org.apache.ibatis.executor.resultset.ResultSetHandler;  
import org.apache.ibatis.executor.statement.StatementHandler;  
import org.apache.ibatis.mapping.BoundSql;  
import org.apache.ibatis.mapping.MappedStatement;  
import org.apache.ibatis.plugin.*;  
import org.apache.ibatis.reflection.MetaObject;  
import org.apache.ibatis.reflection.SystemMetaObject;  
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;  
import org.apache.log4j.Logger;  
  
import java.sql.*;  
import java.util.List;  
import java.util.Properties;  
import java.sql.Connection;  
import org.apache.ibatis.plugin.Interceptor;  
import org.apache.ibatis.plugin.Intercepts;  
import org.apache.ibatis.plugin.Invocation;  
import org.apache.ibatis.plugin.Signature;  
  
/** 
 * Mybatis - 通用分页拦截器 Mybatis拦截器控制分页查询 
 * 真正生成Statement并执行sql的语句是StatementHandler接口的某个实现， 
 * 这样就可以写个插件对StatementHandler的行为进行拦截 
 */  
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }),  
        @Signature(type = ResultSetHandler.class, method = "handleResultSets", args = { Statement.class }) })  
public class PageHelper implements Interceptor {  
  
    private static final Logger logger = Logger.getLogger(PageHelper.class);  
    public static final ThreadLocal<Page> localPage = new ThreadLocal<Page>();  
  
    /** 
     * 开始分页 
     *  
     * @param pageNum 
     * @param pageSize 
     */  
    public static void startPage(int pageNum, int pageSize) {  
        localPage.set(new Page(pageNum, pageSize));  
    }  
  
    /** 
     * 结束分页并返回结果，该方法必须被调用，否则localPage会一直保存下去，直到下一次startPage 
     * @return 
     */  
    public static Page endPage() {  
        Page page = localPage.get();  
        localPage.remove();  
        return page;  
    }  
  
    @Override  
    public Object intercept(Invocation invocation) throws Throwable {  
        if (localPage.get() == null) {  
            return invocation.proceed();  
        }  
        if (invocation.getTarget() instanceof StatementHandler) {  
            StatementHandler statementHandler = (StatementHandler) invocation.getTarget();  
            MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler);  
            // 分离代理对象链(由于目标类可能被多个拦截器拦截，从而形成多次代理，通过下面的两次循环  
            // 可以分离出最原始的的目标类)  
            while (metaStatementHandler.hasGetter("h")) {  
                Object object = metaStatementHandler.getValue("h");  
                metaStatementHandler = SystemMetaObject.forObject(object);  
            }  
            // 分离最后一个代理对象的目标类  
            while (metaStatementHandler.hasGetter("target")) {  
                Object object = metaStatementHandler.getValue("target");  
                metaStatementHandler = SystemMetaObject.forObject(object);  
            }  
            MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");  
            // 分页信息if (localPage.get() != null) {  
            Page page = localPage.get();  
            BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");  
            // 分页参数作为参数对象parameterObject的一个属性  
            String sql = boundSql.getSql();  
            // 重写sql  
            String pageSql = buildPageSql(sql, page);  
            // 重写分页sql  
            metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);  
            Connection connection = (Connection) invocation.getArgs()[0];  
            // 重设分页参数里的总页数等  
            setPageParameter(sql, connection, mappedStatement, boundSql, page);  
            // 将执行权交给下一个拦截器  
            return invocation.proceed();  
        } else if (invocation.getTarget() instanceof ResultSetHandler) {  
            Object result = invocation.proceed();  
            Page page = localPage.get();  
            page.setResult((List) result);  
            return result;  
        }  
        return null;  
    }  
  
    @Override  
    public Object plugin(Object target) {  
        if (target instanceof StatementHandler || target instanceof ResultSetHandler) {  
            return Plugin.wrap(target, this);  
        } else {  
            return target;  
        }  
    }  
  
    @Override  
    public void setProperties(Properties arg0) {  
  
    }  
  
    /** 
     * 修改原SQL为分页SQL 
     * @param sql 
     * @param page 
     * @return 
     */  
    private String buildPageSql(String sql, Page page) {  
        StringBuilder pageSql = new StringBuilder(200);  
        pageSql.append("select * from ( select temp.*, rownum row_id from ( ");  
        pageSql.append(sql);  
        pageSql.append(" ) temp where rownum <= ").append(page.getEndRow());  
        pageSql.append(") where row_id > ").append(page.getStartRow());  
        return pageSql.toString();  
    }  
  
    /** 
     * 获取总记录数 
     * @param sql 
     * @param connection 
     * @param mappedStatement 
     * @param boundSql 
     * @param page 
     */  
    private void setPageParameter(String sql, Connection connection, MappedStatement mappedStatement, BoundSql boundSql, Page page) {  
        // 记录总记录数  
        String countSql = "select count(0) from (" + sql + ")";  
        PreparedStatement countStmt = null;  
        ResultSet rs = null;  
        try {  
            countStmt = connection.prepareStatement(countSql);  
            BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), boundSql.getParameterObject());  
            setParameters(countStmt, mappedStatement, countBS, boundSql.getParameterObject());  
            rs = countStmt.executeQuery();  
            int totalCount = 0;  
            if (rs.next()) {  
                totalCount = rs.getInt(1);  
            }  
            page.setTotal(totalCount);  
            int totalPage = totalCount / page.getPageSize() + ((totalCount % page.getPageSize() == 0) ? 0 : 1);  
            page.setPages(totalPage);  
        } catch (SQLException e) {  
            logger.error("Ignore this exception", e);  
        } finally {  
            try {  
                rs.close();  
            } catch (SQLException e) {  
                logger.error("Ignore this exception", e);  
            }  
            try {  
                countStmt.close();  
            } catch (SQLException e) {  
                logger.error("Ignore this exception", e);  
            }  
        }  
    }  
  
    /** 
     * 代入参数值 
     * @param ps 
     * @param mappedStatement 
     * @param boundSql 
     * @param parameterObject 
     * @throws SQLException 
     */  
    private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws SQLException {  
        ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);  
        parameterHandler.setParameters(ps);  
    }  
  
    /** 
     * Description: 分页   
     */  
    public static class Page<E> {  
        private int pageNum;  
        private int pageSize;  
        private int startRow;  
        private int endRow;  
        private long total;  
        private int pages;  
        private List<E> result;  
  
        public Page(int pageNum, int pageSize) {  
            this.pageNum = pageNum;  
            this.pageSize = pageSize;  
            this.startRow = pageNum > 0 ? (pageNum - 1) * pageSize : 0;  
            this.endRow = pageNum * pageSize;  
        }  
  
        public List<E> getResult() {  
            return result;  
        }  
  
        public void setResult(List<E> result) {  
            this.result = result;  
        }  
  
        public int getPages() {  
            return pages;  
        }  
  
        public void setPages(int pages) {  
            this.pages = pages;  
        }  
  
        public int getEndRow() {  
            return endRow;  
        }  
  
        public void setEndRow(int endRow) {  
            this.endRow = endRow;  
        }  
  
        public int getPageNum() {  
            return pageNum;  
        }  
  
        public void setPageNum(int pageNum) {  
            this.pageNum = pageNum;  
        }  
  
        public int getPageSize() {  
            return pageSize;  
        }  
  
        public void setPageSize(int pageSize) {  
            this.pageSize = pageSize;  
        }  
  
        public int getStartRow() {  
            return startRow;  
        }  
  
        public void setStartRow(int startRow) {  
            this.startRow = startRow;  
        }  
  
        public long getTotal() {  
            return total;  
        }  
  
        public void setTotal(long total) {  
            this.total = total;  
        }  
  
        @Override  
        public String toString() {  
            return "Page{" + "pageNum=" + pageNum + ", pageSize=" + pageSize + ", startRow=" + startRow + ", endRow=" + endRow + ", total=" + total + ", pages=" + pages + '}';  
        }  
    }  
}

第二步，配置Mybatis-config.xml文件

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">  
<configuration>  
  
    <!-- 拦截，用于mybatis分页处理 -->  
    <plugins>  
        <plugin interceptor="com.founder.utils.PageHelper"></plugin>  
    </plugins>  
      
    <mappers>  
        <mapper resource="com/founder/model/Visitlogs_Mapper.xml" />  
    </mappers>  
  
</configuration>

第三步：编辑Dao层和实现类，这里一个方法没有分页，一个使用分页

package com.founder.dao;  
  
import java.util.List;  
  
import com.founder.model.Visitlogs;  
import com.founder.utils.PageHelper;  
  
public interface VisitlogsDao {  
    /** 
     * 查询所有走访日志 
     * @return 
     */  
    public List<Visitlogs> findAllVisitlogs();  
  
    /** 
     * 分页查询所有走访日志 
     * @return 
     */  
    public PageHelper.Page<Visitlogs> findAllVisitlogs(int pageNumber,int pageSize);  
}

实现类方法

@Override  
public List<Visitlogs> findAllVisitlogs() {  
     return sqlSession.selectList(Visitlogs.class.getName()+"_Mapper.findAllVisitlogs");     
}  
@Override  
public PageHelper.Page<Visitlogs> findAllVisitlogs(int pageNumber, int pageSize) {  
    PageHelper.startPage(pageNumber,pageSize);  // 开启分页   
      sqlSession.selectList(Visitlogs.class.getName()+"_Mapper.findAllVisitlogs");     
     return PageHelper.endPage();  // 必须使用  
}

最后一步，配置Controller类调用分页，我这里进行了JSON拼接，对easy ui的datagrid使用，核心的两行代码加粗显示

/** 
 * 查找所有走访日志 
 *  
 * @param request 
 * @param response 
 */  
@RequestMapping("/findAllVisitlogs")  
public void findAllVisitlogs(HttpServletRequest request, HttpServletResponse response) {  
    String obj_page = request.getParameter("page");  
    String obj_rows = request.getParameter("rows");  
    int page = obj_page==""?1:Integer.valueOf(obj_page);    // 当前位于第几页，基于Jquery easyui 参数提供  
    int rows = obj_page==""?10:Integer.valueOf(obj_rows);   // 每页显示多少条数据，基于Jquery easyui 参数提供  
      
    System.out.println("page: "+page+" , rows: "+rows);  
      
    <strong>Page<Visitlogs> visitlogs = visitlogsService.findAllVisitlogs(page,rows);   // 分页查询结果  
      List<Visitlogs> listVisitlogs = visitlogs.getResult();  // 取出分页结果</strong>  

    JsonConfig jsonConfig = new JsonConfig();  
    jsonConfig.setExcludes(new String []{"startRow","total"});  // 排除json格式化的字段  
      
    // 处理日期字段显示  
    jsonConfig.registerJsonValueProcessor(Date.class, new JsonValueProcessor() {  
        // 自定义日期格式  
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  

        /** 处理单个Date对象 */  
        @Override  
        public Object processObjectValue(String propertyName, Object date, JsonConfig config) {  
            return sdf.format(date);  
        }  

        /** 处理数组中的Date对象 */  
        @Override  
        public Object processArrayValue(Object date, JsonConfig config) {  
            return sdf.format(date);  
        }  
    });  

    String json = JSONArray.fromObject(listVisitlogs, jsonConfig).toString();  
    json = "{\"total\":\""+visitlogs.getTotal()+"\",\"rows\":"+json+"}";  
    System.out.println("2、" + json);  
    try {  
        response.setCharacterEncoding("utf-8");  
        PrintWriter out = response.getWriter();  
        out.print(json);  
        out.close();  
    } catch (IOException e) {  
        e.printStackTrace();  
    }  
}

打印结果：

{"total":"12","rows":[{"code":"v0001","id":1,"reportStateCode":"001","responsibiGridNo":1,"responsibleId":1,"visitDataTime":"2014-11-03","visitObjectType":"001","visitType":"001","workCont":"监督市场，把控风险","workTheme":"监督市场"},{"code":"v0002","id":2,"reportStateCode":"001","responsibiGridNo":1,"responsibleId":1,"visitDataTime":"2014-11-03","visitObjectType":"001","visitType":"002","workCont":"审批","workTheme":"审批"},{"code":"v0003","id":3,"reportStateCode":"001","responsibiGridNo":1,"responsibleId":1,"visitDataTime":"2014-11-03","visitObjectType":"002","visitType":"001","workCont":"aa","workTheme":"a"},{"code":"v0004","id":4,"reportStateCode":"002","responsibiGridNo":1,"responsibleId":1,"visitDataTime":"2014-11-03","visitObjectType":"001","visitType":"001","workCont":"bb","workTheme":"b"},{"code":"v0005","id":5,"reportStateCode":"002","responsibiGridNo":1,"responsibleId":1,"visitDataTime":"2014-11-03","visitObjectType":"001","visitType":"001","workCont":"cc","workTheme":"c"},{"code":"v0006","id":6,"reportStateCode":"001","responsibiGridNo":1,"responsibleId":1,"visitDataTime":"2014-11-03","visitObjectType":"001","visitType":"002","workCont":"dd","workTheme":"d"},{"code":"v0007","id":7,"reportStateCode":"001","responsibiGridNo":1,"responsibleId":1,"visitDataTime":"2014-11-03","visitObjectType":"002","visitType":"001","workCont":"ee","workTheme":"e"},{"code":"v0008","id":8,"reportStateCode":"001","responsibiGridNo":1,"responsibleId":1,"visitDataTime":"2014-11-03","visitObjectType":"001","visitType":"001","workCont":"ff","workTheme":"f"},{"code":"v0009","id":9,"reportStateCode":"001","responsibiGridNo":1,"responsibleId":1,"visitDataTime":"2014-11-03","visitObjectType":"001","visitType":"001","workCont":"gg","workTheme":"g"},{"code":"v0010","id":10,"reportStateCode":"001","responsibiGridNo":1,"responsibleId":1,"visitDataTime":"2014-11-03","visitObjectType":"001","visitType":"002","workCont":"hh","workTheme":"h"}]}