robert-niestroj opened SPR-17342 and commented

Please add support for multitenancy in JdbcTemplate.

Scenario: i have an app which uses JPA for CRUD, but for reporting uses native queries executed via JdbcTemplate. Hibernate in our app is working in multitenancy mode with the strategy per schema on MySQL. I tried several things but could not implement similar multitenancy in JdbcTemplate.

Basiclly i do connection.createStatement("USE " + tenantSchema ); in hibernates multitenancyConnectionProvider.

I would like to do something similar in JdbcTemplate.

Reference URL: https://stackoverflow.com/questions/52541380/spring-jdbctemplate-how-to-prepend-every-query

Comment From: gokhanoner

any update on this one?

One thing to mention, JDBC connection has setCatalog method, which can be used to change schema/database, since each DB driver interpret it differently based on to their model, MySQL call SET <schema>, PostgreSQL changes DB etc. This will be more generic solution. Like Hibernate, if Spring has a way of defining ConnectionProvider and TenantResolver for JdbcTemplate, this should work. I assume some changes needed internally as well, since Spring wrap original connection for Transaction tracking etc.

Comment From: brankoiliccc

So this is just ridiculous. Basically JdbcTemplate is hard coded to be single tenant. Because of this we do not even use it, but there is a catch ! Spring team uses it so we have to write ton of our own implementations already provided by spring to not use JdbcTemplate. geez

Comment From: ashwinbks83

I did something like this. 

@Component
@Scope(scopeName="prototype")
public class MultiTenantJdbcTemplate extends JdbcTemplate {


    public MultiTenantJdbcTemplate() {

    }

    @Override
    public void afterPropertiesSet() {
    }

    @Override
    public DataSource getDataSource() {
        System.out.println("getDataSource");
        if(super.getDataSource() != DatabaseContextHolder.getCurrentTenantDataSource()) {
            this.setDataSource(DatabaseContextHolder.getCurrentTenantDataSource());
        }
        // TODO Auto-generated method stub
        return super.getDataSource();
    }

}

Autowired this in the dao class. Any thoughts ?

Comment From: mp911de

Any reason you're not using AbstractRoutingDataSource which is built to resolve a DataSource during runtime?

Comment From: ashwinbks83

@mp911de It was my bad, i made a mistake in the dao implementation and ended up writing all the code that comes with extending JdbcDaoSupport.

Comment From: spring-projects-issues

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.

Comment From: spring-projects-issues

Closing due to lack of requested feedback. If you would like us to look at this issue, please provide the requested information and we will re-open the issue.