Designing objects for Hibernate

Question

I need some help with a design problem. I'm having trouble wrapping my head around how to design an object (or maybe multiple objects) so that I can query the way I would like to.

Let me start by showing a sql query that returns exactly the information I want:

select distinct rights_table.receiver_guid, user_info.name
from rights_table 
inner join user_info
on rights_table.receiver_guid = user_info.guid
where rights_table.giver_guid = '_this_is_the_argument_'

So user_info has one entry for each user. rights_table has info about rights that giver_guid has delegated to receiver_guid. I don't care what the rights are right now - I just want to be able to take a giver_guid and get back a list of all the guids and names of anyone he has given rights to. I'm having trouble conceptualizing how to translate these tables to an object model in such a way that I can perform that query.

If it helps to know, receiver_guid and giver_guid are both (could both be) many-to-one mappings to user_info.guid. user_info.guid is the pk on that table; the pk on rights_table is another column not mentioned here.

Edit: My solution:

As Tim suggested, I put many-to-one mappings on the rights table "object" that linked the rights table to the User table. It took me a while to understand that Hibernate does joins implicity for you when you do mappings like this, so here is what I basically ended up with for HQL:

String hqlQuery =           
    "select right.recipient_guid, user.name " +
    "from rightsTable right, userInfo user " +
    "where right.recipient_guid = user.guid and right.giver_guid like :giver_guid";

Honestly, I don't even know if there are joins happening behind the scene there, but I know that this gives me the information I originally wanted. If anyone has suggestions for how to do this query more efficiently please let me know.

Answer 1

If it helps to know, receiver_guid and giver_guid are both (could both be) many-to-one mappings to user_info.guid. user_info.guid is the pk on that table; the pk on rights_table is another column not mentioned here.

I would try out the following:

• A user has a one-to-many relationship "rightsGiven" to the right entity.
• A user has a one-to-many relationship "rightsReceived" to the right entity.
• A right has a one-to-one relationship "givenBy" to a user.
• A right has a one-to-one relationship "givenTo" to a user.

To get the list of users who obtained some rights by user X, then

• load user X
• traverse the collection "righstGiven" of rights
• for each right get the "givenTo" user

Once you have on object loaded, when you traverse the graph, hibernate will load the object lazily for you. This can result in many queries being executed under the hood by hibernate, especially the Select N+1 problem. Use then eager fetching, so that hibernate uses then a join. E.g. @OneToMany(fetch = FetchType.EAGER). Set eager fetching on "rightsGiven" and "givenTo".

You can then control the so-called max fetch depth to ensure hibernate won't load the whole graph eagerly. In this case we have 2 relationship that need to be fetched eagerly (rightsGiven + givenTo), so set it to 2. This should result in query with two outer join.

Answer 2

Trying to map queries and tables to objects is hard because the conceptual models differ so much. Putting your SQL statement aside and just working from the data you want to extrapolate, I arrive at the following Objects:

• User
  (id, name, Set< RightsAssignment> rights_given, Set< RightsAssignment> rights_recieved, info, ...)
• Right
  (id, name, level, Set< RightsAssignment> assignments, info, ...)
• RightsAssingment
  (id, giver_id, reciever_id, right_id, date, info...)

This way RightsAssignment (pardon the name, change it to something that fits) is an assignment of a right by a user to another user, with optional additional information like the assignment.date field above (you might not need it now, but it makes your models more extendable).
The fields in RightsAssignment would have a ManyToOne's on giver, reciever and right, where as User and Right will have OneToMany's mapped by the fields in RightsAssignment.

To get a list of rights assigned by any one user, you just have to query the RightsAssignment table for entries where the giver_id matches the user.id.

Hope this helps!