How to dynamically change database / directory when using Spring JdbcTemplate

Question

Consider a situation where all client data is stored in its own database / directory, and all such databases are stored in a single RDBMS (client data). Master data (for example, clients, ...) is stored in another DBMS (master data). How can we dynamically access a specific database in RDBMS client data using JdbcTemplate?

Defining DataSourceclient data for each database in RDBMS, and then dynamically selecting one of those proposed here , is not an option for us, since databases are created and destroyed dynamically.

I basically need something like JDBC Connection.setCatalog(String catalog), but I haven't found anything like it in Spring JdbcTemplate.

Answer 1

Perhaps you can wrap the data source DelegatingDataSourceto call setCatalog()in getConnection()and use the wrapped data source to JdbcTemplatecreate:

class MyDelegatingDS extends DelegatingDataSource {
  private final String catalogName;

  public MyDelegatingDS(final String catalogName, final DataSource dataSource) {
    super(dataSource);
    this.catalogName = catalogName;
  }

  @Override
  public Connection getConnection() throws SQLException {
    final Connection cnx = super.getConnection();
    cnx.setCatalog(this.catalogName);
    return cnx;
  }

  // maybe also override the other getConnection();
}

// then use like that: new JdbcTemplate(new MyDelegatingDS("catalogName", dataSource)); 

Answer 2

You can access Connectionfrom JdbcTemplate:

jdbcTemplate.getDataSource().getConnection().setCatalog(catalogName);

You only need to make sure that the database driver supports this functionality.

Answer 3

jdbcTemplate.getDataSource().getConnection().setSchema(schemaName) 

, postgres. @m3th0dman , . , , .