I'm looking into the possibilities of basing my next project on Hibernate, but would like your input on how to solve problems like the one described below. The description is over-simplified in order to keep the details to a minimum, but still accurately describes the problem I'm trying to solve. Here goes:

Let's assume that we're building an application for absent minded professors to keep track of tests that they've been giving in class. There are five professors and fifty students and each professor typically gives five tests over the course of a semester. The students are stored in a table STUDENT which is on one of the Oracle databases that the school's IT department manages (the professors have only read access to this table). The IT department is very paranoid about letting third party applications have access to this schema and refuses to add new tables to this database in order to support our application. In order to work around this, each professor will install the application locally on his office desktop along with a local database (a JDBC compatible database, for instance MySQL). This local database will hold two tables: TEST and GRADE. Each professor will be maintaining his or her tables exclusively.

The tables are defined like this:

STUDENT (student_id, name) where student_is is a long and the primary key, and name is a string.

TEST (test_id, name) where test_id is a long and the primary key, and name is a string.

GRADE (test_id, student_id, grade) where test_id and student_id are longs and the primary key, and grade is a string.

We would like to design a class Student which will hold a collection of tests that the student has taken. The class that holds the test grade is called TestGrade and contains the name of the test and the grade received.

Questions:

1. What approaches exist for creating the Student objects such that they will contain the aforementioned collection of TestGrades? I understand that if both databases were Oracle instances we could have granted access to the STUDENT table and accessed it through a synonym and gone about this as usual. However, since the table is in another database it's not that straightforward. It's my understanding that currently the only way to access heterogenous databases is to define multiple session factories and use an extra query to manually create the collection, perhaps in Student.setStudentId()? Is this correct? Is there a better way, i.e. would it make sense to query for the TestGrade objects first so that the results would be cached in Hibernate and subsequent each call to get the respective student's test grades would not incur a roundtrip to the database?

2. Would the same approach work if the TEST and GRADE tables were on different databases, i.e. we would have three different databases (Oracle, MySQL, and DB2 for instance)?

3. Is there a way to address this on a lower-level or in an alternative way? Somebody in this forum mentioned clustered JDBC, but I think that's only for replicated databases (i.e. load balancing)? I guess at the end of the day it all boils down to where you want the join to occur and the answer is probably as close to the sources as possible. Would it be feasible to provide some support for this in the Hibernate mapping? For instance, referencing an ORM mapping (e.g. TestGrade) using the context of the owner (the Student) even if it's defined for and accessed by another session? I read elsewhere (C-JDBC mailing list) that somebody suggested that C-JDBC include support for doing in-memory joins -- would that be something to consider for Hibernate?

Your feedback is greatly appreciated, thank you in advance!

-Kaare

First, no, Hibernate will not be implementing in-memory joins.

C-JDBC is not just for load-balancing of homogeneous data bases. According to their charter, they are implementing a driver capable of providing unified access to even heterogeneous databases as well. Now, how far along that curve they are is another question ;)

If these were all ORacles databases, the approach I would suggest (that I've used quite a bit with ORM tools) is to link them all using DBLINKs.

If that is not an option (nor C-JDBC), you really only have two options:
1) setting up multiple session factories, one for each database, and manually handling the assocations between the entities.
2) replication; I'd consider simply replicating the student tables to the local databases. Since there is only read-access to the IT's Oracle DB, there's no question of which source is the master, which is the normal difficulty in replication scenarios.

The skinny is that in order to have Hibernate manage the assocation between a Student and their Tests/Grades, all three tables would need to be accessible from a single session factory. A session factory can only access things that are "seeable" through a single Datasource/Connection.