@Sql Failed SQL scripts: The configured DataSource [*] (named 'fooDS') is not the one associated with transaction manager [*] (named 'fooTM')

user_735284 0
Viewed 4662

Update 1 (scroll down)

The setup is as follows:

Our application database is constructed and used by two separate users:

  • SCHEMA - User that has authority to create and grant permissions on tables and
  • APP - User who is granted permissions (INSERT, UPDATE, DELETE, SELECT) (by SCHEMA) for above tables to be used.

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

I am running integration tests with a live Oracle database that contains both these users. on the class itself, I use the @SqlConfig(dataSource = "schemaDataSource", transactionManager = "transactionManagerSchema").

On the test method I place two @Sql that fail because in the SqlScriptsTestExecutionListener class, the transaction is not managing the same datasource. (hence the error message further below).

I've tried setting the datasource to the transaction manager manually as shown in my config class below, however some unknown process seems to override it every time. (My best guess is through the @DataJpaTest annotation but I don't know exactly which of the 11 Auto Configurations does it, as you can see I've already disabled a couple with no effect).

Test Class:

@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());
    }
}

DataSource and TM config:

@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;
    }
}

The full error that I couldn't fit in the title is:

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').

When there is a single DataSource, it appears the Spring auto-configuration model works fine, however, as soon as there are 2 or more, the assumptions break down and the programmer needs to manually fill in the sudden (plentiful) gaps in configuration required.

Am I missing some fundamental understanding surrounding DataSources and TransactionManagers?

Update 1

After some debugging, I have discovered the afterPropertiesSet() method is being called on the bean I created when the TransactionManager is retrieved for use with the @Sql script annotation. This causes whatever EntityManagerFactory it owns (i.e. JpaTransactionManager.entityManagerFactory) to set the datasource according to its configured EntityManagerFactoryInfo.getDataSource(). The EntityManagerFactory itself is being set as a result of the JpaTransactionManager.setBeanFactory method being called (as it implements BeanFactoryAware).

here is the spring code:

// 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()));
    }
}

I then tried creating my own EntityManagerFactory bean to attempt to inject it into my created transaction manager but this seems to be opening up Hibernate Specific classes and I wish to stay abstracted at the JPA level. As well as it being difficult to configure at first glance.

spring-data-jpa spring-boot spring-transactions spring java
1 Answers

Finally, a JPA only solution!

The Solution was to control the creation of the EntityManagerFactoryBeans using the provided spring EntityManagerFactoryBuilder component and inject the EntityManager into the test using the @PersistenceContext annotation.

@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. 
  }
}

Below is the configuration for both datasources. The application related DataSource beans all have @Primary in their definition to disambiguate any @Autowired dependencies. there are no Hibernate specific classes needed other than the Automatic hibernate config done through the @DataJpaTest class.

@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;
    }
}

Actual Test Class:

@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());
    }
}

noteworthy classes:

  • EntityManagerFactoryBuilder - I don't like factory factories, but this one served me well in creating the correct implementation of EntityManagerFactory without depending on any hibernate specific classes. may be injected with @Autowired. The builder bean itself is configured through the HibernateJpaAutoConfiguration class (extends JpaBaseConfiguration) (imported by @DataJpaTest).
  • JpaProperties - useful for maintaining application.properties config in the resulting entitymanagerfactories. enabled through the @EnableConfigurationProperties(JpaProperties.class) annotation above this config class.
  • @PersistenceContext(unitName = "...") - I can inject the correct EntityManager in my test class with this annotation.
user_7352840
Related