Dynamic Table Name Plugin
In database application development, we sometimes need to query different tables based on different conditions. MyBatis-Plus provides a dynamic table name plugin called DynamicTableNameInnerInterceptor
, which allows us to dynamically change table names in SQL statements at runtime. This is particularly useful for handling table sharding logic.
Plugin Introduction
DynamicTableNameInnerInterceptor
is an interceptor provided by MyBatis-Plus. It can dynamically replace table names before executing SQL statements based on configured rules. This feature is highly useful for handling table sharding logic, such as storing data in different tables based on dates.
Quick Start
Configuring the Interceptor
In your Spring Boot configuration class, add DynamicTableNameInnerInterceptor
to the interceptor chain and configure the table name handler:
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;import com.baomidou.mybatisplus.extension.plugins.inner.DynamicTableNameInnerInterceptor;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;
@Configurationpublic class MybatisPlusConfig {
@Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor(); dynamicTableNameInnerInterceptor.setTableNameHandler((sql, tableName) -> { // Parameter retrieval method Map<String, Object> paramMap = RequestDataHelper.getRequestData(); paramMap.forEach((k, v) -> System.err.println(k + "----" + v));
String year = "_2018"; int random = new Random().nextInt(10); if (random % 2 == 1) { year = "_2019"; } return tableName + year; }); interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor); return interceptor; }}
In this example, we define a table name handler that appends either _2018
or _2019
to the table name based on a random number.
Using Dynamic Table Names
In your Mapper interface, no special configuration is required for dynamic table names, as the interceptor will handle the table name dynamically at runtime.
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
public interface UserMapper extends BaseMapper<User> { // ...}
When executing queries, MyBatis-Plus will automatically replace the table name with the actual table name.
Notes
- To prevent accidental replacements, it is recommended to define dynamic table names with complexity, such as using a prefix like
mp_dt_
. - Ensure your table naming rules do not introduce security risks like SQL injection.
- When configuring dynamic table names, consider database compatibility to ensure the replaced table names comply with the database’s naming conventions.
Example Project
To better understand how to use DynamicTableNameInnerInterceptor
, you can refer to the official example project:
This example project demonstrates how to dynamically query different user tables based on the year.
DynamicTableNameInnerInterceptor
is a powerful tool that can help you easily handle dynamic table name scenarios. With proper configuration, MyBatis-Plus can automatically manage complex table sharding logic, improving development efficiency. Remember to follow best practices to ensure system security and stability.