[FIXED] Spring Boot + Hibernate Multi-tenancy: @Transactional not working

Issue

I have a Spring Boot 2 + Hibernate 5 Multi-tenant application connecting to a single PostgreSQL database. I have set this up according to these guides:

• http://www.greggbolinger.com/tenant-per-schema-with-spring-boot/
• https://blog.aliprax.me/schema-based-multitenancy/
• https://fizzylogic.nl/2016/01/24/make-your-spring-boot-application-multi-tenant-aware-in-2-steps/

This works fine as long as I set the tenantId in a Filter or Interceptor before hitting the Controller endpoints.

However, I need to set the tenant inside the controller, as follows:

@RestController
public class CarController {
    @GetMapping("/cars")
    @Transactional
    public List<Car> getCars(@RequestParam(name = "schema") String schema) {
        TenantContext.setCurrentTenant(schema);
        return carRepo.findAll();
    }
}

But at this point a Connection has already been retrieved (for the public schema) and setting the TenantContext has no effect.

I figured @Transactional was supposed to force the method to be run in a separate transaction, and thus the creation of the Hibernate Session would be postponed until the carRepo.findAll() method was called. This does not seem to be the case, since @Transactional does nothing.

This leads me to 2 questions:

1. How can I defer the creation of a Hibernate Session during a request until I managed to set the correct tenant based on some logic not available in a Filter/Interceptor? @Transactional does not seem to do anything.
2. How can I talk to different schemas in the same request or block of code? Imagine 1 repository being only available in the public schema and 1 being in a tenant schema.

---

Other relevant classes (only relevant parts are shown!)

MultiTenantConnectionProviderImpl.java:

@Component
public class MultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider {
    @Override
    public Connection getConnection(String tenantIdentifier) throws SQLException {
        final Connection connection = getAnyConnection();
        connection.setSchema(tenantIdentifier);
        return connection;
    }

    @Override
    public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {
        connection.setSchema(null);
        releaseAnyConnection(connection);
    }
}

TenantIdentifierResolver.java

@Component
public class TenantIdentifierResolver implements CurrentTenantIdentifierResolver {

    @Override
    public String resolveCurrentTenantIdentifier() {
        String tenantId = TenantContext.getCurrentTenant();
        return (tenantId != null) ? tenantId : "public";
    }
    @Override
    public boolean validateExistingCurrentSessions() {
        return true;
    }
}

HibernateConfig.java:

@Configuration
public class HibernateConfig {
    @Autowired
    private JpaProperties jpaProperties;

    @Bean
    public JpaVendorAdapter jpaVendorAdapter() {
        return new HibernateJpaVendorAdapter();
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource,
                                                                       MultiTenantConnectionProvider multiTenantConnectionProviderImpl,
                                                                       CurrentTenantIdentifierResolver currentTenantIdentifierResolverImpl) {
        Map<String, Object> properties = new HashMap<>(jpaProperties.getProperties());
        properties.put(Environment.MULTI_TENANT, MultiTenancyStrategy.SCHEMA);
        properties.put(Environment.MULTI_TENANT_CONNECTION_PROVIDER, multiTenantConnectionProviderImpl);
        properties.put(Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, currentTenantIdentifierResolverImpl);
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);
        em.setPackagesToScan("com.example");
        em.setJpaVendorAdapter(jpaVendorAdapter());
        em.setJpaPropertyMap(properties);
        return em;
    }
}

Solution

In spring the transaction is called when we call the method from another bean class .In this case, if you move the findAll call to a service class and add the transaction on that method then the behavior would be as you expect. The transaction will start when you call the service method by then the schema value is set on TenantContext

Note: Remove the @Transactional from Controller. Since you are doing a read it is better to add readonly property to @Transactional added to service method 'getAllCars()'

@RestController
public class CarController {

    @GetMapping("/cars")
    public List<Car> getCars(@RequestParam(name = "schema") String schema) {
        TenantContext.setCurrentTenant(schema);
        return carService.getAllCars();
    }
}

@Service
public class CarService{

    @Transactional(readOnly=true)
    public List<Car> getAllCars() {
        return carRepo.findAll();
    }
}

Answered By - Tan mally
Answer Checked By - Clifford M. (JavaFixing Volunteer)