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 {
      

Hi Himanshu

I have tried this solution but it is not working.Below are the sample classes of configuration.Please provide any solution to it.

For Data source configuration
Properties
`
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&verifyServerCertificate=false&useSSL=false&noAccessToProcedureBodies=true
jdbc.user=root
jdbc.password=root@1234
jdbc.min-pool=10
jdbc.max-pool=20

######(Hibernate CONFIG)#############
hibernate.show_sql=false
hibernate.param.level=INFO
`

Code
`@Bean(destroyMethod = "close")
public HikariDataSource masterDataSource() {

	HikariConfig config=new HikariConfig();
	config.addDataSourceProperty("cachePrepStmts", "true");
	config.addDataSourceProperty("prepStmtCacheSize", "250");
	config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
	config.setMaximumPoolSize(maxPool);
	config.setJdbcUrl(url);
	config.setDriverClassName(driver);
	config.setUsername(user);
	config.setPassword(password);
	HikariDataSource dataSource=new HikariDataSource(config);
	return dataSource;
}

@Bean(destroyMethod = "close")
public HikariDataSource slaveDataSource() {
	
	HikariConfig config=new HikariConfig();
	config.addDataSourceProperty("cachePrepStmts", "true");
	config.addDataSourceProperty("prepStmtCacheSize", "250");
	config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
	config.setMaximumPoolSize(maxPool);
	config.setJdbcUrl(url);
	config.setDriverClassName(driver);
	config.setUsername(user);
	config.setPassword(password);
	HikariDataSource dataSource=new HikariDataSource(config);
	return dataSource;
}

@Primary
@Bean(name = "dataSource")
@DependsOn(value = {"masterDataSource","slaveDataSource"})
public RoutingDataSource getDataSource() {
	RoutingDataSource routingDataSource=new RoutingDataSource();
	Map<Object, Object> targetDataSources =new HashMap<Object,Object>();
	targetDataSources.put(DbType.MASTER, masterDataSource());
	targetDataSources.put(DbType.SLAVE, slaveDataSource());
	routingDataSource.setTargetDataSources(targetDataSources);
	routingDataSource.setDefaultTargetDataSource(masterDataSource());
	return routingDataSource;
	
}`

` @bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() throws IllegalArgumentException, NamingException, SQLException {
LocalContainerEntityManagerFactoryBean entityManagerFactory =
new LocalContainerEntityManagerFactoryBean();
entityManagerFactory.setDataSource(dataSource);
entityManagerFactory.setPackagesToScan( env.getProperty("entitymanager.packagesToScan"));
// Vendor adapter
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
entityManagerFactory.setJpaVendorAdapter(vendorAdapter);
entityManagerFactory.setPersistenceUnitName("mysql");
entityManagerFactory.setPackagesToScan("com.data.model");
// Hibernate properties
Properties additionalProperties = new Properties();
additionalProperties.put(
"hibernate.dialect",
env.getProperty("hibernate.dialect"));
LOGGER.info("show sql parameter value is [{}]",showSql);
additionalProperties.put(
"hibernate.show_sql",showSql);
additionalProperties.put(
"hibernate.hbm2ddl.auto",
env.getProperty("hibernate.hbm2ddl.auto"));

    additionalProperties.put(
	        "hibernate.ejb.naming_strategy", 
	        env.getProperty("hibernate.naming-strategy"));
	    
    additionalProperties.put("hibernate.id.new_generator_mappings",
			env.getProperty("hibernate.id.new_generator_mappings"));

    entityManagerFactory.setJpaProperties(additionalProperties);
    
    return entityManagerFactory;
  }`

Repository class
`
@repository
public interface BillerCategoryRepository extends JpaRepository<BillerCategory, Long>{

@ReadOnlyConnection
@Transactional(readOnly = true)
public Optional<BillerCategory> findByBillerCategoryId(@Param("billerCategoryId")Long billerCategoryId);
@ReadOnlyConnection
@Transactional(readOnly = true)
public Optional<BillerCategory> findOneByBillerCategoryName(@Param("billerCategoryName")String billerCategoryName);

}
`
In application log I am getting null db type.

INFO [2019-09-18 16:01:17,222] [uuid: {3639ecde6ad0402cb77ca64088ef3a5c} ] c.p.n.c.**RoutingDataSource**: **db look up key null**

Please suggest any solution.