mybatis常用经典分页方法

来自棱镜学院-在线IT教育www.prismcollege.com

分页方法一：

可以查看如下代码，新建一个数据库分页基础类

package com.ssm.utils.pagination.pagebounds;import java.util.List;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.mybatis.spring.SqlSessionFactoryBean;import org.mybatis.spring.SqlSessionUtils;import org.springframework.beans.factory.annotation.Autowired;import com.github.miemiedev.mybatis.paginator.domain.PageBounds;/** * 用于数据库分页 * 使用方法如：public class XXXServiceImpl extends BasePageService implements IXXXService { * 然后代码中使用如：this.getPageList(XXXMapper.class,"selectObjectListMethod", sqlmapperParams, currentPage,pageSize); 其中currentPage由网页客户端保存，每次分页+1 * @author Administrator * */public class BasePageService {@Autowiredprivate SqlSessionFactoryBean sqlSessionFactoryBean;public List<?> getPageList(Class<?> mapperClass, String sqlId,Object sqlParameter, int pageIndex, int pageSize) {SqlSession session = null;try {SqlSessionFactory sessionFactory = sqlSessionFactoryBean.getObject();session = sessionFactory.openSession();if (pageIndex <= 0) {pageIndex = 1;}if (pageSize <= 0) {pageSize = 10;}PageBounds pageBounds = new PageBounds(pageIndex, pageSize);List<Object> pageList = session.selectList(mapperClass.getName()+ "." + sqlId, sqlParameter, pageBounds);return pageList;} catch (Exception e) {e.printStackTrace();} finally {session.close();}return null;}}

分页方法2：

定义一个page基本类，用于网页与后端之间的页面传输封装

import java.io.BufferedReader;import java.io.File;import java.io.FileInputStream;import java.io.InputStreamReader;import com.ssm.util.PageData;public class Page {private int isM1 = 0; // 每页显示记录数private int isM2; // 每页显示记录数private int showCount = 10; // 每页显示记录数private int totalPage; // 总页数private int totalResult; // 总记录数private int currentPage = 1; // 当前页private int currentResult; // 当前记录起始索引private boolean entityOrField; // true:需要分页的地方，传入的参数就是Page实体；false:需要分页的地方，传入的参数所代表的实体拥有Page属性private String pageStr; // 最终页面显示的底部翻页导航，详细见：getPageStr();private PageData pd = new PageData();private int begin;private int end;// 总行数，需要外接传入private int rows;public int getIsM1() {return isM1;}public void setIsM1(int isM1) {this.isM1 = isM1;}public int getIsM2() {return isM2;}public void setIsM2(int isM2) {this.isM2 = isM2;}public int getRows() {return rows;}public void setRows(int rows) {this.rows = rows;}public int getBegin() {begin = (currentPage - 1) * showCount;return begin;}public void setBegin(int begin) {this.begin = begin;}public int getEnd() {end = currentPage * showCount;return end;}public void setEnd(int end) {this.end = end;}public Page() {// 通过page。txt设置每页显示的条数// String xmpath =// String.valueOf(Thread.currentThread().getContextClassLoader().getResource(""));// // System.out.println(xmpath);// xmpath = xmpath.substring(6)+"page.txt";// // System.out.println(xmpath);// this.showCount = Integer.parseInt(readTxtFile(xmpath));}public int getTotalPage() {if (rows % showCount == 0)totalPage = rows / showCount;elsetotalPage = rows / showCount + 1;return totalPage;}public void setTotalPage(int totalPage) {this.totalPage = totalPage;}public int getTotalResult() {return totalResult;}public void setTotalResult(int totalResult) {this.totalResult = totalResult;}public int getCurrentPage() {if (currentPage <= 0)currentPage = 1;if (currentPage > getTotalPage())currentPage = getTotalPage();return currentPage;}public void setCurrentPage(int currentPage) {this.currentPage = currentPage;}// 用于显示页码public String getPageStr() {StringBuffer sb = new StringBuffer();if (totalResult > 0) {sb.append("<ul>\n");if (currentPage == 1) {sb.append("<li><a>共<font color=red>" + totalResult+ "</font>条</a></li>\n");sb.append("<li><input type=\"number\" value=\"\" id=\"toGoPage\" style=\"width:50px;text-align:center;float:left\" placeholder=\"页码\"/></li>\n");sb.append("<li style=\"cursor:pointer;\"><a onclick=\"toTZ();\"  class=\"btn btn-mini btn-success\">跳转</a></li>\n");sb.append("<li><a>首页</a></li>\n");sb.append("<li><a>上页</a></li>\n");} else {sb.append("<li><a>共<font color=red>" + totalResult+ "</font>条</a></li>\n");sb.append("<li><input type=\"number\" value=\"\" id=\"toGoPage\" style=\"width:35px;text-align:center;\" placeholder=\"页码\"/></li>\n");sb.append("<li style=\"cursor:pointer;\"><a onclick=\"toTZ();\"  class=\"btn btn-mini btn-success\">跳转</a></li>\n");sb.append("<li style=\"cursor:pointer;\"><a onclick=\"nextPage(1)\">首页</a></li>\n");sb.append("<li style=\"cursor:pointer;\"><a onclick=\"nextPage("+ (currentPage - 1) + ")\">上页</a></li>\n");}int showTag = 3; // 分页标签显示数量int startTag = 1;if (currentPage > showTag) {startTag = currentPage - 1;}int endTag = startTag + showTag - 1;for (int i = startTag; i <= totalPage && i <= endTag; i++) {if (currentPage == i)sb.append("<li class=\"current\"><a>" + i + "</a></li>\n");elsesb.append("<li style=\"cursor:pointer;\"><a onclick=\"nextPage("+ i + ")\">" + i + "</a></li>\n");}if (currentPage == totalPage) {sb.append("<li><a>下页</a></li>\n");sb.append("<li><a>尾页</a></li>\n");} else {sb.append("<li style=\"cursor:pointer;\"><a onclick=\"nextPage("+ (currentPage + 1) + ")\">下页</a></li>\n");sb.append("<li style=\"cursor:pointer;\"><a onclick=\"nextPage("+ totalPage + ")\">尾页</a></li>\n");}sb.append("<li><a>第" + currentPage + "页</a></li>\n");sb.append("<li><a>共" + totalPage + "页</a></li>\n");// sb.append("<li style=\"cursor:pointer;\"><a onclick=\"toTZ();\"  class=\"btn btn-mini btn-success\">跳转</a></li>\n");sb.append("</ul>\n");sb.append("<script type=\"text/javascript\">\n");sb.append("function nextPage(page){");sb.append("if(true && document.forms[0]){\n");sb.append("var url = document.forms[0].getAttribute(\"action\");\n");sb.append("if(url.indexOf('?')>-1){url += \"&"+ (entityOrField ? "currentPage" : "page.currentPage")+ "=\";}\n");sb.append("else{url += \"?"+ (entityOrField ? "currentPage" : "page.currentPage")+ "=\";}\n");sb.append("document.forms[0].action = url+page;\n");sb.append("document.forms[0].submit();\n");sb.append("}else{\n");sb.append("var url = document.location+'';\n");sb.append("if(url.indexOf('?')>-1){\n");sb.append("if(url.indexOf('currentPage')>-1){\n");sb.append("var reg = /currentPage=\\d*/g;\n");sb.append("url = url.replace(reg,'currentPage=');\n");sb.append("}else{\n");sb.append("url += \"&"+ (entityOrField ? "currentPage" : "page.currentPage")+ "=\";\n");sb.append("}\n");sb.append("}else{url += \"?"+ (entityOrField ? "currentPage" : "page.currentPage")+ "=\";}\n");sb.append("document.location = url + page;\n");sb.append("}\n");sb.append("}\n");sb.append("function toTZ(){");sb.append("var toPaggeVlue = document.getElementById(\"toGoPage\").value;");sb.append("if(toPaggeVlue == ''){document.getElementById(\"toGoPage\").value=1;return;}");sb.append("if(isNaN(Number(toPaggeVlue))){document.getElementById(\"toGoPage\").value=1;return;}");sb.append("nextPage(toPaggeVlue);");sb.append("}\n");sb.append("</script>\n");}pageStr = sb.toString();return pageStr;}public void setPageStr(String pageStr) {this.pageStr = pageStr;}public int getShowCount() {return showCount;}public void setShowCount(int showCount) {this.showCount = showCount;}public int getCurrentResult() {currentResult = (getCurrentPage() - 1) * getShowCount();if (currentResult < 0)currentResult = 0;return currentResult;}public void setCurrentResult(int currentResult) {this.currentResult = currentResult;}public boolean isEntityOrField() {return entityOrField;}public void setEntityOrField(boolean entityOrField) {this.entityOrField = entityOrField;}public PageData getPd() {return pd;}public void setPd(PageData pd) {this.pd = pd;}// 读取文件public String readTxtFile(String filePath) {try {String encoding = "utf-8";File file = new File(filePath);if (file.isFile() && file.exists()) { // 判断文件是否存在InputStreamReader read = new InputStreamReader(new FileInputStream(file), encoding);// 考虑到编码格式BufferedReader bufferedReader = new BufferedReader(read);String lineTxt = null;while ((lineTxt = bufferedReader.readLine()) != null) {return lineTxt;}read.close();} else {System.out.println("找不到指定的文件");}} catch (Exception e) {System.out.println("读取文件内容出错");e.printStackTrace();}return "";}}

下面这个类PageData为扩展使用，目前暂未使用

package com.ssm.util;import java.util.Collection;import java.util.HashMap;import java.util.Iterator;import java.util.Map;import java.util.Set;import javax.servlet.http.HttpServletRequest;public class PageData extends HashMap implements Map{private static final long serialVersionUID = 1L;Map map = null;HttpServletRequest request;//获取请求参数和请求参数值的mappublic PageData(HttpServletRequest request){this.request = request;Map properties = request.getParameterMap();Map returnMap = new HashMap(); Iterator entries = properties.entrySet().iterator(); Map.Entry entry; String name = "";  String value = "";  while (entries.hasNext()) {entry = (Map.Entry) entries.next(); name = (String) entry.getKey(); Object valueObj = entry.getValue(); if(null == valueObj){ value = ""; }else if(valueObj instanceof String[]){ String[] values = (String[])valueObj;for(int i=0;i<values.length;i++){  value = values[i] + ",";}value = value.substring(0, value.length()-1); }else{value = valueObj.toString(); }returnMap.put(name, value); }map = returnMap;}public PageData() {map = new HashMap();}@Overridepublic Object get(Object key) {Object obj = null;if(map.get(key) instanceof Object[]) {Object[] arr = (Object[])map.get(key);obj = request == null ? arr:(request.getParameter((String)key) == null ? arr:arr[0]);} else {obj = map.get(key);}return obj;}public String getString(Object key) {return (String)get(key);}@SuppressWarnings("unchecked")@Overridepublic Object put(Object key, Object value) {return map.put(key, value);}@Overridepublic Object remove(Object key) {return map.remove(key);}public void clear() {map.clear();}public boolean containsKey(Object key) {return map.containsKey(key);}public boolean containsValue(Object value) {return map.containsValue(value);}public Set entrySet() {return map.entrySet();}public boolean isEmpty() {return map.isEmpty();}public Set keySet() {return map.keySet();}@SuppressWarnings("unchecked")public void putAll(Map t) {map.putAll(t);}public int size() {return map.size();}public Collection values() {return map.values();}}

接下来可根据page定义一个更加具体的页面和后端传输的页面封装类，如

import java.io.Serializable;import java.util.List;public class XXXPage extends Page implements Serializable{private String identity_type;private String username;private String name;private String city;private List<String> freeTimeList;private String[] jobType;private List<String> createdTimeList;********************************public String getIdentity_type() {return identity_type;}public void setIdentity_type(String identity_type) {this.identity_type = identity_type;}}

下一步可在controller的action中使用 如

public String listPageData(Model model, XXXPage page,HttpServletRequest request) {

xxxxxxxxx;

}
并在传回客户端时可 model.addAttribute("page", page);

其中 mybatis中操作

List<XXXBean> list = userMapper.findXXXByPage(page);//得到满足条件的招聘用户列表
int rows = xxxMapper.findRows(page);//得到满足条件的行数

page.setRows(rows);

只需要在mapper中sql中 进行sql分页  limit  #{begin,jdbcType=INTEGER},#{showCount,jdbcType=INTEGER} 即可。

分页方法3：

修改mybatis自带的rowbound内存分页为物理分页

新建类Page_duan 

import java.util.List;import java.util.Map;import org.codehaus.jackson.map.ObjectMapper;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import com.google.common.base.Joiner;import com.google.common.collect.Lists;import com.google.common.collect.Maps;public class Page_duan {private static final long serialVersionUID = -399284318168302833L;  private static final Logger logger = LoggerFactory.getLogger(Page_duan.class);private static ObjectMapper mapper = new ObjectMapper();public static String DEFAULT_PAGESIZE = "10";private int pageNo; // 当前页码private int pageSize; // 每页行数private int totalRecord; // 总记录数private int totalPage; // 总页数private Map<String, String> params; // 查询条件private Map<String, List<String>> paramLists; // 数组查询条件private String searchUrl; // Url地址private String pageNoDisp; // 可以显示的页号(分隔符"|"，总页数变更时更新)private Page_duan() {pageNo = 1;pageSize = Integer.valueOf(DEFAULT_PAGESIZE);totalRecord = 0;totalPage = 0;params = Maps.newHashMap();paramLists = Maps.newHashMap();searchUrl = "";pageNoDisp = "";}public int getPageNo() {return pageNo;}public void setPageNo(int pageNo) {this.pageNo = pageNo;}public int getPageSize() {return pageSize;}public void setPageSize(int pageSize) {this.pageSize = pageSize;}public int getTotalRecord() {return totalRecord;}public void setTotalRecord(int totalRecord) {this.totalRecord = totalRecord;refreshPage();}public int getTotalPage() {return totalPage;}public void setTotalPage(int totalPage) {this.totalPage = totalPage;}public Map<String, String> getParams() {return params;}public void setParams(Map<String, String> params) {this.params = params;}public Map<String, List<String>> getParamLists() {return paramLists;}public void setParamLists(Map<String, List<String>> paramLists) {this.paramLists = paramLists;}public String getSearchUrl() {return searchUrl;}public void setSearchUrl(String searchUrl) {this.searchUrl = searchUrl;}public String getPageNoDisp() {return pageNoDisp;}public void setPageNoDisp(String pageNoDisp) {this.pageNoDisp = pageNoDisp;}public static Page_duan newBuilder(int pageNo, int pageSize, String url) {Page_duan page = new Page_duan();page.setPageNo(pageNo);page.setPageSize(pageSize);page.setSearchUrl(url);return page;}/** * 查询条件转JSON */public String getParaJson() {Map<String, Object> map = Maps.newHashMap();for (String key : params.keySet()) {if (params.get(key) != null) {map.put(key, params.get(key));}}String json = "";try {json = mapper.writeValueAsString(map);} catch (Exception e) {logger.error("转换JSON失败", params, e);}return json;}/** * 数组查询条件转JSON */public String getParaListJson() {Map<String, Object> map = Maps.newHashMap();for (String key : paramLists.keySet()) {List<String> lists = paramLists.get(key);if (lists != null && lists.size() > 0) {map.put(key, lists);}}String json = "";try {json = mapper.writeValueAsString(map);} catch (Exception e) {logger.error("转换JSON失败", params, e);}return json;}/** * 总件数变化时，更新总页数并计算显示样式 */private void refreshPage() {// 总页数计算totalPage = totalRecord % pageSize == 0 ? totalRecord / pageSize: (totalRecord / pageSize + 1);// 防止超出最末页（浏览途中数据被删除的情况）if (pageNo > totalPage && totalPage != 0) {pageNo = totalPage;}pageNoDisp = computeDisplayStyleAndPage();}/** * 计算页号显示样式 这里实现以下的分页样式("[]"代表当前页号)，可根据项目需求调整 [1],2,3,4,5,6,7,8..12,13 * 1,2..5,6,[7],8,9..12,13 1,2..6,7,8,9,10,11,12,[13] */private String computeDisplayStyleAndPage() {List<Integer> pageDisplays = Lists.newArrayList();if (totalPage <= 11) {for (int i = 1; i <= totalPage; i++) {pageDisplays.add(i);}} else if (pageNo < 7) {for (int i = 1; i <= 8; i++) {pageDisplays.add(i);}pageDisplays.add(0);// 0 表示 省略部分(下同)pageDisplays.add(totalPage - 1);pageDisplays.add(totalPage);} else if (pageNo > totalPage - 6) {pageDisplays.add(1);pageDisplays.add(2);pageDisplays.add(0);for (int i = totalPage - 7; i <= totalPage; i++) {pageDisplays.add(i);}} else {pageDisplays.add(1);pageDisplays.add(2);pageDisplays.add(0);for (int i = pageNo - 2; i <= pageNo + 2; i++) {pageDisplays.add(i);}pageDisplays.add(0);pageDisplays.add(totalPage - 1);pageDisplays.add(totalPage);}return Joiner.on("|").join(pageDisplays.toArray());}}

新建如下类

import java.util.Properties;import org.apache.ibatis.plugin.Interceptor;import org.apache.ibatis.plugin.Invocation;import java.sql.Connection;  import java.sql.PreparedStatement;  import java.sql.ResultSet;  import java.util.Properties;  import org.apache.commons.jxpath.JXPathContext;  import org.apache.commons.jxpath.JXPathNotFoundException;  import org.apache.ibatis.executor.Executor;  import org.apache.ibatis.executor.parameter.DefaultParameterHandler;  import org.apache.ibatis.mapping.BoundSql;  import org.apache.ibatis.mapping.MappedStatement;  import org.apache.ibatis.mapping.MappedStatement.Builder;  import org.apache.ibatis.mapping.ParameterMapping;  import org.apache.ibatis.mapping.SqlSource;  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.session.ResultHandler;  import org.apache.ibatis.session.RowBounds;@Intercepts({@Signature(type=Executor.class,method="query",args={ MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class })})  public class PageHelper_duan implements Interceptor {@Overridepublic Object intercept(Invocation invocation) throws Throwable {            //当前环境 MappedStatement，BoundSql，及sql取得      MappedStatement mappedStatement=(MappedStatement)invocation.getArgs()[0];          Object parameter = invocation.getArgs()[1];       BoundSql boundSql = mappedStatement.getBoundSql(parameter);       String originalSql = boundSql.getSql().trim();      Object parameterObject = boundSql.getParameterObject();        //Page对象获取，“信使”到达拦截器！      Page_duan page = searchPageWithXpath(boundSql.getParameterObject(),".","page","*/page");        if(page!=null ){        //Page对象存在的场合，开始分页处理        String countSql = getCountSql(originalSql);        Connection connection=mappedStatement.getConfiguration()      .getEnvironment().getDataSource().getConnection()  ;                  PreparedStatement countStmt = connection.prepareStatement(countSql);          BoundSql countBS = copyFromBoundSql(mappedStatement, boundSql, countSql);        DefaultParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, countBS);        parameterHandler.setParameters(countStmt);        ResultSet rs = countStmt.executeQuery();        int totpage=0;        if (rs.next()) {            totpage = rs.getInt(1);          }        rs.close();          countStmt.close();          connection.close();                //分页计算        page.setTotalRecord(totpage);        //对原始Sql追加limit        int offset = (page.getPageNo() - 1) * page.getPageSize();        StringBuffer sb = new StringBuffer();        sb.append(originalSql).append(" limit ").append(offset).append(",").append(page.getPageSize());        BoundSql newBoundSql = copyFromBoundSql(mappedStatement, boundSql, sb.toString());        MappedStatement newMs = copyFromMappedStatement(mappedStatement,new BoundSqlSqlSource(newBoundSql));          invocation.getArgs()[0]= newMs;        }      return invocation.proceed();          }  /**    * 根据给定的xpath查询Page对象    */    private Page_duan searchPageWithXpath(Object o,String... xpaths) {      JXPathContext context = JXPathContext.newContext(o);      Object result;      for(String xpath : xpaths){        try {          result = context.selectSingleNode(xpath);        } catch (JXPathNotFoundException e) {          continue;        }        if ( result instanceof Page_duan ){          return (Page_duan)result;        }      }      return null;    }      /**    * 复制MappedStatement对象    */    private MappedStatement copyFromMappedStatement(MappedStatement ms,SqlSource newSqlSource) {      Builder builder = new Builder(ms.getConfiguration(),ms.getId(),newSqlSource,ms.getSqlCommandType());            builder.resource(ms.getResource());      builder.fetchSize(ms.getFetchSize());      builder.statementType(ms.getStatementType());      builder.keyGenerator(ms.getKeyGenerator());     for(String pro : ms.getKeyProperties())    builder.keyProperty(pro);     builder.timeout(ms.getTimeout());      builder.parameterMap(ms.getParameterMap());      builder.resultMaps(ms.getResultMaps());      builder.resultSetType(ms.getResultSetType());      builder.cache(ms.getCache());      builder.flushCacheRequired(ms.isFlushCacheRequired());      builder.useCache(ms.isUseCache());      return builder.build();    }      /**    * 复制BoundSql对象    */    private BoundSql copyFromBoundSql(MappedStatement ms, BoundSql boundSql, String sql) {      BoundSql newBoundSql = new BoundSql(ms.getConfiguration(),sql, boundSql.getParameterMappings(), boundSql.getParameterObject());      for (ParameterMapping mapping : boundSql.getParameterMappings()) {          String prop = mapping.getProperty();          if (boundSql.hasAdditionalParameter(prop)) {              newBoundSql.setAdditionalParameter(prop, boundSql.getAdditionalParameter(prop));          }      }      return newBoundSql;    }      /**    * 根据原Sql语句获取对应的查询总记录数的Sql语句    */    private String getCountSql(String sql) {      return "SELECT COUNT(*) FROM (" + sql + ") aliasForPage";    }      public class BoundSqlSqlSource implements SqlSource {          BoundSql boundSql;          public BoundSqlSqlSource(BoundSql boundSql) {            this.boundSql = boundSql;          }          public BoundSql getBoundSql(Object parameterObject) {            return boundSql;          }        }      @Override  public Object plugin(Object arg0) {       return Plugin.wrap(arg0, this);    }@Overridepublic void setProperties(Properties arg0) {// TODO Auto-generated method stub}  }

java，架构技术学习 欢迎加QQ群交流：36861484914