Spring Boot Multi-Tenant : impossible to set different tenants

My problem is simple to understand but hard to implement.

I have a Spring Boot application with a classic pattern (Controller / Service / Dao&Repository / Entity) Different schema are used in the database. For example, I can have COMMON_SCHEMA.USERS and SPECIFIC_SCHEMA_A.ITEMS.

The best approch I think is to implement multi-tenant, that's what I did.

And here is my problem :

1. An EndPoint recieved data
2. With this data, I need to find something in COMMON_SCHEMA.USERS, so I set the right tenant
3. After that, I need to find data in SPECIFIC_SCHEMA_A.ITEMS. This is where the problem occurs, the tenant is not set and I have an exception that said the entity ITEMS does not exist in schema COMMON_SCHEMA.

Below are the main classes that are used.

public class MultitenantDataSource extends AbstractRoutingDataSource {
  @Value("${defaultTenant}")
  private String defaultTenant;
  
  @Override
  protected Object determineCurrentLookupKey() {
    String currentTenant = TenantContext.getCurrentTenant();
    return StringUtils.defaultIfBlank(currentTenant, defaultTenant);
  }
}

public class TenantContext {
  private static final ThreadLocal<String> currentTenant = new ThreadLocal<>();
  
  public static void setCurrentTenant(String tenant) {
    currentTenant.set(tenant);
  }
  
  public static String getCurrentTenant() {
    return currentTenant.get();
  }
  
  public static void clear() {
    currentTenant.remove();
  }
}

@RestController
@RequiredArgsConstructor
public class TEST_ControllerImpl extends SessionFactoryUtil 
  implements TEST_Controller {

  private final EvGroupService evGroupService;
  
  @Override
  public ResponseEntity<?> getGrp(String token, String grp) {
    TenantContext.setCurrentTenant("COMMON_TENANT");
    EvGroup result1 = evGroupService.findByCode(grp);
    TenantContext.clear();
    
    TenantContext.setCurrentTenant("SPECIFIC_TENANT_A");
    EvGroup result2 = evGroupService.findByCode(grp);
    TenantContext.clear();
  
    return ResponseEntity.ok(convert(result1));
  }
}

Hope you have the spark that will iluminates my day/week/month/year :D Thanks

I tried ChatGpt of course I tried set @Transactionnal annotation in service method signature (even with Transactional.TxType.REQUIRES_NEW) Look through a lot of post but not the same issue as me.

Try setting setting following property to false

spring:
  jpa:
    open-in-view: false

Otherwise, once you open a session (Connection) to the database, it remains opened until you exit your controller method. Therefore, you can't really change the udnerlying datasource mid-flight.

What happens in your code is following:

  @Override
  public ResponseEntity<?> getGrp(String token, String grp) {
    // sets tenant
    TenantContext.setCurrentTenant("COMMON_TENANT");
    // in background creates database connection using COMMON_TENANT key
    EvGroup result1 = evGroupService.findByCode(grp);
    // clears threadlocal
    TenantContext.clear();
    
    // sets tentant to SPECIFIC_TENANT_A
    TenantContext.setCurrentTenant("SPECIFIC_TENANT_A");
    // because spring.jpa.open-in-view is set to true connection is not closed
    // but reused and still points to COMMON_TENANT
    EvGroup result2 = evGroupService.findByCode(grp);
    TenantContext.clear();
  
    return ResponseEntity.ok(convert(result1));
  }