How to Use MyBatis Interceptor for Dynamic SQL in Spring Boot 3

Introduction

MyBatis is a popular Java persistence framework that allows flexible SQL mapping and execution. Sometimes we need to modify SQL statements at runtime, for example to add conditions, sorting, or pagination. MyBatis provides a powerful mechanism called Interceptor to achieve this.

Interceptor Overview

An interceptor is based on AOP and can insert custom logic before or after target method execution. MyBatis defines four interceptor types:

Executor – intercepts methods like update, query, commit, rollback.

ParameterHandler – intercepts methods such as setParameters.

ResultSetHandler – intercepts methods like handleResultSets, handleOutputParameters.

StatementHandler – intercepts methods like prepare, parameterize, batch, update, query, allowing SQL modification.

Implementing the Interceptor

Define a class that implements org.apache.ibatis.plugin.Interceptor and override intercept, plugin, and setProperties.

Add @Intercepts annotation with the target object and method, e.g.

@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})

Registering the Interceptor

When MyBatis‑Plus is integrated in a Spring Boot project, its auto‑configuration automatically registers all beans of type Interceptor. Therefore, simply declare the interceptor as a Spring component.

@Component
@Slf4j
@Intercepts({
    @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})
})
public class DynamicSqlInterceptor implements Interceptor {
    @Value("${dynamicSql.placeholder}")
    private String placeholder;

    @Value("${dynamicSql.date}")
    private String dynamicDate;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        // 1. Get StatementHandler (contains the SQL)
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        // 2. Use MetaObject to access internal properties
        MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY,
                SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());
        // 3. Retrieve mappedStatement
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
        String id = mappedStatement.getId();
        // 4. Get DAO class
        Class<?> classType = Class.forName(id.substring(0, id.lastIndexOf(".")));
        // 5. Get original SQL
        BoundSql boundSql = statementHandler.getBoundSql();
        String sql = boundSql.getSql();
        log.info("Before replace --- sql: {}", sql);
        String mSql = null;
        // 6. Scan DAO methods for @DynamicSql annotation
        for (Method method : classType.getMethods()) {
            if (method.isAnnotationPresent(DynamicSql.class)) {
                mSql = sql.replaceAll(placeholder, String.format("'%s'", dynamicDate));
                break;
            }
        }
        if (StringUtils.isNotBlank(mSql)) {
            log.info("After replace --- mSql: {}", mSql);
            // 8. Replace SQL in BoundSql via reflection
            Field field = boundSql.getClass().getDeclaredField("sql");
            field.setAccessible(true);
            field.set(boundSql, mSql);
        }
        // 9. Proceed with modified SQL
        return invocation.proceed();
    }

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

    @Override
    public void setProperties(Properties properties) {
        // No properties needed
    }
}

YAML Configuration

# Dynamic SQL configuration
dynamicSql:
  placeholder: "@dynamicSql"
  date: "2023-07-10 20:10:30"

DAO Layer

Annotate methods that need placeholder replacement with @DynamicSql:

public interface DynamicSqlMapper {
    @DynamicSql
    Long count();
}

Mapper XML

Use the placeholder in the SQL:

<mapper namespace="ltd.newbee.mall.core.dao.DynamicSqlMapper">
    <select id="count" resultType="java.lang.Long">
        select count(1) from member
        where create_time > @dynamicSql
    </select>
</mapper>

Testing

@SpringBootTest
@RunWith(SpringRunner.class)
public class DynamicTest {
    @Autowired
    private DynamicSqlMapper dynamicSqlMapper;

    @Test
    public void test() {
        Long count = dynamicSqlMapper.count();
        Assert.notNull(count, "count cannot be null");
    }
}

Test output shows the SQL before and after replacement, confirming that the placeholder is correctly substituted with the configured date.

Interceptor Application Scenarios

SQL execution monitoring – log SQL statements, parameters, and execution time.

SQL pagination – add pagination parameters before query execution.

Automatic assignment of common fields such as createTime and updateTime.

Data permission filtering – enforce tenant or user‑level data isolation by modifying SQL.

General SQL replacement – replace conditions or special characters dynamically (the scenario demonstrated in this article).

Conclusion

The article explains how to implement a MyBatis interceptor to achieve dynamic SQL modification in a Spring Boot 3 environment, providing a clean and maintainable solution for scenarios like time‑based filtering, monitoring, pagination, and data permission control.