Setting hibernate dialect dynamically

Question

I have implemented Hibernate's multitenant database architecture, where a specific database connection is chosen depending on the tenant. I'm using Spring 4.3 and Hibernate 5.2.

All good when tenants are using the same RDBMS, but when they are different, I have to change the dialect setting in hibernate properties dynamically which I don't know how.

My hibernate properties are in dispatcher-servlet.xml:

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd
         http://www.springframework.org/schema/tx  http://www.springframework.org/schema/tx/spring-tx.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

    <context:component-scan base-package="com.example"/>
    <mvc:annotation-driven/>
    <context:property-placeholder location="classpath:application.properties"/>
    <tx:annotation-driven transaction-manager="transactionManager"/>

    <bean id="sessionFactory" class="org.springframework.orm.hibernate5.LocalSessionFactoryBean" >
        <property name="packagesToScan">
            <list>
                <value>com.example.model</value>
            </list>
        </property>
        <property name="hibernateProperties">
            <props>
                <!--<prop key="hibernate.dialect">${hibernate.dialect}</prop>-->
                <prop key="hibernate.show_sql">${hibernate.show_sql:false}</prop>
                <prop key="hibernate.format_sql">${hibernate.format_sql:false}</prop>
                <prop key="hibernate.multiTenancy">DATABASE</prop>
                <prop key="hibernate.tenant_identifier_resolver">com.example.multitenancy.CurrentTenantIdentifierResolverImpl</prop>
                <prop key="hibernate.multi_tenant_connection_provider">com.example.multitenancy.MultiTenantConnectionProviderImpl</prop>
            </props>
        </property>
    </bean>
    <bean id="transactionManager"  class="org.springframework.orm.hibernate5.HibernateTransactionManager">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>
</beans>

Below is the implementation of Hibernate's CurrentTenantIdentifierResolver:

public class CurrentTenantIdentifierResolverImpl implements CurrentTenantIdentifierResolver {
    @Override
    public String resolveCurrentTenantIdentifier() {

        Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
        return Helper.getTenantFromAuthentication(authentication);
    }
    @Override
    public boolean validateExistingCurrentSessions() {
        return true;
    }
}

and the implementation of AbstractDataSourceBasedMultiTenantConnectionProviderImpl:

public class MultiTenantConnectionProviderImpl extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl {

    @Override
    protected DataSource selectAnyDataSource() {
        return getDataSource("tenantId1");
    }
    @Override
    protected DataSource selectDataSource(String tenantIdentifier) {
        return getDataSource(tenantIdentifier);
    }

    private DataSource getDataSource(String prefix) {

        Properties properties = new Properties();
        try {
properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("application.properties"));

        } catch (IOException e) {
            throw new RuntimeException();
        }

        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(properties.getProperty(prefix + ".driverClassName"));
        dataSource.setUrl(properties.getProperty(prefix + ".url"));
        dataSource.setUsername(properties.getProperty(prefix + ".username"));
        dataSource.setPassword(properties.getProperty(prefix + ".password"));
        return dataSource;
    }
}

The application.properties file looks like this:

tenantId1.driverClassName = org.postgresql.Driver
tenantId1.url = <...>
tenantId1.username = <...>
tenantId1.password = <...>

tenantId2.driverClassName = com.mysql.jdbc.Driver
tenantId2.url = <...>
tenantId2.username = <...>
tenantId2.password = <...>

Is there a way to change the hibernate dialect dynamically?

Answer 1

You cannot achieve using single hibernate configuration file. You need to have different configurations files for each database.

For example, you have two database MySql and Oracle:

To configure mysql database

hibernate-mysql.cfg.xml

To configure oracle database

hibernate-oracle.cfg.xml

Create two different sessions for and code should be like this.

private static SessionFactory sessionAnnotationFactory; 

    sessionAnnotationFactory = new Configuration().configure("hibernate-mysql.cfg.xml").buildSessionFactory();

Session MySqlSession = sessionAnnotationFactory.openSession();

For Oracle database configuration

sessionAnnotationFactory = new Configuration().configure("hibernate-oracle.cfg.xml").buildSessionFactory();

Session OracleSession = sessionAnnotationFactory.openSession()