Spring AbstractRoutingDataSource + Hibernate-Hbm2ddlSchemaUpdate runs only by default DB

Question

I use SpringBoot with Hibernate as a persistence provider. For my application, I needed to dynamically choose between two databases.

(For simplicity sake, domain : localhost:8080 ---> hem1 DB domain : 127.0.0.1:8080 ---> hem2 DB ) 

Below is the implementation of AbstractRoutingDB

  public class MyRoutingDataSource extends AbstractRoutingDataSource{ @Override protected Object determineCurrentLookupKey() { /* * this is derived from threadlocal set by filter for each web * request */ return SessionUtil.getDB(); } } 

The following is the database configuration:

  package com.hemant.basic.dataSource; import java.beans.PropertyVetoException; import java.util.HashMap; import java.util.Map; import javax.naming.ConfigurationException; import javax.sql.DataSource; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import com.mchange.v2.c3p0.ComboPooledDataSource; @Configuration public class DBConfig { @Bean(name = "dataSource") public DataSource dataSource() throws PropertyVetoException, ConfigurationException { MyRoutingDataSource routingDB = new MyRoutingDataSource(); Map<Object, Object> targetDataSources = datasourceList(); // hem1 is the default target DB routingDB.setDefaultTargetDataSource(targetDataSources.get(1)); routingDB.setTargetDataSources(targetDataSources); routingDB.afterPropertiesSet(); return routingDB; } private Map<Object, Object> datasourceList() throws PropertyVetoException, ConfigurationException { final Map<Object, Object> datasources = new HashMap<Object, Object>(); ComboPooledDataSource datasource = null; for (int id = 1; id <= 2; id++) { datasource = getDatasource(id); datasources.put(id, datasource); } return datasources; } private ComboPooledDataSource getDatasource(int id) throws PropertyVetoException, ConfigurationException { ComboPooledDataSource datasource = new ComboPooledDataSource(); // set the connection pool properties datasource.setJdbcUrl("jdbc:postgresql://localhost/hem" + id); datasource.setUser("hemant"); datasource.setPassword(""); datasource.setDriverClass("org.postgresql.Driver"); datasource.setMaxPoolSize(30); datasource.setInitialPoolSize(10); datasource.setMinPoolSize(10); return datasource; } } 

Also, the following parameter is specified in the application.properties settings, so automatic scheme updating is enabled.

 # Hibernate ddl auto (create, create-drop, update) spring.jpa.hibernate.ddl-auto = update 

Problem: when I run the application, the hbm2ddl schema update is performed only on hem1 (defaultTargetDb), but not in other target databases

The following is part of the startup logs.

 [main] org.hibernate.tool.hbm2ddl.SchemaUpdate : HHH000228: Running hbm2ddl schema update [main] org.hibernate.tool.hbm2ddl.SchemaUpdate : HHH000102: Fetching database metadata [main] org.hibernate.tool.hbm2ddl.SchemaUpdate : HHH000396: Updating schema [main] java.sql.DatabaseMetaData : HHH000262: Table not found: users [main] java.sql.DatabaseMetaData : HHH000262: Table not found: users [main] java.sql.DatabaseMetaData : HHH000262: Table not found: users [main] org.hibernate.tool.hbm2ddl.SchemaUpdate : HHH000232: Schema update complete`enter code here` 

THIS IS PERFORMED ONLY FOR 1 DB.

** Later, when I execute the URL for the rest, say

GET localhost: 8080 / users - the results are successfully loaded for the updated DB.1.

But when GET 127.0.0.1:8080/users is available, as the schema is not updated / not created, the IT result is SQL Exception **

How can we guarantee that the "hbm2ddl schema update" is performed on all AbstractRoutingDataSource target databases

Answer 1

As far as I know, you cannot set up exporting a schema in a multi-tenant environment. When you define an environment with multiple tenants, by default it will connect to the default database only to get a connection. That is why you create in only one database. He will not access all the bases / schemes that will be created because they do not know each other.

Save the SQL database database file and execute it each time you create a new tenant. I think you can use Spring PersistenceContext to create it for you when required.