Hibernate version:
2.1.6 or 3.0alpha

Name and version of the database you are using:
Oracle 9i2


I am trying to add in support for our data tables in hibernate, but am running into some problems.

Here is the basic setup:
We have multiple servers with differents sets of data tables, let's call these form_a_data, form_b_data,etc. One server might have 3, another 6, etc. Each of these has different fields represented by a separate column ( i.e. not denormalized)

We have a single table that has an entry for every record in the system, which could be used to lookup which is the correct table, let's call this table patientRecords.

What i would like is to have the following setup.

1.) A FormData class which can hold data for any one of these tables.
2.) A PatientRecord class which can refer to any FormData class.
3.) A way of using hsl to query that data. I would like to be able to to write 'where record.forms['baseline'].arrivalDate < record.forms['baseline'].departureDate' or something similar.

I know this is a lot to ask...
Now, I'll tell you what i have tried:
Table-per-class polymorphism:
one-to-one mapping in record to a FormData table with a set of concrete subclasses.This works well,since I can query, although not that elegantly. The problem is that a.) queries run across multiple tables ( not the end of the world), but more importantly, if one of those tables is not there, then it crashes.

CompositeUserType: I implemented something that would return a class called Formdata that has a primary key and a Map of data. This would be ok, except that i don't know how I could query this, since hibernate has no idea what the Map persists to.

I also looked at the Dynamic-class, which almost helps, except that I would like to be able to lookup the metadata at run time.

Is there anyway of doing this? I have spend countless hours working on this, and while I really like hibernate, this is driving me nuts. I am more than happy to contribute code, or whatever in order to get this work, but i need some help.

I have read through all of the discussions of CompositeUsertype, UserType ( which i can't use since i neeed to be able to perform queries), and haven't found what i need.

so... the more I look into this, the more I think that the only way that I can do this is through by implementing the Queryable interface as well as the Persister interface.
This is a real pain, so please tell me if I am missing something.

Well - i am still not sure if this is the best solution, but I wound up implementing a custom persister and the queryable interface. It seems to work so far, although I have a long way to go before it is fully functional.

I will post the code once I get it working.