Multiple datasource, Could not write JSON: failed to lazily initialize a collection of role: could not initialize proxy - no Session

I tried to set up multiple datasource with 2 datasources include

1. datasource1 which is annotated with @Primary.
2. datasource2.

then query data by using findAll()(that belong to @Primary datasource, datasource1) it work without any error.

but got the following error when I called the the findAll() method that belong to datasource2

Resolved [org.springframework.http.converter.HttpMessageNotWritableException: Could not write JSON: failed to lazily initialize a collection of role: com.example.linewebhookjava.entity.datasource2.Projects2.itemUpdates: could not initialize proxy - no Session]

I have tried change @ElementCollection(fetch = FetchType.LAZY) to @ElementCollection(fetch = FetchType.EAGER) in Projects2 entity.

or

place this spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true into application.properties

the error was gone. But the point is why it is fail on datasource2 (secondary datasource).

Conclusion, calling findAll() that belong to datasource with @Primary work fine , but didn't work on findAll() of the secondary datasource.

Additionally, I have tried swapping @Primary annotation with another datasource.

(Here's my code with sql and docker-compose.yml https://github.com/annopud/line-webhook-java/tree/no_session)

By default, Spring sets the following:

spring.jpa.open-in-view=true

This makes Spring to open a transaction every time a call is made to the controller; when you have multiple transaction managers, it does this on the primary one.

The error you posted is related to the json serialization from the entity (with the collection, that in the case of a lazy loading is just a proxy), so it works when the transaction is opened since the "view" layer (in this case, on the primary), but it does not on the other: every call to the TestSecondController has an open transaction, but that is done on the primary transaction manager, so that is useless for Project2 entities serialization. Putting @Transactional annotations on the controllers won't solve since serialization happen after method return, and so bejond the transaction closure.

Try to set spring.jpa.open-in-view=false in the properties file; in that case, if the collections are all read as lazy, you should get on both controllers the same error.

Perhaps the question is: why you have to make this Spring Boot application manage both datasources? Since Spring does not support out-of-the-box distributed transactions (you can rely on solutions like Atomikos) perhaps is better to split the application vertically, making the controllers use the default open-in-view value and mapping entities with lazy loading, in a clearer way.