Changing the catalog on a JdbcTemplate (with Spring)

I had a requirement to change the catalog (i.e. database) with JdbcTemplate. I searched around for a while for an answer and, as is often the case, found a solution scattered across several Stack Overflow and other posts. My configuration is:

• Spring Framework 4.2.5.RELEASE
• spring-jdbc (for JdbcTemplate)
• tomcat-jdbc 8.0.18 (for org.apache.tomcat.jdbc.pool.DataSource)

The key to making this all work is to to extend Spring's DelegatingDataSource and override getConnection() to set the catalog you want:

public class CatalogSpecificDataSource extends DelegatingDataSource {

    private final String catalog;

    public CatalogSpecificDataSource(DataSource orig, String catalog) {
        super(orig);
        this.catalog = catalog;
    }

    @Override
    public Connection getConnection() throws SQLException {
        Connection conn = super.getConnection();
        conn.setCatalog(catalog);
        return conn;
    }
}

The next problem is cleanup: how do you ensure that the connection is reset to the default catalog once it's placed into the pool? The answer is in the tomcat connection pool configuration:

    PoolProperties p = new PoolProperties();
    p.setDefaultCatalog("customers");
    /*
     * set your connection properties here - driver, connection string, etc.
     * then, when you're done with that, add the interceptors
     */
    p.setJdbcInterceptors(
        org.apache.tomcat.jdbc.pool.interceptor.ConnectionState.class.getName() + ";" +
        org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer.class.getName());
    DataSource datasource = new DataSource();
    datasource.setPoolProperties(p);

The ConnectionState interceptor remembers if you changed the catalog and will set it back. When you create your JdbcTemplate you can now:

    JdbcTemplate t = new CatalogDataSource(realDataSource, "someOtherCatalog");

or use Spring's idea of assisted injection. To do that:

Step 1. Create a factory interface

public interface CatalogSpecificDataSourceFactory {
    javax.sql.DataSource getDatasource(String catalog);
}

Step 2. Create a spring bean configuration:

    @Bean
    @Scope(BeanDefinition.SCOPE_PROTOTYPE)
    public CatalogSpecificDataSourceFactory getCatalogSpecificDatasourceFactory() {
        return catalog -> new CatalogSpecificDataSource(getCatalogSpecificDatasource(), catalog);
    }

Step 3. When you want to use it, inject a CatalogSpecificDataSourceFactory then use it to create your Datasource:

    DataSource ds = catalogSpecificDataSoruceFactory.getCatalogSpecificDatasource("booger");
    JdbcTemplate t = new JdbcTemplate(ds);

Or, if you want to get even fancier, set up another assisted factory. In my case, following the assisted injection pattern once again:

    public interface BatchMissionWriterFactory {
        BatchMissionWriter create(String databaseName);
    }

    /* then, in the config: */
    @Bean
    @Scope(BeanDefinition.SCOPE_PROTOTYPE)
    public BatchWidgetWriterFactory getBatchMissionWriterFactory()
    {
        return catalog -> new BatchWidgetWriterFactory (getCatalogDataSourceFactory().getCatalogSpecificDatasource(catalog));
    }

As usual, hope this is helpful to someone - feedback welcome.