Connecting two different databases using Hibernate

[marlajee Borstone]

Hi,
I am developing a project using Hibernate and the requirement is to connect to two different Databases
1. ORacle and
2. SQL Server.
The same set of tables exist in both the tables.(i.e. name of Tables and their fields are same in both the databases).
Both the databases need to be accessed during the life cycle of request from user.
How I can I achieve this using Hibernate ?
I have read few blogs on web, some speaks:
- by making two different hibernate.cfg.xml file and having two SessionFactory Instances.
- Somewhere I can see as Using JTA.
- Some says using ThreadLocal .


But I don't get any stepwise example of codes for any of above approaches.
Can someone plesae help me to point to such step by step solution from where I can get this solved with better understanding of these approaches.

The more descriptive will be more appreciated as I am not expert in Hibernate

regards,
Dhaansumaal

[Cameron Wallace McKenzie]

- by making two different hibernate.cfg.xml file and having two SessionFactory Instances.

This is exactly what you do.

You need two hibernate configuration files. You'll need to call them hibernate_sql.cfg.xml and hiberante_oracle.cfg.xml or something like that to differentiate them.

Then, when you initialized your Configuration object, you specifiy the name of the config files. So, you'll have two Configuration objects, from which you'll get two SessionFactory objects.

When you need an Oracle session, go to the SessionFactory created with the oracle configuration file. Do the same thing for SQL when an SQL file is needed.

There is also the Hibernate Shards project for connecting to multiple databases, but that might be more tooling than you need.

-Cameron McKenzie

[marlajee Borstone]

HI Cameron, thanks for directing me to one specific approach.
However, since the name of Tables and their structure are same in both the databases (SQL & Oracle), does a Single POJO class will meet the pupose of two different databases here ?
Will it not affect the caching and performation of application ?


regards,
Dhansuumaal

[Cameron Wallace McKenzie]

Well, you're blazing new trails on this one. I'm not experienced with this particular scenario, so don't take my word as gospel.

I do believe the classes must be different, so the may have the same names, but they would need to be placed in separate packages. It's certainly sounding a little messy.

If you get it to work, I certainly think it would make an interesting blog, so don't keep any hibernate magic you 'conjure up' to yourself.

-Cameron McKenzie