How to implement data table partitioning by MybatisPlus interceptor

MybatisPlus interceptor implements data table subtable

Many projects have business tables with large data volume or rapid increase in data volume. Since the amount of mysql data volume reaches a certain amount, it will affect our query efficiency. In order to avoid this type of problem, we need to design a sub-table for these two situations in the early design of the project.

The sub-table here refers to horizontal splitting (but the table names are different, the other fields are the same, and the primary key id is not allowed to be repeated). A certain number is used as the suffix name of the split table. The specific number of tables to be divided can be determined by your actual project situation.

First create a auxiliary class for passing the request parameter

/**
  * Request parameters to pass auxiliary class
  */
public class RequestDataHelper {
    /**
      * Request parameter access
      */
    private static final ThreadLocal<Map<String, Object>> REQUEST_DATA = new ThreadLocal<>();

    /**
      * Set request parameters
      *
      * @param requestData Request Parameters MAP Object
      */
    public static void setRequestData (Map<String, Object> requestData) {
        REQUEST_DATA.set(requestData);
    }

    /**
      * Get request parameters
      *
      * @return Request Parameters MAP Object
      */
    public static Map<String, Object> getRequestData () {
        return REQUEST_DATA.get();
    }
}

The main functions of this auxiliary class are:

• a. Set request parameters
• b. Access the request parameters into thread variable
• c. Get request parameters

Simply put, when it involves data operations that require a subtable, put the request parameters into the thread variable.

Then get this parameter in the interceptor, do specific processing, and find the table we want to operate.

You can understand the request parameters here as data associated with the table name after we split the table.

The thread variables here are data that can only be used in the current thread and are isolated from other threads.

This article will not explain in detail.

Before the data layer operation involving subtables (before Mybatisplus or Mybatis add, delete, modify and check data)

Put request parameters into thread variables

(("studentId", ()));
        LambdaQueryWrapper<StudentRecordEntity> queryWrapper = new LambdaQueryWrapper<>();
        (StudentRecordEntity::getStudentId, ());
        List<StudentRecordEntity> studentRecordEntityList = (queryWrapper);

Mybatisplus interceptor code

/**
      * Mybatis plus interceptor
      */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

        //Dynamic table plugin        DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();
        ((sql, tableName) -> {
            // Get parameter method            if (("t_student_record")) {
                Map<String, Object> paramMap = ();
                long studentId= ((("studentId")));
                int mod = (int) (studentId% 16);
                return tableName + "_" + mod;

            } else {
                return tableName;
            }
        });
        (dynamicTableNameInnerInterceptor);
        return interceptor;
    }

Here the t_student_record table is the table to be split. If there are multiple, write multiple in if.

studentId is the request parameter of the thread variable when called. For it, %16 is divided into 16 tables. According to the actual business situation, the number after % is changed to the number after % to the number according to the actual business situation.

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.