mybatis的分页插件：mybatis-paginator、mybatis-pageHelper

来源：互联网发布：ubuntu 移动宽带编辑：程序博客网时间：2024/06/06 00:34

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     @Autowired23     protected SqlSessionFactoryBean sqlSessionFactory;24 25     /**26      * 查询分页数据27      * 28      * @param mapperClass29      * @param sqlId30      * @param sqlParameter31      * @param pageIndex32      * @param pageSize33      * @return34      * @throws Exception35      */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     @Autowired11     FsuMapper fsuMapper;12 13     ...14     15 16     @SuppressWarnings("unchecked")17     @Override18     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     @Autowired12     FsuService fsuService;13 14     ...15 16     17 18     // GET /fsu19     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>

0 0