- 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.
