- 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.
This is some exciting stuff? Hey have you heard you can make $500/week working at home? I heard that from my sister's uncle's wife's aunt's cousin!
ReplyDeleteI LOVE serial Haikus! Nicely done!
ReplyDeleteSome of your gobbeldeygook is stickin off the right side. Otherwise, keep up the good work! :)
ReplyDelete