AbstractRoutingDataSource doesn't change connection

Question

I use AbstractRoutingDataSource to change data source dynamically and ThreadLocal to set up currentLookupKey. It works nice when I use only one data source per http request. I use JpaRepository

@Component
@Primary
public class RoutingDataSource extends AbstractRoutingDataSource {

    @Autowired
    private DatabaseMap databaseMap;

    @Override
    public void afterPropertiesSet() {
        setTargetDataSources(databaseMap.getSourcesMap());
        setDefaultTargetDataSource(databaseMap.getSourcesMap().get("DEFAULT"));
        super.afterPropertiesSet();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return DatabaseContextHolder.getDatabaseType();
    }

}

public class DatabaseContextHolder {

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

    public static void setDatabaseType(String string) {
        contextHolder.set(string);
    }

    public static String getDatabaseType() {
        return (String) contextHolder.get();
    }

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

When I try to get data in my REST controller I get data only from one database.

Some code in my REST controller

DatabaseContextHolder.setDatabaseType("db1");
//here I get data from db1 as expected
//I use JpaRepository
DatabaseContextHolder.clearDatabaseType();
DatabaseContextHolder.setDatabaseType("db2");
//here I should get data from db2 but get from db1

I tried to debug and it looks like Spring obtains data source only once in http request.

This method is called only once.

@Override
public Connection getConnection() throws SQLException {
    return determineTargetDataSource().getConnection();
}

Is there any way to force Spring to change data source.

Answer 1

Your problem could be related with transaction delimitation.

When you define a @Transactional annotation in your code, Spring will create on your behalf all the stuff necessary to begin and end, and commiting or rollback if required, a transaction.

As you can see in the doBegin method in the source code of the DataSourceTransactionManager class - the same applies to other transaction managers - , Spring obtains a Connection when the transaction is initialized - this is why the method getConnection is invoked only once - , and it will reuse that connection across all the underlying operations against the database within that transaction (it makes sense for ACID preservation).

So, if you need to connect to several data sources within the same request processing, you can define different methods in you service code, every one annotated with a @Transactional annotation, and change the underlying data source as you require before invoke them:

DatabaseContextHolder.setDatabaseType("db1");
// Invoke a service method annotated with @Transactional
// It can use the underlying JpaRepositories that you need
DatabaseContextHolder.clearDatabaseType();
DatabaseContextHolder.setDatabaseType("db2");
// Invoke again another (or the same, what you need) service method
// annotated with @Transactional, You should get data from db2 this time