Sunday, May 8, 2016

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) {
        this.catalog = catalog;

    public Connection getConnection() throws SQLException {
        Connection conn = super.getConnection();
        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();
     * set your connection properties here - driver, connection string, etc.
     * then, when you're done with that, add the interceptors
        org.apache.tomcat.jdbc.pool.interceptor.ConnectionState.class.getName() + ";" +
    DataSource datasource = new DataSource();

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:

    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: */
    public BatchWidgetWriterFactory getBatchMissionWriterFactory()
        return catalog -> new BatchWidgetWriterFactory (getCatalogDataSourceFactory().getCatalogSpecificDatasource(catalog));

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