Complex hibernate mapping advice needed. experience, hibernate, determine, specific

amy

This is a Hibernate 3.1 specific question. I also posted it on the Hibernate forum but my experience over there isn't as good as over here hence the post here  .

I have a problem mapping the following.

We have 1 user table. This table has a relation to 1 of 3 tables to create a join with another of 3 tables and to determine the type (spare me I didn't come up with that solution  ).

Currently to retrieve a user we chech the 3 tables to see in which of the tables the user is present and retrieve the record from the desired table. Which isn't a big problem for retrieving 1 user, but when retrieving 100s of records it is a problem.

Schematic representation

Code: /      employee type 1 join table --- employee type1 data table
[USER]------    employee type 2 join table --- employee type2 data table  \      employee type 3 join table --- employee type3 data table
Currently I'm trying to find a solution to place this in the mapping. However I seem not be able to come up with a solution which is managable by Hibernate.

I would like to create a view from all the tables, containing the userId and employee type, and then use the employee type as the discriminator to retrieve the correct employee. However I seem not to be able to specify a select different from the mapping.

Any suggestions would be helpful.

Hi Marten,

Firstly, ahhhhhhhhhh its horrible.  I hate being dumped with jobs like this before, good luck .

Is there no chance of changing the data at all?  Personally compressing all employee type X join table and employee typeX data tables into just employee type join table and employee type data table would be nice if possible.  If not adding some kind of discriminator to the User table would be good.

If you can't change the tables at all, I'll have to give it more thought.

It's horrible indeed, my contractor also thought so and that is why I'm here .

I can change the data partially. I have full control over the User table and the 3 join tables. The other 3 tables containing the employee information I have no control over, they are part of an external system.

I considered creating a view compressing the join type x tables and employee data typex tables. I could give it a try again, just checked it is only readonly data so that should be good enough with a view.

In which way would adding a discriminator to the User table be good? I always thought that we need the discriminator in the table/view containing the data?


Originally Posted by mdeinumIn which way would adding a discriminator to the User table be good? I always thought that we need the discriminator in the table/view containing the data?

I was really just thinking out loud.  I suppose what I should have asked is what query are you struggling with.  If I can see how you are trying to query the data and the currently strategy it would help.

Within the parameters set (not able to change the datastructure of the remote tables) I struggle with the mapping to retrieve the correct employee(type) with the user. It is merely a mapping using.


Originally Posted by mdeinumWithin the parameters set (not able to change the datastructure of the remote tables) I struggle with the mapping to retrieve the correct employee(type) with the user. It is merely a mapping using.

I know Hibernate supports formulas on joins, I think this is just basically SQL to retrieve the data.  

Another idea was adding a discriminator to the User table.  You could then have subclasses of User each with the correct table mappings.  The User super class can actually have the code for all of them, the subclasses are just necessary to do the mappings.  This does however require some data denormalization.

Other than that I'm not really sure.

I managed to get it working. I created a view which integrates the 3 type join tables and 3 data tables. Added a discriminator column, create a mapping for sublcasses.

I needed to add a dummy userId property to the employee class, for the mapping. Employee has another primarykey then the user.

But it is working. Now figure out if performance is better or worse

Sounds like errr......... fun.  Time to get the stopwatch out .