Exploration of the optimal solution for the dynamic table name of Mybatis Plus data sub-table

1. Application scenarios

When you use Mybatis for development, you often encounter a situation: put data in different tables according to month, and when querying data, you need to query different tables with different months.

But we all know that Mybatis is an ORM persistence layer framework, that is: entity relationship mapping, there is a one-to-one mapping relationship between entity objects and database tables. for example:

 @Data
public class Student {
    private Integer id;
    private String stuName;
    private Integer age;
}

Table Structure

 CREATE TABLE `student` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `stu_name` VARCHAR(64) NOT NULL DEFAULT '0' COMMENT '姓名',
    `age` INT(11) NOT NULL COMMENT '年龄',
    PRIMARY KEY (`id`)
)
COMMENT='学生表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

There is a one-to-one correspondence between the Student entity class and the student table. If we want to divide the student table according to the month, such as: student_202206, student_202207, student_202208, an entity class and its Mapper need to operate multiple database monthly tables. , How do we operate the data under Mybatis plus in this case? In fact, there are many methods, I will explain the best solution I have summarized in my practice.

Second, the dynamic table name processor interface implementation

In order to deal with the above-mentioned similar problems, mybatis plus provides a dynamic table name processor interface TableNameHandler , we only need to implement this interface, and apply the configuration of this interface to take effect, then the dynamic table name can be realized.

have to be aware of is:

• Before mybatis plus version 3.4, the dynamic table name processor interface is ITableNameHandler , which needs to be used together with the mybatis plus paging plugin to take effect. We only introduce the implementation after version 3.4 here.
• In mybatis plus 3.4.3.2, this method is abolished: dynamicTableNameInnerInterceptor.setTableNameHandlerMap(map); If you see the dynamic table name implemented in this way, it is also an outdated implementation method. This method has been deleted in the new version.

After a period of practice summary, my implementation class is as follows (based on the version after mybatis plus 3.4.3.2):

 import com.baomidou.mybatisplus.extension.plugins.handler.TableNameHandler;

import java.util.Arrays;
import java.util.List;

/**
 * 按月份参数，组成动态表名
 */
public class MonthTableNameHandler implements TableNameHandler {

    //用于记录哪些表可以使用该月份动态表名处理器（即哪些表按月分表）
    private List<String> tableNames;
    //构造函数，构造动态表名处理器的时候，传递tableNames参数
    public MonthTableNameHandler(String ...tableNames) {
        this.tableNames = Arrays.asList(tableNames);
    }

    //每个请求线程维护一个month数据，避免多线程数据冲突。所以使用ThreadLocal
    private static final ThreadLocal<String> MONTH_DATA = new ThreadLocal<>();
    //设置请求线程的month数据
    public static void setData(String month) {
        MONTH_DATA.set(month);
    }
    //删除当前请求线程的month数据
    public static void removeData() {
        MONTH_DATA.remove();
    }

    //动态表名接口实现方法
    @Override
    public String dynamicTableName(String sql, String tableName) {
        if (this.tableNames.contains(tableName)){
            return tableName + "_" + MONTH_DATA.get();  //表名增加月份后缀
        }else{
            return tableName;   //表名原样返回
        }
    }
}

You first have a basic understanding of the above code, read the following test process, and then look back at the comments in the above code, it will be easier to understand. After the table name processor is written, we need to make the following configuration to make it effective. Configure the content according to the gourd and draw the scoop. For the parts that need attention, I have added notes to everyone.

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

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();
        dynamicTableNameInnerInterceptor.setTableNameHandler(
                //可以传多个表名参数，指定哪些表使用MonthTableNameHandler处理表名称
                new MonthTableNameHandler("student","teacher") 
        );
        //以拦截器的方式处理表名称
        interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
        //可以传递多个拦截器，即：可以传递多个表名处理器TableNameHandler
        //interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
        return interceptor;
    }
}

Third, the test effect

First create a StudentMapper. By default, StudentMapper can only operate the student table, but not the student_YYYYMM table.

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

Let's write a unit test case, the test case test function simulates a controller or service function that requests access.

 @SpringBootTest
class DynamicTableNameTest {
    @Resource
    private StudentMapper studentMapper;

    @Test
    void test() {
        //执行数据操作之前设置月份（实际场景下该参数从请求参数中解析）
        MonthTableNameHandler.setData("202208");
        studentMapper.selectById(1); //以id=2查询student_202208这张表
        //阅后即焚，将ThreadLocal当前请求线程的数据移除
        MonthTableNameHandler.removeData();
    }
}

When we execute this unit test case, we find that the console prints the following information, pay attention to the SQL part, it is really to query the student_202208 table, not the student table. This shows that our dynamic table name implementation is successful.

Welcome to pay attention to my announcement number: Antetokounmpo, reply 003 and present the PDF version of the author's column "The Way of Docker Cultivation", more than 30 high-quality docker articles. Antetokounmpo Blog: zimug.com