Change tenantIdentifier at Controller level

We are implementing multitenancy in our product and we are going with database per tenant design pattern. We followed the tenant per request pattern suggested here spring multitenant and working example The above approach is working well if we already know in which db/schema we want to search.

We have a use-case where we have to search in default schema and if not found than in other schema. But once the request reach to Controller layer, we are not able to switch the context and it keeps the default schema only. I tried with below code -

public class PersonController {

    @Qualifier("otherSchemaDataSource")
    private final DataSource otherSchemaDataSource;

    public PersonDto getPerson(@PathVariable String personId) {
        try {
            return personService.getById(personId); //search with default schema
        } catch (Exception e) {
            // search in other schema if not found in default schema
            TenantContext.setTenantInfo("other_schema");
            Object oo = "other_schema";
            sessionFactory.withStatelessOptions().tenantIdentifier(oo);
            tenantIdentifierResolver.resolveCurrentTenantIdentifier();
            multiTenantConnectionConfig.setDataSource(otherSchemaDataSource);
            multiTenantConnectionConfig.getConnection("other_schema");
            multiTenantConnectionConfig.setDataSource(ecmArchiveDataSourc);
            return personService.getById(personId);
        }

    }
}

Please let us know if we can make this work without adding manual entityManager/ sessionfactories for all the schemas

It should be working but I guess you are mixing some stuffs.

First of all you must create a TenantIdentifierResolver like this:

@Component
public class TenantIdentifierResolver implements CurrentTenantIdentifierResolver, HibernatePropertiesCustomizer {
private String currentTenant = "unknown";
public void setCurrentTenant(String tenant) {
   currentTenant = tenant;
}
@Override
public String resolveCurrentTenantIdentifier() {
   return currentTenant;
}

@Override
public boolean validateExistingCurrentSessions() {
   return false;
}
@Override
public void customize(Map<String, Object> hibernateProperties) {
   hibernateProperties.put(AvailableSettings.MULTI_TENANT_IDENTIFIER_RESOLVER, this);
}
}

It aims in setting and resolving the current tenant to use for CRUD operations. Then you have to create a hibernate MultiTenantConnectionProvider that aims in providing the correct connection to the current tenant DB

@Component
public class NoOpConnectionProvider implements MultiTenantConnectionProvider, HibernatePropertiesCustomizer {
@Autowired
DataSource dataSource;
@Override
public Connection getAnyConnection() throws SQLException {
   return dataSource.getConnection();
}
@Override
public void releaseAnyConnection(Connection connection) throws SQLException {
   connection.close();
}
@Override
public Connection getConnection(Object tenantIdentifier) throws SQLException {
   return dataSource.getConnection();
}
@Override
public void releaseConnection(Object tenantIdentifier, Connection connection)
      throws SQLException {
   connection.close();
}
@Override
public boolean supportsAggressiveRelease() {
   return false;
}
@Override
public void customize(Map<String, Object> hibernateProperties) {
hibernateProperties.put(AvailableSettings.MULTI_TENANT_CONNECTION_PROVIDER, this);
}    
@Override
public boolean isUnwrappableAs(Class<?> unwrapType) {
   return ConnectionProvider.class.isAssignableFrom( unwrapType ) ||
         MultiTenantConnectionProvider.class.isAssignableFrom( unwrapType );
}

@Override
public <T> T unwrap(Class<T> unwrapType) {
   if ( MultiTenantConnectionProvider.class.isAssignableFrom( unwrapType ) ) {
      return (T) this;
   }
   else {
      throw new UnknownUnwrapTypeException( unwrapType );
   }
}
}

Finally you must create a spring AbstractRoutingDataSource

@Component
public class DatabaseRouter extends AbstractRoutingDataSource {
public static final String DBA = "DB_A";
public static final String DBB = "DB_B";
@Autowired
private TenantIdentifierResolver tenantIdentifierResolver;    
DatabaseRouter() {    
  setDefaultTargetDataSource(createEmbeddedDatabase("default"));    
  HashMap<Object, Object> targetDataSources = new HashMap<>();
  targetDataSources.put(DBA, createEmbeddedDatabase(DBA));
  targetDataSources.put(DBB, createEmbeddedDatabase(DBB));
  setTargetDataSources(targetDataSources);
}

@Override
protected String determineCurrentLookupKey() {
  return tenantIdentifierResolver.resolveCurrentTenantIdentifier();
}

private EmbeddedDatabase createEmbeddedDatabase(String name) {    
  return new EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.H2).setName(name).addScript("person-schema.sql")
      .build();
}
}

This aims in telling spring which database to use according to the current tenant value. By doing in this way you can change the current tenant both programmatically (as your request) and by some custom header value (maybe the best approach).

You can find a simple project here