多数据源场景下测试时无法向内存H2表插入数据

为Spring应用新增第二个MySQL数据源后，功能正常，但验收测试失败。原本可用的testdata.sql脚本执行时抛出错误：

org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of class path resource [db/testdata.sql]: DELETE FROM exampletable; nested exception is org.h2.jdbc.JdbcSQLException: Table "EXAMPLETABLE" not found; SQL statement: DELETE FROM exampletable [42102-194]

1. 多数据源场景下，Spring的@Sql注解默认使用未指定@Qualifier的主DataSource执行脚本，但业务表exampletable是通过exampleEntityManagerFactory在exampleDataSource中创建的，两者不属于同一个数据源，导致脚本找不到表。
2. 实体类ExampleTableRecord缺少主键字段，JPA规范要求实体必须有主键，这可能导致Hibernate无法正确生成表结构。

方案1：指定@Sql脚本使用exampleDataSource执行 #

直接在测试类的@Sql注解中配置目标数据源，确保脚本在正确的数据源上执行：

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
// 指定脚本使用exampleDataSource执行
@Sql(value = "/db/testdata.sql", config = @SqlConfig(dataSource = "exampleDataSource"))
class ExampleControllerTests {

    @LocalServerPort
    private int port;

    @Test
    void givenValidRequest_whenCallingExampleEndpoint_returns200() {
        RestAssured.given()
                .contentType("application/json")
                .accept("application/json")
                .get("http://localhost:" + port + "/example/endpoint")
                .then().statusCode(200).body("status", equalTo("exampleField1"));
    }
}

方案2：将exampleDataSource设为主数据源 #

如果业务主要依赖exampleDataSource，可以将其标记为@Primary，让Spring默认所有操作都使用这个数据源，包括@Sql脚本执行：

修改ExampleDataSourceConfig配置 #

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "exampleEntityManagerFactory",
        transactionManagerRef = "exampleTransactionManager",
        basePackages = {"com.companyName.exampleapi.repository.example"}
)
public class ExampleDataSourceConfig {

    @Primary
    @Bean(name = "exampleTransactionManager")
    public PlatformTransactionManager exampleTransactionManager(
            @Qualifier("exampleEntityManagerFactory") EntityManagerFactory exampleEntityManagerFactory) {
        return new JpaTransactionManager(exampleEntityManagerFactory);
    }

    @Primary
    @Bean(name = "exampleEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean exampleEntityManagerFactory(
            EntityManagerFactoryBuilder exampleEntityManagerFactoryBuilder, @Qualifier("exampleDataSource") DataSource exampleDataSource) {

        return exampleEntityManagerFactoryBuilder
                .dataSource(exampleDataSource)
                .packages("com.companyname.exampleapi.model.entity.example")
                .persistenceUnit("example")
                .build();
    }

    @Primary
    @Bean(name = "exampleDataSource")
    public DataSource exampleDataSource(ExampleDataSourceProperties exampleDataSourceProperties) {

        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(exampleDataSourceProperties.getDriverClassName());
        dataSource.setUrl(exampleDataSourceProperties.getUrl());
        dataSource.setUsername(exampleDataSourceProperties.getUsername());
        dataSource.setPassword(exampleDataSourceProperties.getPassword());

        return dataSource;
    }
}

修复实体类主键问题 #

JPA实体必须包含主键，否则Hibernate无法正确生成表结构，修改ExampleTableRecord：

@Data
@NoArgsConstructor(force = true)
@AllArgsConstructor
@Builder
@Schema
@Entity
@Table(name="exampletable")
public class ExampleTableRecord {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id; // 添加主键字段
    
    private String exampleField1;
    private String exampleField2;
}

内容的提问来源于stack exchange，提问作者alaskanloops