Executing SQL Scripts

  1. 按编程方式执行 SQL 脚本：

    • ScriptUtils 提供静态实用程序方法。

    • ResourceDatabasePopulator 提供基于对象的 API。

  2. 使用 @Sql 注解声明性地执行 SQL 脚本：

    • 在集成测试类或方法上配置 SQL 脚本或语句资源路径。

    • 支持多组脚本、脚本执行阶段、事务管理和脚本配置。

在针对关系数据库编写集成测试时，通常有益于运行SQL脚本来修改数据库架构或向表中插入测试数据。`spring-jdbc`模块为_initializing_嵌入式或现有数据库提供了支持，以便在载入Spring`ApplicationContext`时执行SQL脚本。请参阅Embedded database support和Testing data access logic with an embedded database了解详情。

When writing integration tests against a relational database, it is often beneficial to run SQL scripts to modify 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 Spring ApplicationContext is loaded. See Embedded database support and Testing data access logic with an embedded database for details.

尽管在加载 ApplicationContext 时对数据库进行 一次 初始化非常有用，但有时有必要能够在集成测试 期间 修改数据库。以下部分解释了如何在集成测试期间按编程方式和声明性地运行 SQL 脚本。

Although it is very useful to initialize a database for testing once when the ApplicationContext is loaded, sometimes it is essential to be able to modify the database during integration tests. The following sections explain how to run SQL scripts programmatically and declaratively during integration tests.

Executing SQL scripts programmatically

Spring 提供了以下选项，用于在集成测试方法中按编程方式执行 SQL 脚本。

Spring provides the following options for executing SQL scripts programmatically within integration test methods.

  • 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`为使用 SQL 脚本提供了一组静态实用程序方法，主要用于框架内的内部使用。但是，如果您需要完全控制 SQL 脚本的解析和运行方式，那么 `ScriptUtils`可能会比这里描述的其他一些备选方案更适合您的需求。有关更多详细信息，请参阅 `ScriptUtils`中各个方法的 javadoc。

ScriptUtils provides a collection of static utility methods for working with SQL scripts and is mainly intended for internal use within the framework. However, if you require full control over how SQL scripts are parsed and run, ScriptUtils may suit your needs better than some of the other alternatives described later. See the javadoc for individual methods in ScriptUtils for further details.

ResourceDatabasePopulator`提供了一个基于对象的 API，用于通过使用外部资源中定义的 SQL 脚本以编程方式填充、初始化或清理数据库。`ResourceDatabasePopulator`提供用于配置在解析和运行脚本时使用的字符编码、语句分隔符、注释分隔符和错误处理标记的选项。每个配置选项都有一个合理的默认值。有关默认值的详细信息，请参阅 javadoc。要运行在 `ResourceDatabasePopulator`中配置的脚本，您可以调用 `populate(Connection)`方法以针对 `java.sql.Connection`运行填充器，或者调用 `execute(DataSource)`方法以针对 `javax.sql.DataSource`运行填充器。以下示例指定了测试架构和测试数据的 SQL 脚本，设置语句分隔符为 `@@，并针对 `DataSource`运行脚本：

ResourceDatabasePopulator provides an object-based API for programmatically populating, initializing, or cleaning up a database by using SQL scripts defined in external resources. ResourceDatabasePopulator provides options for configuring the character encoding, statement separator, comment delimiters, and error handling flags used when parsing and running the scripts. Each of the configuration options has a reasonable default value. See the javadoc for details on default values. To run the scripts configured in a ResourceDatabasePopulator, you can invoke either the populate(Connection) method to run the populator against a java.sql.Connection or the execute(DataSource) method to run the populator against a javax.sql.DataSource. The following example specifies SQL scripts for a test schema and test data, sets the statement separator to @@, and run the scripts against a DataSource:

  • Java

  • Kotlin

@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 code that uses the test schema and data
}
@Test
fun databaseTest() {
	val populator = ResourceDatabasePopulator()
	populator.addScripts(
			ClassPathResource("test-schema.sql"),
			ClassPathResource("test-data.sql"))
	populator.setSeparator("@@")
	populator.execute(dataSource)
	// run code that uses the test schema and data
}

请注意，ResourceDatabasePopulator`在内部委托给`ScriptUtils`来分析和运行SQL脚本。类似地，`AbstractTransactionalJUnit4SpringContextTests和AbstractTransactionalTestNGSpringContextTests中的`executeSqlScript(..)`方法在内部使用`ResourceDatabasePopulator`来运行SQL脚本。请参阅各种`executeSqlScript(..)`方法的Javadoc以了解进一步详情。

Note that ResourceDatabasePopulator internally delegates to ScriptUtils for parsing and running SQL scripts. Similarly, the executeSqlScript(..) methods in AbstractTransactionalJUnit4SpringContextTests and AbstractTransactionalTestNGSpringContextTests internally use a ResourceDatabasePopulator to run SQL scripts. See the Javadoc for the various executeSqlScript(..) methods for further details.

Executing SQL scripts declaratively with @Sql

除了上述通过编程方式运行 SQL 脚本的机制外，你还可以使用 Spring TestContext Framework 声明式配置 SQL 脚本。具体来说，你可以在测试类或测试方法上声明 @Sql 注解，以配置针对给定数据库在集成测试类或测试方法之前或之后运行的各个 SQL 语句或者 SQL 脚本的资源路径。对 @Sql 的支持是由 SqlScriptsTestExecutionListener 提供的，该监听器默认已启用。

In addition to the aforementioned mechanisms for running SQL scripts programmatically, you can declaratively configure SQL scripts in the Spring TestContext Framework. Specifically, you can declare the @Sql annotation on a test class or test method to configure individual SQL statements or the resource paths to SQL scripts that should be run against a given database before or after an integration test class or test method. Support for @Sql is provided by the SqlScriptsTestExecutionListener, which is enabled by default.

方法级别的`@Sql`声明默认覆盖类级别的声明，但是，这种行为可以通过`@SqlMergeMode`按测试类或测试方法进行配置。请参阅Merging and Overriding Configuration with @SqlMergeMode以了解进一步详情。

Method-level @Sql declarations override class-level declarations by default, but this behavior may be configured per test class or per test method via @SqlMergeMode. See Merging and Overriding Configuration with @SqlMergeMode for further details.

但是，这不适用于配置为 BEFORE_TEST_CLASS 或 AFTER_TEST_CLASS 执行阶段的类级别声明。此类声明无法覆盖，并且对应的脚本和语句将针对每个类执行一次，此外还有任何方法级别的脚本和语句。

However, this does not apply to class-level declarations configured for the BEFORE_TEST_CLASS or AFTER_TEST_CLASS execution phases. Such declarations cannot be overridden, and the corresponding scripts and statements will be executed once per class in addition to any method-level scripts and statements.

Path Resource Semantics

每个路径都解读为 Spring Resource。普通路径（例如，“schema.sql”）被视为相对于定义测试类的包的类路径资源。以斜杠开头的路径被视为绝对类路径资源（例如，/org/example/schema.sql）。引用 URL 的路径（例如，以 classpath:、file:、http: 为前缀的路径）使用指定的资源协议进行加载。

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

以下示例展示了如何在基于 JUnit Jupiter 的集成测试类中同时在类级别和方法级别使用 @Sql：

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

  • Java

  • Kotlin

@SpringJUnitConfig
@Sql("/test-schema.sql")
class DatabaseTests {

	@Test
	void emptySchemaTest() {
		// run code that uses the test schema without any test data
	}

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

	@Test
	fun emptySchemaTest() {
		// run code that uses the test schema without any test data
	}

	@Test
	@Sql("/test-schema.sql", "/test-user-data.sql")
	fun userTest() {
		// run code that uses the test schema and test data
	}
}

Default Script Detection

如果没有指定 SQL 脚本或语句，系统会尝试检测一个 default 脚本，具体取决于 @Sql 所声明的位置。如果无法检测到默认值，系统会抛出一个 IllegalStateException。

If no SQL scripts or statements are specified, an attempt is made to detect a default script, depending on where @Sql is declared. If a default cannot be detected, an IllegalStateException is thrown.

  • Class-level declaration: If the annotated test class is com.example.MyTest, 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, the corresponding default script is classpath:com/example/MyTest.testMethod.sql.

Logging SQL Scripts and Statements

如果你想查看正在执行哪些 SQL 脚本，请将 org.springframework.test.context.jdbc 日志类别设置为 DEBUG。

If you want to see which SQL scripts are being executed, set the org.springframework.test.context.jdbc logging category to DEBUG.

如果你想查看正在执行哪些 SQL 语句，请将 org.springframework.jdbc.datasource.init 日志类别设置为 DEBUG。

If you want to see which SQL statements are being executed, set the org.springframework.jdbc.datasource.init logging category to DEBUG.

Declaring Multiple @Sql Sets

如果你需要为给定测试类或测试方法配置多组 SQL 脚本，但不同脚本组有不同的语法配置、不同的错误处理规则或不同的执行阶段，你可以声明多个 @Sql 实例。你可以使用 @Sql 作为可重复注解，或者可以使用 @SqlGroup 作为声明多个 @Sql 实例的显式容器。

If you need to configure multiple sets of SQL scripts for a given test class or test method but with different syntax configuration, different error handling rules, or different execution phases per set, you can declare multiple instances of @Sql. You can either use @Sql as a repeatable annotation, or you can use the @SqlGroup annotation as an explicit container for declaring multiple instances of @Sql.

以下示例展示了如何使用 @Sql 作为可重复注解：

The following example shows how to use @Sql as a repeatable annotation:

  • Java

  • Kotlin

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

在上述示例中展示的场景中，test-schema.sql 脚本对单行注释使用了不同的语法。

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

以下示例与此前类似，只不过 @Sql 声明已归组在 @SqlGroup 中。使用 @SqlGroup 是可选的，但你可能需要使用 @SqlGroup 以兼容其他 JVM 语言。

The following example is identical to the preceding example, except that the @Sql declarations are grouped together within @SqlGroup. The use of @SqlGroup is optional, but you may need to use @SqlGroup for compatibility with other JVM languages.

  • Java

  • Kotlin

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

Script Execution Phases

默认情况下，SQL 脚本会在对应的测试方法之前运行。但是，如果你需要在测试方法之后运行特定的一组脚本（例如，清理数据库状态），你可以将 @Sql 中的 executionPhase 属性设置为 AFTER_TEST_METHOD，如下面的示例所示：

By default, SQL scripts are run before the corresponding test method. However, if you need to run a particular set of scripts after the test method (for example, to clean up database state), you can set the executionPhase attribute in @Sql to AFTER_TEST_METHOD, as the following example shows:

  • Java

  • Kotlin

@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() {
	// run code that needs the test data to be committed
	// to the database outside of the test's transaction
}
@Test
@Sql("create-test-data.sql",
	config = SqlConfig(transactionMode = ISOLATED))
@Sql("delete-test-data.sql",
	config = SqlConfig(transactionMode = ISOLATED),
	executionPhase = AFTER_TEST_METHOD)
fun userTest() {
	// run code that needs the test data to be committed
	// to the database outside of the test's transaction
}

ISOLATED 和 AFTER_TEST_METHOD 分别从 Sql.TransactionMode 和 Sql.ExecutionPhase 静态导入。

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

在 Spring Framework 6.1 中，可以通过将类级别 @Sql 声明中的 executionPhase 属性设置为 BEFORE_TEST_CLASS 或 AFTER_TEST_CLASS，在测试类之前或之后运行特定的一组脚本，如下面的示例所示：

As of Spring Framework 6.1, it is possible to run a particular set of scripts before or after the test class by setting the executionPhase attribute in a class-level @Sql declaration to BEFORE_TEST_CLASS or AFTER_TEST_CLASS, as the following example shows:

  • Java

  • Kotlin

@SpringJUnitConfig
@Sql(scripts = "/test-schema.sql", executionPhase = BEFORE_TEST_CLASS)
class DatabaseTests {

	@Test
	void emptySchemaTest() {
		// run code that uses the test schema without any test data
	}

	@Test
	@Sql("/test-user-data.sql")
	void userTest() {
		// run code that uses the test schema and test data
	}
}
@SpringJUnitConfig
@Sql("/test-schema.sql", executionPhase = BEFORE_TEST_CLASS)
class DatabaseTests {

	@Test
	fun emptySchemaTest() {
		// run code that uses the test schema without any test data
	}

	@Test
	@Sql("/test-user-data.sql")
	fun userTest() {
		// run code that uses the test schema and test data
	}
}

BEFORE_TEST_CLASS 从 Sql.ExecutionPhase 静态导入。

BEFORE_TEST_CLASS is statically imported from Sql.ExecutionPhase.

Script Configuration with @SqlConfig

你可以使用 @SqlConfig 注解配置脚本解析和错误处理。当在集成测试类上声明为一个类级别注解时，@SqlConfig 充当测试类层次结构中所有 SQL 脚本的全局配置。当直接通过使用 @Sql 注解的 config 属性声明时，@SqlConfig 充当封闭 @Sql 注解中声明的 SQL 脚本的局部配置。@SqlConfig 中的每个属性都有一个隐式默认值，该值在相应属性的 javadoc 中进行了说明。由于在 Java 语言规范中定义了针对注解属性的规则，因此无法将 null 值赋值给注解属性。因此，为了支持对继承的全局配置进行覆盖，@SqlConfig 属性有显式默认值，该值可能是 ""（针对字符串）、{}（针对数组）或 DEFAULT（针对枚举）。这种方法允许针对全局 @SqlConfig 声明，通过提供 ""、{} 或 DEFAULT 以外的值，局部 @SqlConfig 声明有选择地覆盖个别属性。局部 @SqlConfig 属性未提供 ""、{} 或 DEFAULT 以外的显式值时，全局 @SqlConfig 属性将被继承。因此，显式局部配置会覆盖全局配置。

You can configure script parsing and error handling by using the @SqlConfig annotation. When declared as a class-level annotation on an integration test class, @SqlConfig serves as global configuration for all SQL scripts within the test class hierarchy. When declared directly by using the config attribute of the @Sql annotation, @SqlConfig serves as local configuration for the SQL scripts declared within the enclosing @Sql annotation. Every attribute in @SqlConfig 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 assign a value of null to an annotation attribute. Thus, in order to support overrides of inherited global configuration, @SqlConfig attributes have an explicit default value of either "" (for Strings), {} (for arrays), or DEFAULT (for enumerations). This approach lets local declarations of @SqlConfig selectively override individual attributes from global declarations of @SqlConfig by providing a value other than "", {}, or DEFAULT. Global @SqlConfig attributes are inherited whenever local @SqlConfig attributes do not supply an explicit value other than "", {}, or DEFAULT. Explicit local configuration, therefore, overrides global configuration.

@Sql`和 `@SqlConfig`提供的配置选项等效于 `ScriptUtils`和 `ResourceDatabasePopulator`支持的选项，但它们是 `<jdbc:initialize-database/>`XML 命名空间元素提供的选项的超集。有关详细信息，请参阅 javadoc `@Sql 和 javadoc @SqlConfig 中各个属性的 javadoc。

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

Transaction management for @Sql

默认情况下，SqlScriptsTestExecutionListener 推断出使用 @Sql 配置的脚本所需的交易语义。具体来说，SQL 脚本在没有交易的情况下、在现有的由 Spring 管理的交易中（例如，针对注释了 @Transactional 的测试由 TransactionalTestExecutionListener 管理的交易）或在隔离的交易中运行，具体取决于 @SqlConfig 中 transactionMode 属性的配置值和测试的 ApplicationContext 中是否存在 PlatformTransactionManager。然而，作为最低要求，测试的 ApplicationContext 中必须存在一个 javax.sql.DataSource。

By default, the SqlScriptsTestExecutionListener infers the desired transaction semantics for scripts configured by using @Sql. Specifically, SQL scripts are run without a transaction, within an existing Spring-managed transaction (for example, a transaction managed by the TransactionalTestExecutionListener for a test annotated with @Transactional), or within an isolated transaction, depending on the configured value of the transactionMode attribute in @SqlConfig and the presence of a PlatformTransactionManager in the test’s ApplicationContext. As a bare minimum, however, a javax.sql.DataSource must be present in the test’s ApplicationContext.

如果 SqlScriptsTestExecutionListener`用于检测 `DataSource`和 `PlatformTransactionManager`并推断事务语义所使用的算法不符合您的需求，那么您可以通过设置 `@SqlConfig`的 `dataSource`和 `transactionManager`属性来指定显式名称。此外，您可以通过设置 `@SqlConfig`的 `transactionMode`属性（例如，是否应在隔离的事务中运行脚本）来控制事务传播行为。尽管对 `@Sql`的事务管理的所有支持选项进行彻底讨论超出了本参考手册的范围，但 javadoc `@SqlConfig 和 javadoc SqlScriptsTestExecutionListener 的 javadoc 提供了详细信息，并且以下示例展示了一个使用 JUnit Jupiter 和带有 `@Sql`的事务测试的典型测试场景：

If the algorithms used by SqlScriptsTestExecutionListener to detect a DataSource and PlatformTransactionManager and infer the transaction semantics do not suit your needs, you can specify explicit names by setting the dataSource and transactionManager attributes of @SqlConfig. Furthermore, you can control the transaction propagation behavior by setting the transactionMode attribute of @SqlConfig (for example, whether scripts should be run in an isolated transaction). Although a thorough discussion of all supported options for transaction management with @Sql is beyond the scope of this reference manual, the javadoc for @SqlConfig and SqlScriptsTestExecutionListener provide detailed information, and the following example shows a typical testing scenario that uses JUnit Jupiter and transactional tests with @Sql:

  • Java

  • Kotlin

@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() {
		// verify state in test database:
		assertNumUsers(2);
		// run code that uses the 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() {
		// verify state in test database:
		assertNumUsers(2)
		// run code that uses the 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.")
	}
}

请注意，无需在运行 `usersTest()`方法后清理数据库，因为对数据库所做的任何更改（在测试方法内或在 `/test-data.sql`脚本内）都会自动由 `TransactionalTestExecutionListener`回滚（有关详细信息，请参阅 transaction management）。

Note that there is no need to clean up the database after the usersTest() method is run, since any changes made to the database (either within the test method or within the /test-data.sql script) are automatically rolled back by the TransactionalTestExecutionListener (see transaction management for details).

Merging and Overriding Configuration with @SqlMergeMode

从 Spring Framework 5.2 开始，可以将方法级别的 @Sql`声明与类级别的声明合并。例如，这允许您为每个测试类一次提供数据库架构或一些常见测试数据的配置，然后为每个测试方法提供额外的用例特定测试数据。要启用 `@Sql`合并，请使用 `@SqlMergeMode(MERGE)`注释测试类或测试方法。要针对特定测试方法（或特定测试子类）禁用合并，您可以通过 `@SqlMergeMode(OVERRIDE)`切换回默认模式。有关示例和更多详细信息，请参阅 @SqlMergeMode` annotation documentation section。

As of Spring Framework 5.2, it is possible to merge method-level @Sql declarations with class-level declarations. For example, this allows you to provide the configuration for a database schema or some common test data once per test class and then provide additional, use case specific test data per test method. To enable @Sql merging, annotate either your test class or test method with @SqlMergeMode(MERGE). To disable merging for a specific test method (or specific test subclass), you can switch back to the default mode via @SqlMergeMode(OVERRIDE). Consult the @SqlMergeMode annotation documentation section for examples and further details.