Add tenant dynamically in multitenant database application using Spring MVC and Hibernate

Question

i am developing web application which uses multitenant database configuration.

I want to add tenant dynamically.

I added master controller to create master schema which hold tenant record created dynamically.

but problem is when i request to create tenant it went to MultitenantConnectionProvider i created database there but in database i want to scan package com.appointment.schedular.model.tenant and create table in ne database as well.

Source code

MasterDatabaseConfig.java

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        basePackages = "com.appointment.schedular.dao.master",
        entityManagerFactoryRef = "masterEntityManager",
        transactionManagerRef = "masterTransactionManager"
)
@PropertySource("classpath:application.properties")
public class MasterDatabaseConfig {

   @Autowired
   private Environment springEnvironment;

   @Bean(name="masterDataSource")
   public DataSource masterDataSource() {
      DriverManagerDataSource dataSource = new DriverManagerDataSource();
      dataSource.setDriverClassName(springEnvironment.getProperty("master.datasource.classname"));
      dataSource.setUrl(springEnvironment.getProperty("master.datasource.url") + "?createDatabaseIfNotExist=true");
      dataSource.setUsername(springEnvironment.getProperty("master.datasource.user"));
      dataSource.setPassword(springEnvironment.getProperty("master.datasource.password"));
      return dataSource;
   }

   @Bean(name = "masterEntityManager")
   @Primary
   public LocalContainerEntityManagerFactoryBean masterEntityManagerFactory() {
      LocalContainerEntityManagerFactoryBean entityManagerFactoryBean 
              = new LocalContainerEntityManagerFactoryBean();
      entityManagerFactoryBean.setDataSource(masterDataSource());
      entityManagerFactoryBean.setPersistenceProviderClass(HibernatePersistenceProvider.class);
      JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
      entityManagerFactoryBean.setJpaVendorAdapter(vendorAdapter);
      entityManagerFactoryBean.setPackagesToScan(new String[]{"com.appointment.schedular.model.master"});
      entityManagerFactoryBean.setJpaProperties(getHibernateProperties());
      entityManagerFactoryBean.setPersistenceUnitName("master");
      return entityManagerFactoryBean;
   }

   private Properties getHibernateProperties() {
      Properties properties = new Properties();
      properties.put("hibernate.dialect", springEnvironment.getProperty("hibernate.dialect","org.hibernate.dialect.MySQLDialect"));
      properties.put("hibernate.show_sql", springEnvironment.getProperty("hibernate.show_sql", "true"));
      properties.put("hibernate.format_sql", springEnvironment.getProperty("hibernate.format_sql", "true"));
      properties.put("hibernate.hbm2ddl.auto", springEnvironment.getProperty("hibernate.hbm2ddl.auto", "update"));
      return properties;
   }

   @Bean(name = "masterTransactionManager")
   public JpaTransactionManager transactionManager(EntityManagerFactory masterEntityManager) {
      JpaTransactionManager transactionManager = new JpaTransactionManager();
      transactionManager.setEntityManagerFactory(masterEntityManager);
      return transactionManager;
   }
}

TenantDatabaseConfig.java

@Configuration
@EnableTransactionManagement
@ComponentScan("com.appointment.schedular.tenant")
@EnableJpaRepositories(
        entityManagerFactoryRef = "tenantEntityManager",
        transactionManagerRef = "tenantTransactionManager",
        basePackages = {"com.appointment.schedular.dao.tenant"})
@PropertySource("classpath:application.properties")
public class TenantDatabaseConfig {

   @Autowired
   private Environment springEnvironment;

   @Bean
   public JpaVendorAdapter jpaVendorAdapter() {
      return new HibernateJpaVendorAdapter();
   }

   @Bean(name = "tenantDataSource")
   public DataSource tenantDataSource() {
      DriverManagerDataSource dataSource = new DriverManagerDataSource();
      dataSource.setDriverClassName(springEnvironment.getProperty("tenant.datasource.classname"));
      dataSource.setUrl(springEnvironment.getProperty("tenant.datasource.url")+"xy" + "?createDatabaseIfNotExist=true");
      dataSource.setUsername(springEnvironment.getProperty("tenant.datasource.user"));
      dataSource.setPassword(springEnvironment.getProperty("tenant.datasource.password"));
      return dataSource;
   }

   @Bean(name = "tenantEntityManager")
   public LocalContainerEntityManagerFactoryBean entityManagerFactory(
                                       MultiTenantConnectionProvider connectionProvider,
                                       CurrentTenantIdentifierResolver tenantResolver) {
      LocalContainerEntityManagerFactoryBean emfBean = new LocalContainerEntityManagerFactoryBean();

      emfBean.setDataSource(tenantDataSource());
      emfBean.setPackagesToScan("com.appointment.schedular.model.tenant");
      emfBean.setJpaVendorAdapter(jpaVendorAdapter());
      Map<String, Object> properties = new HashMap<>();
      properties.put(org.hibernate.cfg.Environment.MULTI_TENANT, MultiTenancyStrategy.SCHEMA);
      properties.put(org.hibernate.cfg.Environment.MULTI_TENANT_CONNECTION_PROVIDER, connectionProvider);
      properties.put(org.hibernate.cfg.Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, tenantResolver);
      properties.put("hibernate.ejb.naming_strategy", "org.hibernate.cfg.ImprovedNamingStrategy");
        properties.put("hibernate.dialect", springEnvironment.getProperty("hibernate.dialect"
              , "org.hibernate.dialect.MySQLDialect"));
      properties.put("hibernate.show_sql", springEnvironment.getProperty("hibernate.show_sql"
              , "true"));
      properties.put("hibernate.format_sql", springEnvironment.getProperty("hibernate.format_sql"
              , "true"));
      properties.put("hibernate.hbm2ddl.auto", springEnvironment.getProperty("hibernate.hbm2ddl.auto"
              , "update"));
      emfBean.setJpaPropertyMap(properties);
      emfBean.setPersistenceUnitName("master");
      return emfBean;
   }

   @Bean(name = "tenantTransactionManager")
   public JpaTransactionManager transactionManager(EntityManagerFactory tenantEntityManager) {
      JpaTransactionManager transactionManager = new JpaTransactionManager();
      transactionManager.setEntityManagerFactory(tenantEntityManager);
      return transactionManager;
   }
}

MultitenantConnectionProviderImpl.java

@SuppressWarnings("serial")
@Component
@PropertySource("classpath:application.properties")
public class MultiTenantConnectionProviderImpl extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl implements ApplicationListener<ContextRefreshedEvent> {

   @Autowired
   private Environment springEnvironment;

   @Autowired
   private TenantDao tenantDao;

   @Autowired
   @Qualifier("tenantDataSource")
   DataSource masterDataSource;

   /*@Autowired
   @Qualifier("tenantEntityManager")
   EntityManager*/

   private final Map<String, DataSource> map = new HashMap<>();

   @Override
   public void onApplicationEvent(ContextRefreshedEvent contextRefreshedEvent) {
      init();
   }

   private void init() {
      List<Tenant> tenants = tenantDao.findAll();
      for (Tenant tenant : tenants) {
         DataSource genDatasource = constructDataSource(tenant.getTenantKey());
         map.put(tenant.getTenantKey(), genDatasource);

       /*
         LocalContainerEntityManagerFactoryBean entityManagerFactoryBean 
         = new LocalContainerEntityManagerFactoryBean();
         entityManagerFactoryBean.setDataSource(genDatasource);
         entityManagerFactoryBean.setPersistenceProviderClass(HibernatePersistenceProvider.class);
         JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
         entityManagerFactoryBean.setJpaVendorAdapter(vendorAdapter);
         entityManagerFactoryBean.setPackagesToScan(new String[]{"com.appointment.schedular.model.tenant"});

         Map<String, Object> properties = new HashMap<>();
          properties.put("hibernate.ejb.naming_strategy", "org.hibernate.cfg.ImprovedNamingStrategy");
            properties.put("hibernate.dialect", springEnvironment.getProperty("hibernate.dialect"
                  , "org.hibernate.dialect.MySQLDialect"));
          properties.put("hibernate.show_sql", springEnvironment.getProperty("hibernate.show_sql"
                  , "true"));
          properties.put("hibernate.format_sql", springEnvironment.getProperty("hibernate.format_sql"
                  , "true"));
          properties.put("hibernate.hbm2ddl.auto", springEnvironment.getProperty("hibernate.hbm2ddl.auto"
                  , "update"));

         entityManagerFactoryBean.setJpaPropertyMap(properties);
        */ 
      }
   }

   private DataSource constructDataSource(String dbName) {
      DriverManagerDataSource dataSource = new DriverManagerDataSource();
      dataSource.setDriverClassName(springEnvironment.getProperty("tenant.datasource.classname"));
      dataSource.setUrl(springEnvironment.getProperty("tenant.datasource.url") + dbName+ "?createDatabaseIfNotExist=true");
      dataSource.setUsername(springEnvironment.getProperty("tenant.datasource.user"));
      dataSource.setPassword(springEnvironment.getProperty("tenant.datasource.password"));
      try {
         dataSource.getConnection().createStatement().execute("CREATE DATABASE IF NOT EXISTS " + dbName);
      } catch (Exception ex) {
         System.out.println(ex);
      }
      return dataSource;
   }

   @Override
   protected DataSource selectAnyDataSource() {
      return masterDataSource;
   }

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

   public void addTenant(String tenantKey) {
      map.put(tenantKey, constructDataSource(tenantKey));
   }
}

TenantController.java

@Controller
@RequestMapping("/tenant")
public class TenantController {

    @Autowired
    TenantDao tenantRepo;

    @Autowired
    MultiTenantConnectionProviderImpl multiTenantConnectionProviderImpl;


    @SuppressWarnings("rawtypes")
    @CrossOrigin
    @RequestMapping(value = "/", 
                    method = RequestMethod.POST, 
                    consumes = MediaType.APPLICATION_JSON_VALUE)
    public @ResponseBody String registerTenant(@RequestBody Map map) throws JsonProcessingException {

        ObjectMapper mapper = new ObjectMapper();
        Tenant tenant = mapper.convertValue(map, Tenant.class);

        String tenantKey = tenant.getName().replaceAll("[^a-zA-Z]+", "").toLowerCase().trim();
          Optional<Tenant> previouslyStored = tenantRepo.findByTenantKey(tenantKey);
          String response="Sorry your company name ("+tenant.getName()+")"+" is already taken";
          if (!previouslyStored.isPresent()) {
             tenant.setTenantKey(tenantKey);
             tenantRepo.save(tenant);
             multiTenantConnectionProviderImpl.addTenant(tenantKey);
             response = "Successfully registered, your key is " + tenantKey;
             return response;
          }
        return new ObjectMapper().writeValueAsString(response);
    }
}

Answer 1

Replace your MultitenantConnectionProviderImpl.java with this code. Use inject service for creating your tables using configuration class.

@SuppressWarnings("serial")
@Component
@PropertySource("classpath:application.properties")
public class MultiTenantConnectionProviderImpl extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl implements ApplicationListener<ContextRefreshedEvent>,ServiceRegistryAwareService {

   @Autowired
   private Environment springEnvironment;

   @Autowired
   private TenantDao tenantDao;

   @Autowired
   @Qualifier("dataSource1")
   DataSource masterDataSource;

   @Autowired
   MultiTenantConnectionProvider connectionProvider;

   @Autowired
   CurrentTenantIdentifierResolver tenantResolver;

   @Autowired
   TenantDatabaseConfig tenantDatabaseConfig;


   private final Map<String, DataSource> map = new HashMap<>();

   @Override
   public void onApplicationEvent(ContextRefreshedEvent contextRefreshedEvent) {
      init();
   }

   private void init() {
      List<Tenant> tenants = tenantDao.findAll();
      for (Tenant tenant : tenants) {
         map.put(tenant.getTenantKey(), constructDataSource(tenant.getTenantKey()));
      }
   }

   private DataSource constructDataSource(String dbName) {
      DriverManagerDataSource dataSource = new DriverManagerDataSource();
      dataSource.setDriverClassName(springEnvironment.getProperty("tenant.datasource.classname"));
      dataSource.setUrl(springEnvironment.getProperty("tenant.datasource.url") + dbName+"?createDatabaseIfNotExist=true");
      dataSource.setUsername(springEnvironment.getProperty("tenant.datasource.user"));
      dataSource.setPassword(springEnvironment.getProperty("tenant.datasource.password"));

      entityManagerFactory(dataSource,connectionProvider, tenantResolver);


      return dataSource;
   }

   public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource,
           MultiTenantConnectionProvider connectionProvider,
           CurrentTenantIdentifierResolver tenantResolver) {
      LocalContainerEntityManagerFactoryBean emfBean = new LocalContainerEntityManagerFactoryBean();
      emfBean.setDataSource(dataSource);
      emfBean.setPackagesToScan("com.appointment.schedular.model.tenant");
      emfBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
      emfBean.setPersistenceProviderClass(HibernatePersistenceProvider.class);
      Map<String, Object> properties = new HashMap<>();
      properties.put(org.hibernate.cfg.Environment.MULTI_TENANT, MultiTenancyStrategy.DATABASE);
      properties.put(org.hibernate.cfg.Environment.MULTI_TENANT_CONNECTION_PROVIDER, connectionProvider);
      properties.put(org.hibernate.cfg.Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, tenantResolver);
      properties.put("hibernate.ejb.naming_strategy", "org.hibernate.cfg.ImprovedNamingStrategy");
        properties.put("hibernate.dialect", springEnvironment.getProperty("hibernate.dialect"
              , "org.hibernate.dialect.MySQLDialect"));
      properties.put("hibernate.show_sql", springEnvironment.getProperty("hibernate.show_sql"
              , "true"));
      properties.put("hibernate.format_sql", springEnvironment.getProperty("hibernate.format_sql"
              , "true"));
      properties.put("hibernate.hbm2ddl.auto", springEnvironment.getProperty("hibernate.hbm2ddl.auto"
              , "update"));
      emfBean.setJpaPropertyMap(properties);
      emfBean.setPersistenceUnitName(dataSource.toString());
      emfBean.afterPropertiesSet();
      //emfBean.setEntityManagerFactoryInterface((EntityMana)emfBean);
      //emfBean.setBeanName("srgsrohtak");
      return emfBean;
   }

   public JpaTransactionManager transactionManager(EntityManagerFactory tenantEntityManager) {
          JpaTransactionManager transactionManager = new JpaTransactionManager();
          transactionManager.setEntityManagerFactory(tenantEntityManager);
          transactionManager.afterPropertiesSet();
          return transactionManager;
   }

   @Override
   public void injectServices(ServiceRegistryImplementor serviceRegistry) {
       Map lSettings = serviceRegistry.getService(ConfigurationService.class).getSettings();
       DataSource localDs =  (DataSource) lSettings.get("hibernate.connection.datasource");
       masterDataSource = localDs;
   }

   @Override
   protected DataSource selectAnyDataSource() {
      return masterDataSource;
   }

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

   public void addTenant(String tenantKey) {
      map.put(tenantKey, constructDataSource(tenantKey));
   }
}