Multiple Data Sources with same entity and repo

Question

Currently working on a project where my Spring Boot project needs to leverage multiple data sources or schema in the same DB server. I have found several tutorials that teach multiple data source configuration in spring boot where entity foo exists in DataSource A and bar exists in DataSource B namely below.,

https://scattercode.co.uk/2016/01/05/multiple-databases-with-spring-boot- and-spring-data-jpa/ https://scattercode.co.uk/2013/11/18/spring-data-multiple-databases/ https://medium.com/@joeclever/using-multiple-datasources-with-spring-boot-and-spring-data-6430b00c02e7

But my use case is that entities foo and bar are present in multiple schema and I want to use a single entity and repository to access all schema.Data is not replicated in all schema.It is divided among them.

So if I need to search for User John Doe I have to go through Schema 1 and if I don't find him, move onto the next schema.

I have tried all the above tutorials(even though they don't line up with my use case) with the hope that I could hack it to get it working just as a proof of concept. I have also looked into AbstractRoutingDataSource (http://fizzylogic.nl/2016/01/24/make-your-spring-boot-application-multi-tenant-aware-in-2-steps/ , http://kimrudolph.de/blog/spring-datasource-routing) and MultiTentancy but both of these talk about having access to a single schema at any point in time. I just need some guidance or link to follow and get this accomplished.

Thanks in advance.

Cargile

Maybe this could help you #13508022 – Healy

Answer 1

You need to look at AbstractRoutingDataSource and use it.

So if I need to search for User John Doe I have to go through Schema 1 and if I don't find him, move onto the next schema.

Thus you need to search in first schema and if not found, then go on to next schema.

In that example as given in the above link,

 CustomerContextHolder.setCustomerType(CustomerType.GOLD);
 List<Item> items = catalog.getItems();
 if(isEmpty(goldItems)){
  CustomerContextHolder.setCustomerType(CustomerType.SILVER);
  items = catalog.getItems();  
 }

More details can be found in another qn here

Cragsman

MultiTenant support does not work with schema generation. Is there a way to choose it programmatically? Like choosing the instance with a factory and using different @Transactional for data source? I tried that but it always saves to the same data source. – Oza

Answer 2

Managed to resolve the issue by using https://github.com/wmeints/spring-multi-tenant-demo.

Thanks @surya for your recommendation.

Cargile