Change Multitenancy sessions manually

Question

I need to create a multitenanacy application with ability to switch between schemas inside my java-code (not based on a user request).

I've read articles: https://fizzylogic.nl/2016/01/24/make-your-spring-boot-application-multi-tenant-aware-in-2-steps/ http://www.greggbolinger.com/tenant-per-schema-with-spring-boot/ Solution works fine, when the schema is passed in Rest-request.

However I need to implement the following logic:

public void compare(String originalSchema, String secondSchema){
    TenantContext.setCurrentTenant(originalSchema);
    List<MyObject> originalData = myRepository.findData();

    TenantContext.setCurrentTenant(secondSchema);
    List<MyObject> migratedData = myRepository.findData();
}

The point is, that connection is not switched, when I manually set up TenenantContext. MultiTenantConnectionProviderImpl.getConnection is invoked only on the first call to my repository.

 @Component
 public class MultiTenantConnectionProviderImpl implements  MultiTenantConnectionProvider {

     @Override
     public Connection getConnection(String tenantIdentifier) throws SQLException {
          final Connection connection = getAnyConnection();
          try {
               connection.createStatement().execute( "ALTER SESSION SET CURRENT_SCHEMA = " + tenantIdentifier );
          }
          catch ( SQLException e ) {
              throw new HibernateException(
      "Could not alter JDBC connection to specified schema [" + tenantIdentifier + "]",e);
          }
          return connection;
    }
 }

Is it possible to force switching sessions?

Yes, it's possible, but you need to switch session outside the transaction bounds. — Andriy Slobodyanyk, Oct 20 '17 at 10:25
@Andriy Slobodyanyk, I do not create a transaction manually. I have a service, that doesn't have transactional annotations and makes calles to repositories. Where do the transactional bonds come from? — Ermintar, Oct 20 '17 at 10:30

score 7 · Accepted Answer · answered Dec 12 '17 at 15:32

Found a hard-coded solution.

@Service
public class DatabaseSessionManager {

    @PersistenceUnit
    private EntityManagerFactory entityManagerFactory;

    public void bindSession() {
        if (!TransactionSynchronizationManager.hasResource(entityManagerFactory)) {
            EntityManager entityManager = entityManagerFactory.createEntityManager();
            TransactionSynchronizationManager.bindResource(entityManagerFactory, new EntityManagerHolder(entityManager));
        }
    }

    public void unbindSession() {
        EntityManagerHolder emHolder = (EntityManagerHolder) TransactionSynchronizationManager
            .unbindResource(entityManagerFactory);
        EntityManagerFactoryUtils.closeEntityManager(emHolder.getEntityManager());
    }
}

Each block, loading data in a new tenantContext should execute the following:

    databaseSessionManager.unbindSession();
    TenantContext.setCurrentTenant(schema);
    databaseSessionManager.bindSession();
    //execute selects

Works great! Note - if you have spring.jpa.open-in-view set to false, unbindSession will fail with NPE if you don't check to see if the resource exists first. — TheJeff, Nov 16 '20 at 21:13

Andriy Slobodyanyk · Answer 2 · 2017-10-20T11:12:14.460

1

Well, you need it

public interface Service {
    List<MyObject> myObjects();
}

@Service
@Transactional(propagation = Propagation.REQUIRES_NEW)
public class ServiceImpl implements Service {
     @Autowired
     private MyRepository myRepository;

     @Override
     public List<MyObject> myObjects() {
         return myRepository.findData();
     }
}

@Service
public class AnotherService() {
    @Autowired
    private Service service;

    public void compare(String originalSchema, String secondSchema){
        TenantContext.setCurrentTenant(originalSchema);
        List<MyObject> originalData = service.myObjects();

        TenantContext.setCurrentTenant(secondSchema);
        List<MyObject> migratedData = service.myObjects();
    }
}

edited Oct 20 '17 at 11:12

answered Oct 20 '17 at 10:36

Andriy Slobodyanyk

1,965
14
15

Unfortunatly, it works just like autowiring repository. The connetion is not switched – Ermintar Oct 20 '17 at 10:47
@Ermintar, Are you sure that the transaction is not started before with any sql operation? To work around it I propose to findData in new transaction, I've updated my answer. – Andriy Slobodyanyk Oct 20 '17 at 11:11
@ Andriy Slobodyanyk, requires new was of no support. And there's no transaction context around my service - it the only method I call directly. – Ermintar Oct 20 '17 at 11:44
@Ermintar, Even if you do not start the transaction evidently it is created implicitly when you select the data and the second call uses it and it's connection. – Andriy Slobodyanyk Oct 20 '17 at 12:45
@ Andriy Slobodyanyk, I've looked into Spring sources. Actually it's not the thransaction that holds the tenantId, it's the Session. SessionImpl is created and tenant is passed to it from entitymanager. Tenenat is not changed within session. SessionImpl is created once per rest request, and all the transactions work inside session – Ermintar Oct 20 '17 at 14:21
@ Andriy Slobodyanyk, the session is created before any jpa method is invoked or any service marked with Transactional hit. It's created even before the controller (that calls service) is invoked. – Ermintar Oct 20 '17 at 14:27

score 1 · Answer 3 · answered Aug 03 '22 at 19:01

1

Try using

spring.jpa.open-in-view=false

in your application.properties file.

More info on this
What is this spring.jpa.open-in-view=true property in Spring Boot?

Hope this helps..

answered Aug 03 '22 at 19:01

Antron

11
1

Change Multitenancy sessions manually

3 Answers3

Linked