Dynamic dialect with Hibernate and Hikari

Question

I am new at Hibernate. Into my code, the connection to the DB is managed with the Hikari data source.

My code is right now multitenant, but it manages the same hibernate dialect for all tenants.

Is it possible to create a configuration where each tenant can use a different dialect? The type of dialect can be provided as a tenant's property.

---

This is an example of the entityManagerFactory:

     @Bean
     public LocalContainerEntityManagerFactoryBean entityManagerFactory() {

        Map<String, Object> jpaProperties = new HashMap<>();
        jpaProperties.put(..., ...);
        jpaProperties.put(org.hibernate.cfg.Environment.DIALECT, "myDialect");
        
        LocalContainerEntityManagerFactoryBean emfBean = new LocalContainerEntityManagerFactoryBean();
        emfBean.setPackagesToScan(new String[] {MyEntity.class.getPackage().getName()});
        emfBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        emfBean.setJpaPropertyMap(jpaProperties);
        return emfBean;
    }

---

Edit

I was looking to this solution: it suggests to create a duplicated LocalContainerEntityManagerFactoryBean for each dialect. What I do not understand is how can I tell when using one EntityManager (MySQL) and when the other one (Postgres or MsSQL): the solution discriminates the entities (each entity has its own DB) but in my case, all entities are on all DBs. Is the tenant that discriminates.

For example: if I create a second instance of LocalContainerEntityManagerFactoryBean (i.e. msSQLEntityManagerFactory()) with setted the dialect for SQL Server, the application fails to start with:

Application failed to start due to an exceptionorg.springframework.beans.factory.NoUniqueBeanDefinitionException:
 No qualifying bean of type 'javax.persistence.EntityManagerFactory' available:
 expected single matching bean but found 2: 
msSQLEntityManagerFactory,entityManagerFactory

Answer 1

@Tsvetelin answer pointed me on the right direction, but, after applying it, I started having problems with another libs, like Hazelcast that was being user as second level cache.

So, this is what I did:

1. Instead of creating a EntityManagerFactory bean, I created a LocalContainerEntityManagerFactoryBean that instantiates a DynamicTenantEntityManagerFactoryBean.

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
   
        DynamicTenantEntityManagerFactoryBean em = new DynamicTenantEntityManagerFactoryBean();
        em.setPackagesToScan(getPackagesToScan());
        em.setJpaProperties(additionalProperties());
        em.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        return em;
   
    }
   

getPackagesToScan() and additionalProperties() are functions that reads the parameters that was set by Spring Boot on the application context.

private String[] getPackagesToScan() {

    //Check if the defaults packagesToScan needs to be overrided
    String[] packagesToScan = dynamicTenantRoutingPropertiesConfig.getPackagesToScan();

    if (packagesToScan == null) {

        List<String> packageNames = EntityScanPackages.get(context).getPackageNames();
        if (packageNames.isEmpty() && AutoConfigurationPackages.has(context)) {
            packageNames = AutoConfigurationPackages.get(context);
        }

        packagesToScan = packageNames.toArray(new String[0]);

    }

    return packagesToScan;

}

private Properties additionalProperties() {
    
    //jpaProperties is an Autowired bean of type 
    //org.springframework.boot.autoconfigure.orm.jpa.JpaProperties

    final Properties properties = new Properties();
    properties.putAll(jpaProperties.getProperties());
    return properties;
}

DynamicTenantEntityManagerFactoryBean extends LocalContainerEntityManagerFactoryBean and overrides createNativeEntityManagerFactory(), using the same technique as @tsvetelin-yakimov

@Log4j2
@Configurable
public class DynamicTenantEntityManagerFactoryBean extends LocalContainerEntityManagerFactoryBean {

    @Autowired
    private ApplicationContext context;

    @PostConstruct
    public void postConstruct() {

        //Obtem a referencia para o Datasource
        AbstractDynamicTenantRoutingDataSource dataSource
                = context.getBean(AbstractDynamicTenantRoutingDataSource.class);

        //Define o Datasource
        this.setDataSource(dataSource);

    }


    @Override
    protected EntityManagerFactory createNativeEntityManagerFactory() throws PersistenceException {

        log.info("(DynamicTenantEntityManagerFactory): Iniciando Proxy...");
        final int hashcode = UUID.randomUUID().toString().hashCode();
        final Map<String, EntityManagerFactory> entityManagerFactoryMap = new HashMap<>();


        Object emf = Proxy.newProxyInstance(
                Thread.currentThread().getContextClassLoader(),
                new Class[]{SessionFactory.class},
                (proxy, method, args) -> {

                    if (method.getName().equals("hashCode")) {
                        return hashcode;
                    }

                    if (method.getName().equals("equals")) {
                        return proxy == args[0];
                    }

                    //Verifica se o DataSource esta preenchido
                    if (this.getDataSource() == null)
                        throw new PersistenceException("O DataSource não pode ser nulo");

                    //Verifica se ja temos o EntityManager para esse Datasource
                    String currentDriver = ((AbstractDynamicTenantRoutingDataSource) this.getDataSource()).resolveCurrentDriver();
                    EntityManagerFactory instance = entityManagerFactoryMap.get(currentDriver);

                    if (instance == null) {

                        //Obtem o dialeto
                        Dialect dialect = ((AbstractDynamicTenantRoutingDataSource) this.getDataSource()).resolveDialect(currentDriver);

                        log.info(
                                "(DynamicTenantEntityManagerFactory): Iniciando EntityManagerFactory para Driver {} Dialeto {}...",
                                currentDriver,
                                dialect
                        );

                        instance = this.createEntityManagerFactory(
                                dialect
                        );

                        entityManagerFactoryMap.put(
                                currentDriver,
                                instance
                        );

                    }

                    log.debug(
                            "(DynamicTenantEntityManagerFactory): Retornando EntityManagerFactory para Driver {}...",
                            currentDriver
                    );
                    return method.invoke(instance);

                }
        );

        log.info("(DynamicTenantEntityManagerFactory): Iniciando Proxy... Concluido!");
        return (SessionFactory) emf;

    }

    private EntityManagerFactory createEntityManagerFactory(Dialect dialect) {

        EntityManagerFactory factory = super.createNativeEntityManagerFactory();
        factory
                .getProperties()
                .put("hibernate.dialect", dialect.getClass().getName());

        return factory;

    }

}

Answer 2

I finally managed to find a solution for this problem. I managed to get around the problem with the dialects by having an entity manager factory for each dialect (in this case MySQL, Postgres and MS SQL Server).

Create a bean for EntityManagerFactory and return a proxy of that interface and in the handler, based on your logic, you can switch which emf to use to suit the used data source.

I have created a video for this because it seems like there is no documentation online.

Session Scoped Connection

It is pretty similar to what you're trying to achieve but in my case the users are providing the credentials, so it's even more complicated.

Answer 3

That's not really possible as the dialect affects certain quoting rules etc. which you can't just "swap out" at runtime based on a tenant identifier. Just create two persistence units, each pointing to a different data source for every database type. You will have to somehow lookup the appropriate EntityManager/EntityManagerFactory based on your tenant identifier which makes it a bit harder when you want to use Spring Data JPA as that requires a compilation static name reference for the entity manager factory. Maybe you can create a custom EntityManagerFactory that delegates all method calls to the appropriate instance based on the tenant identifier. Overall, this is not so easy and you will probably have to do a lot of trial and error.

IMO it would be better to have a separate application deployment with separate configuration if possible per database type.