How to do database routing in read-only and read-write with Spring

I would strongly suggest to use autoconfiguration as-much as you can, it will make things a little simpler. The main key is to set to delay the getting of the connection and preparing it for the current transaction.

This can be achieved in 2 different ways.

1. Set the prepareConnection property of the JpaDialect to false. If you don't then the JpaTransactionManager will eagerly get Connection and prepare it for the transaction. This is even before it had time to set the current state of the transaction onto the TransactionSynchronizationManager. Which will make the call to TransactionSynchronizationManager.isCurrentTransactionReadOnly always return false (as it is set at the end of the doBegin method in the JpaTransactionManager.

2. Set the hibernate.connection.handling_mode to DELAYED_ACQUISITION_AND_RELEASE_AFTER_TRANSACTION. This will delay the getting of a connection and close the connection after the transaction. Without Spring this is also the default for Hibernate 5.2+ (see the Hibernate User Guide) but for legacy reasons Spring switches this to DELAYED_ACQUISITION_AND_HOLD.

Either of these solutions will work as the preparing of the connection is delayed and the JpaTransactionManager has thus time to sync the state in the TransactionSynchronizationManager.

@Bean
public BeanPostProcessor dialectProcessor() {

    return new BeanPostProcessor() {
        @Override
        public Object postProcessBeforeInitialization(Object bean, String beanName) throws BeansException {
            if (bean instanceof HibernateJpaVendorAdapter) {
                ((HibernateJpaVendorAdapter) bean).getJpaDialect().setPrepareConnection(false);
            }
            return bean;
        }
    };
}

However adding this property to your application.properties will also work:

spring.jpa.properties.hibernate.connection.handling_mode=DELAYED_ACQUISITION_AND_RELEASE_AFTER_TRANSACTION

With either one of these solutions you can now ditch your transaction configuration, jpa etc. There is also an easier way to configure multiple datasources. It is described in the Spring Boot Reference Guide which will reuse as much of the Spring auto-configuration as possible.

First make sure the following is in your application.properties

# DATABASE MASTER PROPERTIES
master.datasource.url=jdbc:h2:mem:masterdb;DB_CLOSE_DELAY=-1
master.datasource.username=sa
master.datasource.password=sa
master.datasource.configuration.pool-name=Master-DB

# DATABASE SLAVE PROPERTIES
slave.datasource.url=jdbc:h2:mem:slavedb;DB_CLOSE_DELAY=-1
slave.datasource.username=sa
slave.datasource.password=sa
slave.datasource.configuration.pool-name=Slave-DB

# JPA PROPERTIES SETTINGS
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.properties.hibernate.connection.provider_disables_autocommit=true
spring.jpa.open-in-view=false

# ENABLE ERRORS IN DESERIALIZATION OF MISSING OR IGNORED PROPERTIES
spring.jackson.deserialization.fail-on-unknown-properties=true
spring.jackson.deserialization.fail-on-ignored-properties=true

# ENABLE ERRORS ON REQUESTS FOR NON-EXISTENT RESOURCES
spring.mvc.throw-exception-if-no-handler-found=true

# DISABLE MAPPINGS OF STATIC RESOURCES (IS NOT USABLE IN DEVELOPMENT OF APIs)
spring.web.resources.add-mappings=false

NOTE: Removed the driver for JDBC (not needed) only set spring.jpa.database-platform you set either database or database-platform not both.

Now with this and the following @Configuration class you will have 2 datasources, the routing one and the BeanPostProcessor as mentioned above (if you choose to use the property you can remove said BeanPostProcessor.

@Configuration
public class DatasourceConfiguration {

    @Bean
    @ConfigurationProperties("master.datasource")
    public DataSourceProperties masterDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("master.datasource.configuration")
    public HikariDataSource masterDataSource(DataSourceProperties masterDataSourceProperties) {
        return masterDataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    @Bean
    @ConfigurationProperties("slave.datasource")
    public DataSourceProperties slaveDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("slave.datasource.configuration")
    public HikariDataSource slaveDataSource(DataSourceProperties slaveDataSourceProperties) {
        return slaveDataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    @Bean
    @Primary
    public TransactionRoutingDataSource routingDataSource(DataSource masterDataSource,  DataSource slaveDataSource) {
        TransactionRoutingDataSource routingDataSource = new TransactionRoutingDataSource();

        Map<Object, Object> dataSourceMap = new HashMap<>();
        dataSourceMap.put(DataSourceType.READ_WRITE, masterDataSource);
        dataSourceMap.put(DataSourceType.READ_ONLY, slaveDataSource);

        routingDataSource.setTargetDataSources(dataSourceMap);
        routingDataSource.setDefaultTargetDataSource(masterDataSource);

        return routingDataSource;
    }

    @Bean
    public BeanPostProcessor dialectProcessor() {

        return new BeanPostProcessor() {
            @Override
            public Object postProcessBeforeInitialization(Object bean, String beanName) throws BeansException {
                if (bean instanceof HibernateJpaVendorAdapter) {
                    ((HibernateJpaVendorAdapter) bean).getJpaDialect().setPrepareConnection(false);
                }
                return bean;
            }
        };
    }
}

This will set up everything you need for this to work and still be able to use as much of the auto-configuration and detection as you can. With this, the only configuration you need to do is this DataSource setup. No JPA, Transaction management etc. as that will be done automatically.

Finally here is a test to test this with (you can test both scenarios). The read-only one will fail because there is no schema there, the save will succeed as there is a schema on the READ_WRITE side of things.

@Test
void testDatabaseSwitch() {
    Assertions.assertThatThrownBy(() -> billionaireService.findAll())
            .isInstanceOf(DataAccessException.class);

    Billionaire newBIllionaire = new Billionaire(null, "Marten", "Deinum", "Spring Nerd.");
    billionaireService.save(newBIllionaire);

}

I solved this problem by changing my implementation of the RoutingConfiguration.java class.

I configured the data source for using the setAutoCommit(false) configuration and added the property hibernate.connection.provider_disables_autocommit with value true.

@Configuration
@EnableTransactionManagement
public class RoutingConfiguration {

    private final Environment environment;

    public RoutingConfiguration(Environment environment) {
        this.environment = environment;
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(@Qualifier("routingDataSource") DataSource routingDataSource) {
        LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();

        entityManagerFactoryBean.setPersistenceUnitName(getClass().getSimpleName());
        entityManagerFactoryBean.setPersistenceProvider(new HibernatePersistenceProvider());
        entityManagerFactoryBean.setDataSource(routingDataSource);
        entityManagerFactoryBean.setPackagesToScan(Billionaires.class.getPackageName());

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        HibernateJpaDialect jpaDialect = vendorAdapter.getJpaDialect();

        jpaDialect.setPrepareConnection(false);

        entityManagerFactoryBean.setJpaVendorAdapter(vendorAdapter);
        entityManagerFactoryBean.setJpaProperties(additionalProperties());

        return entityManagerFactoryBean;
    }

    @Bean
    public JpaTransactionManager transactionManager(EntityManagerFactory entityManagerFactory){
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManagerFactory);
        return transactionManager;
    }

    @Bean
    public TransactionTemplate transactionTemplate(EntityManagerFactory entityManagerFactory) {
        return new TransactionTemplate(transactionManager(entityManagerFactory));
    }

    @Bean
    public TransactionRoutingDataSource routingDataSource(
            @Qualifier("masterDataSource") DataSource masterDataSource,
            @Qualifier("slaveDataSource") DataSource slaveDataSource
    ) {
        TransactionRoutingDataSource routingDataSource = new TransactionRoutingDataSource();

        Map<Object, Object> dataSourceMap = new HashMap<>();
        dataSourceMap.put(DataSourceType.READ_WRITE, masterDataSource);
        dataSourceMap.put(DataSourceType.READ_ONLY, slaveDataSource);

        routingDataSource.setTargetDataSources(dataSourceMap);
        routingDataSource.setDefaultTargetDataSource(masterDataSource());

        return routingDataSource;
    }

    @Bean
    public DataSource masterDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl(environment.getProperty("master.datasource.url"));
        dataSource.setUsername(environment.getProperty("master.datasource.username"));
        dataSource.setPassword(environment.getProperty("master.datasource.password"));
        return connectionPoolDataSource(dataSource, determinePoolName(DataSourceType.READ_WRITE));
    }

    @Bean
    public DataSource slaveDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl(environment.getProperty("slave.datasource.url"));
        dataSource.setUsername(environment.getProperty("slave.datasource.username"));
        dataSource.setPassword(environment.getProperty("slave.datasource.password"));
        return connectionPoolDataSource(dataSource, determinePoolName(DataSourceType.READ_ONLY));
    }

    private HikariDataSource connectionPoolDataSource(DataSource dataSource, String poolName) {
        return new HikariDataSource(hikariConfig(dataSource, poolName));
    }

    private HikariConfig hikariConfig(DataSource dataSource, String poolName) {
        HikariConfig hikariConfig = new HikariConfig();

        hikariConfig.setPoolName(poolName);
        hikariConfig.setMaximumPoolSize(Runtime.getRuntime().availableProcessors() * 4);
        hikariConfig.setDataSource(dataSource);
        hikariConfig.setAutoCommit(false);

        return hikariConfig;
    }

    private Properties additionalProperties() {
        Properties properties = new Properties();

        properties.setProperty("hibernate.dialect", environment.getProperty("spring.jpa.database-platform"));
        properties.setProperty("hibernate.connection.provider_disables_autocommit", "true");

        return properties;
    }

    private String determinePoolName(DataSourceType dataSourceType) {
        return dataSourceType.getPoolName().concat("-").concat(dataSourceType.name());
    }

}

The hibernate.connection.provider_disables_autocommit allows the connection is acquired prior to calling the determineCurrentLookupKey method.