I am working on a POC Java application that will support Multi Tenancy. I run my POC with a JHipster generator and OAUTH2 authentication when spring boots. Each tenant has their own SCHEMA, but tenants and OAUTH2 tables are publicly available. JHipster uses hibernation and spring data to connect to the database. In my example, I use Mysql as a database.
I want to achieve a solution using a single data pool and a single connection pool. HikariCP is used as the JHipster connection pool. In MultiTenantConnectionProvider, I want to modify SCHEMA in the same way as describing a Hibernate document (see Example 16.3.)
http://docs.jboss.org/hibernate/orm/4.2/devguide/en-US/html/ch16.html#d5e4866
When hibernate call getConnection (String tenantIdentifier), I want to set the correct SCHEMA in the MYSQL database. My implementation uses the Mysql command to modify the "USE sample_tenant_identifier" schema. I have users with the names "user" and "admin". Each of these users has its own SCHEMA. The problem that I have is very strange. An example of the entire SELECT operation uses a "user" schema, but INSERT or UPDATE uses SCHEMA "admin". As a result, "admin" sees the data in the "user" SCHEMA, but INSERT data in the "admin" SCHEMA.
package com.mycompany.myapp.tenance.hibernate;
import org.hibernate.HibernateException; import org.hibernate.engine.config.spi.ConfigurationService; import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider; import org.hibernate.service.spi.ServiceRegistryAwareService; import org.hibernate.service.spi.ServiceRegistryImplementor; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import javax.sql.DataSource; import java.sql.Connection; import java.sql.SQLException; import java.util.Map; public class SchemaMultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider, ServiceRegistryAwareService { private final Logger log = LoggerFactory.getLogger(SchemaMultiTenantConnectionProviderImpl.class); DataSource dataSource; @Override public Connection getAnyConnection() throws SQLException { return this.dataSource.getConnection(); } @Override public void releaseAnyConnection(Connection connection) throws SQLException { try { connection.createStatement().execute("USE jhipster;"); } catch (SQLException e) { throw new HibernateException("Could not alter JDBC connection to specified schema [public]", e); } connection.close(); } @Override public Connection getConnection(String tenantIdentifier) throws SQLException { log.debug("Tenatd is:"+tenantIdentifier); final Connection connection = getAnyConnection(); try { connection.createStatement().execute("USE " + tenantIdentifier + ";");
I create a second working example where I create a new DataSource for each tenant and save it in Map. This example works fine, but a parallel card is not what I want for sure.
package com.mycompany.myapp.tenancy.hibernate; import com.zaxxer.hikari.HikariConfig; import com.zaxxer.hikari.HikariDataSource; import org.hibernate.engine.config.spi.ConfigurationService; import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider; import org.hibernate.service.spi.ServiceRegistryAwareService; import org.hibernate.service.spi.ServiceRegistryImplementor; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import javax.sql.DataSource; import java.sql.Connection; import java.sql.SQLException; import java.util.HashMap; import java.util.Map; /** * Created by AdamS on 2015-03-12. */ public class MyMultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider, ServiceRegistryAwareService { private final Logger log = LoggerFactory.getLogger(MyMultiTenantConnectionProviderImpl.class); DataSource dataSource; private Map<String, DataSource> dataSourceMap = new HashMap<String, DataSource>(); public MyMultiTenantConnectionProviderImpl() { getSource("main"); } @Override public void releaseAnyConnection(Connection connection) throws SQLException { connection.close(); } @Override public Connection getAnyConnection() throws SQLException { //return this.dataSource.getConnection(); log.info("get eny connection return main"); return getSource("jhipster").getConnection(); } @Override public Connection getConnection(String tenantIdentifier) throws SQLException { log.info("Tenatd is:" + tenantIdentifier); return getSource(tenantIdentifier).getConnection(); } @Override public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException { log.info("releaseConnection " + tenantIdentifier); connection.close(); } @Override public boolean supportsAggressiveRelease() { return false; } @Override public boolean isUnwrappableAs(Class unwrapType) { return false; } @Override public <T> T unwrap(Class<T> unwrapType) { return null; } @Override public void injectServices(ServiceRegistryImplementor serviceRegistry) { Map lSettings = serviceRegistry.getService(ConfigurationService.class).getSettings(); DataSource localDs = (DataSource) lSettings.get("hibernate.connection.datasource"); dataSource = localDs; } public DataSource getSource(String tentant) { if(dataSourceMap.containsKey(tentant)){ return dataSourceMap.get(tentant); } else { DataSource ds = dataSource(tentant); dataSourceMap.put(tentant,ds); return ds; } } public DataSource dataSource(String tentant) { log.info("Create Datasource "+tentant); HikariConfig config = new HikariConfig(); config.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource"); config.addDataSourceProperty("url", "jdbc:mysql://localhost:3306/"+tentant); config.addDataSourceProperty("user", "root"); config.addDataSourceProperty("password", ""); //MySQL optimizations, see https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration config.addDataSourceProperty("cachePrepStmts", true); config.addDataSourceProperty("prepStmtCacheSize", "250"); config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048"); config.addDataSourceProperty("useServerPrepStmts", "true"); return new HikariDataSource(config); } }
My configuration class:
package com.mycompany.myapp.config; ... @Configuration @EnableJpaRepositories(basePackages = {"com.mycompany.myapp.repository"},entityManagerFactoryRef = "entityManagerFactory",transactionManagerRef = "transactionManager") @EnableJpaAuditing(auditorAwareRef = "springSecurityAuditorAware") //@EnableTransactionManagement() //@EnableAutoConfiguration(exclude = HibernateJpaAutoConfiguration.class) public class DatabaseConfiguration implements EnvironmentAware { private final Logger log = LoggerFactory.getLogger(DatabaseConfiguration.class); private RelaxedPropertyResolver propertyResolver; private Environment env; private DataSource dataSource; @Autowired(required = false) private MetricRegistry metricRegistry; @Override public void setEnvironment(Environment env) { this.env = env; this.propertyResolver = new RelaxedPropertyResolver(env, "spring.datasource."); } @Bean(destroyMethod = "shutdown") @ConditionalOnMissingClass(name = "com.mycompany.myapp.config.HerokuDatabaseConfiguration") @Profile("!" + Constants.SPRING_PROFILE_CLOUD) public DataSource dataSource() { log.debug("Configuring Datasource"); if (propertyResolver.getProperty("url") == null && propertyResolver.getProperty("databaseName") == null) { log.error("Your database connection pool configuration is incorrect! The application" + "cannot start. Please check your Spring profile, current profiles are: {}", Arrays.toString(env.getActiveProfiles())); throw new ApplicationContextException("Database connection pool is not configured correctly"); } HikariConfig config = new HikariConfig(); config.setDataSourceClassName(propertyResolver.getProperty("dataSourceClassName")); if (propertyResolver.getProperty("url") == null || "".equals(propertyResolver.getProperty("url"))) { config.addDataSourceProperty("databaseName", propertyResolver.getProperty("databaseName")); config.addDataSourceProperty("serverName", propertyResolver.getProperty("serverName")); } else { config.addDataSourceProperty("url", propertyResolver.getProperty("url")); } config.addDataSourceProperty("user", propertyResolver.getProperty("username")); config.addDataSourceProperty("password", propertyResolver.getProperty("password")); //MySQL optimizations, see https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration if ("com.mysql.jdbc.jdbc2.optional.MysqlDataSource".equals(propertyResolver.getProperty("dataSourceClassName"))) { config.addDataSourceProperty("cachePrepStmts", propertyResolver.getProperty("cachePrepStmts", "true")); config.addDataSourceProperty("prepStmtCacheSize", propertyResolver.getProperty("prepStmtCacheSize", "250")); config.addDataSourceProperty("prepStmtCacheSqlLimit", propertyResolver.getProperty("prepStmtCacheSqlLimit", "2048")); config.addDataSourceProperty("useServerPrepStmts", propertyResolver.getProperty("useServerPrepStmts", "true")); } if (metricRegistry != null) { config.setMetricRegistry(metricRegistry); } dataSource = new HikariDataSource(config); return dataSource; } @Bean public SpringLiquibase liquibase(DataSource dataSource) { SpringLiquibase liquibase = new SpringLiquibase(); liquibase.setDataSource(dataSource); liquibase.setChangeLog("classpath:config/liquibase/master.xml"); liquibase.setContexts("development, production"); if (env.acceptsProfiles(Constants.SPRING_PROFILE_FAST)) { if ("org.h2.jdbcx.JdbcDataSource".equals(propertyResolver.getProperty("dataSourceClassName"))) { liquibase.setShouldRun(true); log.warn("Using '{}' profile with H2 database in memory is not optimal, you should consider switching to" + " MySQL or Postgresql to avoid rebuilding your database upon each start.", Constants.SPRING_PROFILE_FAST); } else { liquibase.setShouldRun(false); } } else { log.debug("Configuring Liquibase"); } return liquibase; } @Bean public MultiTenantSpringLiquibase liquibaseMt(DataSource dataSource) throws SQLException { MultiTenantSpringLiquibase multiTenantSpringLiquibase = new MultiTenantSpringLiquibase(); multiTenantSpringLiquibase.setDataSource(dataSource); Statement stmt = null; stmt = dataSource.getConnection().createStatement(); ResultSet rs = stmt.executeQuery("SELECT tu.tentantId FROM t_user tu WHERE tu.tentantId IS NOT NULL"); ArrayList<String> schemas = new ArrayList<>(); while(rs.next()) { String schemaName = rs.getString("tentantId"); dataSource.getConnection().createStatement().executeUpdate("CREATE DATABASE IF NOT EXISTS "+schemaName); schemas.add(schemaName); } multiTenantSpringLiquibase.setSchemas(schemas); multiTenantSpringLiquibase.setChangeLog("classpath:config/liquibase/mt_master.xml"); multiTenantSpringLiquibase.setContexts("development, production"); if (env.acceptsProfiles(Constants.SPRING_PROFILE_FAST)) { if ("org.h2.jdbcx.JdbcDataSource".equals(propertyResolver.getProperty("dataSourceClassName"))) { multiTenantSpringLiquibase.setShouldRun(true); log.warn("Using '{}' profile with H2 database in memory is not optimal, you should consider switching to" + " MySQL or Postgresql to avoid rebuilding your database upon each start.", Constants.SPRING_PROFILE_FAST); } else { multiTenantSpringLiquibase.setShouldRun(false); } } else { log.debug("Configuring MultiTenantSpringLiquibase"); } return multiTenantSpringLiquibase; } @Bean public Hibernate4Module hibernate4Module() { return new Hibernate4Module(); } }
And EntityManagerConfiguration:
package com.mycompany.myapp.config; .... /** * Created by AdamS on 2015-03-31. */ @Configuration @EnableTransactionManagement public class EntityManagerConfiguration { @Autowired private DataSource dataSource; @Autowired private JpaVendorAdapter jpaVendorAdapter; @Bean(name = "entityManagerFactory") //@DependsOn("transactionManager") public LocalContainerEntityManagerFactoryBean entityManagerFactory() throws Throwable { HashMap<String, Object> properties = new HashMap<String, Object>(); //properties.put("hibernate.transaction.jta.platform", AtomikosJtaPlatform.class.getName()); //properties.put("javax.persistence.transactionType", "JTA"); properties.put("hibernate.cache.region.factory_class", "org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory"); properties.put("hibernate.cache.use_second_level_cache", "false"); properties.put("hibernate.cache.use_query_cache", "false"); properties.put("hibernate.generate_statistics", "true"); properties.put("hibernate.tenant_identifier_resolver", "com.mycompany.myapp.tenancy.hibernate.MyCurrentTenantIdentifierResolver"); /* MANY DATASOURCES. WORKING SOLUTION */ //properties.put("hibernate.multi_tenant_connection_provider", "com.mycompany.myapp.tenancy.hibernate.MyMultiTenantConnectionProviderImpl"); /*SCHEMA CONFIG THAT IS NOT WORKING*/ properties.put("hibernate.multi_tenant_connection_provider", "com.mycompany.myapp.tenancy.hibernate.SchemaMultiTenantConnectionProviderImpl"); properties.put("hibernate.multiTenancy", "SCHEMA"); LocalContainerEntityManagerFactoryBean entityManager = new LocalContainerEntityManagerFactoryBean(); entityManager.setDataSource(dataSource); entityManager.setJpaVendorAdapter(jpaVendorAdapter); //entityManager.setPackagesToScan("com.mycompany.myapp.domain"); entityManager.setPackagesToScan(new String[] { "com.mycompany.myapp.domain","com.mycompany.myapp.tenancy.domain" }); entityManager.setPersistenceUnitName("persistenceUnit"); entityManager.setJpaPropertyMap(properties); return entityManager; } @Bean(name = "transactionManager") public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory){ JpaTransactionManager transactionManager = new JpaTransactionManager(); transactionManager.setEntityManagerFactory(entityManagerFactory); return transactionManager; } }
Does anyone have an idea why my Hibernate implementation can work this way? The whole project you can find on github:
https://github.com/upway/jhipster-multi-tenancy-poc