I am wondering how it might be possible to work with multiple databases at the same time with Hibernate? I have a scenario with multiple 2 different databases (different DB software, different platforms). AFAIK, this would require Two SessionFactories(each one configured for respective database), then maintaining two Hibernate sessions in my application.

However, while each individual object can be guaranteed to be persisted in either one database or the other, the domain object model is split across the two databases (part is data shared with legacy systems). This means we will be trying to load object **graphs** which span databases - The mappings dont allow objects to be mapped to Tables in different databases, and I can't readily see how we could do this directly in Hibernate.

We had Some discussion about possibly front-ending the two databases with third party software, and this is an option but this has performance drawbacks in our specific case.

Has anybody acheived the same end result in some other way, or have any ideas, patters or suggestions on how it might be possible (no matter how daft!)?

You can not map associations across databases. This has to be solved at the JDBC or database level (Oracle makes this really easy).

It is easy for oracle for same or differnet OS platform, but I think that hibernian have different DB platform
Hibernian,
Do you try cluster jdbc ? I think that You can set hibernate and cluster JDBC

regards

I deal with this extensively. Hibernate seems able to handle a few session factories without any real problem when the database vendors vary or are not linked or federated.

What I did was use the ServletFilter pattern but instead of having the ThreadLocal contain Session objects mine contains a lazily-built map of Session objects (luckily never more than 3 - 5) keyed by the name of the database.

My plan is that I can eventually make my SessionUtil class and mappings smart so it either aupports a single session or one of several sessions depending on which RDBMS my customer is running and whether or not it is federated or linked.

unfortunately one of the database platforms is not oracle or any of the main databases(the other is SQL server), and front-ending these together as a single database at the database level is something we have looked at - it works but is not a runner in our case for certain reasons.
I understand that we can have multiple sessions, but the point is that we want to do association mapping across the databases (which are different vendors).
I realise this is a tall order, and I suspect Christian is right... but might be a nice killer feature in Hibernate3.x perhaps !
many thanks for the input.

Maybe http://c-jdbc.objectweb.org/ helps.

HTH
Ernst

Christian, can you provide more detail on how Oracle make this easy (for use with Hibernate)?

You make database link to other (remote/other local) database and synonym to table (object) on other database - after this you work with synonym similra local table/object

Best