aspose file tools*
The moose likes Object Relational Mapping and the fly likes Multiple user multiple db Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Multiple user multiple db" Watch "Multiple user multiple db" New topic
Author

Multiple user multiple db

Vishal Shaw
Ranch Hand

Joined: Aug 09, 2012
Posts: 179
Hi,

I am building a application in JPA + spring. I have configured entity managers in my xml files. Now the problem is that every user will have their own database. So, I was thinking of a way that I will be switching database connection at runtime for each user login. But the problem arises that there is 1 instance of entity manager running at a time, thus at a time I can configure my connection to use 1 datasource. Please suggest a wayout for this. If I could make my database name dynamic, the problem would have been solved, but alas!! Any suggestion would be of great help

Thanks and regards,
Vishal


Programming is about thinking, NOT coding
Bill Gorder
Bartender

Joined: Mar 07, 2010
Posts: 1632
    
    7

Why would multiple users of the same application ever need their own database? The way out is that you use one database for all of your users. Alternatively if you are looking at hosting a software as a service type thing and each user requires their own database then they also get their own copy of the application. This would be part of initial set-up when they start an account. In either instance you have one application with one database.


[How To Ask Questions][Read before you PM me]
Vishal Shaw
Ranch Hand

Joined: Aug 09, 2012
Posts: 179
Hi Bill,

That's not possible at all , in my case.I don't even know how many user will be there and each user will be abstracted from other. Maintaining one Database for all the users will be cumbersome. In fact the requirement is like that.
In either instance you have one application with one database.

Life would have been much simpler then.
Bill Gorder
Bartender

Joined: Mar 07, 2010
Posts: 1632
    
    7

Then do the second approach

application instance for user 1 --> database for user 1
application instance for user 2 --> database for user 2

For each new user you spin up another instance for that user. That way each user is totally abstracted as you say.


What you are saying is you want one application instance and you want to be able to dynamically connect to different databases at run-time. How are you going to manage multiple users logging in concurrently? How is the application server going to manage all those connections?

I think you need get clarification on requirements. What you are talking about is just not done.
Bill Gorder
Bartender

Joined: Mar 07, 2010
Posts: 1632
    
    7

You could select connection information based on the logged in user and connect to that database with straight JDBC. EntityManagerFactory is a heavy weight object. You don't want to be creating these all over the place and managing its life cycle.
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2051
    
  22

One database per user seems like overkill to me. Usually the requirement is in a hosted environment, your company is hosting an application for multiple clients. Each client has several users. The application infrastructure (web server, any background processing nodes) is shared, but the data is isolated from each other. IBM has a good whitepaper on multitenancy. I encourage you to go through it. The author has really thought things through

Hibernate 4 has built-in support for multi tenancy. They support all the modes that the whitepaper describes. If you are using Hibernate 4 already, it might be a piece of cake. I haven't used it myself, but it seems worth a shot

We have a similar requirement for multi tenancy, and I had made a post very similar to yours about a year ago. In our case, we are on Hibernate 3, and we couldn't use Hibernate's multi tenancy approach. I came up with my own solution, and it's working quite well. My thread is here . I have posted my Spring based solution in that thread. Look at it and see if it makes sense to you
Bill Gorder
Bartender

Joined: Mar 07, 2010
Posts: 1632
    
    7

Jayesh,

Thanks for posting that I was not even aware of multi-tenancy support in Hibernate 4. I still think a database per user is a bad idea ( I don't know how many users we are talking about) but that is my opinion.

Usually the requirement is in a hosted environment, your company is hosting an application for multiple clients. Each client has several users.


agree.


I think what you described in the thread already exist in Spring. See AbstractRoutingDataSource.
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2051
    
  22

Bill Gorder wrote:Jayesh,

Thanks for posting that I was not even aware of multi-tenancy support in Hibernate 4. I still think a database per user is a bad idea ( I don't know how many users we are talking about) but that is my opinion.

Yes I agree database per user is asking for trouble. I am assuming that there is some miscommunication and when they say "user" they mean client




I think what you described in the thread already exist in Spring. See AbstractRoutingDataSource.


Oh Geez! Spring already implemented what I implemented!!. Learn everything new everyday Atleast I have the solace that I am in good company
Vishal Shaw
Ranch Hand

Joined: Aug 09, 2012
Posts: 179
Hi,

First of all I am extremely sorry, because Jayesh is right. By user I meant client. Sorry for the confusion created by my silliness

As for AbstractDataSourceRouting in spring, I tried to implement this in my application then I will have to change the whole structure. We were using JNDI for the datasource until one day the requirement was changed to having multiple databases for the application. Running multiple instances of the application can be a solution. Can you suggest something on this, that would be of great help!!!

@Jayesh I looked at the link provided by you. Actually I also did something similar to this. For every request, I configured a filter that checks if the current datasource is the required one, otherwise reinitialize the entitymanager with the required datasource. But I would like to know if this can get chaotic if load increases?
Bill Gorder
Bartender

Joined: Mar 07, 2010
Posts: 1632
    
    7


As for AbstractDataSourceRouting in spring, I tried to implement this in my application then I will have to change the whole structure. We were using JNDI for the datasource until one day the requirement was changed to having multiple databases for the application.


I guess I don't understand why this won't work. With or without JNDI you should be able to define however many databases you need to. You would just add each new datasource to the Spring configuration and be sure to set the needed information in the ContextHolder on each request so the correct determination of which one to use could be made. With this solution you would have only one LocalContainerEntityManagerFactoryBean (I assume all the datasources are the same vendor)

Running multiple instances of the application can be a solution. Can you suggest something on this, that would be of great help!!!


After understanding better what it is you are trying to do I am not sure this is what you want. This would basically involve provisioning VMs with containers, or using a solution like cloud foundry. I think the approach Jayesh suggested may be more suitable for your requirements though.
Vishal Shaw
Ranch Hand

Joined: Aug 09, 2012
Posts: 179
I think the approach Jayesh suggested may be more suitable for your requirements though.


I am already using a solution close to it
@Jayesh I looked at the link provided by you. Actually I also did something similar to this.


But I have a doubt
But I would like to know if this can get chaotic if load increases?

Bill Gorder
Bartender

Joined: Mar 07, 2010
Posts: 1632
    
    7

Just for clarification the solution Jayesh implemented and AbstractRoutingDataSource are basically the exact same thing. The only reason he did not use AbstractRoutingDataSource is because he was not aware of its existence. Which is why I was not clear on why you said AbstractRoutingDataSource did not work.

As for your concern. One advantage to the multitenancy approach is the ability to do a clustered deployment of your app. With this sort of set up there would also be a load balancer routing requests and depending on the container being used some sort of session replication across the cluster nodes.
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2051
    
  22

Right. I would have used AbstractRoutingDataSource if I knew it existed

Vishal Shaw wrote:

As for AbstractDataSourceRouting in spring, I tried to implement this in my application then I will have to change the whole structure. We were using JNDI for the datasource until one day the requirement was changed to having multiple databases for the application. Running multiple instances of the application can be a solution. Can you suggest something on this, that would be of great help!!!


The way we have it right now is that we use a convention for the schema names. The client's database is named <seeddatabasename>_<clientsuffix>. In my Spring configuration, I have configured a BasicDataSource that points to the seed database. I have a filter that takes the client suffix and puts it in a thread local variable, and my AbstractRoutingDataSource looks up the TL variable and routes to the datasource assosicated with that suffix. If a data source is not created, it clones the seed data source and changes the schema name

This was our simple implementation. The obvious problem here is that all schmeas have to be in same database instance. At some point we might make this more sophisticated.



@Jayesh I looked at the link provided by you. Actually I also did something similar to this. For every request, I configured a filter that checks if the current datasource is the required one, otherwise reinitialize the entitymanager with the required datasource. But I would like to know if this can get chaotic if load increases?


Hmm.. reinitialize EntityManager?! Can you post a code sample? What happens if you get 2 requests concurrently for 2 different clients? Also, I think reinitializing EntityManager will make Hibernate reload the metadata for your schema, which can be a very costly operation. I would suggest that you turn on debug logs and see what's exactly going on behind the scenes if you do this
Vishal Shaw
Ranch Hand

Joined: Aug 09, 2012
Posts: 179
Hi,

Thanks for your replies. I guess I don't have a better choice than implementing AbstractDataSourceRouting . I tried it with a sample application and it worked seamlessly. I will implement it in my application too. Thanks once again for your nice explanation.

Thanks and Regards,
Vishal
Vishal Shaw
Ranch Hand

Joined: Aug 09, 2012
Posts: 179
Jayesh A Lalwani wrote:
my AbstractRoutingDataSource looks up the TL variable and routes to the datasource assosicated with that suffix.




Can you please post some code about how you did this (AbstractRoutingDataSource looks up the TL variable)? As I was going to configure the Routing datasource in my xml. But that would bind it to the list of datasources I provide (correct me if I am wrong). The database name will be dynamic and unknown at the time of application deployment. Your approach is very impressive, only if I could implement it
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2051
    
  22

ok when I say "my" AbstractRoutingDataSource, I mean the class that I implemented. I can't post the code on here, but I did find someone who implemented what you need here
Vishal Shaw
Ranch Hand

Joined: Aug 09, 2012
Posts: 179
Jayesh A Lalwani wrote:ok when I say "my" AbstractRoutingDataSource, I mean the class that I implemented. I can't post the code on here, but I did find someone who implemented what you need here


Thanks, but I have a doubt here

Here we have three datasources, which are defined. But how to make the database name of the datasource dynamic. Maybe something like
, where dbname can be supplied at runtime using TL
Bill Gorder
Bartender

Joined: Mar 07, 2010
Posts: 1632
    
    7

What you have going there definitely won't work for that.

Take a look at setTargetDataSources() on AbstractRoutingDataSource.

Specify the map of target DataSources, with the lookup key as key. The mapped value can either be a corresponding DataSource instance or a data source name String (to be resolved via a DataSourceLookup.

The key can be of arbitrary type; this class implements the generic lookup process only. The concrete key representation will be handled by resolveSpecifiedLookupKey(Object) and determineCurrentLookupKey().


The default DataSourceLookup is JndiDataSourceLookup. This means that it can hold a String rather than a datasource reference and this String will be treated as a JNDI-name.


Also you may like this solution

http://forum.springsource.org/showthread.php?101195-Dynamic-datasources-at-runtime


Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2051
    
  22

Looking at the javadocs, it looks like you can implement your own DataSourceLookup, and put your rules for creating the data source in there. (I would just extend MapDataSourceLookup



Then in your spring configuration define a bean of type DynamicDataSourceLookup, define another bean of type DynamicDataSource , wiere the lookup bean to the data source bean, wire the data source bean into your EM, and you should be good to go. Just make sure you set the thread local variable before you call EM.
Vishal Shaw
Ranch Hand

Joined: Aug 09, 2012
Posts: 179
Thanks Bill and Jayesh. I am definitely going to try the solutions given by you.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Multiple user multiple db
 
Similar Threads
when to use different connections
How to handle concurrent write/read to a db from a servlet
Beginner Problem: son & parent
Connecting to Multiple Schemas
JDBC Connections Freezing with Multiple Users Accessing DB