MybatisPlus：SQL语句打印、SQL分析、自定义主键值策略填充（IdType.INPUT）、动态表名、多租户、枚举、类型处理器、连表自定义SQL（使用wrapper）

1. 简单使用以及配置 - 带分页配置

pom.xml - 依赖

<dependencies>

        <!--web功能的springboot -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!--数据库驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!--JavaBean的属性get.set生成器 -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <!--SpringBoot自动配置mybatis-plus的默认配置 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.2</version>
        </dependency>


        <!--SpringBoot热加载 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
        </dependency>


        <!--SpringBoot单元测试 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

 
 

application.yml

spring:
  application:
    name: mybatisplusStudy
  datasource:
    data-password: root
    password: root
    username: 123456
    url: jdbc:mysql://localhost:3306/interview?serverTimezone=UTC&useSSL=false
    driver-class-name: com.mysql.cj.jdbc.Driver

server:
  servlet:
    context-path: /mybatisplus
  port: 80


mybatis-plus:

  # mapper文件的位置
  mapper-locations: classpath:/mappers/*Mapper.xml

  # 哪个包下的类名可以在mapper文件中不用写全限定名，写类名即可
  type-aliases-package: top.linruchang.mybatisplusstudy.dto
  configuration:
    # 默认true - 数据库字段名 与  JavaBean属性名的对应关系
    # 查询数据库时
    #    1. true 数据库字段名中的下划线去掉并toUpperCase = JavaBean属性名.toUpperCase
    #    2. false 数据库字段名.toUpperCase = JavaBean属性名.toUpperCase
    # update、set数据库时
    #    1. true 只要是驼峰的JavaBean字段，会将驼峰的大写字母 变成  _小写字母
    #    2. false 数据库字段 = JavaBean属性字段
    map-underscore-to-camel-case: false
   #打印SQL日志
   log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    global-config:
      db-config:
        # @FieldId 默认类型为自增长
        id-type: auto
         # @TableName如果要使用需要设置@TableName的属性是keepGlobalPrefix为true
        table-prefix: test_
        

 
 

MyConfig.java - 使MyBatis-plus分页起效

@Configuration
public class MyConfig {

    @Bean
    public PaginationInterceptor getPaginationInterceptor() {
        return new PaginationInterceptor();
    }

}

 
 

BaseDTO.java、ClassType.java

@Data
public class BaseDTO {

    @TableId(type=IdType.AUTO)
    Integer id;

}


@TableName("class_type")
@Data
@Builder
public class ClassType extends BaseDTO {

    @TableField("classNo")
    Integer classNo;

    @TableField("description")
    String description;

}

 
 

ClassTypeDao.java

@Mapper
public interface ClassTypeDao extends BaseMapper<ClassType>{


}

 
 

测试

@SpringBootTest
class SpringbootMybatisplusApplicationTests {

    @Autowired
    ClassTypeDao classTypeDao;

    @Test
    public void test2() {
        IPage<ClassType> classTypeIPage = classTypeDao.selectPage(new Page<>(1, 1), new QueryWrapper<ClassType>().gt("id", 0));

        List<ClassType> records = classTypeIPage.getRecords();

        records.forEach(System.out::println);
    }
}

 
 

 
 

2. 用法

2.0 Wrapper属性

# wrapper.getSqlSegment()  === 无where关键字 
(sys_user.id LIKE #{ew.paramNameValuePairs.MPGENVAL1} AND friendly_link.name = #{ew.paramNameValuePairs.MPGENVAL2} OR nick_name LIKE #{ew.paramNameValuePairs.MPGENVAL3})


# wrapper.wrapper.getCustomSqlSegment()  === 有where关键字
WHERE (sys_user.id LIKE #{ew.paramNameValuePairs.MPGENVAL1} AND friendly_link.name = #{ew.paramNameValuePairs.MPGENVAL2} OR nick_name LIKE #{ew.paramNameValuePairs.MPGENVAL3})


# wrapper.getTargetSql()  == 带问号参数sql非mybatis的手写sql == 即JDBC形式的SQL
(sys_user.id LIKE ? AND friendly_link.name = ? OR nick_name LIKE ?)

2.1 @TableId - 自定义主键生成策略

@TableName("student")
public class Student {
    
    //如果数据库该表的主键不是自增长，且不能为null，则用实体类插入数据到数据库时主键必须有值，否则报错
    @TableId(type=IdType.INPUT)
    Integer id;

}

2.2 @TableField - 自定义字段值填充

 

实现填充处理器MetaObjectHandler

@Component
@Slf4j
public class MyMetaObjectHandler implements MetaObjectHandler {
    @Override
    public void insertFill(MetaObject metaObject) {

        log.info("插入操作。。。。。。。。。。。。");

        this.setInsertFieldValByName("updateTime", new Date(), metaObject);
        this.setInsertFieldValByName("createTime", new Date(), metaObject);
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        log.info("更新操作。。。。。。。。。。。。");

        this.setInsertFieldValByName("updateTime", new Date(), metaObject);
    }
}

 
 
添加填充注解属性

@TableName("student")
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Student extends BaseDTO {

    @TableField("name")
    String name;

    @TableField("sport")
    String sport;
	
	//FieldFill.DEFAULT默认填充不进行处理
    @TableField(value="create_time", fill = FieldFill.INSERT)
    Date createTime;

    @TableField(value="update_time", fill = FieldFill.INSERT_UPDATE)
    Date updateTime;

}

 
 
测试

    @Test
    public void test7() {

        Student student = new Student();
        student.setId("fbc629c6703f0d8188476feb33c1f41f");
        student.setName("ewoi");
        student.setSport("8764431");

        int flag = studentDao.updateById(student);

        System.out.println(flag);

    }

 
 

2.3 锁 - 乐观锁、悲观锁

 

2.3.1 @Version - 乐观锁

开启乐观锁SQL拦截器

@Configuration
@EnableTransactionManagement
public class MyConfig {

    @Bean
    public OptimisticLockerInterceptor getOptimisticLockerInterceptor() {
        return new OptimisticLockerInterceptor();
    }

}

 
 

添加字段version字段

@Data
public class BaseDTO {

    @TableId(type=IdType.UUID)
    String id;
    
    //乐观锁注解开启
    @Version
    Integer version;

}


@TableName("student")
@Data
@ToString(callSuper=true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Student extends BaseDTO {

    @TableField("name")
    String name;

    @TableField("sport")
    String sport;

    @TableField(value="create_time", fill = FieldFill.INSERT)
    Date createTime;

    @TableField(value="update_time", fill = FieldFill.INSERT_UPDATE)
    Date updateTime;

}

 
 
测试

    @Test
    public void test8() {

        Student student = studentDao.selectById("fsfsd");

        System.out.println(student);

        student.setSport("做爱做的侍寝111");

        studentDao.updateById(student);

        student = studentDao.selectById("fsfsd");

        System.out.println(student);


    }

2.4 DML、DQL操作

实体类Student以及与该表交互的StudentDao

@Data
public class BaseDTO {
    @TableId(type=IdType.UUID)
    String id;
}


@TableName("student")
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Student extends BaseDTO {
    
    @TableField("name")
    String name;

    @TableField("sport")
    String sport;

}


@Mapper
public interface StudentDao extends BaseMapper<Student>{
}

 
 

2.4.1 DML - 增删改 - 物理

插入数据

@Autowired
StudentDao studentDao

@Test
public void test3() {

    Student student = new Student();
    student.setName("分公司的");
    student.setSport("泡妞");

    int flag = studentDao.insert(student);

    System.out.println(flag);

}

 
 

更新数据

    @Test
    public void test4() {

        Student student = new Student();
        student.setId("4c6b03074bbbab1ae361cb21f994d23b");
        student.setName("werwe");
        student.setSport("泡妞111111111");

        int flag = studentDao.updateById(student);

        System.out.println(flag);

    }

 
 

删除数据

@Test
    public void test5() {

        int flag = studentDao.deleteById("4c6b03074bbbab1ae361cb21f994d23b");

        System.out.println(flag);

    }

 
 

批量删除数据

@Test
public void test11() {

    int flag = studentDao.deleteBatchIds(Arrays.asList("1", "2"));

    System.out.println(flag);
}

2.4.2 DML - 逻辑删除 - @TableLogic - 仅对注入MyBatisPlus注入的SQL生效，自定义SQL不生效

 
 
添加字段

@Data
public class BaseDTO {

    @TableId(type=IdType.UUID)
    String id;

    //乐观锁注解开启
    @Version
    Integer version;

    //逻辑删除标识符
    @TableLogic
    Integer deleted;

}

 
 
开启配置

@Configuration
@EnableTransactionManagement
public class MyConfig {

    //开启SQL字段自动填充拦截器
    @Bean
    public PaginationInterceptor getPaginationInterceptor() {
        return new PaginationInterceptor();
    }

    //开启乐观锁
    @Bean
    public OptimisticLockerInterceptor getOptimisticLockerInterceptor() {
        return new OptimisticLockerInterceptor();
    }

    //开启逻辑删除
    @Bean
    public LogicDeleteByIdWithFill getLogicDeleteByIdWithFill() {
        return new LogicDeleteByIdWithFill();
    }

}

 
 

设置逻辑删除的值定义

server:
  port: 80

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/book?serverTimezone=UTC&useSSL=false
    driver-class-name: com.mysql.cj.jdbc.Driver
  devtools:
    restart:
      enabled: true


mybatis-plus:
  mapper-locations: classpath:/mapper/*Mapper.xml
  type-aliases-package: top.linruchang.springbootmybatisplus.dto
  global-config:
    db-config:
      table-prefix: test_
      id-type: auto
      # 设置@TableLogic修饰的注解字段，逻辑已删除=1 逻辑未删除=0
      logic-delete-value: 1
      logic-not-delete-value: 0

  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true

 
 
测试

 @Test
    public void test12() {

        int flag = studentDao.deleteBatchIds(Arrays.asList("5", "4", "3"));

        System.out.println(flag);


        List<Student> students = studentDao.selectBatchIds(Arrays.asList("5", "4", "3"));

        System.out.println(students.size());


    }

2.4.3 DQL - 查

@Data
public class BaseDTO {

    @TableId(type=IdType.UUID)
    String id;

    //乐观锁注解开启
    @Version
    Integer version;

}


@TableName("student")
@Data
@ToString(callSuper=true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Student extends BaseDTO {

    @TableField("name")
    String name;

    @TableField("sport")
    String sport;

    @TableField(value="create_time", fill = FieldFill.INSERT)
    Date createTime;

    @TableField(value="update_time", fill = FieldFill.INSERT_UPDATE)
    Date updateTime;

}

 
 

通过多个ID查询多条记录

    @Test
    public void test9() {

        List<Student> students = studentDao.selectBatchIds(Arrays.asList("1", "2"));


        System.out.println(students);

    }

 
 

通过多个字段查询记录

    @Test
    public void test10() {
        HashMap map = new HashMap();
        map.put("sport", "泡妞");

        List<Student> students = studentDao.selectByMap(map);

        students.forEach(System.out::println);

    }

 
 

2.4.4 条件构造器

 

@Test
public void test13() {

    QueryWrapper<Student> queryWrapper = new QueryWrapper<>();

    List<Object> students = studentDao.selectObjs(queryWrapper.eq("name", "ewoi").isNull("version"));

    students.forEach(System.out::println);

}

 

    @Test
    public void test14() {


        QueryWrapper<Student> queryWrapper = new QueryWrapper<>();

        queryWrapper = queryWrapper.like("sport", "侍寝").likeLeft("name", "公司的");

        List<Object> students = studentDao.selectObjs(queryWrapper);

        students.forEach(System.out::println);

    }

 

    @Test
    public void test15() {

        QueryWrapper<Student> queryWrapper = new QueryWrapper<>();

        queryWrapper = queryWrapper.notInSql("id", "select id from student where name = 'rty'");


        List<Student> students = studentDao.selectList(queryWrapper);


        students.forEach(System.out::println);

    }

 
 

2.5 性能分析、SQL打印插件（p6spy） - 支持SQL格式化

 
 
Mybatis-plus官方规则

注意！

driver-class-name 为 p6spy 提供的驱动类
url 前缀为 jdbc:p6spy 跟着冒号为对应数据库连接地址
打印出sql为null,在excludecategories增加commit
批量操作不打印sql,去除excludecategories中的batch
批量操作打印重复的问题请使用MybatisPlusLogFactory (3.2.1新增）
该插件有性能损耗，不建议生产环境使用。

 
 
引进依赖

<!-- SQL性能分析插件 -->
<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
    <version>3.9.0</version>
</dependency>

 
 
applicaiton.yml - 配置

server:
  port: 80
spring:
  datasource:
    username: root
    password: root
    # 注意这里细节
    url: jdbc:p6spy:mysql://localhost:3306/book?serverTimezone=UTC&useSSL=false
#    driver-class-name: com.mysql.cj.jdbc.Driver
	# 注意这里细节
    driver-class-name: com.p6spy.engine.spy.P6SpyDriver
  devtools:
    restart:
      enabled: true


mybatis-plus:
  mapper-locations: classpath:/mapper/*Mapper.xml
  type-aliases-package: top.linruchang.springbootmybatisplus.dto
  global-config:
    db-config:
      table-prefix: test_
      id-type: auto
      # 设置@TableLogic修饰的注解字段，逻辑已删除=1 逻辑未删除=0
      logic-delete-value: 1
      logic-not-delete-value: 0

  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true

 
 
EnhaceP6SpyLogger.java

public class EnhaceP6SpyLogger extends P6SpyLogger {

    /**
     * 参考com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger#formatMessage(int, java.lang.String, long, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
     */
    @Override
    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql, String url) {
        //格式化SQL
        String sqlContent = sql.replaceAll("[\\s]+", " ");
        sqlContent = SqlUtil.formatSql(sqlContent);

        return StringUtils.isNotBlank(sql) ? " Consume Time：" + elapsed + " ms " + now +
                "\n Execute SQL：\n" + sqlContent + "\n" : "";

    }
}

 
 
spy.properties

#3.2.1以上使用
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
#3.2.1以下使用或者不配置
#modulelist=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定义日志打印
# 官网的SQL没有格式化
#logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
# SQL格式化输出
logMessageFormat=work.linruchang.mybatisplussamplestest.config.mybatisplus.EnhaceP6SpyLogger
#日志输出到控制台
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# 使用日志系统记录 sql
#appender=com.p6spy.engine.spy.appender.Slf4JLogger
# 设置 p6spy driver 代理
deregisterdrivers=true
# 取消JDBC URL前缀
useprefix=true
# 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.
excludecategories=info,debug,result,commit,resultset
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 实际驱动可多个
#driverlist=org.h2.Driver
# 是否开启慢SQL记录
outagedetection=true
# 慢SQL记录标准 2 秒
outagedetectioninterval=2

 
 
测试

    @Test
    public void test10() {
        HashMap map = new HashMap();
        map.put("sport", "泡妞");

        List<Student> students = studentDao.selectByMap(map);

        students.forEach(System.out::println);
    }

格式化前-配置官网的com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger

格式化后：配置增强的EnhaceP6SpyLogger

 
 

2.6 代码生成器

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

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>


        <!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.2.0</version>
        </dependency>

        <!--代码生成器-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.3.2</version>
        </dependency>
        
        <!--自动填充JavaBean的 Getter/Setter方法
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <!--api文档接口生成 -->
        <!-- https://mvnrepository.com/artifact/io.springfox/springfox-swagger2 -->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
        </dependency>

        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.9.2</version>
        </dependency>

        <!--模板引擎 - myabtisplus代码生成器需要的模板引擎，不引进来会报错，可以生成代码删除这个就行了-->
        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity-engine-core</artifactId>
            <version>2.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-thymeleaf -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

 
 

 @Test
    public void test16() {


        // 代码生成器
        AutoGenerator mpg = new AutoGenerator();

        // 1. 全局配置
        GlobalConfig gc = new GlobalConfig();

        //生成文件的输入目录
        String projectPath = System.getProperty("user.dir");
        gc.setOutputDir(projectPath + "/src/main/java");
        gc.setAuthor("LinRuChang");

        //生成文件后不打开文件管理器级【我的电脑】
        gc.setOpen(false);
        //添加swagger注解在生成文件中
        gc.setSwagger2(true);

        //设置文件生成日期注释所使用的日期类
        gc.setDateType(DateType.ONLY_DATE);

        //设置插入记录时，ID主键生成的值
        gc.setIdType(IdType.ASSIGN_UUID);

        //生成的文件不覆盖已有的文件
        gc.setFileOverride(false);

        //service类，mapper类即dao类，mapper的xml文件的名字  %s表示表名
        gc.setServiceName("%sService");
        gc.setMapperName("%sDao");
        gc.setXmlName("%sMapper");
        mpg.setGlobalConfig(gc);

        // 2. 数据源配置
        DataSourceConfig dsc = new DataSourceConfig();
        dsc.setUrl("jdbc:mysql://localhost:3306/book?serverTimezone=UTC&useSSL=false&characterEncoding=utf8");
        dsc.setDriverName("com.mysql.cj.jdbc.Driver");
        dsc.setUsername("root");
        dsc.setPassword("root");
        dsc.setDbType(DbType.MYSQL);
        mpg.setDataSource(dsc);

        // 3. 包配置
        PackageConfig pc = new PackageConfig();

        //将该模块的文件放在哪个包里
        pc.setParent("top.linruchang.springbootmybatisautogenerator");
        //模块名 - 可以不配 -
        pc.setModuleName("");
        //控制层、实体类层、service层，dao层的包名设置
        pc.setController("controller");
        pc.setEntity("dto");
        pc.setService("service");
        pc.setMapper("dao");
        mpg.setPackageInfo(pc);

        // 3. 策略配置 - 实体类、controller类生成规则
        StrategyConfig strategy = new StrategyConfig();

        //实体类名的设置 - 表名转驼峰命名
        strategy.setNaming(NamingStrategy.underline_to_camel);

        //实体类的字段名的设置 - 字段名名转驼峰命名
        strategy.setColumnNaming(NamingStrategy.underline_to_camel);

        //是否使用lombok注解在实体类中
        strategy.setEntityLombokModel(true);

        //控制器中的含有@RestController注解 - 常用于前后端分离
        strategy.setRestControllerStyle(true);

        // 写于父类中的公共字段 - 实体类中的公共字段
        strategy.setSuperEntityColumns("id", "version", "deleted");
        //注意这个类需要自己写哦 - 即写上面定义的字段
        strategy.setSuperEntityClass(BaseDTO.class);

        //TODO 表名 - 准备映射的表
        strategy.setInclude("class_type", "student");
        strategy.setControllerMappingHyphenStyle(true);
        //逻辑删除字段
        strategy.setLogicDeleteFieldName("deleted");
        //乐观锁字段
        strategy.setVersionFieldName("version");

        //字段值填充
        TableFill createTime = new TableFill("create_time", FieldFill.INSERT);
        TableFill updateTime = new TableFill("update_time", FieldFill.INSERT_UPDATE);
        strategy.setTableFillList(Arrays.asList(createTime, updateTime));

        mpg.setStrategy(strategy);
        
        
        //执行代码生成
        mpg.execute();


    }

 
 

2.7 自定义主键值策略填充（IdType.INPUT）

注意： 一旦设置了keyGenerator，如果没有全局指定ID的生成策略或在基类中的@TableId中指定主键的生成策略，则默认为IdType.INPUT，则全部的主键值都由keyGenerator进行生成

ArticleTag.java

@Data
@ToString(callSuper=true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@TableName("article_tag")
//用于com.baomidou.mybatisplus.core.incrementer.IKeyGenerator#executeSql的入参
@KeySequence("ArticleTag")
public class ArticleTag extends BaseDB implements Serializable {


    /** 主键 - 记录插入自动填充主键处理{@path application.yml} */
    @TableId(type= IdType.INPUT)
    private String id;

    /**
     * 版本号
     */
    private static final long serialVersionUID = -8898734581249698571L;


    /**标签名 - 限制15个字*/
    private String tagName;

    /**标签所属的用户 - sys_user表的ID*/
    private String userId;

    /**标签是否违规：0未违规 1违规 - 违规不可显示*/
    private Integer isViolation;

}

 

MyBatisPlusConfig.java

@Configuration
public class MyBatisPlusConfig {

    /**
     * 主键生成策略，当com.baomidou.mybatisplus.annotation.TableId#type() 为 IdType.INPUT
     * @return
     */
    @Bean
    public IKeyGenerator keyGenerator() {
        return new IKeyGenerator(){

            @Override
            public String executeSql(String incrementerName) {
                incrementerName = StrUtil.nullToEmpty(StrUtil.trim(incrementerName));
                return StrUtil.format("select CONCAT('{}',SUBSTR(REPLACE(uuid(),'-',''),1,32-LENGTH('{}')))",incrementerName,incrementerName );
            }

            @Override
            public DbType dbType() {
                return DbType.MYSQL;
            }
        };
    }
    
}

 

ArticleTagDao.java

@Mapper
public interface ArticleTagDao extends BaseMapper<ArticleTag> {
}

 

ArticleTagService.java

@Service
public class ArticleTagService extends ServiceImpl<ArticleTagDao, ArticleTag> {
}

 

ArticleTagServiceTest.java

@SpringBootTest
class ArticleTagServiceTest {

    @Autowired
    ArticleTagService articleTagService;

    @Test
    public void keySequenceTest1() {
        ArticleTag articleTag = new ArticleTag();
        articleTag.setTagName("你好");

        boolean insert = articleTag.insert();
        Console.log("插入情况：{}", insert);
        Console.log("实体情况：{}", articleTag);

    }

}

 
 

2.8 动态表名-根据入参决定执行哪个表-会影响手写的SQL语句且手写SQL记得写表别名 - 手写SQL中所有的表名都会经过这层

内置SQL

MyBatisPlusConfig.java

@Configuration
public class MyBatisPlusConfig {



    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();


        //防全表更新与删除插件
        interceptor.addInnerInterceptor(new BlockAttackInnerInterceptor());

        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
        paginationInnerInterceptor.setDbType(DbType.MYSQL);
        interceptor.addInnerInterceptor(paginationInnerInterceptor);

        //乐观锁，字段添加version注解
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());

        //动态表单
        interceptor.addInnerInterceptor(new DynamicTableNameInnerInterceptor(() -> {
            Console.log("动态表单执行成功");
        }, (sql, tableName) -> {
            Console.log("当前执行的SQL：{}\n 表名：{}", sql,tableName);

            Map<String, Object> requestData = RequestDataHelper.getRequestData();

            String tableNum = null;
            if(CollUtil.isNotEmpty(requestData)) {
                String mapValueContent = requestData.values()
                        .stream()
                        .map(Object::toString)
                        .reduce(StrUtil.EMPTY, (t1, t2) -> {
                            return StrUtil.join(StrUtil.DOT, t1, t2);
                        });
                int mapHashValue = HashUtil.fnvHash(mapValueContent);
                int modulus = mapHashValue % 3;
                tableNum = String.valueOf(modulus);
                Console.log("当前参数的Hash值：{}: 取模后：{}",mapHashValue,modulus);
            }


            String resultTableName = tableName;
            if(StrUtil.isNotBlank(tableNum)) {
                resultTableName = StrUtil.format("{}_{}", tableName, tableNum);
            }

            RequestDataHelper.setRequestData(null);
            return resultTableName;
        }));


        return interceptor;
    }


}

 
 
SysUser.java

@Data
@ToString(callSuper=true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@TableName("sys_user")
public class SysUser extends BaseDB<SysUser> implements Serializable {
    private static final long serialVersionUID = 2095940921263481761L;


    /**
     * 用户昵称 - 如果没有设置昵称直接使用账户名显示
     */
    private String nickName;

    /**
     * 头像图片地址
     */
    private String headUrl;

    /**
     * 账户名
     */
    private String loginName;

    /**
     * 手机号
     */
    private String phoneNumber;

    /**
     * 邮箱
     */
    private String email;

    /**
     * 性别
     */
    private Integer gender;

    @TableField("gender")
    private GenderEnum genderEnum;

    /**
     * 个性签名
     */
    private String personalMotto;

    //@ApiModelProperty("我的分类专栏，逗号分隔 - article_category表的id")
    //private String myArticleCategory;


    /**
     * 最近登录时间 - 格式 - yyyyMMddHHmmss
     */
    private String lastLoginTime;


    /**
     * 登录状态：0未登录 1单设备登陆  2多设备登陆
     */
    private Integer loginStatus;


    /** 账户禁用状态：0账户可使用  1账户不可使用（封号） */
    private Integer disabledStatus;

    /**
     * 密码
     */
    private String password;


}

 
 
SysUserDao.java

@Mapper
public interface SysUserDao extends MyBaseMapper<SysUser> {
}

 
 
SysUserService.java

@Service
public class SysUserService extends ServiceImpl<SysUserDao, SysUser> {
}

 
 
SysUserServiceTest.java

@SpringBootTest
class SysUserServiceTest {

    @Test
    public void test12() {

        List<String> nicknames = null;

        Console.log("\n========sys_user=========\n");
        RequestDataHelper.setRequestData(null);
        List<SysUser> sysUsers = sysUserService.listByIds(Arrays.asList("c08d391e02bc11eb9416b42e99ea3e69", "dd9918601ef8071877cd75b6f1a21ad2"));
        nicknames = sysUsers.stream()
                .map(SysUser::getNickName)
                .collect(Collectors.toList());
        Console.log(sysUsers);
        Console.log(nicknames);


        Console.log("\n========sys_user_0=========\n");
        RequestDataHelper.setRequestData(new Dict()
                .set("id","4"));
        sysUsers = sysUserService.listByIds(Arrays.asList("c08d391e02bc11eb9416b42e99ea3e69", "dd9918601ef8071877cd75b6f1a21ad2"));
        nicknames = sysUsers.stream()
                .map(SysUser::getNickName)
                .collect(Collectors.toList());
        Console.log(sysUsers);
        Console.log(nicknames);


        Console.log("\n========sys_user_1=========\n");
        RequestDataHelper.setRequestData(new Dict()
                .set("id","8"));
        sysUsers = sysUserService.listByIds(Arrays.asList("c08d391e02bc11eb9416b42e99ea3e69", "dd9918601ef8071877cd75b6f1a21ad2"));
        nicknames = sysUsers.stream()
                .map(SysUser::getNickName)
                .collect(Collectors.toList());
        Console.log(sysUsers);
        Console.log(nicknames);




        Console.log("\n========sys_user_2=========\n");
        RequestDataHelper.setRequestData(new Dict()
                .set("id","5"));
        sysUsers = sysUserService.listByIds(Arrays.asList("c08d391e02bc11eb9416b42e99ea3e69", "dd9918601ef8071877cd75b6f1a21ad2"));
        nicknames = sysUsers.stream()
                .map(SysUser::getNickName)
                .collect(Collectors.toList());
        Console.log(sysUsers);
        Console.log(nicknames);
    }

}

 
 

执行结果

手写SQL - 必须起表别名因为动态表单插件仅改from表名，where属性的表名不会改

 
MyBatisPlusConfig.java

@Configuration
public class MyBatisPlusConfig {

    /**
     * 主键生成策略，当com.baomidou.mybatisplus.annotation.TableId#type() 为 IdType.INPUT
     *
     * @return
     */
    @Bean
    public IKeyGenerator keyGenerator() {
        return new IKeyGenerator() {

            @Override
            public String executeSql(String incrementerName) {
                incrementerName = StrUtil.nullToEmpty(StrUtil.trim(incrementerName));
                return StrUtil.format("select CONCAT('{}',SUBSTR(REPLACE(uuid(),'-',''),1,32-LENGTH('{}')))", incrementerName, incrementerName);
            }

            @Override
            public DbType dbType() {
                return DbType.MYSQL;
            }
        };
    }


    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

        //动态表单
        interceptor.addInnerInterceptor(new DynamicTableNameInnerInterceptor(() -> {
            Console.log("动态表单执行成功");
        }, (sql, tableName) -> {
            Console.log("当前执行的SQL：{}\n 表名：{}", sql, tableName);

            Map<String, Object> requestData = RequestDataHelper.getRequestData();

            String tableNum = null;
            if (CollUtil.isNotEmpty(requestData)) {
                String mapValueContent = requestData.values()
                        .stream()
                        .map(Object::toString)
                        .reduce(StrUtil.EMPTY, (t1, t2) -> {
                            return StrUtil.join(StrUtil.DOT, t1, t2);
                        });
                int mapHashValue = HashUtil.fnvHash(mapValueContent);
                int modulus = mapHashValue % 3;
                tableNum = String.valueOf(modulus);
                Console.log("当前参数的Hash值：{}: 取模后：{}", mapHashValue, modulus);
            }


            String resultTableName = tableName;
            if (StrUtil.isNotBlank(tableNum)) {
                resultTableName = StrUtil.format("{}_{}", tableName, tableNum);
                RequestDataHelper2.setRequestData(resultTableName, TableInfoHelper.getTableInfo(tableName));
            }

            return resultTableName;
        }));

        //乐观锁，字段添加version注解
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());


        //防全表更新与删除插件
        interceptor.addInnerInterceptor(new BlockAttackInnerInterceptor());

        //租户模式
        interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() {

            /**
             * 租户字段的值  每执行一次SQL一次都会判断一次
             * @return
             */
            @Override
            public Expression getTenantId() {
                Object tenant = RequestDataHelper.getRequestData("tenant");
                return tenant == null ? null : new StringValue(tenant.toString());
            }

            /**
             * 租户的字段名
             * @return
             */
            @Override
            public String getTenantIdColumn() {
                return "tenant";
            }


            /**
             * 某表的SQL是否忽略带上租户字段  每执行一次SQL一次都会判断一次
             * @param tableName 表名  这里的表名可能是动态表单的表名注意区别
             * @return
             */
            @Override
            public boolean ignoreTable(String tableName) {

                //无租户值则直接忽略拼接租户字段
                Object tenant = RequestDataHelper.getRequestData("tenant");
                boolean tenantValueFlag = ObjectUtil.isNotEmpty(tenant);

                //根据实体是否定义tenant属性来评判是否需要租户条件 == 经过动态表单的表名可能非com.baomidou.mybatisplus.annotation.TableName.value定义的表名
                TableInfo tableInfo = ObjectUtil.defaultIfNull(TableInfoHelper.getTableInfo(tableName),(TableInfo)RequestDataHelper2.getRequestData(tableName));
                boolean tanantEntityFlag = tableInfo.getFieldList().stream()
                        .anyMatch(tableFieldInfo -> {
                            return StrUtil.equals(tableFieldInfo.getColumn(), "tenant");
                        });


                //需要租户的表
                return !tanantEntityFlag && (!tenantValueFlag || !Arrays.asList(SysUser.class, FriendlyLink.class).stream()
                        .map(TableInfoHelper::getTableInfo)
                        .map(TableInfo::getTableName)
                        .anyMatch(t -> StrUtil.contains(tableName,t)));
            }
        }));


        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
        paginationInnerInterceptor.setDbType(DbType.MYSQL);
        interceptor.addInnerInterceptor(paginationInnerInterceptor);




        return interceptor;
    }


    /**
     * SQL注入器
     *
     * @return
     */
    @Bean
    public ISqlInjector enhanceISqlInjector() {
        return new DefaultSqlInjector() {
            @Override
            public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
                List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);

                methodList.add(new DeleteByIdPhysical());
                methodList.add(new DeleteByIdLogic());


                return methodList;
            }
        };
    }

}

 
SysUser.java

@Data
@ToString(callSuper=true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@TableName("sys_user")
public class SysUser extends BaseDB<SysUser> implements Serializable {
    private static final long serialVersionUID = 2095940921263481761L;


    /**
     * 用户昵称 - 如果没有设置昵称直接使用账户名显示
     */
    private String nickName;

    /**
     * 头像图片地址
     */
    private String headUrl;

    /**
     * 账户名
     */
    private String loginName;

    /**
     * 手机号
     */
    private String phoneNumber;

    /**
     * 邮箱
     */
    private String email;

    /**
     * 性别
     */
    private Integer gender;

    @TableField("gender")
    private GenderEnum genderEnum;

    /**
     * 个性签名
     */
    private String personalMotto;

    //@ApiModelProperty("我的分类专栏，逗号分隔 - article_category表的id")
    //private String myArticleCategory;


    /**
     * 最近登录时间 - 格式 - yyyyMMddHHmmss
     */
    private String lastLoginTime;


    /**
     * 登录状态：0未登录 1单设备登陆  2多设备登陆
     */
    private Integer loginStatus;


    /** 账户禁用状态：0账户可使用  1账户不可使用（封号） */
    private Integer disabledStatus;

    /**
     * 密码
     */
    private String password;


    /**
     * 租户
     */
    private String tenant;
}

 
FriendlyLink.java

@Data
@ToString(callSuper=true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@TableName("friendly_link")
public class FriendlyLink extends BaseDB{

    private static final long serialVersionUID = -4714261187453073302L;

    /** 所属用户 - 空或空字符则说明是系统链接，有则说明用户链接 */
    private String userId;

    /** 链接名 */
    private String name;

    /** 链接地址 */
    private String url;

    /** 排序 - 值越低越靠前显示 */
    private Integer sort;

    /** 链接类型：系统1 个人2 => 参考{@link LinkTypeEnums#code}*/
    private String type;

    /** 该友情链式是否跳转：0不跳转 1跳转 默认为1=> 参考{@link FlagEnums#code} */
    private String jumpFlag;

    /**其他参数：JSON格式字符串*/
    private String params;

    /**
     * 租户
     */
    private String tenant;

}

 
SysUserDao.java

public interface SysUserDao extends MyBaseMapper<SysUser> {

    SysUser findOneById(String id);

    Map findOneByIdAndFriendLink(String id);
    Map findOneByIdAndFriendLink2(String id);

}

 
SysUserMapper.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="work.linruchang.mybatisplussamplestest.dao.SysUserDao">
    <select id="findOneById" parameterType="string" resultType="work.linruchang.mybatisplussamplestest.bean.SysUser">
        select * from sys_user where id = #{id}
    </select>

    <select id="findOneByIdAndFriendLink" parameterType="string" resultType="java.util.Map">
        select *
            from sys_user
                left join friendly_link  on friendly_link.user_id = sys_user.id
            where sys_user.id = #{id}
    </select>

    <select id="findOneByIdAndFriendLink2" parameterType="string" resultType="java.util.Map">
        select *
            from sys_user as sys_user
                left join friendly_link as friendly_link  on friendly_link.user_id = sys_user.id
            where sys_user.id = #{id}
    </select>
</mapper>

 

SysUserServiceTest.java

@SpringBootTest
class SysUserServiceTest {
    @Test
    public void test15() {
        Map sysUser = sysUserDao.findOneByIdAndFriendLink("d32478155b6530951cf6b3da56848d5c");
        Console.log(sysUser);

        RequestDataHelper.setRequestData(new Dict().set("tenant", 1));
        sysUser = sysUserDao.findOneByIdAndFriendLink("d32478155b6530951cf6b3da56848d5c");
        Console.log(sysUser);

    }
    
    
    @Test
    public void test16() {
        Map sysUser = sysUserDao.findOneByIdAndFriendLink2("d32478155b6530951cf6b3da56848d5c");
        Console.log(sysUser);

        RequestDataHelper.setRequestData(new Dict().set("tenant", 1));
        sysUser = sysUserDao.findOneByIdAndFriendLink2("d32478155b6530951cf6b3da56848d5c");
        Console.log(sysUser);

    }

}

 
 

上面是：findOneByIdAndFriendLink，下面是：findOneByIdAndFriendLink2。避免Bug请手写SQL的时候起表别名

 
 

2.9 多租户 - 手写SQL中所有的表名都会经过这层 - 主表的租户字段加在where上，连表的租户字段加在on上

查看动态表名-手写SQL的代码

MyBatisPlusConfig.java

/**
 * 作用：
 *
 * @author LinRuChang
 * @version 1.0
 * @date 2022/06/12
 * @since 1.8
 **/
@Configuration
public class MyBatisPlusConfig {

    /**
     * 主键生成策略，当com.baomidou.mybatisplus.annotation.TableId#type() 为 IdType.INPUT
     *
     * @return
     */
    @Bean
    public IKeyGenerator keyGenerator() {
        return new IKeyGenerator() {

            @Override
            public String executeSql(String incrementerName) {
                incrementerName = StrUtil.nullToEmpty(StrUtil.trim(incrementerName));
                return StrUtil.format("select CONCAT('{}',SUBSTR(REPLACE(uuid(),'-',''),1,32-LENGTH('{}')))", incrementerName, incrementerName);
            }

            @Override
            public DbType dbType() {
                return DbType.MYSQL;
            }
        };
    }


    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

        //动态表单
        interceptor.addInnerInterceptor(new DynamicTableNameInnerInterceptor(() -> {
            Console.log("动态表单执行成功");
        }, (sql, tableName) -> {
            Console.log("当前执行的SQL：{}\n 表名：{}", sql, tableName);

            boolean needDynamicFlag = Arrays.asList(SysUser.class).stream()
                    .map(TableInfoHelper::getTableInfo)
                    .map(TableInfo::getTableName)
                    .anyMatch(t -> StrUtil.equals(t, tableName));
            if(!needDynamicFlag) {return tableName;}

            Map<String, Object> requestData = RequestDataHelper.getRequestData();

            String tableNum = null;
            if (CollUtil.isNotEmpty(requestData)) {
                String mapValueContent = requestData.values()
                        .stream()
                        .map(Object::toString)
                        .reduce(StrUtil.EMPTY, (t1, t2) -> {
                            return StrUtil.join(StrUtil.DOT, t1, t2);
                        });
                int mapHashValue = HashUtil.fnvHash(mapValueContent);
                int modulus = mapHashValue % 3;
                tableNum = String.valueOf(modulus);
                Console.log("当前参数的Hash值：{}: 取模后：{}", mapHashValue, modulus);
            }


            String resultTableName = tableName;
            if (StrUtil.isNotBlank(tableNum)) {
                resultTableName = StrUtil.format("{}_{}", tableName, tableNum);
                RequestDataHelper2.setRequestData(resultTableName, TableInfoHelper.getTableInfo(tableName));
            }

            return resultTableName;
        }));

        //乐观锁，字段添加version注解
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());


        //防全表更新与删除插件
        interceptor.addInnerInterceptor(new BlockAttackInnerInterceptor());

        //租户模式
        interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() {

            /**
             * 租户字段的值  每执行一次SQL一次都会判断一次
             * @return
             */
            @Override
            public Expression getTenantId() {
                Object tenant = RequestDataHelper.getRequestData("tenant");
                return tenant == null ? null : new StringValue(tenant.toString());
            }

            /**
             * 租户的字段名
             * @return
             */
            @Override
            public String getTenantIdColumn() {
                return "tenant";
            }


            /**
             * 某表的SQL是否忽略带上租户字段  每执行一次SQL一次都会判断一次
             * @param tableName 表名  这里的表名可能是动态表单的表名注意区别
             * @return
             */
            @Override
            public boolean ignoreTable(String tableName) {

                //无租户值则直接忽略拼接租户字段
                Object tenant = RequestDataHelper.getRequestData("tenant");
                boolean tenantValueFlag = ObjectUtil.isNotEmpty(tenant);

                //根据实体是否定义tenant属性来评判是否需要租户条件 == 经过动态表单的表名可能非com.baomidou.mybatisplus.annotation.TableName.value定义的表名
                TableInfo tableInfo = ObjectUtil.defaultIfNull(TableInfoHelper.getTableInfo(tableName),(TableInfo)RequestDataHelper2.getRequestData(tableName));
                boolean tanantEntityFlag = tableInfo.getFieldList().stream()
                        .anyMatch(tableFieldInfo -> {
                            return StrUtil.equals(tableFieldInfo.getColumn(), "tenant");
                        });


                //需要租户的表
                return !tanantEntityFlag || !tenantValueFlag || !Arrays.asList(SysUser.class, FriendlyLink.class).stream()
                        .map(TableInfoHelper::getTableInfo)
                        .map(TableInfo::getTableName)
                        .anyMatch(t -> StrUtil.contains(tableName,t));
            }
        }));


        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
        paginationInnerInterceptor.setDbType(DbType.MYSQL);
        interceptor.addInnerInterceptor(paginationInnerInterceptor);




        return interceptor;
    }


    /**
     * SQL注入器
     *
     * @return
     */
    @Bean
    public ISqlInjector enhanceISqlInjector() {
        return new DefaultSqlInjector() {
            @Override
            public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
                List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);

                methodList.add(new DeleteByIdPhysical());
                methodList.add(new DeleteByIdLogic());


                return methodList;
            }
        };
    }

}

 
 

2.10 枚举@EnumValue

GenderEnum.java

@Getter
@AllArgsConstructor
@ToString
public enum GenderEnum {

    /**
     * 注意：经测试，这个不会生效，即使数据库中确实是空的，也没办法赋值，是MP的Bug
     */
    NO_DELETE(null, "未知"),
    
    MALE(0, "男"),
    FEMALE(1, "女");

    /**
     * 用于标记这是数据库中存储的值
     */
    @EnumValue
    Integer dbValue;
    
    String desc;

}

 

IsDelEnum.java

@Getter
@AllArgsConstructor
@ToString
public enum IsDelEnum {

    NO_DELETE(0, "未删除"),
    DELETED(1, "已删除");

    @EnumValue
    Integer dbValue;
    String desc;

}

 

SysUser.java

@Data
@ToString(callSuper=true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@TableName("sys_user")
public class SysUser extends BaseDB<SysUser> implements Serializable {
    private static final long serialVersionUID = 2095940921263481761L;


    /**
     * 用户昵称 - 如果没有设置昵称直接使用账户名显示
     */
    private String nickName;

    /**
     * 头像图片地址
     */
    private String headUrl;

    /**
     * 账户名
     */
    private String loginName;

    /**
     * 手机号
     */
    private String phoneNumber;

    /**
     * 邮箱
     */
    private String email;

    /**
     * 性别
     */
    private Integer gender;

    @TableField("gender")
    private GenderEnum genderEnum;

    /**
     * 个性签名
     */
    private String personalMotto;

    //@ApiModelProperty("我的分类专栏，逗号分隔 - article_category表的id")
    //private String myArticleCategory;


    /**
     * 最近登录时间 - 格式 - yyyyMMddHHmmss
     */
    private String lastLoginTime;


    /**
     * 登录状态：0未登录 1单设备登陆  2多设备登陆
     */
    private Integer loginStatus;


    /** 账户禁用状态：0账户可使用  1账户不可使用（封号） */
    private Integer disabledStatus;

    /**
     * 密码
     */
    private String password;


}

 

SysUserDao.java

@Mapper
public interface SysUserDao extends MyBaseMapper<SysUser> {
}

 

SysUserServiceTest.java

@SpringBootTest
class SysUserServiceTest {
    @Test
    public void test11() {
        List<SysUser> sysUsers = sysUserService.listByIds(Arrays.asList("c08d391e02bc11eb9416b42e99ea3e69", "dd9918601ef8071877cd75b6f1a21ad2"));
        Console.log(CollectionUtil.size(sysUsers));        
        Console.log(sysUsers);
    }

}

 
 

2.11 类型处理器BaseTypeHandler - 必须开启TableName.autoResultMap=true

 
 
SysUser.java

/**
 * 作用：系统用户信息表(sys_user)
 *
 * @author LinRuChang
 * @version 1.0
 * @date 2020/09/24
 * @since 1.8
 **/
@Data
@ToString(callSuper=true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@TableName(value = "sys_user", autoResultMap = true)
public class SysUser extends BaseDB<SysUser> implements Serializable {
    private static final long serialVersionUID = 2095940921263481761L;


    /**
     * 用户昵称 - 如果没有设置昵称直接使用账户名显示
     */
    private String nickName;

    /**
     * 头像图片地址
     */
    private String headUrl;

    /**
     * 账户名
     */
    private String loginName;

    /**
     * 手机号
     */
    private String phoneNumber;

    /**
     * 邮箱
     */
    private String email;

    /**
     * 性别
     */
    private Integer gender;

    @TableField("gender")
    private GenderEnum genderEnum;

    /**
     * 个性签名
     */
    private String personalMotto;

    //@ApiModelProperty("我的分类专栏，逗号分隔 - article_category表的id")
    //private String myArticleCategory;


    /**
     * 最近登录时间 - 格式 - yyyyMMddHHmmss
     */
    private String lastLoginTime;


    /**
     * 登录状态：0未登录 1单设备登陆  2多设备登陆
     */
    private Integer loginStatus;


    /** 账户禁用状态：0账户可使用  1账户不可使用（封号） */
    private Integer disabledStatus;

    /**
     * 密码
     */
    private String password;


    /**
     * 租户
     */
    private String tenant;

    /**
     * 钱包
     *
     * 每个字段只会在mp中仅注册实例一次
     */
    @TableField(typeHandler = WalletListTypeHandler.class)
    private List<Wallet> wallets;

    /**
     * 其他信息
     * 
     * 每个字段只会在mp中仅注册实例一次
     */
    @TableField(typeHandler = HutooljsonTypeHandler.class)
    private OtherInfo otherInfo;
}

 
 
Wallet.java

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Wallet {
    /**
     * 名称
     */
    private String name;
    /**
     * 各种货币
     */
    private List<Currency> currencyList;
}

 
 
OtherInfo.java

@Data
public class OtherInfo {
    /**
     * 性别
     */
    private String sex;
    /**
     * 居住城市
     */
    private String city;
}

 
 
Currency.java

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Currency {
    /**
     * 类型: 人民币 RMB , 美元 USD
     */
    private String type;
    /**
     * 金额
     */
    private Double amount;
}

 
 
HutooljsonTypeHandler.java

@Slf4j
@MappedTypes({Object.class})
@MappedJdbcTypes(JdbcType.VARCHAR)
public class HutooljsonTypeHandler extends AbstractJsonTypeHandler<Object> {

    private final Class<?> type;

    public HutooljsonTypeHandler(Class<?> type) {
        if (log.isTraceEnabled()) {
            log.trace("HutooljsonTypeHandler(" + type + ")");
        }
        Assert.notNull(type, "Type argument cannot be null");
        this.type = type;
    }

    @Override
    protected Object parse(String json) {
        return JSONUtil.toBean(json,type);
    }

    @Override
    protected String toJson(Object obj) {
        return JSONUtil.toJsonStr(obj);
    }
}

 
 
WalletListTypeHandler.java

@Slf4j
@MappedTypes({Object.class})
@MappedJdbcTypes(JdbcType.VARCHAR)
public class WalletListTypeHandler extends HutooljsonTypeHandler {


    public WalletListTypeHandler(Class<?> type) {
        super(type);
    }

    @Override
    protected Object parse(String json) {
        return JSONUtil.toBean(json, new TypeReference<List<Wallet>>() {
        },true);
    }
}

 
 
SysUserDao.java

@Mapper
public interface SysUserDao extends MyBaseMapper<SysUser> {
}

 
 
SysUserServiceTest.java

@SpringBootTest
class SysUserServiceTest {
    @Test
    public void test17() {
        SysUser sysUser = sysUserDao.selectById("6d72c93aa292cf2ca2e789919a5e7bdc");
        Console.log(sysUser);

        sysUser = sysUserDao.selectById("c08d391e02bc11eb9416b42e99ea3e69");
        Console.log(sysUser);

    }

}

 
 

2.12 连表自定义SQL - 配合wrapper使用

SysUser.java

@Data
@ToString(callSuper=true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@TableName(value = "sys_user", autoResultMap = true)
public class SysUser extends BaseDB<SysUser> implements Serializable {
    private static final long serialVersionUID = 2095940921263481761L;


    /**
     * 用户昵称 - 如果没有设置昵称直接使用账户名显示
     */
    private String nickName;

    /**
     * 头像图片地址
     */
    private String headUrl;

    /**
     * 账户名
     */
    private String loginName;

    /**
     * 手机号
     */
    private String phoneNumber;

    /**
     * 邮箱
     */
    private String email;

    /**
     * 性别
     */
    private Integer gender;

    @TableField("gender")
    private GenderEnum genderEnum;

    /**
     * 个性签名
     */
    private String personalMotto;

    //@ApiModelProperty("我的分类专栏，逗号分隔 - article_category表的id")
    //private String myArticleCategory;


    /**
     * 最近登录时间 - 格式 - yyyyMMddHHmmss
     */
    private String lastLoginTime;


    /**
     * 登录状态：0未登录 1单设备登陆  2多设备登陆
     */
    private Integer loginStatus;


    /** 账户禁用状态：0账户可使用  1账户不可使用（封号） */
    private Integer disabledStatus;

    /**
     * 密码
     */
    private String password;


    /**
     * 租户
     */
    private String tenant;

    /**
     * 钱包
     *
     * 每个字段只会在mp中仅注册实例一次
     */
    @TableField(typeHandler = WalletListTypeHandler.class)
    private List<Wallet> wallets;

    /**
     * 其他信息
     *
     * 每个字段只会在mp中仅注册实例一次
     */
    @TableField(typeHandler = HutooljsonTypeHandler.class)
    private OtherInfo otherInfo;
}

 

FriendlyLink.java

@Data
@ToString(callSuper=true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@TableName("friendly_link")
public class FriendlyLink extends BaseDB{

    private static final long serialVersionUID = -4714261187453073302L;

    /** 所属用户 - 空或空字符则说明是系统链接，有则说明用户链接 */
    private String userId;

    /** 链接名 */
    private String name;

    /** 链接地址 */
    private String url;

    /** 排序 - 值越低越靠前显示 */
    private Integer sort;

    /** 链接类型：系统1 个人2 => 参考{@link LinkTypeEnums#code}*/
    private String type;

    /** 该友情链式是否跳转：0不跳转 1跳转 默认为1=> 参考{@link FlagEnums#code} */
    private String jumpFlag;

    /**其他参数：JSON格式字符串*/
    private String params;

    /**
     * 租户
     */
    private String tenant;

}

 
MyBatisPlusConfig.java

@Configuration
public class MyBatisPlusConfig {

    /**
     * 主键生成策略，当com.baomidou.mybatisplus.annotation.TableId#type() 为 IdType.INPUT
     *
     * @return
     */
    @Bean
    public IKeyGenerator keyGenerator() {
        return new IKeyGenerator() {

            @Override
            public String executeSql(String incrementerName) {
                incrementerName = StrUtil.nullToEmpty(StrUtil.trim(incrementerName));
                return StrUtil.format("select CONCAT('{}',SUBSTR(REPLACE(uuid(),'-',''),1,32-LENGTH('{}')))", incrementerName, incrementerName);
            }

            @Override
            public DbType dbType() {
                return DbType.MYSQL;
            }
        };
    }


    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

        //动态表单
        interceptor.addInnerInterceptor(new DynamicTableNameInnerInterceptor(() -> {
            Console.log("动态表单执行成功");
        }, (sql, tableName) -> {
            Console.log("当前执行的SQL：{}\n 表名：{}", sql, tableName);

            boolean needDynamicFlag = Arrays.asList(SysUser.class).stream()
                    .map(TableInfoHelper::getTableInfo)
                    .map(TableInfo::getTableName)
                    .anyMatch(t -> StrUtil.equals(t, tableName));
            if(!needDynamicFlag) {return tableName;}

            Map<String, Object> requestData = RequestDataHelper.getRequestData();

            String tableNum = null;
            if (CollUtil.isNotEmpty(requestData)) {
                String mapValueContent = requestData.values()
                        .stream()
                        .map(Object::toString)
                        .reduce(StrUtil.EMPTY, (t1, t2) -> {
                            return StrUtil.join(StrUtil.DOT, t1, t2);
                        });
                int mapHashValue = HashUtil.fnvHash(mapValueContent);
                int modulus = mapHashValue % 3;
                tableNum = String.valueOf(modulus);
                Console.log("当前参数的Hash值：{}: 取模后：{}", mapHashValue, modulus);
            }


            String resultTableName = tableName;
            if (StrUtil.isNotBlank(tableNum)) {
                resultTableName = StrUtil.format("{}_{}", tableName, tableNum);
                RequestDataHelper2.setRequestData(resultTableName, TableInfoHelper.getTableInfo(tableName));
            }

            return resultTableName;
        }));

        //乐观锁，字段添加version注解
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());


        //防全表更新与删除插件
        interceptor.addInnerInterceptor(new BlockAttackInnerInterceptor());

        //租户模式
        interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() {

            /**
             * 租户字段的值  每执行一次SQL一次都会判断一次
             * @return
             */
            @Override
            public Expression getTenantId() {
                Object tenant = RequestDataHelper.getRequestData("tenant");
                return tenant == null ? null : new StringValue(tenant.toString());
            }

            /**
             * 租户的字段名
             * @return
             */
            @Override
            public String getTenantIdColumn() {
                return "tenant";
            }


            /**
             * 某表的SQL是否忽略带上租户字段  每执行一次SQL一次都会判断一次
             * @param tableName 表名  这里的表名可能是动态表单的表名注意区别
             * @return
             */
            @Override
            public boolean ignoreTable(String tableName) {

                //无租户值则直接忽略拼接租户字段
                Object tenant = RequestDataHelper.getRequestData("tenant");
                boolean tenantValueFlag = ObjectUtil.isNotEmpty(tenant);

                //根据实体是否定义tenant属性来评判是否需要租户条件 == 经过动态表单的表名可能非com.baomidou.mybatisplus.annotation.TableName.value定义的表名
                TableInfo tableInfo = ObjectUtil.defaultIfNull(TableInfoHelper.getTableInfo(tableName),(TableInfo)RequestDataHelper2.getRequestData(tableName));
                boolean tanantEntityFlag = tableInfo.getFieldList().stream()
                        .anyMatch(tableFieldInfo -> {
                            return StrUtil.equals(tableFieldInfo.getColumn(), "tenant");
                        });


                //需要租户的表
                return !tanantEntityFlag || !tenantValueFlag || !Arrays.asList(SysUser.class, FriendlyLink.class).stream()
                        .map(TableInfoHelper::getTableInfo)
                        .map(TableInfo::getTableName)
                        .anyMatch(t -> StrUtil.contains(tableName,t));
            }
        }));


        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
        paginationInnerInterceptor.setDbType(DbType.MYSQL);
        interceptor.addInnerInterceptor(paginationInnerInterceptor);




        return interceptor;
    }


    /**
     * SQL注入器
     *
     * @return
     */
    @Bean
    public ISqlInjector enhanceISqlInjector() {
        return new DefaultSqlInjector() {
            @Override
            public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
                List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);

                methodList.add(new DeleteByIdPhysical());
                methodList.add(new DeleteByIdLogic());


                return methodList;
            }
        };
    }

}

 
SysUserDao.java

@Mapper
public interface SysUserDao extends MyBaseMapper<SysUser> {

    IPage<Map> findOneByIdAndFriendLink2Page(IPage<Map> pageInfo, @Param("ew") Wrapper<SysUser> wrapper);
}

 
SysUserMapper.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="work.linruchang.mybatisplussamplestest.dao.SysUserDao">

    <select id="findOneByIdAndFriendLink2Page"  resultType="java.util.Map">
        select *
        from sys_user as sys_user
                 left join friendly_link as friendly_link on friendly_link.user_id = sys_user.id
        <where>
            ${ew.sqlSegment}
        </where>

    </select>
</mapper>

 
SysUserServiceTest.java

@SpringBootTest
class SysUserServiceTest {
    @Test
    public void test18() {
        RequestDataHelper.setRequestData(new Dict().set("tenant", 1));

        Page<Map> pageInfo = new Page<>(2, 1);

        LambdaQueryWrapper<SysUser> wrapper = Wrappers.<SysUser>query()
                .like("sys_user.id", "1536530866313883649")
                .eq("friendly_link.name", "Gitee")
                .or().lambda()
                .like(SysUser::getNickName, "嗯嗯**");


        IPage<Map> dataPage = sysUserDao.findOneByIdAndFriendLink2Page(pageInfo, wrapper);
        dataPage.getRecords().stream().forEach(Console::log);
    }

}