Using same jdbcTemplate for two different schemas

Question

I have 2 datasources say dataSourceA and dataSourceB but based upon few calculations, I need to execute the same query in different schemas. Also, it is going to be executed in either of the schemas.

so, at DAO layer , I have one setDataSource() method which is @autowired to the dataSourceA, thus,returning the JDBCTemplate with former DataSource. How can I implement the dataSourceB changes using the same JDBCTemplate as it will be difficult to change at every DAO layer as entire application change will be required.

why don't you use schema name in your queries instead of define schema name in your datasource? — Zico, Aug 07 '17 at 09:28
Go through this link. Not having exact answer but you may get some idea. http://zetcode.com/articles/springbootqualifier/ — Prasath, Aug 07 '17 at 09:32

score 1 · Accepted Answer · answered Aug 07 '17 at 09:36

You could you inject both datasources and select the datasource inside your method according to your logic:

public class SomeDaoImpl implements SomeDao {
    private final JdbcTemplate jdbcTemplateA;
    private final JdbcTemplate jdbcTemplateB;

    @Autowired
    public SomeDaoImpl(JdbcTemplate jdbcTemplateA, JdbcTemplate jdbcTemplateB) {
        // injecting both JdbcTemplate instances
        this.jdbcTemplateA = jdbcTemplateA;
        this.jdbcTemplateB = jdbcTemplateB;
    }

    public void businessLogicMethod(...) {
        // choosing the actual template to be used according to your logic
        JdbcTemplate jdbcTemplate = chooseTemplate(...);
        // now using the template to execute a query
        jdbcTemplate.execute(...);
    }
}

Another option would be to instantiate two SomeDaoImpl instances and inject one JdbcTemplate into each of them, and select the DAO instance in your service layer.

But both these solutions have a flaw: transaction is usually initiated in the service layer (with an interceptor, for example), and it has no idea that you are going to route your requests to another datasource; so it could happen that a transaction starts on one datasource, but the query is executed on another one.

So the clearest solution would be to go one level up and instantiate 2 services, in each of them DAOs with different JdbcTemplate instances. Of course, 2 transaction managers will have to be configured and carefully wired (for example, via @Transactional("transactionManagerA")). More information on this here Spring - Is it possible to use multiple transaction managers in the same application?

Using same jdbcTemplate for two different schemas

1 Answers1