Tables not getting created in multiple databases in spring boot application

Question

I am working on spring boot multi tenancy application. I have configured multi datasources as shown below :

application.properties

spring.multitenancy.datasource1.url=jdbc:mysql://localhost:3306/db1
spring.multitenancy.datasource1.username=root
spring.multitenancy.datasource1.password=****
spring.multitenancy.datasource1.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.hibernate.ddl-auto=update

spring.multitenancy.datasource2.url=jdbc:mysql://localhost:3306/db2
spring.multitenancy.datasource2.username=root
spring.multitenancy.datasource2.password=****
spring.multitenancy.datasource2.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.hibernate.ddl-auto=update

spring.multitenancy.datasource3.url=jdbc:mysql://localhost:3306/db3
spring.multitenancy.datasource3.username=root
spring.multitenancy.datasource3.password=****
spring.multitenancy.datasource3.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.hibernate.ddl-auto=update

DataSourceBasedMultiTenantConnectionProviderImpl.java

@Component
public class DataSourceBasedMultiTenantConnectionProviderImpl extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl {

    private static final long serialVersionUID = 8168907057647334460L;
    private static final String DEFAULT_TENANT_ID = "tenant_1";

    @Autowired
    private DataSource dataSource1;

    @Autowired
    private DataSource dataSource2;

    @Autowired
    private DataSource dataSource3;

    private Map<String, DataSource> map;

    @PostConstruct
    public void load() {
        map = new HashMap<>();
        map.put("tenant_1", dataSource1);
        map.put("tenant_2", dataSource2);
        map.put("tenant_3", dataSource3);
    }

    @Override
    protected DataSource selectAnyDataSource() {
        return map.get(DEFAULT_TENANT_ID);
    }

    @Override
    protected DataSource selectDataSource(String tenantIdentifier) {
        return map.get(tenantIdentifier);
    }
}

MultitenancyProperties.java

@ConfigurationProperties("spring.multitenancy")
public class MultitenancyProperties {

    @NestedConfigurationProperty
    private DataSourceProperties datasource1;

    @NestedConfigurationProperty
    private DataSourceProperties datasource2;

    @NestedConfigurationProperty
    private DataSourceProperties datasource3;

    public DataSourceProperties getDatasource1() {
        return datasource1;
    }

    public void setDatasource1(DataSourceProperties datasource1) {
        this.datasource1 = datasource1;
    }

    public DataSourceProperties getDatasource2() {
        return datasource2;
    }

    public void setDatasource2(DataSourceProperties datasource2) {
        this.datasource2 = datasource2;
    }

    public DataSourceProperties getDatasource3() {
        return datasource3;
    }

    public void setDatasource3(DataSourceProperties datasource3) {
        this.datasource3 = datasource3;
    }
}

MultiTenancyJpaConfiguration.java

@Configuration
@EnableConfigurationProperties(JpaProperties.class)
public class MultiTenancyJpaConfiguration {

    @Autowired
    private DataSource dataSource;

    @Autowired
    private JpaProperties jpaProperties;

    @Autowired
    private MultiTenantConnectionProvider multiTenantConnectionProvider;

    @Autowired
    private CurrentTenantIdentifierResolver currentTenantIdentifierResolver;

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(EntityManagerFactoryBuilder builder) {
        Map<String, Object> hibernateProps = new LinkedHashMap<>();
        hibernateProps.putAll(jpaProperties.getHibernateProperties(dataSource));

        hibernateProps.put(Environment.MULTI_TENANT, MultiTenancyStrategy.DATABASE);
        hibernateProps.put(Environment.MULTI_TENANT_CONNECTION_PROVIDER, multiTenantConnectionProvider);
        hibernateProps.put(Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, currentTenantIdentifierResolver);
        hibernateProps.put(Environment.DIALECT, "org.hibernate.dialect.MySQLDialect");

        return builder.dataSource(dataSource).packages(HotelEntity.class.getPackage().getName()).properties(hibernateProps).jta(false).build();
    }
}

Application launcher

@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
@EnableConfigurationProperties(MultitenancyProperties.class)
public class Application {

    public static void main(String[] args) {
        ApplicationContext ctx = SpringApplication.run(Application.class, args);
    }
}

When I run the boot application, all tables are created in only first data source.
1) How can I create tables in all data sources on application startup?
2) How to see connections opened/closed for each of the data sources?
3) Is there a better way of configuring multi tenancy application using spring boot for better performance?

selectAnyDataSource always return same one maybe you can add there some logic? — HRgiger, Mar 30 '17 at 15:40
selectAnyDataSource code is for selecting the data source based on tenant id. The data is getting populated in all the data sources based on tenantId. But the tables did not get created in all data sources on app startup. I had to manually copy tables to datasource2 and datasource3. — JavaDeveloper, Mar 30 '17 at 15:44
Did you try to run your program in debug mode to see what is initiallized and what not? — AntiTiming, Apr 03 '17 at 13:10
Anyways, you may want to re-think letting hibernate create and update your schema automatically as discussed here: https://stackoverflow.com/questions/221379 and use e.g. https://flywaydb.org/ for creating and updating the schema at startup — AntiTiming, Apr 03 '17 at 13:24

score 3 · Answer 1 · answered Jun 16 '19 at 13:10

3

Try adding the following properties:

spring.jpa.generate-ddl=true

answered Jun 16 '19 at 13:10

W.Stave

39
1

Amazing. Worked for me in an application with multiple datasources. Interestingly enough applications with only one datasource worked without this. – pancakesmaplesyrup Jan 30 '23 at 02:02

score 2 · Answer 2 · answered Apr 07 '17 at 14:50

As @Alex said, you need differnt EntityManagers, TransactionManager and Datasources. Here is how I would do it

@Configuration
@EnableJpaRepositories(
    entityManagerFactoryRef = "dataSource1EntityManagerFactory",
    transactionManagerRef = "dataSource1TransactionManager",
    basePackageClasses = dataSource1Repository.class)
public class DataSource1Config extends SqlConfig{// Put all common code in base class SqlConfig. If not remove it

    @Bean
    @Primary
    public DataSource dataSource1() {
        //create dataSource using MultitenancyProperties::getDataSource1
    }

    @Primary
    @Bean(name = "dataSource1TransactionManager")
    PlatformTransactionManager dataSource1TransactionManager(EntityManagerFactory dataSource1EntityManagerFactory) {
        JpaTransactionManager txManager = new JpaTransactionManager();
        txManager.setEntityManagerFactory(dataSource1EntityManagerFactory);
        return txManager;
    }

    @Primary
    @Bean(name = "dataSource1EntityManagerFactory")
    LocalContainerEntityManagerFactoryBean dataSource1EntityManagerFactory() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource1());
        em.setPackagesToScan(dataSource1Repository.class.getPackage().getName(), dataSource1ModelClass.class.getPackage().getName());
        em.setPersistenceUnitName("dataSource1Db");

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        vendorAdapter.setGenerateDdl(false);
        em.setJpaVendorAdapter(vendorAdapter);
        return em;
    }
}

You can create two other classes like this. Remember to use @Primary on only one instace of datasource, transactionmanger and entitymanager(doesn't matter which one). Another word of caution, make sure Repository classes are in different packages for all three databases.

score 0 · Answer 3 · answered Apr 06 '17 at 11:09

You need 2 different persistence factories, not one, each should produce different EntityManagers for different datasources. Also each entity mapping should be marked to been used only with one entity manager. See full solution here:

http://www.baeldung.com/spring-data-jpa-multiple-databases

Tables not getting created in multiple databases in spring boot application

3 Answers3

Linked