Business Scenario

For systems anticipating high data volume growth, database sharding is a common strategy to maintain performance. A typical approach involves appending suffixes to table names (e.g., converting app_user to app_user_202201) to distribute data. To implement this transparently without modifying business logic code extensively, the application must dynamically rewrite SQL statements at runtime to target the correct sharded table based on input parameters.

Shortcomings of XML-Based Logic

An initial approach might involve using <if> tags within MyBatis XML mappers to concatenate table name suffixes. While functional for a small number of tables, this strategy becomes unmanageable as the number of sharded tables grows. It scatters routing logic across multiple mapping files, making maintenance difficult and increasing the risk of inconsistency. A centralized mechanism, similar to an authentication filter or the PageHelper plugin, is far more efficient for handling cross-cutting concerns like table routing.

Implementation Solution

The MyBatis Interceptor (Plugin) mechanism allows intercepting core method calls within the execution chain. By intercepting the StatementHandler during the prepare phase, we can access and modify the SQL statement before it is sent to the database. The interceptor captures the original SQL, inspects the parameters to determine the target table suffix, rewrites the table name, and proceeds with the execution.

Code Example

The following example demonstrates a custom interceptor that replaces a base table name with a dynamic one derived from the method parameters.

package com.example.mybatis.plugin;

import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.util.Map;
import java.util.Properties;

@Component
@Intercepts({
    @Signature(
        type = StatementHandler.class,
        method = "prepare",
        args = {Connection.class, Integer.class}
    )
})
public class DynamicTableInterceptor implements Interceptor {

    private static final String BASE_TABLE_NAME = "app_gift_info";

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler target = (StatementHandler) invocation.getTarget();
        MetaObject metaObject = SystemMetaObject.forObject(target);
        
        // Retrieve the BoundSql object containing the SQL and parameters
        BoundSql boundSql = target.getBoundSql();
        String originalSql = boundSql.getSql();
        Object parameterObject = boundSql.getParameterObject();

        // Logic to determine the new table name
        String targetTableSuffix = extractTableSuffix(parameterObject);

        if (targetTableSuffix != null && originalSql.contains(BASE_TABLE_NAME)) {
            String dynamicTableName = BASE_TABLE_NAME + "_" + targetTableSuffix;
            String modifiedSql = originalSql.replace(BASE_TABLE_NAME, dynamicTableName);
            
            // Update the SQL in the delegate object
            metaObject.setValue("delegate.boundSql.sql", modifiedSql);
        }

        return invocation.proceed();
    }

    private String extractTableSuffix(Object param) {
        if (param instanceof Map) {
            Map paramMap = (Map) param;
            Object suffix = paramMap.get("tableSuffix");
            return suffix != null ? suffix.toString() : null;
        }
        // Handle cases where parameters might be wrapped differently or use reflection
        return null;
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {
        // Optional configuration properties
    }
}

Key Implementation Details

The @Intercepts annotation defines the target point. Here, StatementHandler.prepare is chosen because the SQL statement is fully prepared but not yet executed. The MetaObject class simplifies reflection, allowing easy access to private fields like the final SQL string within the delegate handler.

Handling Parameter Types

A common pitfall during implementation involves type casting the parameter object. If a mapper method accepts a single parameter without the @Param annotation, MyBatis may pass the object directly rather than wrapping it in a MapperMethod.ParamMap. Attempting to cast it directly to a Map will cause a ClassCastException.

To resolve this, the code should check the instance type before casting. If the parameter is not a Map, one might need to inspect the object fields using reflection or handle the specific object type explicitly. Alternatively, ensuring all mapper methods use @Param annotations or wrap arguments in a DTO object mitigates this issue.