Good strategy advice required

Legga · **Joined:** Thu Jun 30, 2005 10:29 am **Posts:** 4

Please advice what is best for the following scenario:
I have 3 tables:
A <- B <- C
i.e. table C references B using a foreign key, while B in turn references A.
What I want to achieve is when class C is being retreived from the database, I need to fetch along with it a property of its corresponding class A. This property is for info only, it will never be updateable.
I need a GOOD strategy for that, preferrably only using mapping files without any additional programming.
I've read all the docs again and couldn't find a proper solution.
So far I know at least 3 strategies for doing this, but none of them suits me (see below).

Please, advice how to do it properly.

Strategy 1
Map all 3 classes using many-to-one and get all 3 instances (A,B and C) fetched from the database when I retrieve the instance of C. So the property of the corresponding class A would be accessible this way:
c.getB().getA().getItsProperty().
I don't like this one because I want to avoid retreiving all the instances while intermediate ones (class B in this case) are needed only for walking through its foreign key towards class A.

Strategy 2
When I get an instance of C, I initialise it's property in my code using HQL. This code may reside either in the code that uses C or in an Interceptor's onLoad() event.
I don't like this approach since it ivolves programming in every similar case. I would prefer using mapping only, if possible.

Strategy 3
Having a property, declared using a formula tag:
<property>
<formula>(select A.myColumn from A join B join C0_)</formula>
</property>
This one is almost what I want, but there's one serious drawback here - it relies on the Hibernate's naming convention for table aliases (C0_ in this case). I don't want to stick with this assumption as if in the next release this naming convention will be changed, I will have to re-write formulas for all these columns.

As I understand, this is quite common problem in DB programming.
(For instance, if you have tables Department <- Team <- Employee you might want to display Employee's department name when you retrieve an Employee.)
I assume there must be a more decent solution for this common problem in Hibernate.
Please, suggest one if you know.

LaLiLuna · **Posted:** Mon Jan 09, 2006 5:52 am

To create entries you will need to use
3 classes and 3 mappings.
e.g. department, team, member
When reading entries a criteria query will help you and will not load anything you will not need.
As lazy is the default, the teams of a department will not be automatically loaded when the department is loaded.
To read all membersand the department info you may create a query as followed:

Code:

      List results = session.createQuery("select  m,d from Member m join m.team as t join t.department as d where d= ?")
      .setEntity(0, department)
      .list();
      for (Iterator iter = results.iterator(); iter.hasNext();) {
         Object tuple[] = ( Object[]) iter.next();
         log.debug("--"+(Member)tuple[0]);
         log.debug((Department)tuple[1]);
      }
      tx.commit();

Please rate if this is ok for you!
Regards
Sebastian

Legga · **Joined:** Thu Jun 30, 2005 10:29 am **Posts:** 4

Thank you Sebastian, but this is not exactly what I was looking for.
This code still retrieves the entire Department object while I only need its single property. Imagine if Department table has 300 columns!

This Department's property will only be a read-only, so it's enough for me to retreive it and never save. In this case loading the entire row from the DB is quite an overhead...

But thanks anyway!

LaLiLuna · **Posted:** Tue Jan 10, 2006 7:07 pm

In this case you just change
select m,d
to
select m,d.name

that's all.

Regards Sebastian

Please rate, if this helped.