1、引入依赖包
<dependency> <groupId>com.github.miemiedev</groupId> <artifactId>mybatis-paginator</artifactId> <version>1.2.17</version> </dependency> |
2、Mybatis配置文件添加分页插件
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN" "http://ibatis.apache.org/dtd/ibatis-3-config.dtd"> <configuration> <settings> <setting name="cacheEnabled" value="false" /> <setting name="lazyLoadingEnabled" value="false" /> <setting name="aggressiveLazyLoading" value="false"/> </settings> <plugins> <plugin interceptor="com.github.miemiedev.mybatis.paginator.OffsetLimitInterceptor"> <property name="dialectClass" value="com.github.miemiedev.mybatis.paginator.dialect.OracleDialect"/> </plugin> </plugins> </configuration> |
3、applicationContext.xml文件中增加属性配置
<bean class="com.vip.vpal.cpc.dataSource.ReplicationDataSource" id="oDataSource"> <property name="targetDataSources"> <map key-type="java.lang.String"> <entry value-ref="oDataSourceMaster" key="master"></entry> <entry value-ref="oDataSourceSlave" key="slave"></entry> </map> </property> <property name="defaultTargetDataSource" ref="oDataSourceMaster"> </property> </bean> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="oDataSource"/> </bean> <bean id="myBatisSqlSessionFactoryO" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="oDataSource"/> <property name="configLocation" value="classpath:mybatis-config.xml"></property> <property name="mapperLocations" value="classpath*:mapper/*Mapper.xml"/> </bean> |
4、创建一个查询,mybatis映射文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.test.repository.TestRepository"> <select id="selectdUserListPage" resultType="java.lang.String"> SELECT p.id FROM test1 b join test2 p on b.usr_no = p.usr_no where p.transfer_sts = '2' and b.tm_smp >= #{tmSmpStart} and b.tm_smp <= #{tmSmpEnd} and b.CUR_AC_BAL > 0 </select> </mapper> |
5、创建一个接口
package com.test.repository; import com.github.miemiedev.mybatis.paginator.domain.PageBounds; import org.apache.ibatis.annotations.Param; import java.util.List; public interface TestRepository { List<String> selectdUserListPage(@Param("tmSmpStart") String tmSmpStart, @Param("tmSmpEnd") String tmSmpEnd,@Param("pageBounds") PageBounds pageBounds); } |
6、具体的调用和使用
package com.test; import com.github.miemiedev.mybatis.paginator.domain.PageBounds; import com.github.miemiedev.mybatis.paginator.domain.PageList; import com.test.repository; import org.joda.time.DateTime; import org.junit.Test; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.junit.BeforeClass; import org.junit.FixMethodOrder; import org.junit.Test; import org.junit.runner.RunWith; import org.junit.runners.MethodSorters; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.TestExecutionListeners; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; import org.springframework.test.context.support.DependencyInjectionTestExecutionListener; import org.springframework.test.context.transaction.TransactionalTestExecutionListener;import java.util.ArrayList; import java.util.List;@RunWith(SpringJUnit4ClassRunner.class)@ContextConfiguration(locations = {"classpath:META-INF/service-conf/service.xml"})@TestExecutionListeners(value = {DependencyInjectionTestExecutionListener.class, TransactionalTestExecutionListener.class})@FixMethodOrder(MethodSorters.NAME_ASCENDING) public class BalTransferServiceTest{ private static Logger log = LoggerFactory.getLogger(BalTransferServiceTest.class); @Autowired private TestRepository testRepository; @Test public void testSelectPage() { List<String> userIdList = new ArrayList<>(); String tmSmpStart = DateTime.now().minusDays(Integer.valueOf(30)).toString("yyyyMMddHHmmss"); String tmSmpEnd = DateTime.now().toString("yyyyMMddHHmmss"); int page = 1; int pageSize = 20; PageBounds pageBounds = new PageBounds(page, pageSize, Boolean.TRUE); List<String> tempList = testRepository.selectdVipUserListPage(tmSmpStart, tmSmpEnd, pageBounds); PageList pageList = (PageList) tempList; userIdList.addAll(tempList); log.info("tempList={},Page={},totalCount={},TotalPages={}", tempList.size(), pageList.getPaginator().getPage(), pageList.getPaginator().getTotalCount(), pageList.getPaginator().getTotalPages()); Integer totalPages = pageList.getPaginator().getTotalPages(); //如果还有大于1页的数据,需要继续翻页进行查询,这个时候可以控制不查询totalcount的查询 for (int i = 2; i <= totalPages; i++) { pageBounds = new PageBounds(i, pageSize, Boolean.FALSE); tempList = testRepository.selectdUserListPage(tmSmpStart, tmSmpEnd, pageBounds); userIdList.addAll(tempList); } log.info("userIdList={}", userIdList.size()); for (String temp : userIdList) { log.info("id={}", temp); } } } |
7、使用PageBounds这个对象来控制结果的输出,常用的使用方式一般都可以通过构造函数来配置。
//默认构造函数不提供分页,返回ArrayList
new PageBounds();
//取TOPN操作,返回ArrayList
new PageBounds(int limit);
//只排序不分页,返回ArrayList
new PageBounds(Order... order);
//默认分页,返回PageList
new PageBounds(int page, int limit);
//分页加排序,返回PageList
new PageBounds(int page, int limit, Order... order);
//使用containsTotalCount来决定查不查询totalCount,即返回ArrayList还是PageList
new PageBounds(int page, int limit, List<Order> orders, boolean containsTotalCount);
|