How can I configure hibernate to use context-specific connection information?

Question

I'm writing a J2SE (Note, not JEE) application using Hibernate, and I need to provide a different connection to Hibernate for each thread of execution. These connections must be pooled, and each one has at the very least different authentication and, possibly, a different JDBC URL. The connections will be re-used (as can be inferred from the pooling requirement).

What parts of Hibernate/C3P0/et al do I have to override? Can this be accomplished with those tools, or do I need to write my own pooling data source?

Answer 1

You have two questions here:

1. Connections are not thread safe, so each thread must have its own connection. Since you're working with Hibernate, what your application sees is actually a Session obtained from a SessionFactory. To utilize this, you call the SessionFactory#getCurrentSession() method, and configure the current session context in hibernate.cfg.xml:

   <property name="current_session_context_class">thread</property>

   If you've properly configured thread pooling (using c3po or whatever pooling mechanism you favor) in hibernate.cfg.xml, then each thread will get a connection from that pool.
2. To maintain multiple data sources that the application may need to work with, then you need to configure a separate SessionFactory for each JDBC url you'd like to access. In your application you'll need to have some means of selecting with SessionFactory you'll need to choose (e.g. "client ID"), using this you can manage each of the SessionFactory instances in a Map or somesuch data structure (in a JEE app you'd get a reference from JNDI).

To summarize (and generalize), basically a SessionFactory is essentially huge wrapper around a DataSource (and attendant connection pool). It is read-only (and hence thread safe), heavyweight and static, constructed once, and knows everything it needs to about a given DataSource.

A Session, on the other hand is essentially a lightweight wrapper around a Connection. It is not thread safe, often short-lived, and intended to be used and then thrown away.

Hope this helps!

Answer 2

I think the best course of action would be creating a SessionFactory for each data source, with possibly pooled connections - that's what's eqbridges suggested in his answer.

Now, Hibernate does have a ConnectionProvider hook, so I suppose you could write an implementation that would return Connections to different data sources, depending on current thread of execution and some additional parameters. Theoretically, you can then have one SessionFactory instance, which will be using different connections to different databases, supplied by your custom ConnectionProvider implementation. But, one SessionFactory holds quite a bit of data, and that data is then used by Hibernate internally, when opening a Session for a unit of work. Plus, there's a second-level cache associated with it as well.

Unfortunately, how will the factory and Sessions you open from it behave in the face of such a provider is anybody's guess. It feels like a hack to me, and I doubt it was ever considered a viable use-case for a SessionFactory. It can possibly lead to all kinds of, possibly very subtle, bugs or data corruption.

On another note, be sure to exactly measure the cost of creating multiple SessionFactories - it may not be as high as you think. Be sure to compare that with the cost of simply opening the needed JDBC connections. I don't know what kind of results you might get, but I think you should be sure about performance before you resort to more hackish solutions.