Multiple transaction managers in spring and select one at runtime

Question

For each client, I have separate databases but business logic and tables are same for each client. I want common service and dao layer for each client. In dao, I select datasource based on logged user client. In @Transactional, I have to pass bean id of transaction manager. How to make common service layer with @Transactional annotation.

Same question is here

but nobody reply

on a different note, what do you mean by separate databases but same tables? You mean each and every database has the same schema? — Sajib Acharya, May 09 '16 at 13:14
Not answering the question, actually searching for a solution for you, but apart from that, I don't really think this a good idea to have separate databases for each client, when the schema is same, why not use one single database? what happens even if you get 100 clients, that leads to 100 databases, meaning 100 database connections to manage. That is resource hogging. — Sajib Acharya, May 09 '16 at 13:18
This is client requirement. They are saying that their database should be different from other client because of security reason. And they can manage their database in future. Client will not be more than 5. — Ashish Agarwal, May 09 '16 at 13:22
You don't need multiple datasources/transactionamangers. Well you need the `DataSource`s but everything else can be a singleton. We did that over 10 years ago. Solution is documented [here](https://mdeinum.wordpress.com/2007/01/05/one-application-per-client-database/). Depending on your needs the `AbstractRoutingDataSource` might be all you need and no such thing as multiple transaction managers. — M. Deinum, May 09 '16 at 13:23
@Sajib At login page, there is a dropdown to select client company. We check login credential corresponding to his DB. Then we save user company into session. There is Connector factory inwhich we pass user company and it return session factory corresponding to that client. — Ashish Agarwal, May 09 '16 at 13:32
Thanks Deinum, But I want to use spring transaction. I am already using it in this project. This project is almost done with single DB. separate db for each client is new requirement. — Ashish Agarwal, May 09 '16 at 13:42
Have you actually READ the post? That is completely spring related, spring managed transacted etc. Have you also checked the `AbstractRoutingDataSource`? You can of course duplicate all your beans but is that really what you want if the only thing that is different is the database to use? — M. Deinum, May 09 '16 at 14:02
@M.Deinum has a really great post there, you could actually look into that. It does not really depend upon whether the Database connections are transactional or not. Wherever you'd require transactions, simply use the `@Transactional` annotation on the DAO methods. That'll do the trick. In fact, the solution in the post really does not only consider datasources, it can be used on any classes with the right tweaks. — Sajib Acharya, May 09 '16 at 14:14
@SajibAcharya we used it for a lot more then just datasources, but it started with that. I still maintain and use the code, the application mentioned in that post is still in production with more or less the same code, supplying ~ 50 different datasources (amongst others). — M. Deinum, May 09 '16 at 14:15
@M.Deinum, I have already cloned your repository and planning to study it use it in my current projects. Thank you for the codebase. :3 — Sajib Acharya, May 09 '16 at 14:18
@M.Deinum, Thanks for your answer as comment. AbstractRoutingDataSource will work in my case. — Ashish Agarwal, May 09 '16 at 14:29
Well I checked the linked questions, and found that people have posted their replies and the first question also has an accepted answer! — Devraj Gadhavi, May 09 '16 at 14:32

score 0 · Answer 1 · edited May 23 '17 at 12:16

If you want to create a database connection dynamically, then have a look at this SO post.

From the post linked : Basically in JDBC most of these properties are not configurable in the API like that, rather they depend on implementation. The way JDBC handles this is by allowing the connection URL to be different per vendor.

So what you do is register the driver so that the JDBC system can know what to do with the URL:

DriverManager.registerDriver((Driver)
Class.forName("com.mysql.jdbc.Driver").newInstance());

Then you form the URL:

String url =
 "jdbc:mysql://[host][,failoverhost...][:port]/[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]"

And finally, use it to get a connection:

Connection c = DriverManager.getConnection(url);

In more sophisticated JDBC, you get involved with connection pools and the like, and application servers often have their own way of registering drivers in JNDI and you look up a DataSource from there, and call getConnection on it.

In terms of what properties MySQL supports, see here (The link is dead).

EDIT: One more thought, technically just having a line of code which does Class.forName("com.mysql.jdbc.Driver") should be enough, as the class should have its own static initializer which registers a version, but sometimes a JDBC driver doesn't, so if you aren't sure, there is little harm in registering a second one, it just creates a duplicate object in memeory.

I don't know if this will work, since I have not tested it, but you could try.

Now what you could do is, use the @Transactional annotation on top of the DAOs without specifying any values (That works). Now in your DAO classes, instead of injecting any DataSource bean, create your own dataSource dynamically as specified in the above link and then either inject that dependency at runtime, use getter setter methods, or just use the new keyword. I hope that'd do the trick.

NOTE: I have not tested it myself yet, so if this works, do let me know.

score 0 · Answer 2 · answered May 24 '17 at 19:34

You do not need to configure and switch between multiple transaction managers to accomplish your end goal. Instead use the Spring provided org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource mechanism.

Detailed examples can be found here :

Multiple transaction managers in spring and select one at runtime

2 Answers2

Linked