mybatis的物理分页：mybatis-paginator

利用github开源项目mybatis-paginator实现mybatis的物理分页

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>

View Code

 

二、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>

View Code

如果使用​​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>

View Code

 

三、服务层基类

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 }

View Code

 

四、具体的服务层子类调用

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 }

View Code

服务层就处理完了，接下来看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 }

View Code

注：约定分页时，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 }

View Code

 

七、前端页面

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>

View Code

解释：jquery的分页插件，网上一搜索一大堆，我用的是jquery.simplePagination，${pageIndex}、${pageSize}...包括list，这些属性都是后台Action中的model属性

 

后记：

github上还有另一款mybatis的分页插件：​​Mybatis-PageHelper​​ 也十分好用   

使用示例：

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     }

View Code

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.o na.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>

View Code

 

作者：菩提树下的杨过