Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Apologies for previous post: I pressed submit by accident before the message was complete:

Hibernate version: 3

Hi,

I have a database design where multiple conceptual "entities" have been stored into a single table hierarchy. Conceptually there is a header and a key-value table with a variable number of rows. Different entities are stored into the same tables by storing values as separate rows on the attribute table. An existing (non-java) application has metadata to map from the key-value structure into its internal structures. The table hierarchy is simply:

header -< attribute

however, based on the type of header row, the number and type of attributes changes. E.g.

header type 1 | attributes: 1:int 2:string
map to 1 object with int, string properties
header type 2 | attributes: 1:string 2:money 3:date
map to 1 object with string, money, date properties

I want to map, discriminating on the type of the header, the different attribute row values into properties of an object. This is close to a single class per table hierarchy with discriminator, but I do not want the multiple attributes to be placed into a collection - I need to map these to properties on a single object instance.

In the example above, the header type 1 would have 2 rows on the attribute table, these would be mapped to one object instance with an int and string property. The header type 2 would have 3 rows on the attribute table, and would map to one object instance with string, money and date properties.

To complicate matters - there are multiple attribute tables, based on type.


Solutions Examined:

union subclass: This would return one attribute value per row, and result in a collection. I would need to map from the collection to the object, with verbose custom code.

custom query / sql : This would define sql, joining the attribute table on explicitly for each value (i.e. multiple times in the one select), to reformat the multiple rows into multiple columns. This would seem to be fairly clean, apart from the complex and verbose sql.

Question: Are there any other alternative mapping approaches - ideally one that would allow me to use the union subclass, but then (somewhat like a filter) map separate rows returned into distinct properties, as opposed to instances in a collection.

Your table design is like a dynamic Map object. So, you might consider loading these attributes somehow into a Map, or collection. In fact that sees how you're thinking about your database. So, no need for too much custom code, just make getters and setters that use the map or other collection to get at the attributes.

If you want to map this generic/dynamic database into fixed POJOs, then you could consider the join mapping:



OTOH it strikes me why you would want a generic database but then use it with statically and concretely defined POJOs? Perhaps this is a good time to rethink what you really need. Could be that your database is the way it is because it was designed with some weak handcrafted persistence strategy in mind???