7

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.

Community
  • 1
  • 1
pgiecek
  • 7,970
  • 4
  • 39
  • 47
  • Maybe you could wrap the datasource with `DelegatingDataSource` to call `setCatalog()` in `getConnection()`. –  Oct 17 '14 at 08:27
  • Hm, probably some kind of wrapper could be the solution. Now the thing is how to propagate the respective catalog to such a wrapper. Anyway good point. – pgiecek Oct 17 '14 at 08:54
  • On jdbcTemplate creation: `new JdbcTemplate(new MyDelegatingDS(dataSource, "catalogName"));` something like that –  Oct 17 '14 at 09:36
  • Yes, sure, I meant in the context of a whole solution but it is out of the scope of the question itself. Anyway, don't you wanna summarize your comments as an answer? I think it deserves to be accepted since at the moment there is probably not a better solution than `DataSource` wrapper. – pgiecek Oct 17 '14 at 12:33
  • Which DBMS are you using? –  Oct 17 '14 at 13:17
  • I am using MySQL database. – pgiecek Oct 17 '14 at 13:18

3 Answers3

9

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));
Tom Howard
  • 6,516
  • 35
  • 58
  • You may want to add a try/catch around cnx.setCatalog() and close the connection if an Exception occurs. Otherwise you will leak connections (for example if the catalog doesn't exist). – Gert-Jan Oct 17 '22 at 10:54
6

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.

Random42
  • 8,989
  • 6
  • 55
  • 86
  • Yes, I can use this approach but I do not want to work with JDBC (`Connection`, `Statement`, `ResultSet`, ...) directly. It is useless to use `JdbcTemplate` this way. The thing is that I would like to use `JdbcTemplate`'s interface to access data. – pgiecek Oct 17 '14 at 08:45
  • @pgiecek: the point of using JdbcTemplate is to avoid the boiler plate code that you need when you use plain JDBC. If a solution is simple and easy using plain JDBC then why not use it? It can't get any simpler than calling `setCatalog()` –  Oct 17 '14 at 13:18
  • Yes, I agree. I like `JdbcTemplate` because, as you pointed out, it enables to avoid JDBC boiler plate code. And just because of a tiny connection adjustment, I do not want to use JDBC directly. I think that @RC's solution is a good compromise. – pgiecek Oct 17 '14 at 13:27
0
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.

stratagem
  • 525
  • 6
  • 7
  • I am trying the same but it is not working for me. I tried printing the schema before and after this statement, and they return the same result - public. Is there anything else you did to make it work? – sg1291 Apr 04 '22 at 11:36