Mybatis Plus 系列3.3 —— Mapper CRUD之分页查询

发布日期: 2021-02-13

文章字数: 1.2k

阅读时长: 6 分

阅读次数:

Mybatis plus自带了分页插件，如果你已经习惯使用 pagehelper分页，那么可以继续使用pagehelper。这里主要目的是演示一下怎么使用mybatis plus的分页插件！演示前置条件参见Mybatis Plus 3.1 —— Mapper CRUD之查询

首先我们就使用BaseMapper#selectMapsPage分页方法来演示一下：

@Test
@DisplayName("分页1")
public void pageTest1() {
  LambdaQueryWrapper<User> eq = Wrappers.<User>lambdaQuery()
    .gt(User::getAge, 10)
    .eq(User::getIsDelete, false);

  // 分页，查询第一页数据
  Page<User> page = userMapper.selectPage(new Page<>(1, 2), eq);
}

结果：

==>  Preparing: SELECT id,name,age,email,sex,is_delete,create_time,update_time FROM user WHERE (age > ? AND is_delete = ?)
==> Parameters: 10(Integer), false(Boolean)
<==    Columns: id, name, age, email, sex, is_delete, create_time, update_time
<==        Row: 1, Jone, 18, test1@baomidou.com, MAIL, 0, 2021-02-12 22:43:00, 2021-02-12 22:43:00
<==        Row: 2, Jack, 20, test2@baomidou.com, MAIL, 0, 2021-02-12 22:43:00, 2021-02-12 22:43:00
<==        Row: 3, Tom, 28, test3@baomidou.com, MAIL, 0, 2021-02-12 22:43:00, 2021-02-12 22:43:00
<==        Row: 4, Sandy, 21, test4@baomidou.com, FEMALE, 0, 2021-02-12 22:43:00, 2021-02-12 22:43:00
<==        Row: 5, Billie, 24, test5@baomidou.com, FEMALE, 0, 2021-02-12 22:43:00, 2021-02-12 22:43:00
<==      Total: 5

可以看到分页并没有效果。这是由于分页功能并没有默认开启，我们需要做一下相应的配置！

一. 配置分页插件

package cn.justmr.mybatisplus.demo.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MybatisPlusConfig {

    /**
     * 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题
     */
    @Bean
    public MybatisPlusInterceptor paginationInterceptor() {
        MybatisPlusInterceptor plusInterceptor = new MybatisPlusInterceptor();
        // 分页插件
        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);
        // 设置最大分页数， -1: 不受限制
        paginationInnerInterceptor.setMaxLimit(-1L);
        // 溢出总页数后是否进行处理
        paginationInnerInterceptor.setOverflow(false);
        // 生成 countSql 优化掉 join 现在只支持 left join
        paginationInnerInterceptor.setOptimizeJoin(true);
        plusInterceptor.addInnerInterceptor(paginationInnerInterceptor);

        return plusInterceptor;
    }
  
    @Bean
    public ConfigurationCustomizer configurationCustomizer() {
        return configuration -> configuration.setUseDeprecatedExecutor(false);
    }
}

然后再次运行一下上面的测试方法,结果如下：

==>  Preparing: SELECT COUNT(*) FROM user WHERE (age > ? AND is_delete = ?)
==> Parameters: 10(Integer), false(Boolean)
<==    Columns: COUNT(*)
<==        Row: 5
<==      Total: 1
==>  Preparing: SELECT id,name,age,email,sex,is_delete,create_time,update_time FROM user WHERE (age > ? AND is_delete = ?) LIMIT ?
==> Parameters: 10(Integer), false(Boolean), 2(Long)
<==    Columns: id, name, age, email, sex, is_delete, create_time, update_time
<==        Row: 1, Jone, 18, test1@baomidou.com, MAIL, 0, 2021-02-12 22:43:00, 2021-02-12 22:43:00
<==        Row: 2, Jack, 20, test2@baomidou.com, MAIL, 0, 2021-02-12 22:43:00, 2021-02-12 22:43:00
<==      Total: 2

可以看到分页已经生效！

返回结果Page结构如下：

二. 分页&排序

@Test
@DisplayName("分页3")
public void pageTest3() {
  LambdaQueryWrapper<User> eq = Wrappers.<User>lambdaQuery()
    .gt(User::getAge, 10)
    .eq(User::getIsDelete, false);

  // 分页
  Page<User> objectPage = new Page<>(1, 2);
  // 排序
  objectPage.setOrders(OrderItem.descs("age"));

  Page<User> page = userMapper.selectPage(objectPage, eq);
  System.out.println(page);
}

结果：

==>  Preparing: SELECT COUNT(*) FROM user WHERE (age > ? AND is_delete = ?)
==> Parameters: 10(Integer), false(Boolean)
<==    Columns: COUNT(*)
<==        Row: 5
<==      Total: 1
==>  Preparing: SELECT id, name, age, email, sex, is_delete, create_time, update_time FROM user WHERE (age > ? AND is_delete = ?) ORDER BY age DESC LIMIT ?
==> Parameters: 10(Integer), false(Boolean), 2(Long)
<==    Columns: id, name, age, email, sex, is_delete, create_time, update_time
<==        Row: 3, Tom, 28, test3@baomidou.com, MAIL, 0, 2021-02-12 22:43:00, 2021-02-12 22:43:00
<==        Row: 5, Billie, 24, test5@baomidou.com, FEMALE, 0, 2021-02-12 22:43:00, 2021-02-12 22:43:00
<==      Total: 2

三. 自定义分页

有时候用api级别的方法不能满足分页的需求，比如多张表关联查询的时候，这个时候就需要自定义分页了。

这里简单点，就用单张表来演示一下！

public interface UserMapper extends BaseMapper<User> {
    // 自定义分页
    @Select({"<script>",
            "select id, name, age, email, sex, is_delete, create_time, update_time from user",
            "<where>",
                "<if test = 'age != null'>",
                    "age > #{age}",
                "</if>",
            "</where>",
            "</script>"})
    IPage<User> queryPageByAge(Page<?> page, Integer age);
}

// 测试方法
@Test
@DisplayName("分页4")
public void pageTest4() {
  Page<Object> objectPage = new Page<>(1, 2); // 分页
  objectPage.setOrders(OrderItem.descs("age")); // 排序
  IPage<User> userIPage = userMapper.queryPageByAge(objectPage, 18);
  System.out.println(objectPage.equals(userIPage)); // true; 分页返回的对象与传入的对象是同一个
}

结果：

==>  Preparing: SELECT COUNT(*) FROM user WHERE age > ?
==> Parameters: 18(Integer)
<==    Columns: COUNT(*)
<==        Row: 4
<==      Total: 1
==>  Preparing: SELECT id, name, age, email, sex, is_delete, create_time, update_time FROM user WHERE age > ? ORDER BY age DESC LIMIT ?
==> Parameters: 18(Integer), 2(Long)
<==    Columns: id, name, age, email, sex, is_delete, create_time, update_time
<==        Row: 3, Tom, 28, test3@baomidou.com, MAIL, 0, 2021-02-12 22:43:00, 2021-02-12 22:43:00
<==        Row: 5, Billie, 24, test5@baomidou.com, FEMALE, 0, 2021-02-12 22:43:00, 2021-02-12 22:43:00
<==      Total: 2