使用@sql锁的Spring Boot测试表和存储库以及服务无法读取

Question

我也可以手动确认此锁定，但在测试运行时，我不能使用SQL Server Management Studio执行查询（等待并在结束测试时完成）。

我是否可以使用@sql或任何其他数据准备工具进行测试，并让应用程序正常工作，同时还可以在测试后回滚更改？

测试类：

@SpringBootTest(classes = ...App.class)
@ActiveProfiles(profiles = "test")
@ExtendWith(SpringExtension.class)
@Transactional
public class SeleniumExampleIT {
    @LocalServerPort
    protected int port;

    protected WebDriver driver;
    protected NgWebDriver ngWebDriver;

    @BeforeAll
    public static void setupClass() {
        WebDriverManager.chromedriver().setup();
    }

    @BeforeEach
    public void setupTest() {
        var chromeDriver = new ChromeDriver();
        driver = chromeDriver;
        ngWebDriver = new NgWebDriver(chromeDriver);
        ngWebDriver.waitForAngularRequestsToFinish();
    }

    @AfterEach
    public void teardown() {
        if (driver != null) {
            driver.quit();
        }
    }

    @Test
    @Sql({"classpath:sql/test.sql"})
    void listEmployeesTest() {
        //...starting selenium
        // navigating, waiting ..etc
    }
}

EmployeeRepository:

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    @Query("SELECT DISTINCT e FROM Employee e " +
        "LEFT JOIN FETCH e.settings")
    List<Employee> findAll();
    @Query("SELECT DISTINCT e FROM Employee e " +
        "LEFT JOIN FETCH e.settings " +
        "WHERE e.id = :id")
    Optional<Employee> findById(@Param("id") Long id);
}

Answer 1

我能找到的唯一解决方案是不使用@transactional测试。我创建了一个单独的数据库，只用于测试运行。在每次测试之前，我使用如下脚本清理数据库：

-- CHECK IF DATABASE HAS TESTING ALLOWED
IF (OBJECT_ID('dbo.tests_allowed') IS NULL)
BEGIN
    RAISERROR('Testing is NOT allowed in the selected database! Please, check carefully, before removing all data! If you want to allow testing, create table dbo.`tests_allowed`.', 16, 1)
END;

-- DISABLE CHECKS
ALTER TABLE dbo.<table-names> NOCHECK CONSTRAINT ALL;

-- TRUNCATE TABLES
DELETE FROM dbo.<table-names>;

-- ENABLE CHECKS
ALTER TABLE dbo.<table-names> WITH CHECK CHECK CONSTRAINT ALL;

truncate运行后，我用data insert脚本准备数据库，执行如下：

@Test
@DisplayName("Checking settings....")
@Sql(value = {"classpath:sql/truncate-tables.sql", "classpath:sql/example-dataset-1.sql"},
    config = @SqlConfig(transactionMode = SqlConfig.TransactionMode.ISOLATED))
public void check...Test() {}

使用隔离模式，Sql文件将立即提交，我可以在测试期间读取数据。

null