SpringBoot非事务测试下高效清理受影响测试数据库表的方案咨询

核心思路 #

通过代理JDBC数据源拦截所有执行的SQL语句，使用JSqlParser（活跃维护的轻量SQL解析库）提取SQL涉及的表名，收集测试全周期（从@Sql执行前到测试结束）内所有被访问/修改的表，最终在测试完成后统一截断这些表，避免全表扫描或手动指定表的遗漏问题。

依赖引入 #

添加JSqlParser到测试依赖（版本取最新稳定版）：

<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>4.9</version>
    <scope>test</scope>
</dependency>

代码实现 #

1. 线程安全的表收集器 #

用于存储当前测试线程中所有受影响的表名：

import java.util.Collections;
import java.util.HashSet;
import java.util.Set;
import java.util.concurrent.ThreadLocal;

public class SqlTableCollector {
    private static final ThreadLocal<Set<String>> affectedTables = ThreadLocal.withInitial(HashSet::new);

    public static void addTable(String tableName) {
        affectedTables.get().add(tableName.toLowerCase());
    }

    public static Set<String> getAffectedTables() {
        return Collections.unmodifiableSet(affectedTables.get());
    }

    public static void clear() {
        affectedTables.remove();
    }
}

2. JDBC代理组件 #

代理Connection和Statement，拦截SQL执行并解析表名：

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.apache.commons.dbcp2.DelegatingConnection;
import org.apache.commons.dbcp2.DelegatingPreparedStatement;
import org.apache.commons.dbcp2.DelegatingStatement;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class MonitoringConnection extends DelegatingConnection {
    private static final Logger logger = LoggerFactory.getLogger(MonitoringConnection.class);

    public MonitoringConnection(Connection target) {
        super(target);
    }

    @Override
    public Statement createStatement() throws SQLException {
        return new MonitoringStatement(super.createStatement());
    }

    @Override
    public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
        return new MonitoringStatement(super.createStatement(resultSetType, resultSetConcurrency));
    }

    @Override
    public PreparedStatement prepareStatement(String sql) throws SQLException {
        parseAndCollectTables(sql);
        return new MonitoringPreparedStatement(super.prepareStatement(sql), sql);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        parseAndCollectTables(sql);
        return new MonitoringPreparedStatement(super.prepareStatement(sql, resultSetType, resultSetConcurrency), sql);
    }

    private void parseAndCollectTables(String sql) {
        try {
            Statement statement = CCJSqlParserUtil.parse(sql);
            TablesNamesFinder tablesFinder = new TablesNamesFinder();
            tablesFinder.getTableList(statement).forEach(SqlTableCollector::addTable);
        } catch (JSQLParserException e) {
            logger.warn("Failed to parse SQL: {}", sql, e);
        }
    }

    private static class MonitoringStatement extends DelegatingStatement {
        private static final Logger logger = LoggerFactory.getLogger(MonitoringStatement.class);

        public MonitoringStatement(Statement target) {
            super(target);
        }

        @Override
        public boolean execute(String sql) throws SQLException {
            parseAndCollectTables(sql);
            return super.execute(sql);
        }

        @Override
        public int executeUpdate(String sql) throws SQLException {
            parseAndCollectTables(sql);
            return super.executeUpdate(sql);
        }

        @Override
        public java.sql.ResultSet executeQuery(String sql) throws SQLException {
            parseAndCollectTables(sql);
            return super.executeQuery(sql);
        }

        private void parseAndCollectTables(String sql) {
            try {
                Statement statement = CCJSqlParserUtil.parse(sql);
                TablesNamesFinder tablesFinder = new TablesNamesFinder();
                tablesFinder.getTableList(statement).forEach(SqlTableCollector::addTable);
            } catch (JSQLParserException e) {
                logger.warn("Failed to parse SQL: {}", sql, e);
            }
        }
    }

    private static class MonitoringPreparedStatement extends DelegatingPreparedStatement {
        private static final Logger logger = LoggerFactory.getLogger(MonitoringPreparedStatement.class);
        private final String sql;

        public MonitoringPreparedStatement(PreparedStatement target, String sql) {
            super(target);
            this.sql = sql;
        }

        @Override
        public boolean execute() throws SQLException {
            parseAndCollectTables(sql);
            return super.execute();
        }

        @Override
        public int executeUpdate() throws SQLException {
            parseAndCollectTables(sql);
            return super.executeUpdate();
        }

        @Override
        public java.sql.ResultSet executeQuery() throws SQLException {
            parseAndCollectTables(sql);
            return super.executeQuery(sql);
        }

        private void parseAndCollectTables(String sql) {
            try {
                Statement statement = CCJSqlParserUtil.parse(sql);
                TablesNamesFinder tablesFinder = new TablesNamesFinder();
                tablesFinder.getTableList(statement).forEach(SqlTableCollector::addTable);
            } catch (JSQLParserException e) {
                logger.warn("Failed to parse SQL: {}", sql, e);
            }
        }
    }
}

3. 代理数据源 #

包装原有数据源，返回代理Connection：

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class MonitoringDataSource extends AbstractDataSource {
    private final DataSource targetDataSource;

    public MonitoringDataSource(DataSource targetDataSource) {
        this.targetDataSource = targetDataSource;
    }

    @Override
    public Connection getConnection() throws SQLException {
        return new MonitoringConnection(targetDataSource.getConnection());
    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        return new MonitoringConnection(targetDataSource.getConnection(username, password));
    }

    @Override
    @SuppressWarnings("unchecked")
    public <T> T unwrap(Class<T> iface) throws SQLException {
        if (iface.isInstance(this)) {
            return (T) this;
        }
        return targetDataSource.unwrap(iface);
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return iface.isInstance(this) || targetDataSource.isWrapperFor(iface);
    }
}

4. Spring测试监听器 #

集成Spring测试生命周期，控制表收集和截断时机：

import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.jdbc.datasource.AbstractDataSource;
import org.springframework.test.context.TestContext;
import org.springframework.test.context.support.AbstractTestExecutionListener;
import org.springframework.test.context.support.SqlScriptsTestExecutionListener;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Set;

public class TableCleanupTestExecutionListener extends AbstractTestExecutionListener {

    @Override
    public int getOrder() {
        // 确保在@Sql执行前启动收集，在测试结束后执行清理
        return SqlScriptsTestExecutionListener.DEFAULT_ORDER - 1;
    }

    @Override
    public void beforeTestClass(TestContext testContext) throws Exception {
        // 替换原有数据源为代理数据源
        DataSource originalDataSource = testContext.getApplicationContext().getBean(DataSource.class);
        MonitoringDataSource monitoringDataSource = new MonitoringDataSource(originalDataSource);

        ConfigurableApplicationContext configurableContext = (ConfigurableApplicationContext) testContext.getApplicationContext();
        configurableContext.getBeanFactory().registerSingleton("dataSource", monitoringDataSource);
    }

    @Override
    public void beforeTestMethod(TestContext testContext) throws Exception {
        // 测试前清空表收集器
        SqlTableCollector.clear();
    }

    @Override
    public void afterTestMethod(TestContext testContext) throws Exception {
        Set<String> affectedTables = SqlTableCollector.getAffectedTables();
        if (affectedTables.isEmpty()) {
            return;
        }

        // 执行截断操作
        DataSource dataSource = testContext.getApplicationContext().getBean(DataSource.class);
        try (Connection conn = dataSource.getConnection()) {
            DatabaseMetaData metaData = conn.getMetaData();
            String truncateSql = buildTruncateSql(affectedTables, metaData.getDatabaseProductName());
            try (Statement stmt = conn.createStatement()) {
                stmt.execute(truncateSql);
            }
        }
        SqlTableCollector.clear();
    }

    private String buildTruncateSql(Set<String> tables, String databaseProduct) {
        StringBuilder sb = new StringBuilder();
        boolean isMySQL = "MySQL".equalsIgnoreCase(databaseProduct);
        boolean isPostgreSQL = "PostgreSQL".equalsIgnoreCase(databaseProduct);

        for (String table : tables) {
            if (sb.length() > 0) {
                sb.append("; ");
            }
            sb.append("TRUNCATE TABLE ").append(table);
            if (isMySQL) {
                sb.append(" CASCADE");
            } else if (isPostgreSQL) {
                sb.append(" RESTART IDENTITY CASCADE");
            }
            // 可扩展适配Oracle、SQL Server等其他数据库
        }
        return sb.toString();
    }
}

测试集成 #

在测试类中注册自定义监听器，结合@Sql使用：

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.TestExecutionListeners;
import org.springframework.test.context.support.SqlScriptsTestExecutionListener;
import org.springframework.test.context.support.TestExecutionListeners.MergeMode;
import org.springframework.test.context.jdbc.Sql;

@SpringBootTest
@TestExecutionListeners(
        listeners = {TableCleanupTestExecutionListener.class, SqlScriptsTestExecutionListener.class},
        mergeMode = MergeMode.MERGE_WITH_DEFAULTS
)
@Sql(scripts = "/test-data.sql")
public class SampleServiceTest {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    public void testDataManipulation() {
        // 测试逻辑：插入、更新、查询操作
        jdbcTemplate.update("INSERT INTO product (id, name) VALUES (?, ?)", 1001, "Test Product");
        String productName = jdbcTemplate.queryForObject("SELECT name FROM product WHERE id = ?", String.class, 1001);
    }
}

注意事项 #

• SQL解析兼容性：JSqlParser支持主流数据库语法，但特殊自定义语法可能解析失败，可通过日志排查并添加适配逻辑
• 数据库适配：TRUNCATE语法在不同数据库有差异（如MySQL的CASCADE、PostgreSQL的RESTART IDENTITY），需根据实际使用的数据库调整buildTruncateSql方法
• 性能影响：代理和SQL解析会带来轻微性能开销，仅建议在测试环境使用
• 权限要求：执行TRUNCATE语句需要对应数据库的操作权限
• 表名大小写：统一转为小写避免数据库大小写敏感问题

内容的提问来源于stack exchange，提问作者gdantimi