Multi-user Datasources - Spring + Hibernate
I might be wrong about the (strict) need to have one SessionFactory per database, as suggested by some resources:
I'll take some time to re-read everything tomorrow (I didn't get all the details to be honest) and to fully understand the implications of such a setup (although it seems clear that it will break the second-level cache). I'll come back on this later.
I'm writing a web app that supports multiple users. Each user has their own database - using H2. all database schemas are the same.
I wonder how this will scale... How many users do you have? How do you run H2, what mode?
So I'm stuck at how to associate a user's database with that user - maybe associated it in the HTTPSession, and extend spring's AbstractRoutingDataSource?
You'll have to build a SessionFactory per user and associate it to the logged user (in a Map, using the login as key) and then obtain a Session from a given SessionFactory. Binding the lifecycle of the SessionFactory to the HTTP session seems to be a good idea (to save some memory) but I am not sure Spring will be very helpful here. I might be wrong but a variation of the HibernateUtil class and a fully programmatic approach looks easier. I'm not sure you'll need multiple connections per user by the way.
but wouldn't this effect Hibernate's cache?
What cache?
Another way is to have a SessionFactory with each datasource, even though every datasource's schema is the same... so I see that as a waste.
Oh, it's a waste, but that's what you want to do (one database per user). And you don't have the choice (you need one SessionFactory per datadabase). Why do you need one database per user actually? Are you sure this is a wise decision? As already hinted, this means much troubles, won't scale well, adds complexity, etc. Why not using a single database and associating data to the user?
Anyways selecting the datasource needs to be dynamic - they can't be pre-configured in context files, as each new user will have its own database created. Is there any existing frameworks/solutions?
Not to my knowledge. Which is also why I think you'll have to do everything programatically.
I don't know too much about Hibernate Shards, maybe that works?
Given the dynamic needs of your application, I don't see how it could help.
Thanks to the help from the 2 people (Pascal and org.life.java)!
It is possible, but with some problems: e.g. the hibernate 2nd level cache/query cache.
This link supplied by Pascal is a very good resource:
http://www.jroller.com/kenwdelong/entry/horizontal_database_partitioning_with_spring.
My main motivation for giving each user a separate database is because the data is likely to grow rapidly, thus horizontal partitioning is required.