4

I'm creating a server REST app using Spring 2.x, Spring Data REST, Hibernate 5.x, Mysql.

I configured multitenant following this guideline: https://dzone.com/articles/spring-boot-hibernate-multitenancy-implementation with the only difference I'm using a DB per tenant.

I've a MultiTenantConnectionProvider that create connections to the DB and a TenantIdentifierResolver to get the current tenant.

Some relevant pieces of code:

@Component public class TenantIdentifierResolver implements CurrentTenantIdentifierResolver { @Override public String resolveCurrentTenantIdentifier() { String tenantId = TenantContext.getCurrentTenant(); if (tenantId != null) { return tenantId; } return DEFAULT_TENANT_ID; } @Override public boolean validateExistingCurrentSessions() { return true; } }

...

@Component @Profile("prod") public class MultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider { private static final long serialVersionUID = 3193007611085791247L; private Logger log = LogManager.getLogger(); private Map<String, HikariDataSource> dataSourceMap = new ConcurrentHashMap<String, HikariDataSource>(); @Autowired private TenantRestClient tenantRestClient; @Autowired private PasswordEncrypt passwordEncrypt; @Override public void releaseAnyConnection(Connection connection) throws SQLException { connection.close(); } @Override public Connection getAnyConnection() throws SQLException { Connection connection = getDataSource(TenantIdResolver.TENANT_DEFAULT).getConnection(); return connection; } @Override public Connection getConnection(String tenantId) throws SQLException { Connection connection = getDataSource(tenantId).getConnection(); return connection; } @Override public void releaseConnection(String tenantId, Connection connection) throws SQLException { log.info("releaseConnection " + tenantId); 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; } public HikariDataSource getDataSource(@NotNull String tentantId) throws SQLException { if (dataSourceMap.containsKey(tentantId)) { return dataSourceMap.get(tentantId); } else { HikariDataSource dataSource = createDataSource(tentantId); dataSourceMap.put(tentantId, dataSource); return dataSource; } } public HikariDataSource createDataSource(String tenantId) throws SQLException { log.info("Create Datasource for tenant {}", tenantId); try { Database database = tenantRestClient.getDatabase(tenantId); DatabaseInstance databaseInstance = tenantRestClient.getDatabaseInstance(tenantId); if (database != null && databaseInstance != null) { HikariConfig hikari = new HikariConfig(); String driver = ""; String options = ""; switch (databaseInstance.getType()) { case MYSQL: driver = "jdbc:mysql://"; options = "?useLegacyDatetimeCode=false&serverTimezone=UTC&useUnicode=yes&characterEncoding=UTF-8&useSSL=false"; break; default: driver = "jdbc:mysql://"; options = "?useLegacyDatetimeCode=false&serverTimezone=UTC&useUnicode=yes&characterEncoding=UTF-8&useSSL=false"; } hikari.setJdbcUrl(driver + databaseInstance.getHost() + ":" + databaseInstance.getPort() + "/" + database.getName() + options); hikari.setUsername(database.getUsername()); hikari.setPassword(passwordEncrypt.decryptPassword(database.getPassword())); // MySQL optimizations, see // https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration hikari.addDataSourceProperty("cachePrepStmts", true); hikari.addDataSourceProperty("prepStmtCacheSize", "250"); hikari.addDataSourceProperty("prepStmtCacheSqlLimit", "2048"); hikari.addDataSourceProperty("useServerPrepStmts", "true"); hikari.addDataSourceProperty("useLocalSessionState", "true"); hikari.addDataSourceProperty("useLocalTransactionState", "true"); hikari.addDataSourceProperty("rewriteBatchedStatements", "true"); hikari.addDataSourceProperty("cacheResultSetMetadata", "true"); hikari.addDataSourceProperty("cacheServerConfiguration", "true"); hikari.addDataSourceProperty("elideSetAutoCommits", "true"); hikari.addDataSourceProperty("maintainTimeStats", "false"); hikari.setMinimumIdle(3); hikari.setMaximumPoolSize(5); hikari.setIdleTimeout(30000); hikari.setPoolName("JPAHikari_" + tenantId); // mysql wait_timeout 600seconds hikari.setMaxLifetime(580000); hikari.setLeakDetectionThreshold(60 * 1000); HikariDataSource dataSource = new HikariDataSource(hikari); return dataSource; } else { throw new SQLException(String.format("DB not found for tenant %s!", tenantId)); } } catch (Exception e) { throw new SQLException(e.getMessage()); } } }

I've also configured Hibernate in this way:

@Configuration @Profile("prod") public class HibernateConfig { @Autowired private JpaProperties jpaProperties; @Bean public JpaVendorAdapter jpaVendorAdapter() { return new HibernateJpaVendorAdapter(); } @Bean public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource, MultiTenantConnectionProvider multiTenantConnectionProviderImpl, CurrentTenantIdentifierResolver currentTenantIdentifierResolverImpl) { Map<String, Object> properties = new HashMap<>(); properties.putAll(jpaProperties.getHibernateProperties(new HibernateSettings())); properties.put(Environment.MULTI_TENANT, MultiTenancyStrategy.DATABASE); properties.put(Environment.MULTI_TENANT_CONNECTION_PROVIDER, multiTenantConnectionProviderImpl); properties.put(Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, currentTenantIdentifierResolverImpl); LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean(); em.setDataSource(dataSource); em.setPackagesToScan("com.server"); em.setJpaVendorAdapter(jpaVendorAdapter()); em.setJpaPropertyMap(properties); return em; } }

And this my application.properties:

spring.datasource.url=jdbc:mysql://url:3306/empty?useLegacyDatetimeCode=false&serverTimezone=UTC&useSSL=false spring.datasource.username=empty spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver spring.datasource.password=empty spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57Dialect spring.jpa.hibernate.ddl-auto: validate spring.jpa.hibernate.naming.physical- strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl spring.jpa.show-sql: false

During the startup of the application I see Spring create a connection pool to the db I configured in the properties file.

I would like to avoid this because all my connection are created by MultiTenantConnectionProviderImpl. I would like to continue having the facility of injected EntityManager and Datasource in my beans.

I already saw how to disable Spring Boot Datasource configuration here, but doing that I'm not able anymore to inject Datasource in my application.

Do you have some suggestion to how remove completely the definition of the datasource from the properties file and inject the datasource programmatically in the app from MultiTenantConnectionProviderImpl?

Improve this question

1 Answer 1

Reset to default
1

The following is a complete example on how to create a datasource programmatically.

Just parameter are taken from properties file, instead of hard-coding inside java classes.

When you define multiple datasources you have to define one @Primary and just one, then you will have a @Qualifier that identifies each datasource. It should be straightforward how to manage them.

@RequiredArgsConstructor @PropertySource({"classpath:persistence/persistence-primarydb.properties"}) @EnableJpaRepositories(basePackages = "io.vforge.cauldron.repository.primary", entityManagerFactoryRef = "primaryEntityManagerFactory", transactionManagerRef = "primaryTransactionManager") @EnableJpaAuditing @Configuration public class CauldronPrimaryDatasource { private final Environment env; @Primary @Bean public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory() { LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean(); em.setDataSource(primaryDataSource()); em.setPackagesToScan("io.vforge.cauldron.model.primary"); HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter(); em.setJpaVendorAdapter(vendorAdapter); HashMap<String, Object> properties = new HashMap<>(); properties.put("hibernate.hbm2ddl.auto", env.getProperty("primary.hibernate.hbm2ddl.auto")); properties.put("hibernate.dialect", env.getProperty("primary.hibernate.dialect")); properties.put("hibernate.show_sql", env.getProperty("primary.hibernate.show_sql")); em.setJpaPropertyMap(properties); return em; } @Primary @Bean public HikariDataSource primaryDataSource() { final HikariDataSource dataSource = new HikariDataSource(); dataSource.setJdbcUrl(env.getProperty("primary.datasource.url")); dataSource.setUsername(env.getProperty("primary.datasource.username")); dataSource.setPassword(env.getProperty("primary.datasource.password")); return dataSource; } @Primary @Bean public PlatformTransactionManager primaryTransactionManager() { JpaTransactionManager transactionManager= new JpaTransactionManager(); transactionManager.setEntityManagerFactory( primaryEntityManagerFactory().getObject()); return transactionManager; } }
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.