MyBatis之PageHelper分页插件

深碍√TFBOYSˉ_ 2022-04-11 11:44 267阅读 0赞

我们在上一篇整合SpringBoot的基础之上，再进行PageHelper分页插件的实验。

首先我们先在数据库内准备一些用于测试的数据

![20181206093518345.png][]

pom文件引入pagehelper-spring-boot-starter

<dependency>
    	<groupId>com.github.pagehelper</groupId>
    	<artifactId>pagehelper-spring-boot-starter</artifactId>
    	<version>1.2.10</version>
    </dependency>

配置文件application.properties对PageHelper插件进行配置

#配置PageHelper
    # 数据库方言
    pagehelper.helper-dialect=mysql
    # 判断合理性比如负数和超过最大总数
    pagehelper.reasonable=true
    # 支持方法参数
    pagehelper.support-methods-arguments=true

配置完毕，我们可以使用PageHelper了，在UserService中定义一个需要进行分页的方法

public Map<String, Object> listUser(User user, int pageNum, int pageSize) {
    	Map<String, Object> result = new HashMap<String, Object>();
            //只对该语句后的第一个查询语句得到的数据进行分页
    	Page<?> page=PageHelper.startPage(pageNum, pageSize);
    	List<User> list = userMapper.listUser(user);
    	result.put("data", list);
    	result.put("pages", page.getTotal());
    	return result;
    }

如上所示，我们在需要进行分页的持久层方法userMapper.listUser(user)上面，添加一行代码分页的代码

Page<?> page=PageHelper.startPage(pageNum, pageSize);

UserMapper.xml不需要进行特殊的处理，只需编写正常的查询SQL

<select id="listUser" resultType="User">
    	select
    	<include refid="Base_Column_List" />
    	from user
    	<where>
    		<if test="id != null and id!=''">
    			and id = #{id}
    		</if>
    		<if test="name != null and name!=''">
    			and name like concat('%',#{name},'%')
    		</if>
    		<if test="age != null and age!=''">
    			and age=#{age}
    		</if>
    	</where>
    	order by id
    </select>

准备完毕，我们可以进行单元测试了

@Test
    public void listUser() {
    	User user =new User();	
    	user.setName("test");
    	Map<String, Object> result=userService.listUser(user,1,5);
    	List<User> list =(List<User>) result.get("data");
    	for(User u:list){
    		log.info("u:"+u);
    	}
    	log.info("pages:"+result.get("pages"));	
    }

查看结果

2018-12-06 09:20:19.768 DEBUG 2484 --- [           main] com.yj.dao.UserMapper.listUser_COUNT     : ==>  Preparing: SELECT count(0) FROM user WHERE name LIKE concat('%', ?, '%') 
    2018-12-06 09:20:19.913 DEBUG 2484 --- [           main] com.yj.dao.UserMapper.listUser_COUNT     : ==> Parameters: test(String)
    2018-12-06 09:20:19.930 DEBUG 2484 --- [           main] com.yj.dao.UserMapper.listUser_COUNT     : <==      Total: 1
    2018-12-06 09:20:19.933 DEBUG 2484 --- [           main] com.yj.dao.UserMapper.listUser           : ==>  Preparing: select id, name,age from user WHERE name like concat('%',?,'%') order by id LIMIT ? 
    2018-12-06 09:20:19.937 DEBUG 2484 --- [           main] com.yj.dao.UserMapper.listUser           : ==> Parameters: test(String), 5(Integer)
    2018-12-06 09:20:19.940 DEBUG 2484 --- [           main] com.yj.dao.UserMapper.listUser           : <==      Total: 5
    2018-12-06 09:20:19.942  INFO 2484 --- [           main] com.yj.service.UserServiceTest           : u:User [id=1, name=test1, age=18]
    2018-12-06 09:20:19.943  INFO 2484 --- [           main] com.yj.service.UserServiceTest           : u:User [id=2, name=test2, age=18]
    2018-12-06 09:20:19.944  INFO 2484 --- [           main] com.yj.service.UserServiceTest           : u:User [id=3, name=test3, age=18]
    2018-12-06 09:20:19.944  INFO 2484 --- [           main] com.yj.service.UserServiceTest           : u:User [id=4, name=test4, age=18]
    2018-12-06 09:20:19.944  INFO 2484 --- [           main] com.yj.service.UserServiceTest           : u:User [id=5, name=test5, age=18]
    2018-12-06 09:20:19.944  INFO 2484 --- [           main] com.yj.service.UserServiceTest           : pages:14

这样，我们就利用PageHelper分页插件，实现了数据的查询和数据总数的查询

[20181206093518345.png]: /images/20220411/2cdb4776726c4cd99c605d225c9054dc.png