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

I don't have hibernate in front of me to test, looking at the hibernate manual seems to suggest this small change:

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

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

This should give you back just the Entity1 objects, with the Entity2 property already initialized.

If this doesn't work, then try replacing the inner join in the SQL with a where clause, i.e.

select {entity1.*} from Entity1Table entity1, Entity2Table entity2 WHERE entity1.fk = entity2.id ");

This is then syntactically equivalent to the example given in the hibernate docs.

Answer 2

See if this can help you...