Using two datasources in Spring Boot

Question

I'm using Spring Boot 1.3.3 in my project with one database, now i want to use two databases with the same schema but different connections.

I want to use same repositories, entities and find the way to tell spring which datasource i want to use depending on the situation.

Answer 1

If anyone has this problem, i've found the solution:

First your application.properties should look like this:

datasource:
primary:
    url: jdbc:mysql://localhost:3306/primary_db
    username: your_username
    password: your_password
    driver-class-name: com.mysql.jdbc.Driver
secondary:
    url: jdbc:mysql://localhost:3306/secondary_db
    username: your_username
    password: your_password
    driver-class-name: com.mysql.jdbc.Driver

After that, you have to create an enum with your databases:

public enum Database {
    PRIMARY,
    SECONDARY
}

Then, you create a ThreadLocal:

public class DatabaseThreadContext {

    private static final ThreadLocal<Database> current = new ThreadLocal<>();

    public static void setCurrentDatabase(Database database) {
        current.set(database);
    }

    public static Object getCurrentDatabase() {
        return current.get();
    }

}

Here comes the magic, you have to use AbstractRoutingDataSource which was implemented in Spring 2 back in 2007:

public class RoutingDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return DatabaseThreadContext.getCurrentDatabase();
    }

}

Finally inject a Configuration in your Spring Boot App:

@Configuration
public class DatabaseRouter {

    @Bean
    @ConfigurationProperties(prefix="datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix="datasource.secondary")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @Primary
    public DataSource dataSource() {
        Map<Object, Object> targetDatasources = new HashMap<Object, Object>(){{
            put(Database.SECONDARY, secondaryDataSource());
            put(Database.PRIMARY, primaryDataSource());
        }};
        RoutingDataSource routingDataSource = new RoutingDataSource();
        routingDataSource.setDefaultTargetDataSource(primaryDataSource());
        routingDataSource.setTargetDataSources(targetDatasources);
        routingDataSource.afterPropertiesSet();
        return routingDataSource;
    }

}

In every request, if you want to change between your databases you just use this function: DatabaseThreadContext.setCurrentDatabase(Database.PRIMARY);.

Also, you can have more than two databases at the same time.