Initialize multiple databases in Spring

Question

I'm using AbstractRoutingDatasource to route between databases in runtime. In real condition on the informix database everything works just fine.

For tests I created a spring profile to use in memory H2 databases. After running the spring app whith the test profile I checked the local databases using the h2 console. No schema was created.

I tried using schema.sql in recources and hibernates:

generate-ddl: true
hibernate:
  ddl-auto: create-drop

hibernate throws

java.lang.IllegalStateException: Cannot determine target DataSource for lookup key [null]

As I understand hibernate ist trying to generate only the "routed" datasource. Since no DatabaseContextHolder is set (null) this fails.

So both ways fail.

Is there a way to initialize all databases with (the same) schema?

I list my configuration below.

bootstrap.yml:

spring:
  profiles: acceptanceTest
  config:
    name: standalone
  cloud:
    config:
      enabled: false
      discovery:
        enabled: false
  jpa:
    database-platform: org.hibernate.dialect.H2Dialect
    generate-ddl: true
    hibernate:
      ddl-auto: create-drop
    properties:
      hibernate:
        show_sql: false
        use_sql_comments: false
        format_sql: false
  h2:
    console:
      enabled: true
      path: /h2
datasource:
  mc:
    driver-class-name: 'org.h2.Driver'
    url: 'jdbc:h2:mem:test-mc-db;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE'
    username: sa
    password: ''
    platform: h2
    initialize: true
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      maximum-pool-size: 10
      minimum-idle: 0
      idle-timeout: 60000
      pool-name: MarkHikariPool
  cw:
    driver-class-name: 'org.h2.Driver'
    url: 'jdbc:h2:mem:test-cw-db'
    username: sa
    password: ''
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      maximum-pool-size: 10
      minimum-idle: 0
      idle-timeout: 60000
      pool-name: MarkHikariPool

MainApp.java:

@ComponentScan({ "de.md.mark" })
@EnableDiscoveryClient
@SpringBootApplication(
    exclude =
        {
            DataSourceAutoConfiguration.class,
            DataSourceTransactionManagerAutoConfiguration.class,
            HibernateJpaAutoConfiguration.class
        }
)
public class MainApp
{
    public static void main(String[] args)
    {

DataSourceConfiguration.java:

@Configuration
@EnableJpaRepositories(
    basePackageClasses = { MarkTypeRepository.class, MarkRepository.class }, entityManagerFactoryRef = "markEntityManager",
    transactionManagerRef = "markTransactionManager"
)
@EnableTransactionManagement
public class DataSourceConfiguration
{
    @Autowired(required = false)
    private PersistenceUnitManager persistenceUnitManager;

    @Bean
    @ConfigurationProperties("app.jpa")
    @Primary
    public JpaProperties jpaProperties()
    {
        return new JpaProperties();
    }

    @Bean
    @ConfigurationProperties(prefix = "datasource.mc")
    public DataSource mcDataSource()
    {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "datasource.cw")
    public DataSource cwDataSource()
    {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @Primary
    public DataSource dataSource()
    {
        DataSourceRouter router = new DataSourceRouter();

        final HashMap<Object, Object> map = new HashMap<>(DatabaseEnvironment.values().length);
        map.put(DatabaseEnvironment.MC, mcDataSource());
        map.put(DatabaseEnvironment.CW, cwDataSource());
        router.setTargetDataSources(map);
        return router;
    }

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean markEntityManager(final JpaProperties jpaProperties)
    {
        EntityManagerFactoryBuilder builder = createEntityManagerFactoryBuilder(jpaProperties);

        return builder.dataSource(dataSource()).packages(MarkTypeEntity.class).persistenceUnit("markEntityManager").build();
    }

    @Bean
    @Primary
    public JpaTransactionManager markTransactionManager(@Qualifier("markEntityManager") final EntityManagerFactory factory)
    {
        return new JpaTransactionManager(factory);
    }

    private EntityManagerFactoryBuilder createEntityManagerFactoryBuilder(JpaProperties jpaProperties)
    {
        JpaVendorAdapter jpaVendorAdapter = createJpaVendorAdapter(jpaProperties);
        return new EntityManagerFactoryBuilder(jpaVendorAdapter, jpaProperties.getProperties(), this.persistenceUnitManager);
    }

    private JpaVendorAdapter createJpaVendorAdapter(JpaProperties jpaProperties)
    {
        AbstractJpaVendorAdapter adapter = new HibernateJpaVendorAdapter();
        adapter.setShowSql(jpaProperties.isShowSql());
        adapter.setDatabase(jpaProperties.getDatabase());
        adapter.setDatabasePlatform(jpaProperties.getDatabasePlatform());
        adapter.setGenerateDdl(jpaProperties.isGenerateDdl());
        return adapter;
    }
}

Answer 1

After a lot research in the last two days I found a solution to initialize all datasources. I created a method to create and initialize the local H2 databases using the SchemaExport.class of hibernate. Since I only need the local db's for testing, I call the method for each DatabaseEnvirement in a @Before cucumber method.

public void createDatabase(DatabaseEnvironment environment)
throws Exception
{
    // load hibernate configuration from hibernate.cfg.xml in classpath
    Configuration configuration = new Configuration().configure();
    MetadataSources metadata =
    new MetadataSources(new StandardServiceRegistryBuilder().applySettings(configuration.getProperties()).build());

    ClassPathScanningCandidateComponentProvider scanner = new ClassPathScanningCandidateComponentProvider(true);
    scanner.addIncludeFilter(new AnnotationTypeFilter(Entity.class));

    for (BeanDefinition def : scanner.findCandidateComponents(MarkEntity.class.getPackage().getName()))
    {
        metadata.addAnnotatedClass(Class.forName(def.getBeanClassName()));
    }

    Connection connection = DriverManager.getConnection("jdbc:h2:mem:test-" + environment.name().toLowerCase(), "sa", "");

    SchemaExport export = new SchemaExport((MetadataImplementor) metadata.buildMetadata(), connection);
    export.create(true, true);
}


@Before
public void beforeTest()
    throws Exception
{
    // initialise databases
    for (DatabaseEnvironment env : DatabaseEnvironment.values())
    {
        createDatabase(env);
    }
}

and this is how my hibernate.cfg.xml looks like:

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>

        <property name="dialect">org.hibernate.dialect.H2Dialect</property>

        <property name="show_sql">true</property>

        <property name="hbm2ddl.auto">create-drop</property>

    </session-factory>
</hibernate-configuration>