Mybatis-Plus实现动态表名sql解析器

Mybatis-Plus实现动态表名sql解析器

在实现动态表名sql解析之前我们先配置动态表名的创建

动态表名的创建

配置mapper

public interface SysTestMapper extends BaseMapper<SysTest> {

    void createTable(@Param("tableName") String tableName);

}

配置mapper.xml

<mapper namespace="com.demo.studynew.mapper.SysTestMapper">

    <update id="createTable" parameterType="String">
        CREATE TABLE ${tableName} (
          `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
          `name` varchar(20) DEFAULT NULL,
          `gmt_create` datetime DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  </update>

</mapper>

测试

@Test
void testCreateTable(){
     sysTestMapper.createTable("sys_test_2019");
}

至此动态表名的创建配置完成。

动态表名sql解析

添加依赖

<dependency>
   <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.0</version>
</dependency>

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

配置yml

spring:
  servlet:
    multipart:
      max-file-size: 50MB
      max-request-size: 100MB
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF8&useSSL=false
    driver-class-name: com.mysql.jdbc.Driver
file:
  path: /Users/jisen/Desktop
  folder: /files/

## mybatis-plus
mybatis-plus:
  mapper-locations: classpath*:mapper/**/*.xml
  configuration:
    map-underscore-to-camel-case: true
  global-config:
    db-config:
      update-strategy: ignored

编写sql解析器

package com.demo.studynew;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TableNameHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.DynamicTableNameInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.HashMap;
import java.util.Map;


@Configuration
@MapperScan("com.demo.studynew.**")
public class MybatisPlusConfig {

    public static ThreadLocal<String> TABLE_NAME = new ThreadLocal<String>();

    /**
     * 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题(该属性会在旧插件移除后一同移除)
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);
        interceptor.addInnerInterceptor(paginationInnerInterceptor);
        DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();
        Map<String, TableNameHandler> tableNameHandlerMap = new HashMap<String, TableNameHandler>();
        tableNameHandlerMap.put("sys_test", new TableNameHandler() {
            @Override
            public String dynamicTableName(String sql, String tableName) {
                return TABLE_NAME.get();
            }
        });
        dynamicTableNameInnerInterceptor.setTableNameHandlerMap(tableNameHandlerMap);
        interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
        return interceptor;
    }
}

测试

package com.demo.studynew;

import com.demo.studynew.entity.SysTest;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.time.LocalDateTime;
import java.util.List;


@RestController
@Api(tags = "动态表名测试")
public class SysTestController {

    @Autowired
    private SysTestMapper sysTestMapper;

    @GetMapping("list")
    @ApiOperation("列表")
    public List<SysTest> list() {
        MybatisPlusConfig.TABLE_NAME.set("sys_test_2020");
        return sysTestMapper.selectList(null);
    }

    @GetMapping("add")
    @ApiOperation("新增")
    public String add(){
        MybatisPlusConfig.TABLE_NAME.set("sys_test_2020");
        SysTest sysTest = new SysTest();
        sysTest.setName("test1");
        sysTest.setGmtCreate(LocalDateTime.now());
        sysTestMapper.insert(sysTest);
        return "ok";
    }
}

至此动态表名sql解析完成。

参考：

https://blog.csdn.net/qq_21896123/article/details/108437839

https://blog.csdn.net/dothetrick/article/details/113152874