Using multiple dynamic datasources with Hibernate

[neojonas]

I've been using Spring 2.x and Hibernate for many years now, but I'm dealing with a new problem I haven't encountered before. I'm starting on a new project where it'll be a fun little web-based game. A user first logs into the web app and it shows them their saved games or allows them to create a new game. From there it launches them into their game.

I was thinking about creating a "master" database that would have all the user accounts and what games they have saved. And each game would be in it's own database since they are very independent. I thought this might be a good way to separate the data, increase performance, and allow me to backup games individually, etc. Each game database would have the same schema. These game databases can get very large so I thought separating them would increase performance. A user is only connected to 1 game at a time.

So first, is this a good design pattern for this setup? Is having a master database and tons of game databases the way to go?

Second, can this be done with Spring and Hibernate? Can I have multiple dynamic datasources for a user? Every user will initially have a connection to the master and eventually get another connection to a game database. Can I create session factories on the fly when they create a new game and allow them to use it?

Thanks for your help!

[neojonas]

I've been doing a lot of research on this and I guess what I'm going for is a Multi-Tenant Architecture using Spring 3, Hibernate, and MySQL.

I've read in some places that I need a SessionFactory for every database. In my case there could be tens of thousands of databases and it seems SessionFactories are expensive so that doesn't seem to be a good solution.

I would like to have just 2 session factories. One for the master db and then one for the game databases. The game database session factory would have to use different datasources for different users. How to best do this is still unknown to me. Any advice?

A lot of people reference this blog entry (http://blog.springsource.com/2007/01...ource-routing/), but they have their 3 datasources defined in xml. My databases/datasources can be created on the fly during runtime. I'm not sure how to do that with the solution in the blog.

[bkraut]

This is also something that bothers me. I'm developing a hosting application, where the same application would need to read data from the master and instantiate dynamicaly new datasource for specific user session.

[litius]

@neojonas,

I would recommend to look into NoSQL and Distributed Cache to achieve scalability that you are looking for.

The truth of the matter is: Games, Users, Dogs, Cats.. are not really relational, they are what they are, and deserve to be represented with rich data structures rather than flattened and dumped to tables.

There is a growing movement to support NoSQL and Distributed Cache naively in Spring ( check out Spring Data, Spring Gemfire, etc.. projects ). But most of the solutions are easy enough to integrate with Spring on your own.

/Anatoly