Dynamically set hibernate.dialect properties in spring boot

Question

I have gone through available example and tutorials on how to set hibernate.dialect property correctly but found no approach suitable for my situation.

This tutorial is working best for me, but it lacks the ability to set hibernate.dialect property dynamically as I have different types of databases to connect to:

• MS SQL
• Oracle
• H2
• MySQL

With incorrect dialect, my JPA (delete/update) queries fail.

With below implementation of @Configuration, which works perfectly, how may I be able to set hibernate.dialect dynamically at runtime for each datasource?

Thank you in advance.

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        basePackages = "com.example.multidb",
        entityManagerFactoryRef = "multiEntityManager",
        transactionManagerRef = "multiTransactionManager"
)
public class PersistenceConfiguration {

    private final String PACKAGE_SCAN = "com.example.multidb";    

    @Primary
    @Bean(name = "mainDataSource")
    @ConfigurationProperties("app.datasource.main")
    public DataSource mainDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }    

    @Bean(name = "clientADataSource")
    @ConfigurationProperties("app.datasource.clienta")
    public DataSource clientADataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean(name = "clientBDataSource")
    @ConfigurationProperties("app.datasource.clientb")
    public DataSource clientBDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean(name = "multiRoutingDataSource")
    public DataSource multiRoutingDataSource() {
        Map<Object, Object> targetDataSources = new HashMap<>();

        targetDataSources.put(DBTypeEnum.MAIN, mainDataSource());
        targetDataSources.put(DBTypeEnum.CLIENT_A, clientADataSource());
        targetDataSources.put(DBTypeEnum.CLIENT_B, clientBDataSource());

        MultiRoutingDataSource multiRoutingDataSource = new MultiRoutingDataSource();

        multiRoutingDataSource.setDefaultTargetDataSource(mainDataSource());
        multiRoutingDataSource.setTargetDataSources(targetDataSources);        

        return multiRoutingDataSource;
    }   

    @Bean(name = "multiEntityManager")
    public LocalContainerEntityManagerFactoryBean multiEntityManager() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();

        em.setDataSource(multiRoutingDataSource());
        em.setPackagesToScan(PACKAGE_SCAN);

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();

        em.setJpaVendorAdapter(vendorAdapter);
        em.setJpaProperties(hibernateProperties());        

        return em;
    }

    @Bean(name = "multiTransactionManager")
    public PlatformTransactionManager multiTransactionManager() {
        JpaTransactionManager transactionManager
                = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(
                multiEntityManager().getObject());        

        return transactionManager;
    }

    @Primary
    @Bean(name = "dbSessionFactory")
    public LocalSessionFactoryBean dbSessionFactory() {
        LocalSessionFactoryBean sessionFactoryBean = new LocalSessionFactoryBean();

        sessionFactoryBean.setDataSource(multiRoutingDataSource());
        sessionFactoryBean.setPackagesToScan(PACKAGE_SCAN);
        sessionFactoryBean.setHibernateProperties(hibernateProperties());

        return sessionFactoryBean;
    }

    private Properties hibernateProperties() {
        Properties properties = new Properties();
        properties.put("hibernate.show_sql", true);
        properties.put("hibernate.format_sql", true);        

        //set hibernate.dialect for each datasource

        return properties;
    }
}

Answer 1

I've created a working example for you, I'll describe it here but if you want to jump in the code yourself it's available at this GitHub repo.

In my case, I've created two data sources, one for User and another one for Item.

Here the entities:

package com.marcosbarbero.so.multiple.ds.entity.user;

import lombok.Data;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Data
@Entity
@Table(schema = "user")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    private String name;

    @Column(unique = true, nullable = false)
    private String email;

    private int age;
}

package com.marcosbarbero.so.multiple.ds.entity.item;

import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Data
@Entity
@Table(schema = "item")
public class Item {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;

    private String name;
}

Note, It's important to have distinct packages for each domain. Then I created the Repositories

package com.marcosbarbero.so.multiple.ds.repository.user;

import com.marcosbarbero.so.multiple.ds.entity.user.User;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<User, Integer> {
}

package com.marcosbarbero.so.multiple.ds.repository.item;

import com.marcosbarbero.so.multiple.ds.entity.item.Item;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ItemRepository extends JpaRepository<Item, Integer> {
}

Nothing special about the repos. Let's move to the final piece, the configuration.

1. I created a @ConfigurationProperties class to externalize my configuration, bear with me, I know the naming is not the best :)

package com.marcosbarbero.so.multiple.ds.config;

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

import java.util.HashMap;
import java.util.Map;

@Data
@Component
@ConfigurationProperties(prefix = "multi-datasource")
public class MultipleDataSourceProperties {

    private UserDataSourceProperties user = new UserDataSourceProperties();

    private ItemDataSourceProperties item = new ItemDataSourceProperties();

    @Data
    public static class UserDataSourceProperties {
        private HibernateProperties hibernate = new HibernateProperties();
    }

    @Data
    public static class ItemDataSourceProperties {
        private HibernateProperties hibernate = new HibernateProperties();
    }

    @Data
    public static class HibernateProperties {
        private Map<String, String> properties = new HashMap<>();
    }

}

We'll see the properties configuration file soon.

2. Now let's create the DataSource for the User:

package com.marcosbarbero.so.multiple.ds.config;

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;

@Configuration
@EnableJpaRepositories(
        basePackages = "com.marcosbarbero.so.multiple.ds.repository.user",
        entityManagerFactoryRef = "userEntityManager",
        transactionManagerRef = "userTransactionManager"
)
public class UserDataSourceConfig {

    private final MultipleDataSourceProperties properties;

    public UserDataSourceConfig(MultipleDataSourceProperties properties) {
        this.properties = properties;
    }

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean userEntityManager() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(userDataSource());
        em.setPackagesToScan("com.marcosbarbero.so.multiple.ds.entity.user");

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        em.setJpaPropertyMap(properties.getUser().getHibernate().getProperties());

        return em;
    }

    @Primary
    @Bean
    @ConfigurationProperties("multi-datasource.user")
    public DataSource userDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Primary
    @Bean
    public PlatformTransactionManager userTransactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(userEntityManager().getObject());
        return transactionManager;
    }
}

The important part for you at this class is the line em.setJpaPropertyMap(properties.getUser().getHibernate().getProperties()); it's getting the User's Hibernate configuration properties from our @ConfigurationProperties class defined above.

3. Now let's do the same for the Item:

package com.marcosbarbero.so.multiple.ds.config;

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;

@Configuration
@EnableJpaRepositories(
        basePackages = "com.marcosbarbero.so.multiple.ds.repository.item",
        entityManagerFactoryRef = "itemEntityManager",
        transactionManagerRef = "itemTransactionManager"
)
public class ItemDataSourceConfig {

    private final MultipleDataSourceProperties properties;

    public ItemDataSourceConfig(MultipleDataSourceProperties properties) {
        this.properties = properties;
    }

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean itemEntityManager() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(itemDataSource());
        em.setPackagesToScan("com.marcosbarbero.so.multiple.ds.entity.item");

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        em.setJpaPropertyMap(properties.getItem().getHibernate().getProperties());

        return em;
    }

    @Primary
    @Bean
    @ConfigurationProperties("multi-datasource.item")
    public DataSource itemDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Primary
    @Bean
    public PlatformTransactionManager itemTransactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(itemEntityManager().getObject());
        return transactionManager;
    }
}

4. The application.properties

multi-datasource.item.jdbcUrl=jdbc:h2:mem:spring_jpa_item;DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS ITEM
multi-datasource.item.username=sa
multi-datasource.item.password=sa

multi-datasource.item.hibernate.properties.hibernate.hbm2ddl.auto=create-drop
multi-datasource.item.hibernate.properties.hibernate.cache.use_second_level_cache=false
multi-datasource.item.hibernate.properties.hibernate.cache.use_query_cache=false
multi-datasource.item.hibernate.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

multi-datasource.user.jdbcUrl=jdbc:h2:mem:spring_jpa_user;DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS USER
multi-datasource.user.username=sa
multi-datasource.user.password=sa

multi-datasource.user.hibernate.properties.hibernate.hbm2ddl.auto=create-drop
multi-datasource.user.hibernate.properties.hibernate.cache.use_second_level_cache=false
multi-datasource.user.hibernate.properties.hibernate.cache.use_query_cache=false
multi-datasource.user.hibernate.properties.hibernate.dialect=org.hibernate.dialect.OracleDialect

5. Some unit tests

package com.marcosbarbero.so;

import com.marcosbarbero.so.multiple.ds.entity.item.Item;
import com.marcosbarbero.so.multiple.ds.entity.user.User;
import com.marcosbarbero.so.multiple.ds.repository.item.ItemRepository;
import com.marcosbarbero.so.multiple.ds.repository.user.UserRepository;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import static org.junit.jupiter.api.Assertions.assertNotNull;

@SpringBootTest
public class JPAMultipleDBTest {

    @Autowired
    private UserRepository userRepository;

    @Autowired
    private ItemRepository itemRepository;

    @Test
    public void whenCreatingUser_thenCreated() {
        User user = new User();
        user.setName("John");
        user.setEmail("john@test.com");
        user.setAge(20);
        user = userRepository.save(user);

        assertNotNull(userRepository.findById(user.getId()));
    }

    @Test
    public void whenCreatingProduct_thenCreated() {
        Item item = new Item();
        item.setName("Book");
        item.setId(2);
        item = itemRepository.save(item);

        assertNotNull(itemRepository.findById(item.getId()));
    }
}

I think it also worth mentioning, to make it all work I disabled the DataSourceAutoConfiguration, it's simple as that:

@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)

And again, it all is available at this GitHub repo.