04.分页查询
分页查询
分页插件
插件配置
<!-- spring xml 方式 -->
<property name="plugins">
<array>
<bean class="com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor">
<property name="sqlParser" ref="自定义解析类、可以没有"/>
<property name="dialectClazz" value="自定义方言类、可以没有"/>
<!-- COUNT SQL 解析.可以没有 -->
<property name="countSqlParser" ref="countSqlParser"/>
</bean>
</array>
</property>
<bean id="countSqlParser" class="com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize">
<!-- 设置为 true 可以优化部分 left join 的sql -->
<property name="optimizeJoin" value="true"/>
</bean>
//Spring boot方式
@Configuration
@MapperScan("com.baomidou.cloud.service.*.mapper*")
public class MybatisPlusConfig {
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
// 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求 默认false
// paginationInterceptor.setOverflow(false);
// 设置最大单页限制数量，默认 500 条，-1 不受限制
// paginationInterceptor.setLimit(500);
// 开启 count 的 join 优化,只针对部分 left join
paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
return paginationInterceptor;
}
}
XML 自定义分页
- UserMapper.java 方法内容
public interface UserMapper {//可以继承或者不继承BaseMapper
/**
* <p>
* 查询 : 根据state状态查询用户列表，分页显示
* </p>
*
* @param page 分页对象,xml中可以从里面进行取值,传递参数 Page 即自动分页,必须放在第一位(你可以继承Page实现自己的分页对象)
* @param state 状态
* @return 分页对象
*/
IPage<User> selectPageVo(Page<?> page, Integer state);
}
- UserMapper.xml 等同于编写一个普通 list 查询，mybatis-plus 自动替你分页
<select id="selectPageVo" resultType="com.baomidou.cloud.entity.UserVo">
SELECT id,name FROM user WHERE state=#{state}
</select>
- UserServiceImpl.java 调用分页方法
public IPage<User> selectUserPage(Page<User> page, Integer state) {
// 不进行 count sql 优化，解决 MP 无法自动优化 SQL 问题，这时候你需要自己查询 count 部分
// page.setOptimizeCountSql(false);
// 当 total 为小于 0 或者设置 setSearchCount(false) 分页插件不会进行 count 查询
// 要点!! 分页返回的对象与传入的对象是同一个
return userMapper.selectPageVo(page, state);
}
案例
首先，我们定义我们的分页参数：
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ParamSome {
private Integer yihao;
private String erhao;
}
@Data
@Accessors(chain = true)
@EqualsAndHashCode(callSuper = true)
public class MyPage<T> extends Page<T> {
private static final long serialVersionUID = 5194933845448697148L;
private Integer selectInt;
private String selectStr;
private String name;
public MyPage(long current, long size) {
super(current, size);
}
}
然后我们定义如下的 UserMapper：
@Mapper
public interface UserMapper extends BaseMapper<User> {
/**
* 3.x 的 page 可以进行取值,多个入参记得加上注解
* 自定义 page 类必须放在入参第一位
* 返回值可以用 IPage<T> 接收 也可以使用入参的 MyPage<T> 接收
* <li> 3.1.0 之前的版本使用注解会报错,写在 xml 里就没事 </li>
* <li> 3.1.0 开始支持注解,但是返回值只支持 IPage ,不支持 IPage 的子类</li>
*
* @param myPage 自定义 page
* @return 分页数据
*/
// @Select("select * from user where (age = #{pg.selectInt} and name = #{pg.selectStr}) or (age = #{ps.yihao} and name = #{ps.erhao})")
MyPage<User> mySelectPage(@Param("pg") MyPage<User> myPage, @Param("ps") ParamSome paramSome);
@ResultMap("userChildrenMap")
@Select("<script>select u.id,u.name,u.email,u.age,c.id as \"c_id\",c.name as \"c_name\",c.user_id as \"c_user_id\" " +
"from user u " +
"left join children c on c.user_id = u.id " +
"<where>" +
"<if test=\"selectInt != null\"> " +
"and u.age = #{selectInt} " +
"</if> " +
"<if test=\"selectStr != null and selectStr != ''\"> " +
"and c.name = #{selectStr} " +
"</if> " +
"</where>" +
"</script>")
MyPage<UserChildren> userChildrenPage(MyPage<UserChildren> myPage);
MyPage<User> mySelectPageMap(MyPage<User> pg, Map<String, Object> map);
List<User> mySelectMap(Map<String, Object> param);
List<User> myPageSelect(MyPage<User> myPage);
List<User> iPageSelect(IPage<User> myPage);
List<User> rowBoundList(RowBounds rowBounds, Map<String, Object> map);
}
然后完善 UserMapper.xml：
<?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.baomidou.mybatisplus.samples.pagination.mapper.UserMapper">
<resultMap id="userChildrenMap" type="com.baomidou.mybatisplus.samples.pagination.model.UserChildren">
<id column="id" property="id"/>
<result column="age" property="age"/>
<result column="email" property="email"/>
<result column="name" property="name"/>
<collection property="c" ofType="com.baomidou.mybatisplus.samples.pagination.entity.Children" columnPrefix="c_">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="user_id" property="userId"/>
</collection>
</resultMap>
<select id="mySelectPage" resultType="com.baomidou.mybatisplus.samples.pagination.entity.User">
select *
from user
where (age = #{pg.selectInt} and name = #{pg.selectStr})
or (age = #{ps.yihao} and name = #{ps.erhao})
</select>
<select id="mySelectPageMap" resultType="com.baomidou.mybatisplus.samples.pagination.entity.User">
select * from user
<where>
<if test="map.name!=null and map.name!=''">
name like #{map.name}
</if>
</where>
</select>
<select id="mySelectMap" resultType="com.baomidou.mybatisplus.samples.pagination.entity.User">
select * from user
<where>
<if test="name!=null and name!=''">
name like #{name}
</if>
</where>
</select>
<select id="myPageSelect" resultType="com.baomidou.mybatisplus.samples.pagination.entity.User">
select * from user
<where>
<if test="name!=null and name!=''">
name like '%'||#{name}||'%'
</if>
</where>
</select>
<select id="iPageSelect" resultType="com.baomidou.mybatisplus.samples.pagination.entity.User">
select * from user
<where>
<if test="name!=null and name!=''">
name like #{name}
</if>
</where>
</select>
<select id="rowBoundList" resultType="com.baomidou.mybatisplus.samples.pagination.entity.User">
select * from user
<where>
<if test="name!=null and name!=''">
name like #{name}
</if>
</where>
</select>
</mapper>
最后，完整的测试文件如下：
@Slf4j
@SpringBootTest
class PaginationTest {
@Resource
private UserMapper mapper;
@Test
void lambdaPagination() {
Page<User> page = new Page<>(1, 3);
Page<User> result = mapper.selectPage(page, Wrappers.<User>lambdaQuery().ge(User::getAge, 1).orderByAsc(User::getAge));
assertThat(result.getTotal()).isGreaterThan(3);
assertThat(result.getRecords().size()).isEqualTo(3);
}
@Test
void tests1() {
log.error("----------------------------------baseMapper 自带分页-------------------------------------------------------");
Page<User> page = new Page<>(1, 5);
page.addOrder(OrderItem.asc("age"));
Page<User> userIPage = mapper.selectPage(page, Wrappers.<User>lambdaQuery().eq(User::getAge, 20).like(User::getName, "Jack"));
assertThat(page).isSameAs(userIPage);
log.error("总条数 -------------> {}", userIPage.getTotal());
log.error("当前页数 -------------> {}", userIPage.getCurrent());
log.error("当前每页显示数 -------------> {}", userIPage.getSize());
List<User> records = userIPage.getRecords();
assertThat(records).isNotEmpty();
log.error("----------------------------------json 正反序列化-------------------------------------------------------");
String json = JSON.toJSONString(page);
log.info("json ----------> {}", json);
Page<User> page1 = JSON.parseObject(json, new TypeReference<Page<User>>() {
});
List<User> records1 = page1.getRecords();
assertThat(records1).isNotEmpty();
assertThat(records1.get(0).getClass()).isEqualTo(User.class);
log.error("----------------------------------自定义 XML 分页-------------------------------------------------------");
MyPage<User> myPage = new MyPage<User>(1, 5).setSelectInt(20).setSelectStr("Jack");
ParamSome paramSome = new ParamSome(20, "Jack");
MyPage<User> userMyPage = mapper.mySelectPage(myPage, paramSome);
assertThat(myPage).isSameAs(userMyPage);
log.error("总条数 -------------> {}", userMyPage.getTotal());
log.error("当前页数 -------------> {}", userMyPage.getCurrent());
log.error("当前每页显示数 -------------> {}", userMyPage.getSize());
}
@Test
void tests2() {
/* 下面的 left join 不会对 count 进行优化,因为 where 条件里有 join 的表的条件 */
MyPage<UserChildren> myPage = new MyPage<>(1, 5);
myPage.setSelectInt(18).setSelectStr("Jack");
MyPage<UserChildren> userChildrenMyPage = mapper.userChildrenPage(myPage);
List<UserChildren> records = userChildrenMyPage.getRecords();
records.forEach(System.out::println);
/* 下面的 left join 会对 count 进行优化,因为 where 条件里没有 join 的表的条件 */
myPage = new MyPage<UserChildren>(1, 5).setSelectInt(18);
userChildrenMyPage = mapper.userChildrenPage(myPage);
records = userChildrenMyPage.getRecords();
records.forEach(System.out::println);
}
private <T> void print(List<T> list) {
if (!CollectionUtils.isEmpty(list)) {
list.forEach(System.out::println);
}
}
@Test
void testMyPageMap() {
MyPage<User> myPage = new MyPage<User>(1, 5).setSelectInt(20).setSelectStr("Jack");
mapper.mySelectPageMap(myPage, Maps.newHashMap("name", "%a"));
myPage.getRecords().forEach(System.out::println);
}
@Test
void testMap() {
mapper.mySelectMap(Maps.newHashMap("name", "%a")).forEach(System.out::println);
}
@Test
void myPage() {
MyPage<User> page = new MyPage<>(1, 5);
page.setName("a");
mapper.myPageSelect(page).forEach(System.out::println);
}
@Test
void iPageTest() {
IPage<User> page = new Page<User>(1, 5) {
private String name = "%";
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
};
List<User> list = mapper.iPageSelect(page);
System.out.println("list.size=" + list.size());
System.out.println("page.total=" + page.getTotal());
}
/**
* 只查询当前页的记录，不查询总记录数
*/
@Test
void currentPageListTest() {
//使用三参数的构造器创建Page对象
//第三个参数isSearchCount：传true则查询总记录数;传false则不查询总记录数（既不进行count查询）
Page<User> page = new Page<>(1,3,false);
Page<User> result = mapper.selectPage(page, Wrappers.<User>lambdaQuery().ge(User::getAge, 20));
assertThat(result.getRecords().size()).isEqualTo(3);
//因为没有进行count查询，total值为0
assertThat(result.getTotal()).isEqualTo(0);
}
@Test
void rowBoundsTest() {
RowBounds rowBounds = new RowBounds(0, 5);
List<User> list = mapper.rowBoundList(rowBounds, Maps.newHashMap("name", "%"));
System.out.println("list.size=" + list.size());
}
@Test
void selectAndGroupBy() {
LambdaQueryWrapper<User> lq = new LambdaQueryWrapper<>();
lq.select(User::getAge).groupBy(User::getAge);
for (User user : mapper.selectList(lq)) {
System.out.println(user.getAge());
}
}
@Autowired
IUserService userService;
@Test
void lambdaPageTest() {
LambdaQueryChainWrapper<User> wrapper2 = userService.lambdaQuery();
wrapper2.like(User::getName, "a");
userService.page(new Page<>(1, 10), wrapper2.getWrapper()).getRecords().forEach(System.out::print);
}
@Test
void test() {
userService.lambdaQuery().like(User::getName, "a").list().forEach(System.out::println);
Page page = userService.lambdaQuery().like(User::getName, "a").page(new Page<>(1, 10));
page.getRecords().forEach(System.out::println);
}
}
PageHelper
PageHelper 是老版本的分页插件，首先进行插件配置：
/**
* pagehelper的分页插件
*/
@Bean
public PageInterceptor pageInterceptor() {
return new PageInterceptor();
}
@SpringBootTest
class DefaultTest {
@Autowired
private UserMapper mapper;
@Test
void test() {
Page<User> mpPage = mapper.selectPage(new Page<>(1, 2), Wrappers.<User>query().eq("id", 1));
assertThat(mpPage.getTotal()).isEqualTo(1L);
List<User> records = mpPage.getRecords();
assertThat(records).isNotEmpty();
assertThat(records.size()).isEqualTo(1);
// pagehelper
PageInfo<User> info = PageHelper.startPage(1, 2).doSelectPageInfo(() -> mapper.selectById(1));
assertThat(info.getTotal()).isEqualTo(1L);
List<User> list = info.getList();
assertThat(list).isNotEmpty();
assertThat(list.size()).isEqualTo(1);
}
@Test
void testIn() {
List<Long> ids = Arrays.asList(1L, 2L);
Page<User> mpPage = mapper.selectPage(new Page<>(1, 5), Wrappers.<User>query().in("id", ids));
assertThat(mpPage.getTotal()).isEqualTo(2L);
List<User> records = mpPage.getRecords();
assertThat(records).isNotEmpty();
assertThat(records.size()).isEqualTo(2);
// pagehelper
PageInfo<User> info = PageHelper.startPage(1, 5)
.doSelectPageInfo(() -> mapper.selectList(Wrappers.<User>query().in("id", ids)));
assertThat(info.getTotal()).isEqualTo(2L);
List<User> list = info.getList();
assertThat(list).isNotEmpty();
assertThat(list.size()).isEqualTo(2);
}
}