Disable Spring datasource configuration in Hibernate multi tenant configuration

Question

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?

score 1 · Accepted Answer · answered Oct 09 '18 at 21:09

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;
    }

}

Disable Spring datasource configuration in Hibernate multi tenant configuration

1 Answers1