Skip to content

Spring 测试中的 SQL 脚本执行详解 🚀

概述

在进行集成测试时,我们经常需要操作数据库——创建表结构、插入测试数据、清理数据等。Spring TestContext Framework 提供了强大的 SQL 脚本执行功能,让我们能够轻松地在测试中管理数据库状态。

TIP

想象一下,如果没有这些工具,每次测试前后都要手动准备和清理数据库,那将是多么繁琐的工作!Spring 的 SQL 脚本执行功能就是为了解决这个痛点而生的。

为什么需要 SQL 脚本执行? 🤔

在实际开发中,我们经常遇到这些场景:

  • 测试隔离性:每个测试都需要干净的数据环境
  • 数据准备:测试前需要插入特定的测试数据
  • 环境清理:测试后需要清理产生的数据
  • 数据库结构变更:测试不同的数据库模式

编程式执行 SQL 脚本

使用 ResourceDatabasePopulator

这是最灵活的方式,适合需要精确控制脚本执行的场景:

kotlin
@SpringBootTest
class DatabaseIntegrationTest {
    
    @Autowired
    private lateinit var dataSource: DataSource
    
    @Test
    fun testWithCustomScripts() {
        // 创建数据库填充器
        val populator = ResourceDatabasePopulator().apply {
            // 添加多个脚本文件
            addScripts(
                ClassPathResource("schema/test-schema.sql"),
                ClassPathResource("data/test-data.sql")
            )
            // 自定义分隔符(默认是分号)
            setSeparator("@@") 
            // 设置字符编码
            setSqlScriptEncoding("UTF-8")
            // 设置是否忽略失败的语句
            setContinueOnError(false)
        }
        
        // 执行脚本
        populator.execute(dataSource) 
        
        // 执行测试逻辑
        // 验证数据是否正确插入
        val jdbcTemplate = JdbcTemplate(dataSource)
        val userCount = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM users", 
            Int::class.java
        )
        
        assertThat(userCount).isGreaterThan(0)
    }
}
java
@SpringBootTest
public class DatabaseIntegrationTest {
    
    @Autowired
    private DataSource dataSource;
    
    @Test
    void testWithCustomScripts() {
        // 创建数据库填充器
        ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
        populator.addScripts(
            new ClassPathResource("schema/test-schema.sql"),
            new ClassPathResource("data/test-data.sql")
        );
        populator.setSeparator("@@"); 
        populator.setSqlScriptEncoding("UTF-8");
        populator.setContinueOnError(false);
        
        // 执行脚本
        populator.execute(dataSource); 
        
        // 执行测试逻辑
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        Integer userCount = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM users", 
            Integer.class
        );
        
        assertThat(userCount).isGreaterThan(0);
    }
}

NOTE

ResourceDatabasePopulator 内部使用 ScriptUtils 来解析和执行 SQL 脚本,提供了丰富的配置选项来处理不同格式的 SQL 文件。

声明式执行 SQL 脚本 - @Sql 注解 ⭐

基本用法

@Sql 注解是最常用的方式,它让 SQL 脚本的管理变得非常简单:

kotlin
@SpringBootTest
@Sql("/schema/test-schema.sql") 
class UserServiceTest {
    
    @Autowired
    private lateinit var userService: UserService
    
    @Test
    fun testEmptyDatabase() {
        // 测试空数据库的情况
        val users = userService.findAllUsers()
        assertThat(users).isEmpty()
    }
    
    @Test
    @Sql("/data/user-test-data.sql") 
    fun testWithTestData() {
        // 这个测试会执行类级别的 schema 脚本 + 方法级别的数据脚本
        val users = userService.findAllUsers()
        assertThat(users).hasSize(3)
        assertThat(users[0].name).isEqualTo("张三")
    }
}

脚本路径解析规则

Spring 提供了灵活的路径解析机制:

路径解析规则

  • 相对路径"schema.sql" → 相对于测试类所在包的 classpath 资源
  • 绝对路径"/org/example/schema.sql" → 绝对 classpath 资源
  • URL 路径"classpath:schema.sql""file:/path/to/schema.sql"
  • 属性占位符"${test.sql.path}/schema.sql" (Spring Framework 6.2+)

默认脚本检测

当没有指定脚本路径时,Spring 会自动查找默认脚本:

kotlin
@SpringBootTest
class UserRepositoryTest {
    
    @Test
    @Sql
    fun testUserCreation() {
        // 自动查找:classpath:com/example/UserRepositoryTest.testUserCreation.sql
    }
}

TIP

默认脚本命名规则:

  • 类级别:classpath:包路径/类名.sql
  • 方法级别:classpath:包路径/类名.方法名.sql

高级功能

多个 @Sql 注解

当需要不同的配置或执行阶段时,可以使用多个 @Sql 注解:

kotlin
@Test
@Sql(
    scripts = ["/schema/test-schema.sql"],
    config = SqlConfig(commentPrefix = "`") 
)
@Sql("/data/test-user-data.sql")
fun testComplexScenario() {
    // 第一个脚本使用自定义注释前缀
    // 第二个脚本使用默认配置
}

脚本执行阶段

控制脚本在测试的不同阶段执行:

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 
)
fun testWithCleanup() {
    // 测试需要数据提交到数据库外部事务
    // 测试后自动清理数据
}

类级别的执行阶段

Spring Framework 6.1+ 支持类级别的执行阶段:

kotlin
@SpringBootTest
@Sql(
    scripts = ["/schema/test-schema.sql"],
    executionPhase = BEFORE_TEST_CLASS 
)
@Sql(
    scripts = ["/cleanup/drop-tables.sql"],
    executionPhase = AFTER_TEST_CLASS 
)
class DatabaseTestSuite {
    
    @Test
    fun test1() {
        // 使用已创建的 schema
    }
    
    @Test
    fun test2() {
        // 使用相同的 schema
    }
}

事务管理 💡

事务模式配置

@Sql 与 Spring 的事务管理完美集成:

kotlin
@SpringBootTest
@Transactional
class TransactionalSqlTest {
    
    @Autowired
    private lateinit var jdbcTemplate: JdbcTemplate
    
    @Test
    @Sql("/test-data.sql")
    fun testTransactionalBehavior() {
        // 验证数据库状态
        val userCount = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM users",
            Int::class.java
        )
        assertThat(userCount).isEqualTo(2)
        
        // 执行业务逻辑...
        // 测试结束后,所有更改都会自动回滚
    }
}

IMPORTANT

在事务性测试中,SQL 脚本的更改和测试方法中的更改都会在测试结束后自动回滚,确保测试之间的隔离性。

自定义事务配置

kotlin
@Test
@Sql(
    scripts = ["/test-data.sql"],
    config = SqlConfig(
        dataSource = "customDataSource", 
        transactionManager = "customTransactionManager", 
        transactionMode = ISOLATED 
    )
)
fun testWithCustomTransaction() {
    // 使用自定义的数据源和事务管理器
}

配置合并 - @SqlMergeMode

控制类级别和方法级别的 @Sql 配置如何合并:

kotlin
@SpringBootTest
@Sql("/schema/base-schema.sql")
@SqlMergeMode(MERGE) 
class MergedSqlTest {
    
    @Test
    @Sql("/data/user-data.sql")
    fun testWithMergedScripts() {
        // 执行:base-schema.sql + user-data.sql
    }
    
    @Test
    @Sql("/data/product-data.sql")
    @SqlMergeMode(OVERRIDE) 
    fun testWithOverriddenScripts() {
        // 只执行:product-data.sql(忽略类级别的脚本)
    }
}

实际应用场景

场景1:用户服务集成测试

完整的用户服务测试示例
kotlin
@SpringBootTest
@TestPropertySource(properties = ["spring.jpa.hibernate.ddl-auto=none"])
class UserServiceIntegrationTest {
    
    @Autowired
    private lateinit var userService: UserService
    
    @Autowired
    private lateinit var jdbcTemplate: JdbcTemplate
    
    companion object {
        @JvmStatic
        @BeforeAll
        @Sql(scripts = ["/schema/user-schema.sql"], executionPhase = BEFORE_TEST_CLASS)
        fun setupSchema() {
            // 在所有测试前创建表结构
        }
    }
    
    @Test
    @Sql("/data/single-user.sql")
    fun `should find user by id`() {
        val user = userService.findById(1L)
        
        assertThat(user).isNotNull
        assertThat(user?.name).isEqualTo("张三")
        assertThat(user?.email).isEqualTo("[email protected]")
    }
    
    @Test
    @Sql("/data/multiple-users.sql")
    fun `should find all active users`() {
        val activeUsers = userService.findActiveUsers()
        
        assertThat(activeUsers).hasSize(2)
        assertThat(activeUsers.map { it.name })
            .containsExactly("李四", "王五")
    }
    
    @Test
    @Sql(scripts = ["/data/user-with-orders.sql"])
    @Sql(
        scripts = ["/cleanup/delete-orders.sql"],
        executionPhase = AFTER_TEST_METHOD
    )
    fun `should delete user and cascade orders`() {
        // 验证初始数据
        val orderCount = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM orders WHERE user_id = 1",
            Int::class.java
        )
        assertThat(orderCount).isGreaterThan(0)
        
        // 执行删除操作
        userService.deleteUser(1L)
        
        // 验证级联删除
        val remainingOrders = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM orders WHERE user_id = 1",
            Int::class.java
        )
        assertThat(remainingOrders).isEqualTo(0)
    }
}

场景2:多数据源测试

kotlin
@SpringBootTest
class MultiDataSourceTest {
    
    @Test
    @Sql(
        scripts = ["/master-data.sql"],
        config = SqlConfig(dataSource = "masterDataSource")
    )
    @Sql(
        scripts = ["/slave-data.sql"],
        config = SqlConfig(dataSource = "slaveDataSource")
    )
    fun testDataReplication() {
        // 测试主从数据库的数据一致性
    }
}

调试和日志 🔍

启用 SQL 脚本日志

application-test.yml 中配置:

yaml
logging:
  level:
    org.springframework.test.context.jdbc: DEBUG  # 显示执行的脚本
    org.springframework.jdbc.datasource.init: DEBUG  # 显示执行的 SQL 语句

常见问题排查

常见陷阱

  1. 脚本路径错误:确保脚本文件在正确的 classpath 位置
  2. 字符编码问题:使用 SqlConfig(encoding = "UTF-8") 指定编码
  3. 事务隔离:理解不同事务模式的影响
  4. 脚本执行顺序:注意多个 @Sql 注解的执行顺序

最佳实践 ✅

  1. 脚本组织

    • 将 schema 脚本和数据脚本分开
    • 使用有意义的文件名
    • 按功能模块组织目录结构
  2. 性能优化

    • 对于大量数据,考虑使用 @Sql(executionPhase = BEFORE_TEST_CLASS)
    • 避免在每个测试方法中重复执行相同的 schema 脚本
  3. 测试隔离

    • 使用事务回滚确保测试隔离
    • 必要时使用 AFTER_TEST_METHOD 清理数据
  4. 可维护性

    • 使用 @SqlMergeMode 合理组织脚本继承
    • 为复杂场景提供清晰的注释

通过掌握这些 SQL 脚本执行技术,你可以轻松地管理测试数据,编写更加可靠和可维护的集成测试! 🎉