SpringBoot Multiple DB Connections using Routing DataSource

application-dev.yml

spring:
    profiles:
        active: dev
    datasource:
        dataSourceClassName: com.mysql.jdbc.jdbc2.optional.MysqlDataSource
        url: jdbc:mysql://localhost:3306/webservice_sample?useUnicode=true&characterEncoding=utf8
        databaseName:
        serverName:
        username: root
        password:
        cachePrepStmts: true
        prepStmtCacheSize: 250
        prepStmtCacheSqlLimit: 2048
        useServerPrepStmts: true
        
   slave-datasource:
        dataSourceClassName: com.mysql.jdbc.jdbc2.optional.MysqlDataSource
        url: jdbc:mysql://slave-localhost:3306/webservice_sample?useUnicode=true&characterEncoding=utf8
        databaseName:
        serverName:
        username: root
        password:
        cachePrepStmts: true
        prepStmtCacheSize: 250
        prepStmtCacheSqlLimit: 2048
        useServerPrepStmts: true
     
   jpa:
        database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
        database: MYSQL
        openInView: false
        show_sql: true
        generate-ddl: false
        hibernate:
            ddl-auto: update
            naming-strategy: org.springframework.boot.orm.jpa.hibernate.SpringNamingStrategy
        properties:
                    hibernate.cache.use_second_level_cache: false
                    hibernate.cache.use_query_cache: false
                    hibernate.generate_statistics: true
                    org.hibernate.envers.audit_table_suffix: _history

DatabaseConfiguration.java

@Configuration
@EnableJpaAuditing(dateTimeProviderRef = "dateTimeProvider")
@EnableJpaRepositories("com.sample.repository")
@EnableTransactionManagement
public class DatabaseConfiguration implements EnvironmentAware {

    private final Logger log = LoggerFactory.getLogger(DatabaseConfiguration.class);

    private RelaxedPropertyResolver dataSourcePropertyResolver;
    private RelaxedPropertyResolver slaveDataSourcePropertyResolver;

    private Environment env;

    @Override
    public void setEnvironment(Environment env) {
        this.env = env;
        this.dataSourcePropertyResolver = new RelaxedPropertyResolver(env, "spring.datasource.");
        this.slaveDataSourcePropertyResolver = new RelaxedPropertyResolver(env, "spring.slave-datasource.");
    }

    @Bean(destroyMethod = "close")
    public DataSource masterDataSource() {
        log.debug("Configuring Master Datasource");
        if (dataSourcePropertyResolver.getProperty("url") == null
                && dataSourcePropertyResolver.getProperty("databaseName") == null) {
            log.error("Your database connection pool configuration is incorrect! The application" +
                            " cannot start. Please check your Spring profile, current profiles are: {}",
                    Arrays.toString(env.getActiveProfiles()));

            throw new ApplicationContextException("Database connection pool is not configured correctly");
        }
        HikariConfig config = new HikariConfig();
        config.setDataSourceClassName(dataSourcePropertyResolver.getProperty("dataSourceClassName"));
        if (StringUtils.isEmpty(dataSourcePropertyResolver.getProperty("url"))) {
            config.addDataSourceProperty("databaseName",
                    dataSourcePropertyResolver.getProperty("databaseName"));
            config.addDataSourceProperty("serverName",
                    dataSourcePropertyResolver.getProperty("serverName"));
        } else {
            config.addDataSourceProperty("url", dataSourcePropertyResolver.getProperty("url"));
        }
        config.addDataSourceProperty("user", dataSourcePropertyResolver.getProperty("username"));
        config.addDataSourceProperty("password", dataSourcePropertyResolver.getProperty("password"));

        //MySQL optimizations, see https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration
        if ("com.mysql.jdbc.jdbc2.optional.MysqlDataSource".equals(
                dataSourcePropertyResolver.getProperty("dataSourceClassName"))) {
            config.addDataSourceProperty("cachePrepStmts",
                    dataSourcePropertyResolver.getProperty("cachePrepStmts", "true"));
            config.addDataSourceProperty("prepStmtCacheSize",
                    dataSourcePropertyResolver.getProperty("prepStmtCacheSize", "250"));
            config.addDataSourceProperty("prepStmtCacheSqlLimit", dataSourcePropertyResolver
                    .getProperty("prepStmtCacheSqlLimit", "2048"));
        }
        return new HikariDataSource(config);
    }

    @Bean(destroyMethod = "close")
    public DataSource slaveDataSource() {
        log.debug("Configuring Slave Datasource");
        if (slaveDataSourcePropertyResolver.getProperty("url") == null
                && slaveDataSourcePropertyResolver.getProperty("databaseName") == null) {
            log.warn("Your database connection pool configuration for slave is not present/incorrect! The application" +
                            " will use master datasource only. Please check your Spring profile, current profiles are: {}",
                    Arrays.toString(env.getActiveProfiles()));

            return masterDataSource();
        }
        HikariConfig config = new HikariConfig();
        config.setDataSourceClassName(slaveDataSourcePropertyResolver.getProperty("dataSourceClassName"));
        if (StringUtils.isEmpty(slaveDataSourcePropertyResolver.getProperty("url"))) {
            config.addDataSourceProperty("databaseName",
                    slaveDataSourcePropertyResolver.getProperty("databaseName"));
            config.addDataSourceProperty("serverName",
                    slaveDataSourcePropertyResolver.getProperty("serverName"));
        } else {
            config.addDataSourceProperty("url", slaveDataSourcePropertyResolver.getProperty("url"));
        }
        config.addDataSourceProperty("user", slaveDataSourcePropertyResolver.getProperty("username"));
        config.addDataSourceProperty("password", slaveDataSourcePropertyResolver.getProperty("password"));

        //MySQL optimizations, see https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration
        if ("com.mysql.jdbc.jdbc2.optional.MysqlDataSource".equals(
                slaveDataSourcePropertyResolver.getProperty("dataSourceClassName"))) {
            config.addDataSourceProperty("cachePrepStmts",
                    slaveDataSourcePropertyResolver.getProperty("cachePrepStmts", "true"));
            config.addDataSourceProperty("prepStmtCacheSize",
                    slaveDataSourcePropertyResolver.getProperty("prepStmtCacheSize", "250"));
            config.addDataSourceProperty("prepStmtCacheSqlLimit", slaveDataSourcePropertyResolver
                    .getProperty("prepStmtCacheSqlLimit", "2048"));
        }
        return new HikariDataSource(config);
    }

    @Bean
    @Primary
    public DataSource dataSource() {
        log.debug("Configuring Datasource");
        RoutingDataSource routingDataSource = new RoutingDataSource();

        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DbType.MASTER, masterDataSource());
        targetDataSources.put(DbType.SLAVE, slaveDataSource());

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

        return routingDataSource;
    }

    @Bean
    public Hibernate4Module hibernate4Module() {
        return new Hibernate4Module();
    }
}

DbContextHolder.java

public class DbContextHolder {

    private static final ThreadLocal<DbType> contextHolder = new ThreadLocal<DbType>();

    public static void setDbType(DbType dbType) {
        if(dbType == null){
            throw new NullPointerException();
        }
        contextHolder.set(dbType);
    }

    public static DbType getDbType() {
        return (DbType) contextHolder.get();
    }

    public static void clearDbType() {
        contextHolder.remove();
    }
}

DbType.java

public enum DbType {
    MASTER,
    SLAVE
}

ReadOnlyConnection.java

@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ReadOnlyConnection {

}

ReadOnlyConnectionInterceptor.java

@Aspect
@Component
public class ReadOnlyConnectionInterceptor implements Ordered {

    private int order;

    /*
    The last bit to clarify is the magical @Value("20").
    It is used to set the order parameter of our interceptor.
    The thing is, we need to make sure that the DataSource type is set before the @Transactional annotation kicks in.
    Otherwise connection will already be bound to the thread at the time our @ReadOnlyConnection gets processed.
    So basically we need set the order below the order of transactions annotation (20 < 100).
     */
    @Value("20")
    public void setOrder(int order) {
        this.order = order;
    }

    @Override
    public int getOrder() {
        return order;
    }

    @Pointcut(value="execution(public * *(..))")
    public void anyPublicMethod() { }

    @Around("@annotation(readOnlyConnection)")
    public Object proceed(ProceedingJoinPoint pjp, ReadOnlyConnection readOnlyConnection) throws Throwable {
        try {
            DbContextHolder.setDbType(DbType.SLAVE);
            Object result = pjp.proceed();
            DbContextHolder.clearDbType();
            return result;
        } finally {
            // restore state
            DbContextHolder.clearDbType();
        }
    }
}

RoutingDataSource.java

@Slf4j
public class RoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        log.info("db look up key " + DbContextHolder.getDbType());
        return DbContextHolder.getDbType();
    }
}

Usage.java

    @ReadOnlyConnection
    @Transactional(readOnly = true)
    public XXX getById {