CODEWITHSUNDEEP
javamysqlspring-dataspring-jdbcjdbctemplate
pgiecek
pgiecek

Reputation: 8220

How to dynamically change database/catalog when using Spring JdbcTemplate

Consider a situation where all client data is stored in its own database/catalog and all such databases are stored in a single RDBMS (client-data). Master data (e.g. clients, ...) is kept in another RDBMS (master-data). How can we dynamically access a particular database in client-data RDBMS by means of JdbcTemplate?

Defining DataSource for each database in client-data RDBMS and then dynamically select one as suggested here is not an option for us since the databases are created and destroyed dynamically.

I would basically need something like JDBC's Connection.setCatalog(String catalog) but I have not found anything like that available in Spring JdbcTemplate.

Upvotes: 8

Views: 10154

Answers (3)

user180100
user180100

Reputation:

Maybe you could wrap the datasource with DelegatingDataSource to call setCatalog() in getConnection() and use the wrapped datasource on JdbcTemplate creation: 

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));

Upvotes: 9

stratagem
stratagem

Reputation: 545

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

Was what I needed for switching schema using postgres. Props to @m3th0dman for putting me on the right track. I'm only adding this in case others find this answer searching for switching schema as I was.

Upvotes: 0

Random42
Random42

Reputation: 9159

You can access the Connection from JdbcTemplate:

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

You'll only have to make sure the database driver supports this functionality.

Upvotes: 6

Related Questions