14

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.

Improve this question
4
  • Do you specifially want to do this with a native SQL query, or would HQL do? Commented Aug 25, 2013 at 20:59
  • You can do an inner join using HQL on unmapped relations like this: select * from Entity1 e1, Entity2 e2 where e1.fk = e2.id. This sadly does not work for outer joins. Commented Jul 21, 2014 at 11:19
  • this is easy in HQL, what is in your query which can not be done with HQL? Commented Oct 9, 2015 at 4:39
  • I'm facing the exact same issue - I found this bug ticket which describes the same issue hibernate.atlassian.net/browse/HHH-2831 but it has been closed without fixing Commented Jul 24, 2018 at 22:20

5 Answers 5

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

3 Comments

Changing the join syntax doesn't work. And removing the columns for entity2 from the select clause just results in a "column not found" error thrown by Hibernate.
Thanks for trying it. I'm puzzled - as far as I can see it should be exactly as the native SQL example in the hibernate text that returns "cat" with "dog" property also fetched.
Atention! some databases use Cross Join by default (example postgresql) A CROSS JOIN clause produce the Cartesian Product of rows in two or more tables. Using ", " is not same as INNER JOIN
0

See if this can help you...

Improve this answer

Comments

0

One way could be to use Theta-Style joins:

select u from User u join u.permissions p where p.code = :code;

Permissions is a collection mapped to user class.

Improve this answer

Comments

0

I think what you want is a fetch join. That is a join that brings columns back from the database which are turned into objects, but where those objects are only added to the session cache, rather than returned in the results.

There are different ways of doing this via JPA, the legacy Hibernate API, the legacy criteria API, etc.

If you're using a native SQL, you should be able to do it by calling SQLQuery::addFetch, replacing your call to addJoin with:

qry.addFetch("entity2", "entity1", "fk");

Or something like that. I have to confess i don't really understand what the parameters are supposed to mean, and i couldn't find any good documentation on it with some quick searches.

Improve this answer

3 Comments

addFetch is since Hibernate 3.6. The question is for Hibernate 3.5.2.
@xmedeko: You're right, good catch. I can't see a way to do a pure fetch join on an SQLQuery with Hibernate 3.5.
This does not work. addFetch is a more customizable variant of addJoin (but basically the same) as it returns FetchReturn instead of void. Sadly, FetchReturn does not contain an option to turn off the explicit returning of the join.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.