MyBatis分页处理

查看原文：http://www.ibloger.net/article/291.html

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

大牛中的文章已经说明很清楚了，这里就说一下使用方法吧，我也是刚接触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;}@Overridepublic 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();// 重写sqlString pageSql = buildPageSql(sql, page);// 重写分页sqlmetaStatementHandler.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;}@Overridepublic Object plugin(Object target) {if (target instanceof StatementHandler || target instanceof ResultSetHandler) {return Plugin.wrap(target, this);} else {return target;}}@Overridepublic 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;}@Overridepublic 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);}

实现类方法

@Overridepublic List<Visitlogs> findAllVisitlogs() { return sqlSession.selectList(Visitlogs.class.getName()+"_Mapper.findAllVisitlogs");   }@Overridepublic 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对象 */@Overridepublic Object processObjectValue(String propertyName, Object date, JsonConfig config) {return sdf.format(date);}/** 处理数组中的Date对象 */@Overridepublic 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"}]}