Query tables across multiple tenants (same table name)

Question

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

I want the application at start time to loop through all tenants, read the configuration and act upon it. Prior to moving to Spring Data JPA (backed by hibernate) this was easy as I was connecting to each database instance separately.

I don't think I can use Spring's @Transactional as it only sets up a single 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 do have a class MultiTenantConnectionProviderImpl extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl that will give me a dataSource for a given tenant, but I'm not sure how to use that in a @Service class's method?

Answer 1

I'm not sure if I should remove my previous answer, edit it or what. So if a MOD can let me know proper procedure I'll be happy to comply.

Turns out I was right about the use of @Transactional not going to work. I ended up using an custom implementation of and AbstractRoutingDataSource to replace my MultiTenantConnectionProviderImpl and CurrentTenantResolverImpl. I use this new data source instead of setting the hibernate.multiTenancy hibernate.multi_tenant_connection_provider and hibernate.tenant_identifier_resolver

My temporary override class looks like this:

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 just provide my custom data source to my Entity Manager factory bean when creating it.

@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);
        }
    }
}