Executing SQL scripts

When writing integration tests for a relational database, it is often useful to run SQL scripts to change the database schema or insert test data into tables. The spring-jdbc module provides support for initializing an embedded or existing database by executing SQL scripts when the ApplicationContext is loaded in Spring. For more information, see "Embedded database support" and "Testing data access logic using the built-in database".

Although it is very reasonable to initialize the database for testing once when loading the ApplicationContext, sometimes you still need to be able to change the database during integration tests. The following sections explain how to run SQL scripts programmatically and declaratively during integration tests.

Running SQL Scripts Programmatically

Spring provides the following tools for running SQL scripts programmatically in integration methods testing.

• org.springframework.jdbc.datasource.init.ScriptUtils

• org.springframework.jdbc.datasource.init.ResourceDatabasePopulator

• org.springframework.test.context.junit4.AbstractTransactionalJUnit4SpringContextTests

• org.springframework.test.context.testng.AbstractTransactionalTestNGSpringContextTests

ScriptUtils provides a collection of static methods for working with SQL scripts and is mainly intended for internal use within the framework. However, if you need complete control over the parsing and execution of SQL scripts, ScriptUtils may be better suited than some of the other alternatives described below. For more information, see javadoc on individual methods in ScriptUtils.

ResourceDatabasePopulator provides an object-oriented API for programmatically populating, initializing, or cleaning the database using SQL scripts defined in external resources. ResourceDatabasePopulator provides options for configuring the character encoding, statement separator, comment delimiters, and error handling flags used in parsing and script execution. Each of the configuration parameters has a default value. For details on default values, see javadoc. To execute scripts configured in ResourceDatabasePopulator, you can call either the populate(Connection) method to execute the populator against java.sql.Connection, or the execute(DataSource) method to execute the fill module against the javax.sql.DataSource. The following example specifies SQL scripts for the test schema and test data, the statement delimiter is set to @@, and the scripts are run against the DataSource relation:

@Test
void databaseTest() {
    ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
    populator.addScripts(
            new ClassPathResource("test-schema.sql"),
            new ClassPathResource("test-data.sql"));
    populator.setSeparator("@@");
    populator.execute(this.dataSource);
    // run the code using the test circuit and data
}

@Test
fun databaseTest() {
    val populator = ResourceDatabasePopulator()
    populator.addScripts(
            ClassPathResource("test-schema.sql"),
            ClassPathResource("test-data.sql"))
    populator.setSeparator("@@")
    populator.execute(dataSource)
    // run the code, using the test schema and data
}

Note that ResourceDatabasePopulator internally delegates to ScriptUtils the authority to parsing and executing SQL scripts. Similarly, the executeSqlScript(..) methods in AbstractTransactionalJUnit4SpringContextTests and AbstractTransactionalTestNGSpringContextTests internally use the ResourceDatabasePopulator to execute SQL scripts . For more information, see the Javadoc on the various executeSqlScript(..) methods.

Declaratively executing SQL scripts using the @Sql annotation

In addition to the above mechanisms for executing SQL scripts programmatically, you can configure SQL scripts declaratively in the Spring TestContext Framework. In particular, you can declare a @Sql annotation on a test class or test method to configure individual SQL statements or SQL script resource paths to be executed against a given database before or after the integration method testing. Support for the @Sql annotation is provided by the SqlScriptsTestExecutionListener listener, which is enabled by default.

Path resource semantics

Each path is interpreted as Resource from Spring. A simple path (for example, "schema.sql") is considered a classpath resource that refers to the package in which the test class is defined. A path starting with a slash is considered an absolute classpath resource (for example, "/org/example/schema.sql"). The path that references the URL (for example, the path prefixed with classpath:, file:, http:) is loaded using the specified resource protocol .

The following example shows how to use the @Sql annotation at the class level and at the method level in a JUnit Jupiter-based integration test class:

@SpringJUnitConfig
@Sql("/test-schema.sql")
class DatabaseTests {
    @Test
    void emptySchemaTest() {
         // execute code that uses the test schema without any test data
    }
    @Test
    @Sql({"/test-schema.sql", "/test-user-data.sql"})
    void userTest() {
        // execute code using the test schema and test data
    }
}

@SpringJUnitConfig
@Sql("/test-schema.sql")
class DatabaseTests {
    @Test
    fun emptySchemaTest() {
         // execute code that uses the test schema without any test data
    }
    @Test
    @Sql("/test-schema.sql", "/test-user-data.sql")
    fun userTest() {
        // execute code that uses test circuit and test data
    }
}

Default Script Detection

If no scripts or SQL statements are specified, an attempt is made to determine a default script depending on where the @Sql annotation is declared. If the default value cannot be determined, an IllegalStateException exception will be thrown.

• Class level declaration: If the annotated test class is com.example.MyTest, then the corresponding default script is classpath:com/example/MyTest.sql.

• Method-level declaration: If the annotated test method is named testMethod() and is defined in the class com.example.MyTest, then the corresponding script will be named classpath:com/example/MyTest.testMethod.sql by default.

Declaring multiple sets of annotations @Sql

If you need to configure multiple sets of SQL scripts for a given class or test method, but with different syntax configuration, different error handling rules, or different execution phases for each set, you can declare multiple instances of the @Sql annotation. In Java 8, you can use the @Sql annotation as a repeating annotation. Alternatively, you can use the @SqlGroup annotation as an explicit container to declare multiple instances of the @Sql annotation.

The following example shows how to use the @Sql annotation as a repeating annotation in Java 8:

@Test
@Sql(scripts = "/test-schema.sql", config = @SqlConfig(commentPrefix = "`"))
@Sql("/test-user-data.sql") void userTest() {
    // execute code using test schema and test data
}

// Duplicate annotations with non-SOURCE persistence are not yet supported by Kotlin

In the script presented in the previous example, the test-schema.sql script uses a different syntax for single-line comments.

The following example is identical to the previous one, except that the declarations of the @Sql annotation are grouped together into the @SqlGroup annotation. In Java 8 and above, the use of the @SqlGroup annotation is optional, but the @SqlGroup annotation may need to be used for compatibility with other JVM languages such as Kotlin.

@Test
@SqlGroup({
    @Sql(scripts = "/test-schema.sql", config = @SqlConfig(commentPrefix = "`")),
    @Sql(" /test-user-data.sql")
)}
void userTest() {
    // execute code using the test schema and test data
}

@Test
@SqlGroup(
    Sql("/test-schema.sql", config = SqlConfig(commentPrefix = "`")),
    Sql("/test-user-data.sql"))
fun userTest() {
    // execute code using the test circuit and test data
}

Script execution phases

Default SQL -scripts are executed before the corresponding test method. However, if you need to run a specific set of scripts after a test method (for example, to clean up the database state), you can use the executionPhase attribute in the @Sql annotation, as shown in the following example:

@Test
@Sql(
    scripts = "create-test-data.sql",
    config = @SqlConfig(transactionMode = ISOLATED)
)
@Sql(
    scripts = "delete-test-data.sql",
    config = @SqlConfig(transactionMode = ISOLATED),
    executionPhase = AFTER_TEST_METHOD
)
void userTest() {
    // execute the code that instructs to commit test data
    // to the database outside the test transaction
}

@Test
@SqlGroup(
    Sql("create-test-data.sql",
        config = SqlConfig( transactionMode = ISOLATED)),
    Sql("delete-test-data.sql",
        config = SqlConfig(transactionMode = ISOLATED),
        executionPhase = AFTER_TEST_METHOD))
fun userTest() {
    // execute the code that instructs to capture the test data
    // in database outside the test transaction
}

Note that ISOLATED and AFTER_TEST_METHOD are statically imported from Sql.TransactionMode and Sql.ExecutionPhase, respectively.

Script configuration using @SqlConfig

Script parsing can be configured and error handling using the @SqlConfig annotation. If @SqlConfig is declared as a class-level annotation for an integration test class, then it serves as a global configuration for all SQL scripts in the test class hierarchy. When directly declared using the config attribute of the @Sql annotation, the @SqlConfig annotation serves as local configuration for SQL scripts declared in the @Sql annotation. Each attribute in the @SqlConfig annotation has an implicit default value, which is documented in the javadoc of the corresponding attribute. Due to the rules defined for annotation attributes in the Java Language Specification, it is unfortunately not possible to set an annotation attribute to null. Thus, to support overriding the inherited global configuration, the @SqlConfig annotation attributes have an explicit default value of either "" (for strings), {} ( for arrays), or DEFAULT (for enumerations). This approach allows local @SqlConfig annotation declarations to selectively override individual attributes from global @SqlConfig annotation declarations by providing a value other than "", {} or DEFAULT. Global attributes of the @SqlConfig annotation are always inherited unless local attributes of the @SqlConfig annotation provide an explicit value other than "", {} or DEFAULT. Therefore, explicit local configuration takes precedence over global configuration.

The configuration options provided by the @Sql and @SqlConfig annotations are equivalent to those supported by ScriptUtils and ResourceDatabasePopulator, but are a superset of those provided by the XML namespace element <jdbc:initialize-database/>. For details, see the javadoc for individual attributes in the annotations @Sql and @SqlConfig.

Transaction management for annotation @Sql

By default, the SqlScriptsTestExecutionListener creates the desired transaction semantics for scripts configured with the @Sql annotation. Specifically, SQL scripts are executed without a transaction within an existing Spring-managed transaction (for example, a transaction managed by TransactionalTestExecutionListener for a test marked with the @Transactional annotation) or within an isolated transaction , depending on the configured value of the transactionMode attribute in the @SqlConfig annotation and the presence of a PlatformTransactionManager in the ApplicationContext test. However, at a minimum, javax.sql.DataSource must be present in the ApplicationContext of the test.

If the algorithms used by SqlScriptsTestExecutionListener for If the definitions of DataSource and PlatformTransactionManager and the transaction semantics output do not satisfy your needs, you can specify explicit names by setting the dataSource and transactionManager attributes in the @SqlConfig annotation. You can also control transaction propagation logic by setting the transactionMode attribute in the @SqlConfig annotation (for example, whether scripts should be run in an isolated transaction). Although a detailed discussion of all supported options for managing transactions using the @Sql annotation is beyond the scope of this reference guide, the javadoc for the @SqlConfig and listener SqlScriptsTestExecutionListener contain detailed information, and the following example shows a typical testing scenario using JUnit Jupiter and transactional tests with the @Sql annotation:

@SpringJUnitConfig(TestDatabaseConfig.class)
@Transactional
class TransactionalSqlScriptsTests {
    final JdbcTemplate jdbcTemplate;
    @Autowired
    TransactionalSqlScriptsTests(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
    @Test
    @Sql("/test-data.sql")
    void usersTest() {
         // check the status in the test database:
        assertNumUsers(2);
        // execute the code, using test data...
    }
    int countRowsInTable(String tableName) {
        return JdbcTestUtils.countRowsInTable(this.jdbcTemplate, tableName);
    }
    void assertNumUsers(int expected) {
        assertEquals(expected, countRowsInTable("user"),
            "Number of rows in the [user] table.");
    }
}
    

@SpringJUnitConfig(TestDatabaseConfig::class)
@Transactional
class TransactionalSqlScriptsTests @Autowired constructor(dataSource: DataSource) {
    val jdbcTemplate: JdbcTemplate = JdbcTemplate(dataSource)
    @Test
    @Sql("/test-data.sql")
    fun usersTest() {
        // check the status in the test database:
        assertNumUsers(2)
        // execute the code, using test data...
    }
    fun countRowsInTable(tableName: String): Int {
        return JdbcTestUtils.countRowsInTable(jdbcTemplate, tableName)
    }
    fun assertNumUsers(expected: Int) {
        assertEquals(expected, countRowsInTable("user"),
                "Number of rows in the [user] table.")
    }
}

Note that there is no need to clear the database after running the usersTest() method, since any changes made to the database (either in the test method or in the /test-data.sql script) are automatically rolled back by the TransactionalTestExecutionListener (see more details in the section on transaction management).

Merge and override configuration using @SqlMergeMode annotation

Starting with Spring Framework 5.2, you can merge method-level @Sql annotation declarations with class-level declarations. For example, this would allow you to provide configuration for a database schema or some general test data for a test class once, and then provide additional, case-specific test data for each test method. To enable @Sql annotation merging, annotate your test class or test method with the @SqlMergeMode(MERGE) annotation. To disable merging for a specific test method (or a specific test subclass), you can switch back to the default mode using the @SqlMergeMode(OVERRIDE) annotation. Refer to the documentation section on the @SqlMergeMode for examples and more detailed information.