7.分页

7.1 作用

• 减少数据处理量，一次数据量太大了对数据库和网络压力太大

SELECT * FROM school.`user` LIMIT startIndex,pageSize
SELECT * FROM school.`user` LIMIT 2  -- 0,2
SELECT * FROM school.`user` LIMIT 2,2 -- startIndex从0开始

• 使用mybatis实现分页步骤，核心sql

• 接口

public List<User> selectUserMap(Map<String,Integer> map);

• mapper.xml

<select id="selectUserMap"  parameterType="map" resultMap="userMap">select * from user limit #{startIndex},#{pageSize}
</select>

• 测试

@Test
public void testMybatis02(){SqlSession sqlSession = MybatisUtil.getSqlSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class);HashMap<String, Integer> map = new HashMap<>();map.put("startIndex",1);map.put("pageSize",2);List<User> users = mapper.selectUserMap(map);System.out.println(users.size());users.forEach(val->{logger.debug(val.toString());});sqlSession.close();
}

7.2 pagehelper分页插件使用

• PageHelper首先将前端传递的参数保存到page这个对象中，使用PageHelper.startPage在当前线程上下文中设置一个ThreadLocal变量，将page的副本存放入ThreadLoacl中，在 ThreadLocal中设置了分页参数，接着查询执行的时候，利用了mybatis提供的拦截器，取得ThreadLocal的值，获取当前线程中的分页参数，重新拼装分页SQL，完成分页，查询结束后在 finally 语句中清除ThreadLocal中的查询参数，这样保证分页的时候，sql和参数互不影响，

protected static final ThreadLocal<Page> LOCAL_PAGE = new ThreadLocal<Page>();
/**
* 设置 Page 参数
*
* @param page
*/
protected static void setLocalPage(Page page) {LOCAL_PAGE.set(page);
}/*** 开始分页** @param pageNum      页码* @param pageSize     每页显示数量* @param count        是否进行count查询* @param reasonable   分页合理化,null时用默认配置* @param pageSizeZero true且pageSize=0时返回全部结果，false时分页,null时用默认配置*/public static <E> Page<E> startPage(int pageNum, int pageSize, boolean count, Boolean reasonable, Boolean pageSizeZero) {Page<E> page = new Page<E>(pageNum, pageSize, count);page.setReasonable(reasonable);page.setPageSizeZero(pageSizeZero);//当已经执行过orderBy的时候Page<E> oldPage = getLocalPage();if (oldPage != null && oldPage.isOrderByOnly()) {page.setOrderBy(oldPage.getOrderBy());}setLocalPage(page);return page;}

• 倒入jar依赖

<dependency><groupId>com.github.pagehelper</groupId><artifactId>pagehelper</artifactId><version>5.3.0</version>
</dependency>

• 在mybatis-config.xml核心配置文件中配置plugins

• 注意mybatis-config.xml各元素顺序

The content of element type “configuration” must match "(properties?,settings?,typeAliases?,typeHandlers?,objectFactory?,objectWrapperFactory?,reflectorFactory?,plugins?,environments?,databaseIdProvider?,mappers?)

<!--分页插件-->
<plugins><plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
</plugins>

• 在接口interface UserMapper编写方法,注意不传分页参数（物理分页）

List<User> selectUserPagehelper();

• 在UserMapper.xml编写sql

<select id="selectUserPagehelper" resultType="com.zk.pojo.User">select * from user
</select>

• 调用测试

@Test
public void testMybatis04(){SqlSession sqlSession = MybatisUtil.getSqlSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class);/*** 开始分页** @param pageNum  页码* @param pageSize 每页显示数量*/PageHelper.startPage(2,2);List<User> users = mapper.selectUserPagehelper();PageInfo<User> userPageInfo = new PageInfo<>(users);//return userPageInfo; System.out.println(userPageInfo.getPageSize());System.out.println(users.size());users.forEach(val->{logger.debug(val.toString());});sqlSession.close();
}

• 结果
  

7.3 RowBounds分页插件使用

• 不是用sql分页实现
• 实现接口

List<User> selectUserRowBounds();

• 在UserMapper.xml编写sql

<select id="selectUserPagehelper" resultType="com.zk.pojo.User">select * from user
</select>

• 调用

@Test
public void testMybatis05(){SqlSession sqlSession = MybatisUtil.getSqlSession();//RowBounds创建RowBounds rowBounds = new RowBounds(1,2);//java代码层面分页List<User> user = sqlSession.selectList("com.zk.dao.UserMapper.selectUserRowBounds",null,rowBounds);System.out.println(user.size());user.forEach(val->{logger.debug(val.toString());});sqlSession.close();
}

• 结果
  

本专栏下一篇：MyBatis注解开发