Dynamic SQL with MyBatis Interceptors in Spring Boot

Introduction

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

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 such as prepare, parameterize, batch, update, query, enabling SQL modification and logging.

Implementing an Interceptor

Define a class that implements

org.apache.ibatis.plugin.Interceptor

and override

intercept

,

plugin

, and

setProperties

.

Add the

@Intercepts

annotation with the target type 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, it automatically registers all beans that implement

Interceptor

. The following diagram shows the auto‑configuration process.

Define the interceptor bean with

@Component

and

@Slf4j

annotations.

<code>@Component<br/>@Slf4j<br/>@Intercepts({<br/>    @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})<br/>})<br/>public class DynamicSqlInterceptor implements Interceptor {<br/>    @Value("${dynamicSql.placeholder}")<br/>    private String placeholder;<br/><br/>    @Value("${dynamicSql.date}")<br/>    private String dynamicDate;<br/><br/>    @Override<br/>    public Object intercept(Invocation invocation) throws Throwable {<br/>        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();<br/>        MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());<br/>        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");<br/>        String id = mappedStatement.getId();<br/>        Class<?> classType = Class.forName(id.substring(0, id.lastIndexOf(".")));<br/>        BoundSql boundSql = statementHandler.getBoundSql();<br/>        String sql = boundSql.getSql();<br/>        log.info("Before replace --- sql: {}", sql);<br/>        String mSql = null;<br/>        for (Method method : classType.getMethods()) {<br/>            if (method.isAnnotationPresent(DynamicSql.class)) {<br/>                mSql = sql.replaceAll(placeholder, String.format("'%s'", dynamicDate));<br/>                break;<br/>            }<br/>        }<br/>        if (StringUtils.isNotBlank(mSql)) {<br/>            log.info("After replace --- mSql: {}", mSql);<br/>            Field field = boundSql.getClass().getDeclaredField("sql");<br/>            field.setAccessible(true);<br/>            field.set(boundSql, mSql);<br/>        }<br/>        return invocation.proceed();<br/>    }<br/><br/>    @Override<br/>    public Object plugin(Object target) {<br/>        return Plugin.wrap(target, this);<br/>    }<br/><br/>    @Override<br/>    public void setProperties(Properties properties) {<br/>        // No properties needed for this example<br/>    }<br/>}<br/></code>

Configuration

YAML

Define the placeholder and date values in

application.yml

:

<code># Dynamic SQL configuration<br/>dynamicSql:<br/>  placeholder: "@dynamicSql"<br/>  date: "2023-07-10 20:10:30"<br/></code>

DAO Layer

Annotate methods that require dynamic SQL with

@DynamicSql

:

<code>public interface DynamicSqlMapper {<br/>    @DynamicSql<br/>    Long count();<br/>}<br/></code>

Mapper XML

Replace the date condition with the placeholder:

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

Testing

<code>@SpringBootTest<br/>@RunWith(SpringRunner.class)<br/>public class DynamicTest {<br/><br/>    @Autowired<br/>    private DynamicSqlMapper dynamicSqlMapper;<br/><br/>    @Test<br/>    public void test() {<br/>        Long count = dynamicSqlMapper.count();<br/>        Assert.notNull(count, "count cannot be null");<br/>    }<br/>}<br/></code>

Execution log shows the SQL before and after replacement:

<code>2023-07-11 22:13:33.375 INFO  DynamicSqlInterceptor - replace before --- sql: select count(1) from member where create_time > @dynamicSql<br/>2023-07-11 22:13:33.376 INFO  DynamicSqlInterceptor - replace after --- mSql: select count(1) from member where create_time > '2023-07-10 20:10:30'<br/></code>

Typical Use Cases

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

SQL pagination – modify queries to add limit/offset before execution.

Automatic assignment of common fields such as createTime and updateTime.

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

General SQL condition replacement – as demonstrated in this tutorial.

Conclusion

The article demonstrates how to create a MyBatis interceptor to dynamically replace SQL placeholders at runtime, providing a clean and reusable solution for scenarios like time‑based filtering, pagination, and data security.