Hibernate custom SQL query with join - avoiding returning a list of arrays

Question

I have a custom SQL query in Hibernate (3.5.2) in which I want to return a mapped object, and an associated (joined) object. However, Hibernate seems to be giving me a list of arrays rather than a list of objects.

To simplify my situation a bit :-

Entity1 contains a foreign key to Entity2, and the mapped objects are set up so that Entity1 has an object property referencing Entity2. I want to retrieve a list of Entity1 objects, but with the associated object reference already initialised (so that the associated object has been loaded).

Now, I can do this with a custom SQL query like this:

final SQLQuery qry = hibernateSession.createSQLQuery(
    "select {entity1.*}, {entity2.*} from entity1 inner join entity2 on entity1.fk = entity2.id ");

qry.setReadOnly(true);
qry.addEntity("entity1", Entity1.class);
qry.addJoin("entity2", "entity1.entity2");

List list = qry.list();  // Returns list of arrays!!

This works, in that all the Entity1 objects are correctly initialised. However, the list that I get back IS NOT a plain list of Entity1 objects. It is in fact a list of arrays, where each array contains 2 elements - Entity1 and Entity2. I'm assuming this is because I've put two alias entries in the SELECT clause.

If I remove the second alias (for Entity2), I just get "column not found" errors - presumably because Hibernate can't find the fields to initialise entity2 from.

Any ideas? I have a query that can return the fields for the primary and associated object, but I want the List returned to just be a list of Entity1 objects.

Pre-emptive comment: Yes, I know I could probably re-structure this and do the query a different way (criteria API etc). But this is what I'm stuck with at the moment. In this particular situation I'm constrained by some other factors, so was hoping there was just some way of telling Hibernate what I want!

Thanks.

Answer 1

Here Entity 1 (child)contain foreign key to Entity 2 (parent) there should be a parent type variable in Entity1(child) class's pojo . Let this is 'E' now the query will be :

Select ent1.* from Entity1 ent1 inner join ent1.E.id

Here id is primary key of Entity2