java - @Sql 失败的 SQL 脚本 : The configured DataSource [*] (named 'fooDS' ) is not the one associated with transaction manager [*] (named 'fooTM' )

Question

更新 1(向下滚动)

---

设置如下:

我们的应用程序数据库由两个独立的用户构建和使用:

• SCHEMA - 有权创建和授予表权限的用户
• APP - 被授予权限(INSERT、UPDATE、DELETE、SELECT)(通过 SCHEMA)以使用上述表格的用户。

This enables us to lock any schema changes until needed so no profound changes happen through the app user.

---

我正在使用包含这两个用户的实时 Oracle 数据库运行集成测试。在类本身上，我使用了 @SqlConfig(dataSource = "schemaDataSource", transactionManager = "transactionManagerSchema")。

在测试方法中，我放置了两个失败的 @Sql，因为在 SqlScriptsTestExecutionListener 类中，事务未管理相同的数据源。 (因此在下面进一步显示错误消息)。

我已经尝试手动将数据源设置为事务管理器，如下面的配置类所示，但是似乎每次都有一些未知进程覆盖它。 (我最好的猜测是通过 @DataJpaTest 注释，但我不知道到底是 11 Auto Configurations 中的哪一个做了它，正如你所看到的，我已经禁用了几个但没有效果)。

测试类:

@RunWith(SpringRunner.class)
@DataJpaTest(excludeAutoConfiguration = {TestDatabaseAutoConfiguration.class, DataSourceAutoConfiguration.class})
@FlywayTest
@SqlConfig(dataSource = TestDataSourceConfig.SCHEMA_DATA_SOURCE, transactionManager = "transactionManagerSchema")
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.NONE, classes = {TestDataSourceConfig.class, TestFlywayConfig.class})
@EntityScan(basePackageClasses = BaseEnum.class)
public class NotificationTypeEnumTest {

    @Autowired
    private EntityManager em;

    @Test
    @Sql(statements = {"INSERT INTO MYAPP_ENUM (ENUM_ID, \"TYPE\", \"VALUE\") VALUES (MYAPP_ENUM_ID_SEQ.nextval, '" + NotificationTypeEnum.DTYPE + "', 'foo')"}, executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)
    @Sql(statements = {"DELETE FROM MYAPP_ENUM"}, executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)
    public void canFetchNotificationTypeEnum() throws Exception {
        TypedQuery<NotificationTypeEnum> query = em.createQuery("select a from NotificationTypeEnum a", NotificationTypeEnum.class);
        NotificationTypeEnum result = query.getSingleResult();
        assertEquals("foo", result.getValue());
        assertEquals(NotificationTypeEnum.DTYPE, result.getConfigType());
    }
}

数据源和 TM 配置:

@Slf4j @Configuration @EnableTransactionManagement
public class TestDataSourceConfig {
    public static final String SCHEMA_DATA_SOURCE = "schemaDataSource";
    public static final String SCHEMA_TRANSACTION_MANAGER = "schemaTransactionManager";

    /*Main Datasource and supporting beans*/

    @Bean @Primary @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource() { return new DriverManagerDataSource(); }

    @Bean @Primary @Autowired
    public PlatformTransactionManager transactionManager(EntityManagerFactory emf) { return new JpaTransactionManager(emf); }

    @Bean(name = SCHEMA_DATA_SOURCE) @ConfigurationProperties(prefix = "myapp.datasource.test_schema")
    public DataSource schemaDataSource() { return new DriverManagerDataSource(); }

    @Bean(name = SCHEMA_TRANSACTION_MANAGER) @Autowired
    public PlatformTransactionManager transactionManagerSchema(@Qualifier(SCHEMA_DATA_SOURCE) DataSource dataSource) {
        JpaTransactionManager jpaTransactionManager = new JpaTransactionManager();
        jpaTransactionManager.setDataSource(dataSource);
        return jpaTransactionManager;
    }
}

我无法放入标题的完整错误是:

java.lang.IllegalStateException: Failed to execute SQL scripts for test context
...
SOME LONG STACK TRACE
...
the configured DataSource [org.springframework.jdbc.datasource.DriverManagerDataSource] (named 'schemaDataSource') is not the one associated with transaction manager [org.springframework.orm.jpa.JpaTransactionManager] (named 'transactionManagerSchema').

---

当只有一个 DataSource 时，Spring 自动配置模型似乎工作正常，但是，一旦有 2 个或更多，假设就会失效，程序员需要手动填充在所需的配置中突然出现(大量)差距。

我是否缺少对数据源和事务管理器的一些基本理解？

---

更新1

经过一些调试，我发现在检索 TransactionManager 以与 @Sql 脚本注释一起使用时，我创建的 bean 正在调用 afterPropertiesSet() 方法.这会导致它拥有的任何 EntityManagerFactory(即 JpaTransactionManager.entityManagerFactory)根据其配置的 EntityManagerFactoryInfo.getDataSource() 设置数据源。 EntityManagerFactory 本身是调用 JpaTransactionManager.setBeanFactory 方法的结果(因为它实现了 BeanFactoryAware)。

这是 Spring 代码:

// JpaTransactionManager.java
@Override
public void setBeanFactory(BeanFactory beanFactory) throws BeansException {
    if (getEntityManagerFactory() == null) {
        if (!(beanFactory instanceof ListableBeanFactory)) {
            throw new IllegalStateException("Cannot retrieve EntityManagerFactory by persistence unit name " +
                    "in a non-listable BeanFactory: " + beanFactory);
        }
        ListableBeanFactory lbf = (ListableBeanFactory) beanFactory;
        setEntityManagerFactory(EntityManagerFactoryUtils.findEntityManagerFactory(lbf, getPersistenceUnitName()));
    }
}

然后我尝试创建自己的 EntityManagerFactory bean 以尝试将其注入(inject)到我创建的事务管理器中，但这似乎打开了 Hibernate 特定类，我希望在 JPA 级别保持抽象。以及乍一看很难配置。

Answer 1

最后，一个仅 JPA 的解决方案!

解决方案是使用提供的 spring EntityManagerFactoryBuilder 组件控制 EntityManagerFactoryBeans 的创建，并使用 @PersistenceContext 将 EntityManager 注入(inject)到测试中> 注释。

@SqlConfig(dataSource = TestDataSourceConfig.SCHEMA_DATA_SOURCE, transactionManager = SCHEMA_TRANSACTION_MANAGER, transactionMode = SqlConfig.TransactionMode.ISOLATED)
...
public class MyJUnitTest {
  @PersistenceContext(unitName = "pu")
  private EntityManager em;
  ...

  @Test
  @Sql(statements = {"SOME SQL USING THE PRIVILEGED SCHEMA CONNECTION"}, ...)
  public void myTest() {
    em.createQuery("...").getResultList() // uses the APP database user. 
  }
}

下面是两个数据源的配置。与应用程序相关的 DataSource bean 在其定义中都具有 @Primary 以消除任何 @Autowired 依赖项的歧义。除了通过 @DataJpaTest 类完成的自动 hibernate 配置之外，不需要 Hibernate 特定类。

@Configuration
@EnableTransactionManagement
@EnableConfigurationProperties(JpaProperties.class)
public class TestDataSourceConfig {

    public static final String SCHEMA_DATA_SOURCE = "schemaDS";
    public static final String SCHEMA_TRANSACTION_MANAGER = "schemaTM";
    public static final String SCHEMA_EMF = "schemaEMF";

    /*Main Datasource and supporting beans*/

    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource() {
        return new DriverManagerDataSource();
    }

    @Bean @Primary @Autowired
    public PlatformTransactionManager transactionManager(EntityManagerFactory emf) { return new JpaTransactionManager(emf); }

    @Bean @Primary
    public LocalContainerEntityManagerFactoryBean emfBean(
            EntityManagerFactoryBuilder entityManagerFactoryBuilder,
            DataSource datasource,
            JpaProperties jpaProperties) {
        return entityManagerFactoryBuilder
                .dataSource(datasource)
                .jta(false)
                .packages(CourseOffering.class)
                .persistenceUnit("pu")
                .properties(jpaProperties.getProperties())
                .build();
    }

    @Bean(name = SCHEMA_EMF)
    public LocalContainerEntityManagerFactoryBean emfSchemaBean(
            EntityManagerFactoryBuilder entityManagerFactoryBuilder,
            @Qualifier(SCHEMA_DATA_SOURCE) DataSource schemaDataSource,
            JpaProperties jpaProperties) {
        return entityManagerFactoryBuilder
                .dataSource(schemaDataSource)
                .jta(false)
                .packages(CourseOffering.class)
                .persistenceUnit("spu")
                .properties(jpaProperties.getProperties())
                .build();
    }

    @Bean(name = SCHEMA_DATA_SOURCE)
    @ConfigurationProperties(prefix = "myapp.datasource.test_schema")
    public DataSource schemaDataSource() { return new DriverManagerDataSource(); }

    @Bean(name = SCHEMA_TRANSACTION_MANAGER)
    public PlatformTransactionManager transactionManagerSchema(
            @Qualifier(SCHEMA_EMF) EntityManagerFactory emfSchemaBean) {
        JpaTransactionManager jpaTransactionManager = new JpaTransactionManager();
        jpaTransactionManager.setEntityManagerFactory(emfSchemaBean);
        return jpaTransactionManager;
    }
}

实际测试类:

@RunWith(SpringRunner.class) // required for all spring tests
@DataJpaTest(excludeAutoConfiguration = {TestDatabaseAutoConfiguration.class, DataSourceAutoConfiguration.class}) // this stops the default data source and database being configured.
@SqlConfig(dataSource = TestDataSourceConfig.SCHEMA_DATA_SOURCE, transactionManager = SCHEMA_TRANSACTION_MANAGER, transactionMode = SqlConfig.TransactionMode.ISOLATED) // make sure the @Sql statements are run using the SCHEMA datasource and txManager in an isolated way so as not to cause problems when running test methods requiring these statements to be run.
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.NONE, classes = {TestDataSourceConfig.class})
@TestExecutionListeners({ 
    SqlScriptsTestExecutionListener.class, // enables the @Sql script annotations to work.
    SpringBootDependencyInjectionTestExecutionListener.class, // injects spring components into the test (i.e. the EntityManager)
    TransactionalTestExecutionListener.class}) // I have this here even though the @Transactional annotations don't exist yet as I plan on using them in further tests.
public class NotificationTypeEnumTest {

    @PersistenceContext(unitName = "pu") // required to inject the correct EntityManager
    private EntityManager em;

    // these statements are 
    @Test
    @Sql(statements = {"INSERT INTO MYAPP_ENUM (ENUM_ID, \"TYPE\", \"VALUE\") VALUES (MYAPP_ENUM_ID_SEQ.nextval, '" + NotificationTypeEnum.DTYPE + "', 'foo')"}, executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)
    @Sql(statements = {"DELETE FROM MYAPP_ENUM"}, executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)
    public void canFetchNotificationTypeEnum() throws Exception {
        TypedQuery<NotificationTypeEnum> query = em.createQuery("select a from NotificationTypeEnum a", NotificationTypeEnum.class); // notification type is just a subclass of the BaseEnum type
        NotificationTypeEnum result = query.getSingleResult();
        assertEquals("foo", result.getValue());
        assertEquals(NotificationTypeEnum.DTYPE, result.getConfigType());
    }
}

---

值得注意的类(class):

• EntityManagerFactoryBuilder - 我不喜欢工厂工厂，但是这个工厂在创建 EntityManagerFactory 的正确实现方面非常有用，而不依赖于任何 hibernate 特定类。可以用 @Autowired 注入(inject)。构建器 bean 本身是通过 HibernateJpaAutoConfiguration 类(扩展 JpaBaseConfiguration)(由 @DataJpaTest 导入)配置的。
• JpaProperties - 有助于在生成的 entitymanagerfactories 中维护 application.properties 配置。通过此配置类上方的 @EnableConfigurationProperties(JpaProperties.class) 注释启用。
• @PersistenceContext(unitName = "...") - 我可以使用此注释在我的测试类中注入(inject)正确的 EntityManager。