How to fetch tenant identifier stored in a table of default schema?

Question

I am working to enable multitenancy in my spring based application using hibernate. I created a custom implementation of CurrentTenantIdentifierResolver and overidden the resolveCurrentTenantIdentifier() method to determine the tenant identifier. The application works fine when I provide a hard-coded tenant identifier.

But then a requirement came up to fetch the tenant identifier from a table in default schema of database based on a value in request header. I searched regarding this at many places and did some hit and trials but with little success.

Any help regarding this would be greatly appreciated. Please let me know what all information I need to provide for better understanding of the problem scenario.

CustomTenantIdentifierResolver.java

public class CustomTenantIdentifierResolver implements CurrentTenantIdentifierResolver {

     public static final String DEFAULT_TENANT_SCHEMA = "public";

    @Override
    public String resolveCurrentTenantIdentifier() {
        try {
            Provider<TenantRequestContext> tenantProvider = SpringContext
                .getBean(Provider.class);
            if (tenantProvider == null) {
                return DEFAULT_TENANT_SCHEMA;
            } else {
                TenantRequestContext tenantRequestContext = tenantProvider
                    .get();
                String tenantId = tenantRequestContext.getTenantIdValue();
                String tenantSchema = tenantRequestContext.getTenantSchema(tenantId);
                return tenantSchema;
            }
        } catch (Exception ex) {
            return DEFAULT_TENANT_SCHEMA;
        }
        // return "myschema";
    }

    @Override
    public boolean validateExistingCurrentSessions() {
        return true;
    }

}

TenantRequestContextImpl.java

@Component  
@Scope(value = WebApplicationContext.SCOPE_REQUEST, proxyMode = ScopedProxyMode.TARGET_CLASS)  
public class TenantRequestContextImpl  implements TenantRequestContext{

    @Autowired
    private TenantReadService tenantReadService;

    @Override
    public String getTenantIdValue() {
        String tenantId =((ServletRequestAttributes)RequestContextHolder.currentRequestAttributes())
            .getRequest().getHeader("tenantId");
        return tenantId;
    }

    @Override
    public String getTenantSchema(String tenantId) {
        Tenant tenant = tenantReadService.findById(Integer.parseInt(tenantId));
        return tenant.getTenantSchemaName();
    }
}

TenantReadServiceImpl.java

@Repository
public class TenantReadServiceImpl implements TenantReadService {

    @Autowired
    private SessionFactory defaultSessionFactory;

    public TenantReadServiceImpl() {

    }

    public TenantReadServiceImpl(SessionFactory defaultSessionFactory) {
        this.defaultSessionFactory = defaultSessionFactory;
    }

    @Override
    @Transactional
    public Tenant findById(Integer tenantId) {
        String hql = "from Tenant where id=" + tenantId;
        Query query = defaultSessionFactory.getCurrentSession().createQuery(hql);
        Tenant tenant = (Tenant) query.uniqueResult();
        defaultSessionFactory.getCurrentSession().clear();
        return tenant;
    }
}

MultitenancyPlatformConfig.java

@Configuration
@EnableTransactionManagement
@ComponentScan("com.mypackage")
public class MultitenancyPlatformConfig {

    @Bean(name="defaultDataSource")
    public DataSource dataSource() {
        final JndiDataSourceLookup dsLookup = new JndiDataSourceLookup();
        DataSource dataSource = dsLookup.getDataSource(java:comp/env/jdbc/myDataSource);
        return dataSource;
    }

    @Autowired
    @Bean(name = "defaultSessionFactory")
    public SessionFactory getSessionFactory(DataSource defaultDataSource) {
        LocalSessionFactoryBuilder sessionBuilder = new LocalSessionFactoryBuilder(
            defaultDataSource);
        sessionBuilder.addAnnotatedClasses(Tenant.class);
        sessionBuilder.addProperties(hibProperties());
        return sessionBuilder.buildSessionFactory();
    }

    private Properties hibProperties() {
       Properties properties = new Properties();
       properties.put("hibernate.format_sql",
            "true");
       properties.put("hibernate.dialect",
            "org.hibernate.dialect.PostgreSQLDialect");
       properties.put("hibernate.default_schema", "public");
       return properties;
    }

    @Autowired
    @Bean(name = "tenantReadService")
    public TenantReadService getTenantReadService(SessionFactory defaultSessionFactory) {
        return new TenantReadServiceImpl(defaultSessionFactory);
    }
}

MyPlatformConfig.java

@Configuration
@EnableTransactionManagement
@ComponentScan("com.mypackage")
@EnableJpaRepositories("com.mypackage.repository")
    public class MyPlatformConfig {

    @Bean
    public DataSource dataSource() {
        final JndiDataSourceLookup dsLookup = new JndiDataSourceLookup();
        DataSource dataSource = dsLookup.getDataSource("java:comp/env/jdbc/ihubDataSource");
        return dataSource;
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        vendorAdapter.setGenerateDdl(false);
        vendorAdapter.setDatabase(Database.POSTGRESQL);
        vendorAdapter.setDatabasePlatform("org.postgresql.Driver");

        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
        factory.setJpaVendorAdapter(vendorAdapter);
        factory.setPackagesToScan("com.mypackage.entity");
        factory.setJpaProperties(hibProperties());
        return factory;
    }

    private Properties hibProperties() {
        Properties properties = new Properties();
        properties.put("hibernate.dialect","org.hibernate.dialect.PostgreSQLDialect");
        properties.put("hibernate.format_sql","true");
        properties.put("hibernate.tenant_identifier_resolver"," com.mypackage.tenantresolvers.CustomTenantIdentifierResolver");
        properties.put("hibernate.multi_tenant_connection_provider", "com.mypackage.connectionproviders.MultiTenantConnectionProviderImpl");
        properties.put("hibernate.multiTenancy", "SCHEMA");
        return properties;
    }

    @Bean
    public JpaTransactionManager transactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManagerFactory().getObject());
        return transactionManager;
    }
}

Answer 1

Here is how I would solve the problem :

1. Use a custom ServletFilter to extract the value from the request headers.
2. In your custom ServletFilter : perform a query on the default schema to get the tenant identifier and put it in a ThreadLocal
3. In resolveCurrentTenantIdentifier just return the value from the ThreadLocal.