Query tables for multiple tenants (same table name)

Question

I have a system in which there is an unknown number of tenants (different database instances on the same database server). I have a working code in which the user logs in, and the correct tenant is selected, and I can read the configuration table for this tenant.

I want the application to run through all tenants during the launch, read the configuration and act on it. Prior to migrating to Spring Data JPA (with hibernate support), this was easy since I connected to each database instance separately.

I don’t think I can use Spring @Transactional as it establishes only one connection.

I hope to use the same repository interface with the same bean as this works when I only need to hit one tenant at a time.

I have a class MultiTenantConnectionProviderImpl extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl that will give me the data source for this tenant, but I'm not sure how to use this in the method of the @Service class?

Answer 1

I'm not sure if I should delete my previous answer, edit it or what. Therefore, if MOD can tell me the correct procedure, I will be happy to perform.

Turns out I was right about using @Transactional , which won't work. I ended up using a custom implementation and AbstractRoutingDataSource to replace my MultiTenantConnectionProviderImpl and CurrentTenantResolverImpl . I use this new data source instead of setting hibernate.multiTenancy hibernate.multi_tenant_connection_provider and hibernate.tenant_identifier_resolver

My temporary override class is as follows:

 public class MultitenancyTemporaryOverride implements AutoCloseable { static final ThreadLocal<String> tenantOverride = new NamedThreadLocal<>("temporaryTenantOverride"); public void setCurrentTenant(String tenantId) { tenantOverride.set(tenantId); } public String getCurrentTenant() { return tenantOverride.get(); } @Override public void close() throws Exception { tenantOverride.remove(); } } 

My TenantRoutingDataSource looks like this:

 @Component public class TenantRoutingDataSource extends AbstractDataSource implements InitializingBean { @Override public Connection getConnection() throws SQLException { return determineTargetDataSource().getConnection(); } @Override public Connection getConnection(String username, String password) throws SQLException { return determineTargetDataSource().getConnection(username, password); } @Override public void afterPropertiesSet() throws Exception { } protected String determineCurrentLookupKey() { Authentication authentication = SecurityContextHolder.getContext().getAuthentication(); String database = "shared"; if (authentication != null && authentication.getPrincipal() instanceof MyUser) { MyUser user = (MyUser) authentication.getPrincipal(); database = user.getTenantId(); } String temporaryOverride = MultitenancyTemporaryOverride.tenantOverride.get(); if (temporaryOverride != null) { database = temporaryOverride; } return database; } protected DataSource determineTargetDataSource() { return selectDataSource(determineCurrentLookupKey()); } public DataSource selectDataSource(String tenantIdentifier) { //I use C3P0 for my connection pool PooledDataSource pds = C3P0Registry.pooledDataSourceByName(tenantIdentifier); if (pds == null) pds = getComboPooledDataSource(tenantIdentifier); return pds; } private ComboPooledDataSource getComboPooledDataSource(String tenantIdentifier) { ComboPooledDataSource cpds = new ComboPooledDataSource(tenantIdentifier); cpds.setJdbcUrl("A JDBC STRING HERE"); cpds.setUser("MyDbUsername"); cpds.setPassword("MyDbPassword"); cpds.setInitialPoolSize(10); cpds.setMaxConnectionAge(10000); try { cpds.setDriverClass("com.informix.jdbc.IfxDriver"); } catch (PropertyVetoException e) { throw new RuntimeException("Weird error when setting the driver class", e); } return cpds; } } 

Then I simply provide my custom data source to my Entity Manager factory bean when it is created.

 @Service public class TestService { public void doSomeGets() { List<String> tenants = getListSomehow(); try(MultitenancyTemporaryOverride tempOverride = new MultitenancyTemporaryOverride()) { for(String tenant : tenants) { tempOverride.setCurrentTenant(tenant); //do some work here, which only applies to the tenant } } catch (Exception e) { logger.error(e); } } } 

Answer 2

I think I'm close to one solution, but I'm not too happy with that. I would like a better answer.

EDITED: It turns out that this does not quite work, since Spring or Hibernate appears only to call only the current tenant identifier identifier, and not for every call to the @Transactional method

This is due to a change in the implementation of CurrentTenantIdentifierResolver to not only look at the current user (if installed), to get his current tenant ID (before the developer, to figure out how to set it) ... he should also look at the local stream variable to Find out if an override is set.

Using this approach, I can temporarily set tenantID ... call the service method with my multi-tenant transaction manager, and then get the data.

My test service:

 @Service public class TestService { @Transactional(transactionManager = "sharedTxMgr") public void doSomeGets() { List<String> tenants = getListSomehow(); try(MultitenancyTemporaryOverride tempOverride = new MultitenancyTemporaryOverride()) { for(String tenant : tenants) { tempOverride.setCurrentTenant(tenant); doTenantSpecificWork(); } } catch (Exception e) { logger.error(e); } } @Transactional(transactionManager = "tenantSpecificTxMgr") public void doTenantSpecificWork() { //do some work here, which only applies to the tenant } } 

My class that wraps the ThreadLocal installation by implementing AutoCloseable to make sure the variable is cleared.

 public class MultitenancyTemporaryOverride implements AutoCloseable { static final ThreadLocal<String> tenantOverride = new ThreadLocal<>(); public void setCurrentTenant(String tenantId) { tenantOverride.set(tenantId); } public String getCurrentTenant() { return tenantOverride.get(); } @Override public void close() throws Exception { tenantOverride.remove(); } } 

My tenant's implementation using local thread

 public class CurrentTenantIdentifierResolverImpl implements CurrentTenantIdentifierResolver { @Override public String resolveCurrentTenantIdentifier() { Authentication authentication = SecurityContextHolder.getContext().getAuthentication(); logger.debug(ToStringBuilder.reflectionToString(authentication)); String database = "shared"; if (authentication != null && authentication.getPrincipal() instanceof MyUser) { MyUser user = (MyUser) authentication.getPrincipal(); database = user.getTenantId(); } String temporaryOverride = MultitenancyTemporaryOverride.tenantOverride.get(); if(temporaryOverride != null) { database = temporaryOverride; } return database; }