信步漫谈之MyBatisPlus——CRUD与分页

• 1 目的
• 2 程序结构
• 3 程序代码
• 4 测试程序
• 5 参考资料（感谢）

1 目的

使用MybatisPlus进行增删改查以及分页操作

2 程序结构

com.alfred.mybatisplus.crudpage
    ∟ pom.xml
    ∟ src
        ∟ main
            ∟ java
                ∟ com
                    ∟ alfred
                        ∟ mybatisplus
                            ∟ crudpage
                                ∟ config
                                    ∟ MybatisPlusConfig.java
                                ∟ CrudApplication.java
                                ∟ mapper
                                    ∟ UserMapper.java
                                ∟ po
                                    ∟ User.java
            ∟ resources
                ∟ application.yml
                ∟ db
                    ∟ data-h2.sql
                    ∟ schema-h2.sql
                ∟ mapper
                    ∟ UserMapper.xml
        ∟ test
            ∟ java
                ∟ com
                    ∟ alfred
                        ∟ mybatisplus
                            ∟ crudpage
                                ∟ mapper
                                    ∟ test
                                        ∟ CrudTest.java

3 程序代码

• MybatisPlusConfig.java

package com.alfred.mybatisplus.crudpage.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.autoconfigure.ConfigurationCustomizer;
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;

/**
 * @author alfred
 * @since 2021-01-27
 */
@Configuration
public class MybatisPlusConfig {

    /**
     * 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.H2));
        return interceptor;
    }

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

• UserMapper.java

package com.alfred.mybatisplus.crudpage.mapper;

import com.alfred.mybatisplus.crudpage.po.User;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;

/**
 * @Author: alfred
 * @Date: 2021/1/27
 * @description:
 */
@Mapper
public interface UserMapper extends BaseMapper {

}

• User.java

package com.alfred.mybatisplus.crudpage.po;

import lombok.Builder;
import lombok.Data;
import lombok.experimental.Accessors;

/**
 * 用户实体对应表 user
 * @Author alfred
 * @Date 2021-01-27
 */
@Builder
@Data
@Accessors(chain = true)
public class User {

    private Long id;
    private String name;
    private Integer age;
    private String email;
}

• CrudApplication.java

package com.alfred.mybatisplus.crudpage;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * @Author: alfred
 * @Date: 2021/1/27
 * @description:
 */
@SpringBootApplication
public class CrudApplication {

    public static void main(String[] args) {
        SpringApplication.run(CrudApplication.class);
    }

}

• data-h2.sql

DELETE FROM user;

INSERT INTO user (id, name, age, email)
VALUES (1, 'Jone', 18, 'test1@baomidou.com'),
       (2, 'Jack', 20, 'test2@baomidou.com'),
       (3, 'Jack', 20, 'test2@baomidou.com'),
       (4, 'Jack', 20, 'test2@baomidou.com'),
       (5, 'Jack', 20, 'test2@baomidou.com'),
       (6, 'Jack', 20, 'test2@baomidou.com'),
       (7, 'Jack', 20, 'test2@baomidou.com'),
       (8, 'Jack', 20, 'test2@baomidou.com'),
       (9, 'Jack', 20, 'test2@baomidou.com'),
       (10, 'Jack', 20, 'test2@baomidou.com'),
       (11, 'Jack', 20, 'test2@baomidou.com'),
       (12, 'Jack', 20, 'test2@baomidou.com'),
       (13, 'Jack', 20, 'test2@baomidou.com'),
       (14, 'Jack', 20, 'test2@baomidou.com'),
       (15, 'Tom', 28, 'test3@baomidou.com'),
       (16, 'Sandy', 21, 'test4@baomidou.com'),
       (17, 'Billie', 24, 'test5@baomidou.com');

• schema-h2.sql

DROP TABLE IF EXISTS user;

CREATE TABLE user
(
	id BIGINT(20) NOT NULL COMMENT '主键ID',
	name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
	age INT(11) NULL DEFAULT NULL COMMENT '年龄',
	email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (id)
);

• UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>

• application.yml

# DataSource Config
spring:
  datasource:
    driver-class-name: org.h2.Driver
    schema: classpath:db/schema-h2.sql
    data: classpath:db/data-h2.sql
    url: jdbc:h2:mem:mybatisplus
    username: root
    password: test


# Logger Config
logging:
  level:
    com.alfred.mybatisplus.crudpage: debug

# MyBatis-Plus 配置
mybatis-plus:
  mapper-locations: classpath:/mapper/*Mapper.xml

4 测试程序

• CrudTest.java

package com.alfred.mybatisplus.crudpage.mapper.test;

import static org.assertj.core.api.Assertions.assertThat;

import com.alfred.mybatisplus.crudpage.mapper.UserMapper;
import com.alfred.mybatisplus.crudpage.po.User;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;

/**
 * @Author: alfred
 * @Date: 2021/1/27
 * @Description:
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class CrudTest {

    @Resource
    UserMapper userMapper;

    /**
     * 新增
     */
    @Test
    public void add() {
        User user = User.builder().name("alfred").email("alfred@me.com").age(22).build();
        assertThat(userMapper.insert(user)).isGreaterThan(0);
    }

    /**
     * 删除
     */
    @Test
    public void del() {
        assertThat(userMapper.deleteById(3)).isGreaterThan(0);
        assertThat(userMapper.delete(Wrappers.query().lambda().eq(User::getName, "Jone"))).isGreaterThan(0);
    }

    /**
     * 修改
     */
    @Test
    public void upd() {
        assertThat(userMapper.update(User.builder().name("Sandy2").build(),
                Wrappers.lambdaUpdate().eq(User::getName, "Sandy"))).isGreaterThan(0);
    }

    /**
     * 查询数量
     */
    @Test
    public void selectCount() {
        Assert.assertEquals(userMapper.selectCount(null), Integer.valueOf(16));
    }

    /**
     * 条件查询
     */
    @Test
    public void selectByCondition() {
        userMapper.selectList(Wrappers.lambdaQuery().eq(User::getName, "alfred")).forEach(x -> {
            assertThat(x.getName()).isEqualTo("alfred");
        });
    }

    /**
     * 分页查询
     */
    @Test
    public void selectPage() {
        Page page = new Page<>(1, 3);
        Page result = userMapper.selectPage(page, Wrappers.lambdaQuery().ge(User::getAge, 1).orderByAsc(User::getAge));
        assertThat(result.getTotal()).isGreaterThan(3);
        assertThat(result.getRecords().size()).isEqualTo(3);
    }

}

5 参考资料（感谢）

mybatis-plus官网指南
官网示例项目集合