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
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
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
You don't need multiple datasources/transactionamangers. Well you need the DataSources but everything else can be a singleton. We did that over 10 years ago. Solution is documented here. Depending on your needs the AbstractRoutingDataSource might be all you need and no such thing as multiple transaction managers. — M. Deinum

Sajib Acharya Sajib Acharya · Accepted Answer · 2016-05-09T13:58:05

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.

Multiple transaction managers in spring and select one at runtime

2 Answers