I'm working on a system that must use multiple databases (several Oracle, DB2 servers). The information I require for my upper level objects is split across the databases. These systems were setup at different locations by different managments so some information is missing in some places, different id formats, etc. I've extracted the field information I require and boiled it down to the bear minimum I need to make the application assignment I've been given work.

Here I'll try to provide an example to explain this better:

//goal object (I assume I'll have to make subclasses to handle this correctly)
User (f_name, l_name, phone, address)

There's a table in one of the Oracle databases that is pooled with user information from all of the database systems (user_id, f_name, l_name), but no address infomation is gathered. The user_id field is specific to the database that provided it and there's a source_system field that distinguishes how to lookup the "user" on the correct database. The DB2 database contains address information, but the oracle users don't. A requirement for this project is the ability to display the address information for users. If no address information exists then the user must enter it before proceeding. The address information should be kept "live" whenever possible (read from the production database, no snapshots allowed).

I've decided that it's probally best to included some type of source_system_id to allow the system to identify which database to pull the information from, but I'm interested in getting some feedback from the user community to see if they have any suggestions for me.

My apologies, the post above got submitted before I finished writing it.

In addition to the information above I've been considering user a subclass/composite setup. For example:

User (Parent)
OracleUser (Child)
DB2User (Child)

The information that both systems share would be part of the User class reading from the centralized Oracle table. The DB2User should pull the address information from the DB2 system and the Oracle system would require the addition of a user_address table somewhere that it can store the addresses entered. I'm not sure how to achieve this functionality though. The DB2/OracleUsers seem like they would need to be composite classes containing a User element and adding the address functionality, but not subclassing User directly, since they will need to use different Session instances and Dialects.

Thanks you in advance for your suggestions and advice,
Tyler Pitchford

P.S. Once again I apologize for the half post above and any typos it contains.

Just a comment: You can not use one SessionFactory to access two databases at the same time. You have two use two, each with its own configuration/mappings. You can also not maintain object associations across databases.

Michael,

Thank you for your reply. I previously read the documentation on requiring multiple SessionFactory objects and incorprated that into my design. I also assumed that Hibernate didn't natively support associations across databases. Would you happen to know if any work/research has been done on the topic of hetrogeneous joins/associations across multiple databases, specifically related to Hibernate (didn't catch anything on the forums before I posted this thread)?

I'm working on constructing a simple prototype of the "composition" ideas I mentioned above to see if it's a feasible idea. I wanted to verify that hibernate would correctly manage a DB2User that was composed of a User object and the DB2 specfic mappings.

Example of the concept:


then in code:


My hope is that hibernate would marshall the DB2User specific info to the DB2 tables and the User specific information to the all server "dump" tables. While it's not as "clean" in code as true inheritance, it should serve the purpose. Hopefully, I'll have the time to test that next week as it seems like a good option for this project and a "decent" way to fake cross database associations.

Your thoughts?,
Tyler

That should of course work, you are in effect not doing anything unnormal, if i get this correct this are just two different mappings with two SessionFactorys. Should not be a problem.

I don't really know about work on cross-database-joins, probably the cjdbc folks are working/have planed something in that direction, but that certainly would not be an easy task.

I wonder if Hibernate3 allows references between Hibernate mappings of the tables residing in different databases?

For example, say I have a table User, which is stored in database A. I also have a table Teacher, which is stored in database B and is mapped to a subclass of table A. User.hbm.xml mappings for the User table are loaded into one SessionFactory, which connects to the database A, and Teacher.hbm.xml mappings are loaded in a different SessionFactory, which connects to the database B.

So, the question is whether this scenario is supported by Hibernate3? And if it is, then how should the Teacher table be mapped to the User table? Should it be mapped as a subclass, joined-subclass, or something else.

If this is not supported by Hibernate, then what would be a good workaround for this problem?

Thanks.

Alec

It looks like Ingres "gateway" and OpenSQL stuff is free. This stuff can integrate databases from different vendors and application will see it as single database (distributed database). Probably it is transactional ( if your DB vesion supports XA).

baliukas is on the right track....cross-joining between databases should be done at a level LOWER than jdbc (mainly at the db-level) to allow for proper transactions semantics. (and even if you dont care about transactions then performance would also be better when done at the lower layers)