13

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.

David
  • 962
  • 2
  • 10
  • 29
  • Do you specifially want to do this with a native SQL query, or would HQL do? – Tom Anderson Aug 25 '13 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](http://stackoverflow.com/questions/9892008/hql-left-join-of-un-related-entities). – r0estir0bbe Jul 21 '14 at 11:19
  • this is easy in HQL, what is in your query which can not be done with HQL? – Sindhoo Oad Oct 09 '15 at 04:39
  • I'm facing the exact same issue - I found this bug ticket which describes the same issue https://hibernate.atlassian.net/browse/HHH-2831 but it has been closed without fixing – cbreezier Jul 24 '18 at 22:20

5 Answers5

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

Noman Akhtar
  • 690
  • 1
  • 12
  • 17
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.

Ren
  • 1,111
  • 5
  • 15
  • 24
user2311125
  • 1
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.

Tom Anderson
  • 46,189
  • 17
  • 92
  • 133
  • `addFetch` is since Hibernate 3.6. The question is for Hibernate 3.5.2. – xmedeko Mar 12 '14 at 14:48
  • @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. – Tom Anderson Mar 13 '14 at 18:05
  • 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. – r0estir0bbe Jul 21 '14 at 07:59
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.

mdma
  • 56,943
  • 12
  • 94
  • 128
  • 1
    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. – David Jun 25 '10 at 13:03
  • 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. – mdma Jun 25 '10 at 17:43
  • 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 – Vasile Bors Feb 02 '17 at 08:27
0

See if this can help you...

Community
  • 1
  • 1
dira
  • 30,304
  • 14
  • 54
  • 69