I've seen in other posts Gavin suggesting use of a single session factory when connecting to multiple datasources sharing the same schema and domain model.

We provide the connection to our session factories openSession method in a thread local at the start of a transaction. The implications we see are:

- limited to working with one datasource per tx since 1st level cache may be invalid (i.e. two entities with the same id are accessed in a single tx).
- 2nd level cache not applicable, would introduce invalid data. Limited to transaction level cache.

Are there any other caveats?

Roll

Hi,

Apologies for bumping this post, however, I'd love to hear some reaction from the Hibernate team.

This is a topic of heated debate in my organization as we've got to address this in a coming iteration.

Thanks,
Roll

No thoughts?

This is what we decided to do:

We're going with multiple session factories. Although it is slightly more to maintain (multiple mbean services in JBoss + multiple data sources) and we currently don't see any benefit in our application of 2nd level cache, we don't want to constrain ourselves to not having the cache in the future. Most of our entities are highly transactional (lots of reads and writes) and very few read-only entities.

On an interesting side note: we're passing the session factory name in a custom JAAS login module that connects to the correct factory upon authentication. The correct session factory is then used for the duration of that principles session until he logs out.

Any comments welcome...

I'm on a project with similar requirements (i.e. the user will log in and basically select the database they need to work with - same schema and domain model) to your's. We've got the added complication of using Spring on top of Hibernate.

Just curious how you're making the correct SessionFactory apply for the length of a principal's HTTP session?

We're using JBoss as our EJB container. I'm assuming you're using Spring but not using EJBs, right?

In any case, what we do is authenticate our user's via JAAS and put the session factory name in a custom principal and stuff it into the session context. Whenever a user makes a subsequent invocation to the middle-tier the principal will be available to the user's session context. When we prepare a session, we pull out the session factory name from the principal in the session context, and use it to retrieve the correct session factory for each transaction.

We use the transaction-per-request model.

I understand. You're right in that we're not using EJBs. :)

I think we'll end up stuffing the user's selection into a HTTP Session attribute and use a Servlet Filter to place that value (and clean it up after the request) into a ThreadLocal. That way we don't have to pass around that info through the tiers and can use that for the basis of the selection.