Spring Boot Two Datasource configuration for H2 DB failed to create tables and insert data

Question

I am looking to configure the two DS in my Spring Boot application and have followed link - https://medium.com/@joeclever/using-multiple-datasources-with-spring-boot-and-spring-data-6430b00c02e7. We've created application.yml file.

spring:
  datasource:
    jdbcUrl: jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
    driverClassName: org.h2.Driver
    username: sa
    password: 
  jpa:
    hibernate:
      ddl-auto: create-drop
    show-sql: true
  h2:
    console:
      enabled: true
      path: /h2-console


my:
  datasource:
    jdbcUrl: jdbc:h2:mem:test2db;DB_CLOSE_DELAY=-1
    driverClassName: org.h2.Driver
    username: sa
    password:
  jpa:
    hibernate:
      ddl-auto: create-drop
    show-sql: true
  h2:
    console:
      enabled: true
      path: /h2-console

Here is the code

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "localKEntityManagerFactory",
        transactionManagerRef = "localKTransactionManager",
        basePackages = "com.mastercard.merchantbinding.repository.K")
@EntityScan(basePackages = ENTITY_PACKAGE)
public class LocalKH2DataSourceConfig {
    @Bean(name = "localKdataSource")
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "localKEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(EntityManagerFactoryBuilder builder,
            @Qualifier("localKdataSource") DataSource dataSource) {
        return builder.dataSource(dataSource).packages(ENTITY_PACKAGE).persistenceUnit("LOCAL_K").build();
    }

    @Bean(name = "localKTransactionManager")
    public PlatformTransactionManager transactionManager(
            @Qualifier("localKEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

Another

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "localSEntityManagerFactory",
        transactionManagerRef = "localSTransactionManager",
        basePackages = "com.mastercard.merchantbinding.repository.S")
@EntityScan(basePackages = ENTITY_PACKAGE)
public class LocalSH2DataSourceConfig {
    @Primary
    @Bean(name = "localSdataSource")
    @ConfigurationProperties(prefix = "my.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "localSEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(EntityManagerFactoryBuilder builder,
            @Qualifier("localSdataSource") DataSource dataSource) {
        return builder.dataSource(dataSource).packages(ENTITY_PACKAGE).persistenceUnit("LOCAL_S").build();
    }

    @Primary
    @Bean(name = "localSTransactionManager")
    public PlatformTransactionManager transactionManager(
            @Qualifier("localSEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

Error -

Table TRAN_DATA not found; SQL statement: --insert into ABC ------- [42S02-60]  

Any pointers?

Sulfa

Answer 1

Your configuration is correct, however the reason why auto-ddl is not working has to do with the fact that you are manually creating the LocalContainerEntityManagerFactoryBean, during the creation of your datasources.

You can mitigate this by instructing each bean to perform the DDL operation manually like:

@Primary
@Bean(name = "entityManagerFactory")
public LocalContainerEntityManagerFactoryBean entityManagerFactory(EntityManagerFactoryBuilder builder, Qualifier("dataSource") DataSource dataSource) {
    Map<String, Object> properties = new HashMap<>();
    properties.put("hibernate.hbm2ddl.auto", "update");
    return builder.dataSource(dataSource)
        .packages("com.foo.springtest.users.models")
        .persistenceUnit("user")
        .properties(properties)
        .build();
}

Notice the following two lines that instruct hibernate to run DDL:

Map<String, Object> properties = new HashMap<>();
properties.put("hibernate.hbm2ddl.auto", "update");

Pyuria