Trouble with inner joins in hibernate (HQL)

Question

I'm using Struts+Spring+Hibernate and I'm having trouble getting my HQL to execute properly.

I have four objects, objectA, objectB, objectC, and objectD. Object A has a one to many relationship with Object B, and they are related as ObjectB has a FK ObjectA.Id. This pattern continues through the objects ie Object B has a one-many relationship with Object C etc.

What I've been doing right now is calling "FROM ObjectXVO WHERE objectXId = ?" to get a list of objects. My application is becoming more complicated so I need to do some more complicated HQL.

Essentially what I need is, given ObjectBVO.objectBId I need to return certain columns of the ObjectA that has the given ObjectB, certain columns of the ObjectCs that are part of the given ObjectB, and ALL columns of the ObjectDs that are part of the ObjectCs that are part of the given ObjectBs.

This is my SQL Statement that I'm using in Microsoft SQL Server. It's working and hopefully will demonstrate my model.

SELECT ObjectB.ObjectBID, ObjectB.ObjectBName, ObjectC.ObjectCDescription, 
ObjectD.*, ObjectA.ObjectAID
FROM ObjectB, ObjectC, ObjectD, ObjectA
WHERE ObjectB.ObjectBID = 2 
AND ObjectA.ObjectAID = ObjectB.ObjectAID
AND ObjectB.ObjectBID = ObjectC.ObjectBID
AND ObjectD.ObjectCID = ObjectC.ObjectCID

This is the code in the DAOimpl object I'm attempting to use to return List< ObjectDVO >

List<ObjectDVO> objectDs;
try{
String hql = "SELECT ObjectBVO.objectBId, ObjectBVO.objectBName,
    ObjectCVO.objectCDescription, ObjectCVO.objectCId,"
+" ObjectAVO.objectAId, ObjectAVO.objectAName, ObjectDVO"
    +" FROM ObjectBVO, ObjectAVO, ObjectCVO, ObjectDVO"
    +" WHERE ObjectBVO.objectBId = ?"
+" AND ObjectAVO.objectAId = ObjectBVO.objectAId"
+" AND ObjectBVO.objectBId = ObjectCVO.objectBId"
+" AND ObjectDVO.objectCId = ObjectCVO.objectCId";
objectDs = getHibernateTemplate().find(hql, objectBID);
}

This just throws an error saying null. I've looked at the HQL documentation and some tutorials but it's not really clear how to do this more multiple VO objects. Can anyone help? Thanks.

Answer 1

You must assign aliases to your entities, and return/use those aliases.

Moreover, given your description, you should have OneToMany/ManyToOne associations between your entities, but you don't have anything of the sort. All you have is IDs pointing to other entities.

A query that returns multiple fields like that won't return instances of ObjectDVO. It will return a List<Object[]>, where each Object[] will contains one of the values returned by the query: objectBId at index 0, objectBName at index 1, etc.

And finally, a VO is preciseliy not an entity. Your entities shouldn't be named VO. That's basically like appending a Fruit suffix on Animals.

Anyway, if you keep the entities as shown (which you shouldn't do, but introduce associations instead), your query should look like this:

select b.objectBId, b.objectBName, c.objectCDescription, c.objectCId,
       a.objectAId, a.objectAName, d
from ObjectBVO b, ObjectAVO a, ObjectCVO c, ObjectDVO d
where b.objectBId = ?
and a.objectAId = b.objectAId
and b.objectBId = c.objectBId
and d.objectCId = c.objectCId

With ManyToOne associations, you could simply do

select d from D d
inner join fetch d.c c
inner join fetch c.b b
inner join fetch b.a a

and you would get a List<D>. On each D instance, you could the do

String nameOfA = d.getC().getB().getA().getName();

Last note: a property is part of a class. So naming objectAId the ID of ObjectA is redundant. Name it id: objectA.getId() is much more readable than objectA.getObjectAId().