springboot集成mybatis-plus插件，完成分页和条件查询

1.创建springboot项目，引入pom依赖

<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.1.2</version>
		</dependency>
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-generator</artifactId>
			<version>3.1.2</version>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.0.29</version>
		</dependency>

		<!-- 提供mysql驱动 -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.38</version>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>1.16.16</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-freemarker</artifactId>
		</dependency>
		<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>fastjson</artifactId>
			<version>1.2.54</version>
		</dependency>
		<dependency>
			<groupId>com.github.ikidou</groupId>
			<artifactId>TypeBuilder</artifactId>
			<version>1.0</version>
			<scope>test</scope>
		</dependency>

2.application.yml配置文件

server:
  port: 8000
spring:
  profiles:
    active: dev

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: classpath:/mapper/*Mapper.xml

logging:
  level:
    com.baomidou.mybatisplus.samples.pagination: debug

spring:
  datasource:
    # 使用druid数据源
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/ds_0?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: root
    filters: stat
    maxActive: 20
    initialSize: 1
    maxWait: 60000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: select 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20

3.设置分页配置

@Configuration
@MapperScan("com.nh.mapper")
public class MybatisPlusConfig {

    @Bean
    public PaginationInterceptor paginationInterceptor(){
        // 开启 count 的 join 优化,只针对 left join !!!
        return new PaginationInterceptor().setCountSqlParser(new JsqlParserCountOptimize(true));
    }
}

4.mapper接口文件

public interface UserMapper extends BaseMapper<User> {
    
    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);
}

5.xml文件

<select id="mySelectPage" resultType="com.nh.vo.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.nh.vo.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.nh.vo.User">
        select * from user
        <where>
            <if test="name!=null and name!=''">
                name like #{name}
            </if>
        </where>
    </select>

    <select id="myPageSelect" resultType="com.nh.vo.User">
        select * from user
        <where>
            <if test="name!=null and name!=''">
                name like '%'||#{name}||'%'
            </if>
        </where>
    </select>

    <select id="iPageSelect" resultType="com.nh.vo.User">
        select * from user
        <where>
            <if test="name!=null and name!=''">
                name like #{name}
            </if>
        </where>
    </select>

6.单元测试

@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, TypeBuilder.newInstance(Page.class).addTypeParam(User.class).build());
		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);
	}



	@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());
	}