How to Dynamically Switch Databases in MyBatis Using a JSqlParser Interceptor

In a logistics sorting scenario, only one data source is available but many identical databases (one per device) store the same tables; a reporting service needs to query all databases while configuring just a single data source.

The solution avoids Sharding‑JDBC and instead modifies the SQL before execution: it parses the statement with JSqlParser, prefixes each table name with the target database name, and then runs the altered SQL.

Implementation uses MyBatis' SqlSource object, which reads SQL from XML. By intercepting the SqlSource and wrapping it with a decorator, the original SQL can be replaced. The core class is AbstractDBNameInterceptor with a static inner class SqlSourceDecorator that overrides getBoundSql to inject the database name.

public abstract class AbstractDBNameInterceptor {
    static class SqlSourceDecorator implements SqlSource {
        private static final String SQL_FIELD_NAME = "sql";
        private final SqlSource sqlSource;
        public SqlSourceDecorator(SqlSource sqlSource) { this.sqlSource = sqlSource; }
        @Override
        public BoundSql getBoundSql(Object parameterObject) {
            try {
                BoundSql boundSql = sqlSource.getBoundSql(parameterObject);
                String dbName = getSpecificDBName(parameterObject);
                if (isValid(dbName)) {
                    String targetSQL = getRequiredSqlWithSpecificDBName(boundSql, dbName);
                    updateSql(boundSql, targetSQL);
                }
                return boundSql;
            } catch (Exception e) { throw new RuntimeException(e); }
        }
        private boolean isValid(String dbName) { return StringUtils.isNotEmpty(dbName) && !"null".equals(dbName); }
        private String getRequiredSqlWithSpecificDBName(BoundSql boundSql, String dbName) throws JSQLParserException {
            String originSql = boundSql.getSql();
            Set<String> tables = TablesNamesFinder.findTables(originSql);
            for (String table : tables) { originSql = originSql.replaceAll(table, dbName + "." + table); }
            return originSql;
        }
        private void updateSql(BoundSql boundSql, String sql) throws NoSuchFieldException, IllegalAccessException {
            Field field = boundSql.getClass().getDeclaredField(SQL_FIELD_NAME);
            field.setAccessible(true);
            field.set(boundSql, sql);
        }
    }
    // ... other reusable methods
}

The abstract interceptor defines reusable methods and expects subclasses to implement getSpecificDBName, which extracts the database name from the DAO method parameters (e.g., based on device identifiers).

A concrete interceptor SelectDBNameInterceptor extends the abstract class and implements MyBatis' Interceptor interface, delegating to updateDBName:

@Intercepts({
    @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
    @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class})
})
public class SelectDBNameInterceptor extends AbstractDBNameInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable { return updateDBName(invocation); }
}

By configuring this interceptor in MyBatis, SQL statements are automatically rewritten to target the correct database, enabling dynamic database switching without adding multiple data sources.