I am building an application which will need to present a homogenous view of data from disparate datasources. In other words, I have about seven different databases that each have similiar table structure. The key being similiar as they are not all alike, some tables have rows that others do not.

So, my general thought is to define a series of Interfaces and then have objects specific to each database implement that interface.

So for instance I have interface Foo. I would then create the following POJOs: Foo_DB1, Foo_DB2, Foo_DB3, Foo_DB4, Foo_DB5, Foo_DB6, Foo_DB7. I would configure the SessionFactory with the hbm.xml files for each of the 7 different foo objects. The only way that the Session would know which one it was connecting to would by the java.sql.Connection object I pass to it.

So, I have two questions.
#1. What does everyone think about that idea? (If you don't like it, I'd really like alternative ideas.)
#2. Can I run this query "from com.mypackage.Foo foo" and pick up the proper Foo_DB object?

Cheers,
Chris

This will not work that simply, if you map foo_db2 to a table which is not present in db1, this will surely not work. You will need different mapping files for every database, which you select accordingly on SessionFactory build time. In this case you can drop the whole multiple class thing and just map a Foo class, but different in every mapping file. Hibernate can not work on different databases for the same mapping file, so you have to merge the results manually I think.

Agreed with Michael.
If the extra columns are not used and have no constraints, the tables and columns name are the exact same, then you can use the same mapping (don't map extra columns).
Otherwise, do one mapping per concrete DB.

Your java code will be the same.

You cannot work on several DB in 1 session.

Okay, I am thinking we are talking about the same thing. From my original post:
> I would configure the SessionFactory with the hbm.xml files for each of the 7 different foo objects.

Sorry, I thought I was making it clear that I was going to have 7 hbm.xml files, one per database.

Then again, perhaps I am going about this all wrong. The basic premise is that I have several different database all which will eventually map to same Hibernate object.

Okay, fine. So just use one class, and map it in 7 different ways.

Thanks a lot!