JdbcTemplate - Add support for multitenancy [SPR-17342]

[spring-projects-issues]

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

[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.

[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

[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 ?

[mp911de]

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

[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.

[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.

[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.