I've read a lot of posts and blogs about this issue, which essentially boils down to this: I have a database schema that is replicated across X databases, this number in practice will most likely be in the hundreds. Each database represents one corporate account entity. This structure is necessary for encapsulation of data, ease of backup, and ability to horizontally scale databases across multiple DB servers if that becomes an issue. I've come very close to accepting the fact that this can't be done with a single hibernate SessionFactory. From what I've read, the SessionFactory is "expensive to create." Once created, how expensive is it to keep around? Is it practical to think that I could have hundreds of created-on-demand session factories registered in memory which will be used by each client as necessary and expire after some period of inactivity? Has anyone else used hibernate with a multiple database setup (or even multiple tables representing each account)? How did you accomplish it? Thank you in advance!

You should check on the hibernate shards project. This is all about partitioning data across multiple databases. I think the database schema has to be identical on every database but it handles merging results together when you query and inserting/updating (based on a defined partition "key").

It's not mature yet but probably worth a look.

I had looked into shards previously and I believe my issue there was the same as it is with hibernate core, and that is one SessionFactory only refers to one database connection (actual or logical, in the case of shards). Shards sounds like a great way to distribute tons of data across many DBs but at least from what I was able to glean, I couldn't see it as a solution to my account-specific database issue.

after revisiting shards documentation and messing with the source code, it appears that this is probably going to be the solution I was looking for. Thanks for your response, I am finally going to give you the credit you deserve for the answer!

I have a similar requirement with multiple schema with identical table structure, one schema for each of our clients.

After I read your post I went to check out the Shard doc. It seems like for Shard to work all object for a single table must have a unique id across all Shards. But if that's the case I don't really need to use Shard...Hibernate 2nd-level cache will work with a single SessionFactory for multiple schemas if the object id's is unique across all databases....

Am I missing something?

I will preface this response with the disclaimer that I've only been working with hibernate for about a month now and definitely consider myself to be a newbie... but my understanding of a SessionFactory object is that it abstracts a connection (or pool of connections) to a single DataSource. Therefore I think you can only address objects that have an id resident in a table in that database. You are probably right about the second level cache, because that can be set up to be application-scoped in which case it applies for just more than one SessionFactory, but I think at the primary cache level, which is just for once session instance, you are restricted to one database.

This is my understanding which is likely slightly convoluted. I would love to have someone prove me wrong on this issue because I am trying to use multiple schema, like you are.

Thanks for your reply......

I think you are absolutely correct in that 1st-level caching will work properly because it's session based caching.

My main concern is with 2nd-level caching and i think that if the Object Id is unique across all schemas then 2L caching will work properly without the usage of Shard.

Thanks.
I'm a newbie too...