Multi-tenant-Architecture and Second level caching issues (spring, hibernate 5.4.21.Final , multi-tenancy, caching ehcache )

Question

I have implemented multi-tenant datasource to route between the schemas at runtime,our requirements are as follow single instance single database with multiple identical schemas I will determine the target datasource at runtime , we store the database' credentials in a Catalog database that our product connects to at starting of the application x-tenant-id --> "username,url,password,....."

for each request I can inquire from the catalog database the credentials of this database and then create the DataSource using AtomikosDataSourceBean , I have used interceptor to intercept every request to extract the x-tenant-id from header and put this string in class called branchContextHolder that has a threadlocal then I called the dataSourceRouting bean that extends AbstractRoutingDataSource and then called the following method determineCurrentLookupKey(), till this time, I have not used AbstractDataSourceBasedMultiTenantConnectionProviderImpl or CurrentTenantIdentifierResolver,

I have found out that a wrong action happens related to second level caching, Entities annotated by @Cache and @Cachable are cashed in the context of all datasources for example if tenant A try to inquire Schema A and a query was cached, if tenant B called schema B Hibernate will return the cached value and this wrong action I have added those two classes bstractDataSourceBasedMultiTenantConnectionProviderImpl and CurrentTenantIdentifierResolver, but this issue is still exists

I will show you the code to make things more clear

` public class DataSourceInterceptor extends HandlerInterceptorAdapter {

@Autowired
DBConfigRepository dbConfigRepository;

@Autowired
DataSourceRouting datasourceRouting;

@Autowired
TenantIdentifierResolver tenantIdentifierResolver;

@Autowired
private StandardPBEStringEncryptor configurationEncryptor;

/**
 * This method is called before the actual request handler (controller method)
 * is invoked.
 */

@Override
public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler)
        throws Exception {

    String tenant = request.getHeader("X-Tenant-Id");
    Optional<DBConfig> optionalDBConfig = dbConfigRepository.findByName(tenant);
    if (!optionalDBConfig.isPresent()) {
        throw new WalletNotFoundException();
    }
    DBConfig currentDBConfig = optionalDBConfig.get();
    if (!datasourceRouting.getCustomDataSources().containsKey(tenant)) {


        String encryptedUserName = currentDBConfig.getUserName();
        String decryptedUserName = decrypt(encryptedUserName);

        String encryptedPassword = currentDBConfig.getPassword();
        String decryptedPassword = decrypt(encryptedPassword);

        // override
        currentDBConfig.setUserName(decryptedUserName);
        currentDBConfig.setPassword(decryptedPassword);
        DataSource dataSource = createDataSource(currentDBConfig);
        datasourceRouting.addDataSource(tenant, dataSource);
    }

    BranchContextHolder.setBranchContext(tenant);
    tenantIdentifierResolver.resolveCurrentTenantIdentifier();

    // determine the current data source based on the branch.
    datasourceRouting.determineCurrentLookupKey();
    return super.preHandle(request, response, handler);
}

private String decrypt(String encryptedText) {
    return configurationEncryptor.decrypt(encryptedText);
}

private DataSource createDataSource(DBConfig dbConfig) {
    AtomikosDataSourceBean dataSourceWithConnectionPooling = new AtomikosDataSourceBean();
    dataSourceWithConnectionPooling.setXaDataSourceClassName("oracle.jdbc.xa.client.OracleXADataSource");

    dataSourceWithConnectionPooling.setUniqueResourceName(dbConfig.getName());

    dataSourceWithConnectionPooling.setXaProperties(createXaProperties(dbConfig));
    // connection pooling
    dataSourceWithConnectionPooling.setMinPoolSize(dbConfig.getMinPoolSize());
    dataSourceWithConnectionPooling.setMaxPoolSize(dbConfig.getMaxPoolSize());
    dataSourceWithConnectionPooling.setMaxIdleTime(dbConfig.getMaxIdleTime());
    dataSourceWithConnectionPooling.setBorrowConnectionTimeout(dbConfig.getNewConnectionWaitTimeout());
    try {
        dataSourceWithConnectionPooling.setLoginTimeout(dbConfig.getNewConnectionWaitTimeout());
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    dataSourceWithConnectionPooling.setTestQuery("select * from dual");
    dataSourceWithConnectionPooling.setReapTimeout(dbConfig.getTransactionTimeout());
    dataSourceWithConnectionPooling.setMaintenanceInterval(dbConfig.getTransactionTimeout());

    return dataSourceWithConnectionPooling;
}

private Properties createXaProperties(DBConfig dbConfig) {
    Properties xaProperties = new Properties();
    xaProperties.setProperty("URL", dbConfig.getUrl());
    xaProperties.setProperty("user", dbConfig.getUserName());
    xaProperties.setProperty("password", dbConfig.getPassword());
    xaProperties.setProperty("maxStatements", String.valueOf(dbConfig.getCacheStatementsSize()));
    xaProperties.setProperty("ImplicitCachingEnabled", "true");
    xaProperties.setProperty("explicitCachingEnabled", "true");
    // Add other xaProperties as needed

    xaProperties.setProperty("connectionCacheProperties.maxStatementsLimit",
            String.valueOf(dbConfig.getCacheStatementsSize()));
    xaProperties.setProperty("connectionCacheProperties.connectionWaitTimeout",
            String.valueOf(dbConfig.getNewConnectionWaitTimeout()));


    return xaProperties;
}

/**
 * @return the dbConfigRepository
 */
public DBConfigRepository getDbConfigRepository() {
    return dbConfigRepository;
}

/**
 * @param dbConfigRepository the dbConfigRepository to set
 */
public void setDbConfigRepository(DBConfigRepository dbConfigRepository) {
    this.dbConfigRepository = dbConfigRepository;
}

@Override
public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex)
        throws Exception {
    // Clear the context at the end of the request
    BranchContextHolder.clearBranchContext();
}

}

public class DataSourceBasedMultiTenantConnectionProviderImpl extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl {

@Autowired
private DataSourceRouting datasourceRouting;

@Override
protected DataSource selectAnyDataSource() {
    return datasourceRouting.getResolvedDefaultDataSource();
}

@Override
protected DataSource selectDataSource(String tenantIdentifier) {
    DataSource dataSource = DataSourceRouting.getDataSource(tenantIdentifier);

    try (Connection connection = dataSource.getConnection()) {
        String schema = connection.getSchema();
        System.out.println("Schema for " + tenantIdentifier + ": " + schema);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return dataSource;
}

} @Component public class TenantIdentifierResolver implements CurrentTenantIdentifierResolver, HibernatePropertiesCustomizer {

@Override
public String resolveCurrentTenantIdentifier() {
    // Retrieve the current tenant identifier from your BranchContextHolder or
    // elsewhere
    if (BranchContextHolder.getBranchContext() != null)
        System.out.println("tenant Identifier " + BranchContextHolder.getBranchContext());
    return BranchContextHolder.getBranchContext() != null ? BranchContextHolder.getBranchContext() : "DBConfig";
}

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

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

}

} public class DataSourceRouting extends AbstractRoutingDataSource { /* * this map is the one we can deal with in the interceptor to check if the * x-tenant-id sent in the request exists in our map or not if it exist then we * call the corresponding data source in O(1) else if we inquire using this * tenant id from the DBConfig table and create another entity in this map using * addDataSource(String key, DataSource dataSource) method in this class * */ private static Map<Object, Object> customDataSources = new HashMap<>();

/**
 * Determines the current lookup key, which is the tenant identifier. Called by
 * the interceptor class and used in the determineTargetDataSource() method of
 * the parent class to return the data source mapped with the current tenant id.
 */
@Override
protected Object determineCurrentLookupKey() {
    return BranchContextHolder.getBranchContext();
}

/**
 * Overrides the setTargetDataSources method in the parent class to update the
 * target data source map. This map is used to create the resolvedDataSource map
 * in the parent class, which is then utilized in the
 * determineTargetDataSource() method for routing to different data sources.
 *
 * It is essential to call afterPropertiesSet() to update the
 * resolvedDataSources map in the superclass, as the determineCurrentLookupKey()
 * method relies on resolvedDataSources to get the data source corresponding to
 * a key.
 *
 * @param targetDataSources The map containing tenant identifiers and their
 *                          associated data sources.
 */
@Override
public void setTargetDataSources(Map<Object, Object> targetDataSources) {
    super.setTargetDataSources(targetDataSources);
    afterPropertiesSet();
}

/**
 * Adds a new data source to the customDataSources map, associating it with the
 * provided key. This method is called in the interceptor to dynamically add
 * data sources at runtime.
 *
 * @param key        The tenant identifier.
 * @param dataSource The data source corresponding to the tenant identifier.
 */
public void addDataSource(String key, DataSource dataSource) {
    customDataSources.put(key, dataSource);
    setTargetDataSources(customDataSources);
}

/**
 * Gets the customDataSources map, which contains the mapping between tenant
 * identifiers and data sources.
 *
 * @return The customDataSources map.
 */
public static Map<Object, Object> getCustomDataSources() {
    return customDataSources;
}

public static DataSource getDataSource(String tenantIdentifier) {
    return (DataSource) customDataSources.get(tenantIdentifier);
}

}` I need to add some logic to take x-tenant-id into consideration in caching

I am trying to route to different datasources at runtime , and that happened successfully , but the issue in the second level caching ,since they are identical schema so the entities that is annotated by @Cache and @Cachable will be cached for all the queries regardless which tenant identifer