Multiple data source and schema creation in Spring Boot

Question

I'm using Spring Boot. I finally managed to setup two data sources, but now I'm facing another issue.

1. with two data sources in place spring.jpa.hibernate.ddl-auto=create seems to stop working in my spring boot application, only spring.jpa.generate-ddl=true do the job now

2. I can not manage to select the auto-creation strategy for each of the data sources. I would prefer to create the schema for data source one, and just use the created schema in second DB with data source two.

Any body have idea how to resolve any of these issues? Note I don't want to completely throw away the auto-config if possible. I don't even know yet, if hibernate is able to just initialize schema in one persistence unit.

application.properties

spring.datasource-internal.url=jdbc:hsqldb:mem:testdb
spring.datasource-internal.username=sa
spring.datasource-internal.password=sa
spring.datasource-internal.driver-class-name=org.hsqldb.jdbcDriver
spring.datasource-internal.jpa.database-platform=org.hibernate.dialect.HSQLDialect

spring.datasource-external.url=jdbc:hsqldb:mem:testexternal
spring.datasource-external.username=sa
spring.datasource-external.password=sa
spring.datasource-external.driver-class-name=org.hsqldb.jdbcDriver
spring.datasource-external.jpa.database-platform=org.hibernate.dialect.HSQLDialect

flyway.enabled=false
spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true
spring.jpa.generate-ddl=true

DBInternalConfig

    @Configuration
    @EnableTransactionManagement
    @EnableJpaRepositories(basePackages = "cz.data.internal",
            entityManagerFactoryRef = "internalEntityManagerFactory",
            transactionManagerRef = "internalTransactionManager")
    public class DBConfigInternal {


        public static final String INTERNAL = "internal";

        @Bean(name = "internalDataSource")
        @Primary
        @ConfigurationProperties(prefix = "spring.datasource-internal")
        public DataSource internalDataSource() {
            return DataSourceBuilder.create().build();
        }

        @Bean(name = "internalEntityManagerFactory")
        @Primary
        public LocalContainerEntityManagerFactoryBean internalEntityManagerFactory(
                EntityManagerFactoryBuilder builder) {
            return builder
                    .dataSource(internalDataSource())
                    .packages("cz.data.internal.entity")
                    .persistenceUnit(INTERNAL)
                    .build();
        }

        @Bean(name = "internalTransactionManager")
        @Primary
        public PlatformTransactionManager internalTransactionManager() {
            JpaTransactionManager jpaTransactionManager = new JpaTransactionManager();
            jpaTransactionManager.setDataSource(internalDataSource());
            jpaTransactionManager.setPersistenceUnitName(INTERNAL);
            return jpaTransactionManager;
        }
    }

DBExternalConfig

    @Configuration
    @EnableTransactionManagement
    @EnableJpaRepositories(
            basePackages = "cz.data.external",
            entityManagerFactoryRef = "externalEntityManagerFactory",
            transactionManagerRef = "externalTransactionManager")
    public class DBConfigExternal {


        public static final String EXTERNAL = "external";

        @Bean(name = "externalDataSource")
        @ConfigurationProperties(prefix = "spring.datasource-external")
        public DataSource externalDataSource() {
            return DataSourceBuilder.create().build();
        }

        @Bean(name = "externalEntityManagerFactory")
        public LocalContainerEntityManagerFactoryBean externalEntityManagerFactory(
                EntityManagerFactoryBuilder builder) {
            return builder
                    .dataSource(externalDataSource())
                    .packages("cz.data.external.entity")
                    .persistenceUnit(EXTERNAL)
                    .build();
        }

        @Bean(name = "externalTransactionManager")
        public PlatformTransactionManager externalTransactionManager() {
            JpaTransactionManager jpaTransactionManager = new JpaTransactionManager();
            jpaTransactionManager.setDataSource(externalDataSource());
            jpaTransactionManager.setPersistenceUnitName(EXTERNAL);
            return jpaTransactionManager;
        }
    }

M.W.

Answer 1

spring.jpa.hibernate.ddl-auto=create has stopped working, not because you have two DataSources, but because your application's creating its own LocalContainerEntityManagerFactoryBeans. This has the effect of disabling the auto-configuration of a LocalContainerEntityManagerFactoryBean so you now have to configure it yourself.

You can configure the two entity managers to have different schema generation behaviour like this (the first's doing update, the second's doing create):

@Bean(name = "externalEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean externalEntityManagerFactory(
        EntityManagerFactoryBuilder builder) {
    Map<String, Object> properties = new HashMap<String, Object>();
    properties.put("hibernate.hbm2ddl.auto", "update");
    return builder
            .dataSource(externalDataSource())
            .packages("cz.data.external.entity")
            .persistenceUnit(EXTERNAL)
            .properties(properties)
            .build();
}

@Bean(name = "internalEntityManagerFactory")
@Primary
public LocalContainerEntityManagerFactoryBean internalEntityManagerFactory(
        EntityManagerFactoryBuilder builder) {
    Map<String, Object> properties = new HashMap<String, Object>();
    properties.put("hibernate.hbm2ddl.auto", "create");
    return builder
            .dataSource(internalDataSource())
            .packages("cz.data.internal.entity")
            .persistenceUnit(INTERNAL)
            .properties(properties)
            .build();
}