SpringBoot整合MybatisPlus3.X之分页插件(四)

注：详细请看2.X博客中，3.X直接上代码。

建议装一个MybatisX插件，可以在Mapper和Xml来回切换

• pom.xml

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.2.0</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>runtime</scope>
    </dependency>
​
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.49</version>
        <scope>test</scope>
    </dependency>
    <!-- for testing -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

• 配置类

  @Configuration
  @MapperScan("com.mp.pagination.mapper")
  public class MybatisPlusConfig {
  ​
      /**
       * 分页插件
       */
      @Bean
      public PaginationInterceptor paginationInterceptor() {
          // 开启 count 的 join 优化,只针对 left join !!!
          return new PaginationInterceptor().setCountSqlParser(new JsqlParserCountOptimize(true));
      }
  }

• application.yml

  # DataSource Config
  spring:
    datasource:
      driver-class-name: org.h2.Driver
      url: jdbc:h2:tcp://192.168.180.115:19200/~/mem/test
      username: root
      password: test
  ​
  # Logger Config
  logging:
    level:
      com.mp.pagination: debug
  ​
  mybatis-plus:
    mapper-locations: classpath:/mapper/*Mapper.xml

• 实体类

  @Data
  public class Children {
      private Long id;
      private String name;
      private Long userId;
  }
  ​
  @Data
  public class User {
      private Long id;
      private String name;
      private Integer age;
      private String email;
  }

• Dao层

  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(@Param("pg") MyPage<User> myPage, @Param("map") Map param);
  ​
      List<User> mySelectMap(Map param);
  ​
      List<User> myPageSelect(MyPage<User> myPage);
  ​
      List<User> iPageSelect(IPage<User> myPage);
  ​
      List<User> rowBoundList(RowBounds rowBounds, Map map);
  }
  ​

• model层

  @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);
      }
  }
  ​
  @Data
  @NoArgsConstructor
  @AllArgsConstructor
  public class ParamSome {
  ​
      private Integer yihao;
      private String erhao;
  }
  ​
  @Data
  @ToString(callSuper = true)
  @EqualsAndHashCode(callSuper = true)
  public class UserChildren extends User {
  ​
      private List<Children> c;
  }

• 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.mp.pagination.mapper.UserMapper">
  ​
      <resultMap id="userChildrenMap" type="com.mp.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.mp.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.mp.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.mp.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.mp.pagination.entity.User">
          select * from user
          <where>
              <if test="name!=null and name!=''">
                  name like #{name}
              </if>
          </where>
      </select>
  ​
      <select id="myPageSelect" resultType="com.mp.pagination.entity.User">
          select * from user
          <where>
              <if test="name!=null and name!=''">
                  name like '%'||#{name}||'%'
              </if>
          </where>
      </select>
  ​
      <select id="iPageSelect" resultType="com.mp.pagination.entity.User">
          select * from user
          <where>
              <if test="name!=null and name!=''">
                  name like #{name}
              </if>
          </where>
      </select>
  ​
      <select id="rowBoundList" resultType="com.mp.pagination.entity.User">
          select * from user
          <where>
              <if test="name!=null and name!=''">
                  name like #{name}
              </if>
          </where>
      </select>
  </mapper>

• 测试类

  注：这里的MyPage对Page做了一层嵌套，其实一般不用，都是通过实体类的参数带到SQL中

  @RunWith(SpringRunner.class)
  @SpringBootTest
  class PaginationApplicationTests {
  ​
      @Resource
      private UserMapper mapper;
  ​
      @Test
      public void lambdaPagination() {
          Page<User> page = new Page<>(1, 3);
          QueryWrapper<User> wrapper = new QueryWrapper<>();
          wrapper.lambda().ge(User::getAge, 1).orderByAsc(User::getAge);
          IPage<User> result = mapper.selectPage(page, wrapper);
          System.out.println(result.getTotal());
          Assert.assertTrue(result.getTotal() > 3);
          Assert.assertEquals(3, result.getRecords().size());
      }
  ​
      @Test
      public void tests1() {
          System.out.println("----- baseMapper 自带分页 ------");
          Page<User> page = new Page<>(1, 5);
          IPage<User> userIPage = mapper.selectPage(page, new QueryWrapper<User>()
                  .eq("age", 20).eq("name", "Jack"));
         // assertThat(page).isSameAs(userIPage);
          System.out.println("总条数 ------> " + userIPage.getTotal());
          System.out.println("当前页数 ------> " + userIPage.getCurrent());
          System.out.println("当前每页显示数 ------> " + userIPage.getSize());
          print(userIPage.getRecords());
          System.out.println("----- baseMapper 自带分页 ------");
  ​
          System.out.println("json 正反序列化 begin");
          String json = JSON.toJSONString(page);
          Page<User> page1 = JSON.parseObject(json, Page.class);
          print(page1.getRecords());
          System.out.println("json 正反序列化 end");
  ​
          System.out.println("----- 自定义 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);
          System.out.println("总条数 ------> " + userMyPage.getTotal());
          System.out.println("当前页数 ------> " + userMyPage.getCurrent());
          System.out.println("当前每页显示数 ------> " + userMyPage.getSize());
          print(userMyPage.getRecords());
          System.out.println("----- 自定义 XML 分页 ------");
      }
  ​
      @Test
      public 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
      public void testMyPageMap() {
          MyPage<User> myPage = new MyPage<User>(1, 5).setSelectInt(20).setSelectStr("Jack");
          Map map = new HashMap(1);
          map.put("name", "%a");
          mapper.mySelectPageMap(myPage, map);
          myPage.getRecords().forEach(System.out::println);
      }
  ​
      @Test
      public void testMap() {
          Map map = new HashMap(1);
          map.put("name", "%a");
          mapper.mySelectMap(map).forEach(System.out::println);
      }
  ​
      @Test
      public void myPage() {
          MyPage<User> page = new MyPage<>(1, 5);
          page.setName("a");
          mapper.myPageSelect(page).forEach(System.out::println);
      }
  ​
      @Test
      public 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
      public void rowBoundsTest() {
          RowBounds rowBounds = new RowBounds(0, 5);
          Map map = new HashMap(1);
          map.put("name", "%");
          List<User> list = mapper.rowBoundList(rowBounds, map);
          System.out.println("list.size=" + list.size());
      }
  ​
  }