Configure Multiple (Routing) Datasources in Spring Boot + Spring Data And Switch Them At Runtime

[@Cyecize]

An issue with dialects was found after I recorded the video. This commit fixed the issue: github.com/Cyecize/Spring-Data-Routing-Datasources/commit/184ebcd1b31944081e66000fc1ef8ea543224754 If you only want to use one type of SQL Server, then this will not affect you!

[@ShwetaSingh-je1mu]

What was the dialect issue can you please elaborate, because I might be facing same issue

[@Cyecize]

@@ShwetaSingh-je1mu The issue is that once you select a dialect for hibernate it will use that same dialect even if you change the connection. So when I change the connection from MySQL to MSSQL for example, it uses the MySQL dialect, which does not work well with MSSQL. Regardless, if you get the latest version from my github page, you will now have this issue.

[@ShwetaSingh-je1mu]

@@Cyecize Thanks for your reply. I was facing the same issue and implemented your solution it worked, but there was another issue: I am using liquibase and after I implemented your solution liquibase is having issue while table creation only for postgres db. Can you help me with this?

[@Cyecize]

@@ShwetaSingh-je1mu I've only used flyway, but I guess the idea is the same. The logic for applying DB migrations should not be dependent on spring data, so you can just pass the connection that it needs. Additionally, when I use multi DB, I keep 2 copies of the same flyway file. Eg. v1_mysql_some_updates and then v1_posgres_some_updates This way I can choose which migration to apply based on what DB I am running

[@ShwetaSingh-je1mu]

@@Cyecize Thank you so much!

[@vut9690]

This is EXACTLY what I am looking for my project at work. THANKS!!!

[@Cyecize]

And this is EXACTLY the comments I was hoping for when I did this back then ♥

[@huutuan209]

Thank you very much, your lesson still valid to this day

[@maurocaredda5717]

I like this implementation, i added some logic to manage the switch via header with an interceptor and works flawless, thanks a lot

[@shoaibpatel6461]

I am looking for similar implementation where switch is managed based on header value. Can you point to some material on this?

[@maurocaredda5717]

hi, sure i posted an example code in my GitHub , you can check here github.com/Mauro2888/spring_multi_datasource

[@UlquiorraEspada88]

It's possible to enable the annotation at service class level for all methods ?

[@Cyecize]

Of course, you just have to code it that way :D

[@sachinacharya6740]

i don't want to set a default datasource in abstract routing datasource, how can i implement it

[@TsvetelinYakimov]

I did this long time ago so I don't remember if it could be possible but you can always have a dummy connection as a default. You can set up empty in memory database database to use just to boot up spring

[@sachinacharya6740]

​@@TsvetelinYakimov my case here is i have a parameter based on which i want to select specific datasource where the entity must be saved/updated i dont want any datasource as primary or default, all my datasource will be using the same set of entities and repositories

[@EliSanchez99]

It helped me a lot, thank you very much!

[@artshorts512]

@Cyecize can we connect two database to one application , im trying to connect first one is primary database which have 3 month old data and second database which has old data than 3 month . i cant do changes in existing code. can i implement it without change in all repository, services packages bcoz there are more than 500 apis . please give me suggestion . two database for one package .

[@TsvetelinYakimov]

I am not sure what logic you want to use to switch between dbs, but there must be at least one place to add the routing logic. I think it is possible with very little code change, but with no changes at all it will be hard.

[@abdulrahmanelrawas2029]

This is very helpful. Thank you.

[@rajivperera1624]

Can you do a sample spring data app where application connect to database schema based on username and pwd dynamically as arguments when running the app. The system should have one db but multiple schemas based on username and password. I tried with spring data but was not able to achive.

[@Cyecize]

Hey, Check out my other video kzbin.info/www/bejne/o5SroJSGfs5_bNk It's pretty much what you need.

[@rajivperera1624]

@@Cyecize I watched this video and good work as always. But in that you have created dynamic datasource for different db types and it is pre setup by declaring the db types your going to set. What I am trying to achive is only one db but has multiple schemas and the jpa datasource should load them dynamicaly based on user input. Ex: spring cli app reads args where args are username and pwd and when user enters userXX and pwdXX it should go to userXX schema and if userYY it should go to userYY schema. This setup cannot be pre setup like one is for mssql, another is for mysql wise as their are hundreds of schemas. So I am trying to load datasource dynamically based on user input to the same db. What I cant figure is how to do this in JPA?

[@Cyecize]

@@rajivperera1624 That is also shown in that video 😀 In addition to having the ability to connect to different types of DBs, you can also choose which schema to connect to. So if you strip out the unnecessary things you can end up with what you need. Ofcourse, my logic is to keep the connection per session, but you can modify the manager to make it so that it stores that connection based on some user or whatever else is needed. For your case I imagine a manager written something like this: - Have a list of schema names (loaded when the app is starting) - Have a Map to keep already established connections and lazy load new ones. - Use similar logic for swapping data sources as on this video or the other one. - Think of some way to hint which data source to use. Maybe header or body param.

[@rajivperera1624]

@@Cyecize can you point what classes to be taken out of the backend code to achive this as for example I think I cannot drop classes like the entity manager you have written.

[@0equalsTo1]

can we use Map insted of ThreadLocal ?

[@cyesoft]

The idea of ThreadLocal is to keep a unique value per Thread. This code is called from multiple threads and we want to make sure different threads do not mix and stack transactions on the same place. The way we ensure that nested transactions for example are executed in order, is by ensuring that they are properly positioned in the stack. I can see a way we can do it with a Map, but that map has to keep track of the ID of the thread, then this map has to be synchronized or it has to be a ConcurrentHashMap. This adds quite a bit of complexity and ThreadLocal is in this case exactly the solution we need.

[@rajatchaturvedi6393]

@Cyecize i want to do same thing with mongodb...can u provide the direction

[@Cyecize]

Hey, the solution should be pretty similar. Since we're using the repository pattern here, just replace the repository implementation with a mongo db one and you should be good.

[@anwartan5076]

Bro, the annotation doesn't working in controller

[@TsvetelinYakimov]

I'll check that and see why. But anyway, you should use this annotation on a service level really.

[@anwartan5076]

@@TsvetelinYakimov sorry, i mean when i run the service which has withdatabase annotation, it doesn't work properly and the result always follow withdatabase the first time run

[@cyesoft]

@@anwartan5076 It is tested and it should work. Could you please share a link to your code, I will try to help out.

[@alexkorn3692]

Just add @Transactional in your Controller class

[@xandrviking1113]

So loud keyboard 😂