Hiya. Re-typing all of this cause I lost the first one in a power cut. Argh.
I'm working on a rather complex requirement whereby certain tables need to have user-configurable extra fields based on it's category. The data for these extra fields are stored in extra tables, and each instance may have more than one category.
I've got a moderately functional version working using plain jdbc, but it's rather unwieldly, and I'd really like to be able to use hibernate. The only pojo (of relevance) is something like:
Code:
class Thing {
long id;
String someOtherField;
Map<String,Object> extraFields;
}
A number of "categories" are defined (and stored in Java configuration objects), such as
Code:
Category A:
String x
int y
Category B:
Date w
String z
The "root" table (thing) is part of the schema, the category tables are generated dynamically (with a CREATE statement executed through jdbc), ending up with something like:
Code:
table thing
id bigint //primary key
someotherfield varchar
table thing_a
id bigint //primary key AND foreign key to thing.id
x varchar
y int
table thing_b
id bigint //primary key AND foreign key to thing.id
w date
z varchar
Now supposing we have the following data:
Code:
thing:
id|other
--------
1 | p
2 | q
3 | r
4 | s
thing_a:
id| x| y
---------
2 | m| n
4 | o| p
thing_b:
id| w| z
---------
3 | i | j
4 | k | l
(I do have the option of also including a "category" field in the "thing" table, however this is redundant; the existence of rows in the other tables implies the categories; 1 had no category, 2 is cat. a, 3 is cat b and 4 is both categories a and b.)
The SELECT statement is generated based on the entered category; java code looks at the configuration information and generates the query. If no category is entered, sql is
Code:
SELECT * FROM thing
and 4 rows are returned. The contents are used to populate the regular fields of the Thing pojos, and the map is left empty.
If category A is selected, it executes
Code:
SELECT * FROM thing t JOIN thing_a a ON a.id=t.id
Rows 2 and 4 are returned. The data from table "thing" populates the pojo and the date from the "thing_a" table goes in the map. Category B works similarly.
If both category A and B are selected (or some "virtual" category C, which is inherits both of them), we get
Code:
SELECT * FROM thing t JOIN thing_a a ON a.id=t.id JOIN thing_b b ON b.id = t.id
Only row 4 is returned, and all the data from both A and B tables are put in the map.
This allows arbitrary WHERE clauses to be added so that I can filter/search/sort based on any of the fields in either the root table or any of the selected extra tables (provided there's no name clashes, which I ensure in the configuration object).
So my question is, can I use hibernate to do this?
I've looked at the option of dynamically generating hbm.xml files for the extra fields, but this doesn't seem to meet the criteria. What I've described is quite similar to the <joined-subclass> concept, but this doesn't allow the multiple inheritance (which I need), and worse, since it's done at runtime, I can't have an actual class for each category, so the data comes back as a map. If I do a query on the root table (Thing), then those with no category come back as Thing instances, but those with a category come back as a map.
I've also looked at using <dynamic-component>, but this also doesn't work; it doesn't support the multiple inheritance (again), and would put the the entirety of each category in a table each, meaning I couldn't do polymorphic queries (which I need to do).
So what I'm thinking of is keeping the jdbc code for the extra fields, and making the root Thing class a hibernate managed pojo. The extraFields field would have some special annotation (formula?) that allows me to inject the correct join sql for the query.
The otehr idea I haven't really thought through is to have multiple mappings for the Thing class, one for each category (and combination of categories) that has a one-to-one mapping defined for the extraFields attribute, each one somehow specifying the table to join with. Not sure if I can have multiple mappings for the same class, or if I can use a one-to-one mapping to populate a map with the fields of another table.
Of course, this all only covers the R, and I'd need to work out how to do the CUD. Again, this is already done in the jdbc version I've done, and works as a hook when a create or update is done on the root object; the values in the map are used to construct a CREATE or UPDATE statement that's executed through jdbc. Any hibernate solution would also need to allow something similar.
I hope that all makes sense. Is there something like this available? Or am I stuck with my dodgy jdbc solution, with none of the advantages of hibernate?
Cheers.
Login
FAQ
Search