Spring dynamically choosing between data sources (alternative to ThreadLocal)

I’ve read about AbstractRoutingDataSource and the standard ways to bind a datasource dynamically in this article:

public class CustomerRoutingDataSource extends AbstractRoutingDataSource {

   @Override
   protected Object determineCurrentLookupKey() {
      return CustomerContextHolder.getCustomerType();
   }
} 

It uses a ThreadLocal context holder to “set” the DataSource:

public class CustomerContextHolder {

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

   public static void setCustomerType(CustomerType customerType) {
      Assert.notNull(customerType, "customerType cannot be null");
      contextHolder.set(customerType);
   }

   public static CustomerType getCustomerType() {
      return (CustomerType) contextHolder.get();
   } 

   // ...
}

I have a quite complex system where threads are not necessarily in my control, say:

• Scheduled EJB reads a job list from the database
• For each Job it fires a Spring (or Java EE) batch job.
• Each job have its origin and destination databases (read from a central database).
• Multiple jobs will run in parallel
• Jobs may be multithreaded.
• ItemReader will use the origin data source that was set for that specific job (origin data source must be bound to some repositories)
• ItemWriter will use the destination data source that was set for that specific job (destination data source must also be bound to some repositories).

So I’m feeling somewhat anxious about ThreadLocal, specially, I’m not sure if the same thread will be used to handle multiple jobs. If that happens origin and destination databases may get mixed.

How can I “store” and bind a data source dynamically in a safe way when dealing with multiple threads?

Answer

I could not find a way to setup Spring to play nice with my setup and inject the desired DataSource, so I’ve decided to handle that manually.

Detailed solution:

1. I changed my repositories to be prototypes so that a new instance is constructed every time that I wire it:

   @Repository
   @Scope(BeanDefinition.SCOPE_PROTOTYPE)
   

2. I’ve introduced new setDataSource and setSchema methods in top level interfaces / implementations that are supposed to work with multiple instances / schemas.

3. Since I’m using spring-data-jdbc-repository my setDataSource method simple wraps the DataSource with a new JdbcTemplate and propagate the change.

   setJdbcOperations(new JdbcTemplate(dataSource));
   

4. My implementation is obtaining the DataSources directly from the application server:

   final Context context = new InitialContext();
   final DataSource dataSource = (DataSource) context.lookup("jdbc/" + dsName);
   

5. Finally, for multiples schemas under the same database instance, I’m logging in with a special user (with the correct permissions) and using a Oracle command to switch to the desired schema:

   getJdbcOperations().execute("ALTER SESSION SET CURRENT_SCHEMA = " + schema);
   

While this goes against the Dependency inversion principle it works and is handling my concurrency requirements very well.