Upul Doluweera Upul Doluweera - 10 days ago 5
Java Question

Dual DB Connectivity in Spring?

I have a web application which uses an Oracle DB as the back end data source. This Oracle DB instance is being refreshed daily and while its being refreshed we have to switch to another DB (a copy of the original DB) seamlessly and while the other DB (copy) is being refreshed it should fall back to the original Oracle DB.

We are using Spring JDBC.

What is the best way to achieve this ?

Answer

Since DevOps related solution was not agreed by the management I had to go ahead with AbstractRoutingDataSource based implementation.

The solution is simple, all you have to do is extends the AbstractRoutingDataSource and provide a routing criteria by overriding determineCurrentLookupKey() method.

Your Customized Data source will look like this

public class ResourceAwareMyDataSource extends AbstractRoutingDataSource {

    private static final Logger logger = LoggerFactory.getLogger(ResourceAwareMyDataSource.class);

    @Override
    protected Object determineCurrentLookupKey() {

        try {
            //Check whether the primary DB is active 
            if(primaryIsActive()) {
                return "Primary"
            }
        } catch (Exception e) {
            logger.error("Error occurred when checking  primary",e);
            logger.info("Switching back to Secondary..");
            return "Secondary";
        }
        //Primary is inactive
        logger.info("Primary is Inactive, Switching back to Secondary");
        return "Secondary";
    }
}

This will be your Java configuration for the above data source

@Bean
@Qualifier("myCustomizedDataSource")
public DataSource resourceAwareMyDataSource() {

    ResourceAwareMyDataSource dataSource = new ResourceAwareMyDataSource ();
    Map<Object, Object> targetDataSourcesMap = new HashedMap();
    targetDataSourcesMap.put("Primary", getPrimaryDataSource());
    targetDataSourcesMap.put("Secondary", getSecondaryDataSource());
    dataSource.setTargetDataSources(targetDataSourcesMap);
    return dataSource;
}

As usual your primary and secondary data source will look like this, Here I am providing tow basic data sources to connect to primary and secondary

public DataSource getPrimaryDataSource() {
    BasicDataSource dataSource = new BasicDataSource();
    dataSource.setDriverClassName(...);
    dataSource.setUrl(...);
    dataSource.setUsername(...);
    dataSource.setPassword(...);
    dataSource.setValidationQuery(...);
    return dataSource;
}

public DataSource getSecondaryDataSource() {
    BasicDataSource dataSource = new BasicDataSource();
    dataSource.setDriverClassName(...);
    dataSource.setUrl(...);
    dataSource.setUsername(...);
    dataSource.setPassword(...);
    dataSource.setValidationQuery(....);
}