Java – use spring MVC and hibernate to dynamically add tenants in multi tenant database applications

I am developing a web application that uses a multi - tenant database configuration

I want to add tenants dynamically

I added a master controller to create a master schema that contains dynamically created tenant records

But the problem is that when I request to create a tenant, it goes to the multitenantconnectionprovider where I create the database, but in the database I want to scan the package com appointment. schedular. model. Tenant and create tables in the ne database

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","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","org.hibernate.dialect.MysqLDialect"));
          properties.put("hibernate.show_sql","true"));
          properties.put("hibernate.format_sql","true"));
          properties.put("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);
    }
}

Solution

Replace your multitenantconnectionproviderimpl. With this code java. Create tables using injection services and configuration classes

@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,MultiTenancyStrategy.DATABASE);
      properties.put(org.hibernate.cfg.Environment.MULTI_TENANT_CONNECTION_PROVIDER,"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));
   }
}