My Project has multiple databases having one master database and 35 other databases (having same schema and same configuration)
Now currently we are using c3p0 connection pool and using jdbc for getting connections from pool.
I had tried different ways for moving existing implementation into hibernate/JPA but it leads to heavy boilerplate code where i need to declare datasources and dao for every db schema. (https://o7planning.org/en/11653/using-multiple-datasources-with-spring-boot-and-jpa)
I want to design the db flow in such a way that when a request for connection has been asked my db config class should be able to return the JPA connection and I could do respective functionality on my service class
Let me know if i can make my query understandable or any flaw in my approach
I found
mulit tenancy
solution by implementing using Spring BootAbstractRoutingDatasource
.I had defined one national database (also as default db) and two state databases.
1) I had defined the all db datasources in following class.
2) Then, While defining datasource bean, I had bind these datasources to custom
AbstractRoutingDatasource
class3) Then, following is the definition of our custom
AbstractRoutingDatasource
classThis is the all configuration is required to setup multtenant application
Lastly, we define our Controller, Service and Entity class
Service Class
(Initially, I was using only JpaRepository for getting the records. Later, I found that i can also useEntityManagerFactory
for executing HQL Queries instead of using JpaRepository. So, its upto to the developer whatever they find it convenient)Finally, the Entity Class
Code can be optimized by placing the db configuration on properties file.
I hope you will find the answer helpful
References:
https://www.baeldung.com/spring-abstract-routing-data-source
https://javadeveloperzone.com/spring-boot/spring-boot-jpa-multi-tenancy-example/