Mastering Spring Boot @Sql: Real-World Integration Test Cases

1. Introduction

Integration testing requires a consistent database state. The Spring Test framework provides the @Sql annotation to declaratively execute SQL scripts before or after tests, simplifying data setup and cleanup.

2. Practical Cases

2.1 Execute SQL Scripts

@Sql

is repeatable and can be placed at class or method level; method‑level declarations override class‑level ones. The SqlScriptsTestExecutionListener runs the scripts. Example: users.sql runs before testSave, schema.sql runs once when the test class loads.

Script content:

Code example:

@Sql(
    value = {"classpath:/sql/schema.sql"},
    executionPhase = ExecutionPhase.BEFORE_TEST_CLASS)
@SpringBootTest
public class UserServiceTest {
    @Sql({"classpath:/sql/users.sql"})
    @Test
    public void testSave() {
    }
}

2.2 Execute SQL Statements Directly

When only a few statements are needed, they can be written directly in the annotation.

@Sql(statements = {
    "INSERT INTO t_user (name, age, email) VALUES ('Zhang San', 25, '[email protected]');",
    "INSERT INTO t_user (name, age, email) VALUES ('pack_xg', 33, '[email protected]');"
})
@Test
public void testExecuteSql() {
}

Result screenshot:

2.3 No Script or Statements

If @Sql is present without scripts or statements, Spring looks for a file named after the test method (e.g., com/pack/UserServiceTest.testNoSql.sql). The error output shows the missing file.

2.4 Execution Phases

The annotation supports four phases:

ExecutionPhase.BEFORE_TEST_METHOD (default): runs before each test method.

ExecutionPhase.AFTER_TEST_METHOD: runs after each test method.

ExecutionPhase.AFTER_TEST_CLASS: runs once after all test methods in the class.

ExecutionPhase.BEFORE_TEST_CLASS: runs once before any test method in the class.

@Sql(
    scripts = {"classpath:/sql/users.sql"},
    executionPhase = ExecutionPhase.BEFORE_TEST_METHOD)
@Test
public void testSave() {
    // ...
}

2.5 @SqlGroup

@SqlGroup

can combine multiple @Sql declarations, though it is optional because @Sql is repeatable.

@SqlGroup({
    @Sql("/sql/users.sql"),
    @Sql("/sql/xxx.sql")
})
@Test
public void testSqlGroup() {
}

2.6 @SqlMergeMode

When both class‑level and method‑level @Sql are used, @SqlMergeMode(MERGE) ensures that scripts from both levels are executed instead of the method level overriding the class level.

@SpringBootTest
@SqlMergeMode(MergeMode.MERGE)
@Sql({"/sql/product-test1.sql"})
public class ProductServiceTest {
    @Sql({"/sql/product-test2.sql"})
    @Test
    public void testQueryProduct() {
        // assertions
    }
}

2.7 @SqlConfig

@SqlConfig

customizes how scripts are parsed, for example setting errorMode to CONTINUE_ON_ERROR so that execution proceeds even if a script fails.

@Sql(
    scripts = {"/sql/users.sql", "/sql/xxx.sql"},
    config = @SqlConfig(errorMode = ErrorMode.CONTINUE_ON_ERROR))
@Test
public void testConfigSQL() {
}