how to Resolve "could not initialize proxy - no session" error when using Spring repository

I'm working on a mutitenant project it maintains different schema for each tenant, followed Project

As we are dynamically switching the tenants so it looks like some configuration is missed which is closing the session or not keeping the session open to fetch the LAZY loaded objects. Which results in "could not initialize proxy - no session" error.

Please check below link to access the complete project and db schema scripts, please follow the steps given in Readme file.
Project

It will be helpful if someone can point out the issue in the code.

i tried to put service methods in @Transactional annotation but that didn't work.

I'm expecting it to make another call to the LAZY loaded object, This project is simplefied verson of the complex project, actually i have lot more lazy loaded objects.

Issue:-
I'm getting no Session error "could not initialize proxy [com.amran.dynamic.multitenant.tenant.entity.Tenant#1] - no Session"
at line 26 (/dynamicmultitenant/src/main/java/com/amran/dynamic/multitenant/tenant/service/ProductServiceImpl.java)

The issue is that your transaction boundaries are not correct. In TenantDatabaseConfig and MasterDatabaseConfig you've correctly added @EnableTransactionManagement, which will setup transactions when requested.

However - the outermost component that has an (implicit) @Transactional annotation is the ProductRepository (by virtue of it being implemented by the SimpleJpaRepository class - which has the annotation applied to it - https://github.com/spring-projects/spring-data-jpa/blob/864c7c454dac61eb602674c4123d84e63f23d766/spring-data-jpa/src/main/java/org/springframework/data/jpa/repository/support/SimpleJpaRepository.java#L95 )

and so your productRepository.findAll(); call will start a transaction, create a JPA session, run the query, close the session, close the transaction, which means that there is no longer any transaction / session open in which to perform the lazy-loading.

Therefore, your original attempt of

> i tried to put service methods in @Transactional annotation but that didn't work.

IS the correct thing to do.

You don't say exactly what you tried to do, and where, but there are a few things that could have gone wrong. Firstly, make sure you're adding a org.springframework.transaction.annotation.Transactional and not a javax.transaction.Transactional annotation.

Secondly (and the more likely problem in this scenario), you'll need to configure the annotation with which transaction manager the transaction should be bound to, otherwise it may use an existing / new transaction created against the master DB connection, not the tenant one.

In this case, I think that:

@Service
@Transactional(transactionManager = "tenantTransactionManager")
public class ProductServiceImpl implements ProductService {

should work for you, and make all the methods of the service be bound to a transaction on the tenant DB connection.

EDIT: Answering a follow-up question:

> can you please also suggest a better way to inject my tenantTransactionManager in all my service classes, as I don't want to mention tenantTxnManger in all service classes if there is any better way to do it ?

Yes, sure. You can create a meta-annotation that applies multiple other annotations, so you could create:

    /**
     * Marks class as being a service operating on a single Tenant
     */
    @Target(ElementType.TYPE)
    @Retention(RetentionPolicy.RUNTIME)
    @Service
    @Transactional("tenantTransactionManager")
    public @interface TenantService {
    }

and then you can simply annotate your service classes with @TenantService instead of @Service:

@TenantService
public class ProductServiceImpl implements ProductService {