github上有一个专门针对mybatis的物理分页开源项目：mybatis-paginator，兼容目前绝大多数主流数据库，十分好用，下面是使用步骤：
环境：struts2 + spring + mybatis
一、pom.xml中添加依赖项
1 <dependency> 2 <groupId>com.github.miemiedev</groupId> 3 <artifactId>mybatis-paginator</artifactId> 4 <version>1.2.15</version> 5 </dependency>
二、mybatis映射文件中按常规写sql语句
1 <select id="getFsuList" resultType="N_CA_FSU"> 2 Select t.RECID recId, 3 t.GROSSWEIGHT grossWeight, 4 t.TOTALGROSSWEIGHT totalGrossWeight, 5 t.GROSSWEIGHTUNITCODE grossWeightUnitCode, 6 ... 7 8 From N_CA_FSU t 9 Where ... 10 </select>
如果使用mybatis-spring来整合mybatis，sqlSessionFactory参考下面修改（主要是加载分页插件）
1 <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> 2 <property name="dataSource" ref="dataSource" /> 3 <property name="configLocation" value="classpath:mybatis-config.xml"></property> 4 <property name="typeAliasesPackage" value="acc.entity"></property> 5 <property name="mapperLocations" value="classpath:mybatis/**/*.xml"></property> 6 <property name="plugins"> 7 <list> 8 <bean 9 class="com.github.miemiedev.mybatis.paginator.OffsetLimitInterceptor"> 10 <property name="dialectClass" 11 value="com.github.miemiedev.mybatis.paginator.dialect.OracleDialect"></property> 12 </bean> 13 </list> 14 </property> 15 </bean>
三、服务层基类
1 package acc.service.support; 2 3 import java.io.Serializable; 4 import java.util.List; 5 6 import org.apache.ibatis.session.SqlSession; 7 import org.apache.ibatis.session.SqlSessionFactory; 8 import org.mybatis.spring.SqlSessionFactoryBean; 9 import org.mybatis.spring.SqlSessionUtils; 10 import org.slf4j.Logger; 11 import org.slf4j.LoggerFactory; 12 import org.springframework.beans.factory.annotation.Autowired; 13 14 import com.github.miemiedev.mybatis.paginator.domain.PageBounds; 15 16 public class BaseServiceImpl implements Serializable { 17 18 private static final long serialVersionUID = 1293567786956029903L; 19 20 protected Logger logger = LoggerFactory.getLogger(this.getClass()); 21 22 @Autowired 23 protected SqlSessionFactoryBean sqlSessionFactory; 24 25 /** 26 * 查询分页数据 27 * 28 * @param mapperClass 29 * @param sqlId 30 * @param sqlParameter 31 * @param pageIndex 32 * @param pageSize 33 * @return 34 * @throws Exception 35 */ 36 protected List<?> getPageList(Class<?> mapperClass, String sqlId, 37 Object sqlParameter, int pageIndex, int pageSize) throws Exception { 38 SqlSession session = null; 39 try { 40 SqlSessionFactory sessionFactory = sqlSessionFactory.getObject(); 41 session = SqlSessionUtils.getSqlSession(sessionFactory); 42 if (pageIndex <= 0) { 43 pageIndex = 1; 44 } 45 if (pageSize <= 0) { 46 pageSize = 10; 47 } 48 PageBounds pageBounds = new PageBounds(pageIndex, pageSize); 49 return session.selectList(mapperClass.getName() + "." + sqlId, 50 sqlParameter, pageBounds); 51 } finally { 52 session.close(); 53 } 54 55 } 56 57 }
四、具体的服务层子类调用
1 package acc.service.support; 2 3 ... 4 5 @Service 6 public class FsuServiceImpl extends BaseServiceImpl implements FsuService { 7 8 private static final long serialVersionUID = 6560424159072027262L; 9 10 @Autowired 11 FsuMapper fsuMapper; 12 13 ... 14 15 16 @SuppressWarnings("unchecked") 17 @Override 18 public PageList<N_CA_FSU> getAll(int pageIndex, int pageSize) 19 throws Exception { 20 return (PageList<N_CA_FSU>) getPageList(FsuMapper.class, "getFsuList", 21 null, pageIndex, pageSize); 22 } 23 24 ... 25 26 27 }
服务层就处理完了，接下来看Action层
五、Action基类
1 package acc.action; 2 3 import org.apache.struts2.ServletActionContext; 4 import org.apache.struts2.convention.annotation.ParentPackage; 5 import org.slf4j.Logger; 6 import org.slf4j.LoggerFactory; 7 8 import acc.lms.invoker.utils.StringUtils; 9 10 import com.opensymphony.xwork2.ActionSupport; 11 12 @ParentPackage("default") 13 public class BaseController extends ActionSupport { 14 15 protected Logger logger = LoggerFactory.getLogger(this.getClass()); 16 17 private static final long serialVersionUID = -8955001188163866079L; 18 19 private int pageSize = 15; 20 21 private int pageIndex = 1; 22 23 private int totalCounts = 0; 24 private int totalPages = 0; 25 26 public int getPageSize() { 27 return pageSize; 28 } 29 30 public void setPageSize(int pageSize) { 31 this.pageSize = pageSize; 32 } 33 34 public int getPageIndex() { 35 String t = ServletActionContext.getRequest().getParameter("pageIndex"); 36 if (!StringUtils.isEmpty(t)) { 37 pageIndex = Integer.parseInt(t); 38 } 39 return pageIndex; 40 } 41 42 public int getTotalCounts() { 43 return totalCounts; 44 } 45 46 public void setTotalCounts(int totalCounts) { 47 this.totalCounts = totalCounts; 48 } 49 50 public int getTotalPages() { 51 return totalPages; 52 } 53 54 public void setTotalPages(int totalPages) { 55 this.totalPages = totalPages; 56 } 57 58 }
注：约定分页时，url类似 /xxx.action?pageIndex=N
六、具体的Action子类调用
1 package acc.action; 2 3 ... 4 5 @Results({ @Result(name = "success", type = "redirectAction", params = { 6 "actionName", "fsu" }) }) 7 public class FsuController extends BaseController implements 8 ModelDriven<Object> { 9 10 ... 11 @Autowired 12 FsuService fsuService; 13 14 ... 15 16 17 18 // GET /fsu 19 public HttpHeaders index() throws Exception { 20 list = fsuService.getAll(getPageIndex(), getPageSize()); 21 22 setPageSize(list.getPaginator().getLimit()); 23 setTotalCounts(list.getPaginator().getTotalCount()); 24 setTotalPages(list.getPaginator().getTotalPages()); 25 26 return new DefaultHttpHeaders("index").disableCaching(); 27 } 28 29 ... 30 31 }
七、前端页面
1 <link href="${pageContext.request.contextPath}/resources/css/simplePagination/simplePagination.css" rel="stylesheet" type="text/css"/> 2 <script type="text/javascript" src="${pageContext.request.contextPath}/resources/js/common/jquery-1.7.1.min.js"></script> 3 <script type="text/javascript" src="${pageContext.request.contextPath}/resources/js/common/simplePagination/jquery.simplePagination.js"></script> 4 <script type="text/javascript"> 5 var pageIndex = ${pageIndex}; 6 var pageSize = ${pageSize}; 7 var totalPages = ${totalPages}; 8 var totalCounts = ${totalCounts}; 9 10 $(document).ready(function() { 11 12 $("#page-box").pagination({ 13 items: totalCounts, 14 itemsOnPage: pageSize, 15 currentPage:pageIndex, 16 cssStyle: 'light-theme', 17 prevText:'<', 18 nextText:'>', 19 onPageClick:function(page){ 20 gotoPage(page); 21 } 22 }); 23 showPageInfo(); 24 25 }); 26 27 function gotoPage(page) { 28 window.location = "${pageContext.request.contextPath}/fsu?pageIndex=" + page; 29 } 30 31 function showPageInfo(){ 32 $("#page-info").html(pageSize + "条/页，共" + totalCounts + "条，第" + pageIndex + "页，共" + totalPages + "页"); 33 } 34 </script> 35 36 37 <table class="tableE"> 38 <thead> 39 <tr> 40 <th>运单号</th> 41 <th>起始站</th> 42 ... 43 </tr> 44 </thead> 45 46 <tbody> 47 <s:iterator value="list"> 48 <tr> 49 <td>${waybillNumber}</td> 50 <td>${origin}</td> 51 ... 52 </tr> 53 </s:iterator> 54 </tbody> 55 </table> 56 57 58 <div id="page-box"></div>
解释：jquery的分页插件，网上一搜索一大堆，我用的是jquery.simplePagination，${pageIndex}、${pageSize}...包括list，这些属性都是后台Action中的model属性
后记：
github上还有另一款mybatis的分页插件：Mybatis-PageHelper 也十分好用，使用说明参考：http://git.oschina.net/free/Mybatis_PageHelper/blob/master/wikis/HowToUse.markdown
使用示例：
1 @Test 2 public void testPagination() { 3 HUserMapper userMapper = context.getBean(HUserMapper.class); 4 Map<String, Object> param = new HashMap<>(); 5 param.put("city", "上海"); 6 //startPage后紧接的第1个mybatis查询方法被会分页 7 PageHelper.startPage(3, 10);//第3页开始,每页10条 8 PageInfo<HUser> pageInfo = new PageInfo<>(userMapper.queryByMap(param)); 9 for (HUser u : pageInfo.getList()) { 10 log.info("userId:{}", u.getUserId()); 11 } 12 log.info("pageIndex:{},pageSize:{},pageCount:{},recordCount:{}", 13 pageInfo.getPageNum(), pageInfo.getPageSize(), 14 pageInfo.getPages(), pageInfo.getTotal()); 15 16 }
mybatis-config.xml中的配置：
1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 3 "http://mybatis.org/dtd/mybatis-3-config.dtd"> 4 <configuration> 5 <settings> 6 <setting name="logImpl" value="LOG4J2"/> 7 </settings> 8 9 <plugins> 10 <plugin interceptor="com.github.pagehelper.PageHelper"> 11 <!--下面的参数详解见http://git.oschina.net/free/Mybatis_PageHelper/blob/master/wikis/HowToUse.markdown--> 12 <property name="dialect" value="mysql"/> 13 <property name="reasonable" value="true"/> 14 <property name="offsetAsPageNum" value="true"/> 15 <property name="rowBoundsWithCount" value="true"/> 16 <property name="pageSizeZero" value="true"/> 17 </plugin> 18 19 <plugin interceptor="tk.mybatis.mapper.mapperhelper.MapperInterceptor"> 20 <property name="mappers" value="tk.mybatis.mapper.common.Mapper"/> 21 <property name="IDENTITY" value="MYSQL"/> 22 <property name="notEmpty" value="true"/> 23 </plugin> 24 </plugins> 25 26 </configuration>
