CODEWITHSUNDEEP
hibernate
David
David

Reputation: 1032

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

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.

Upvotes: 14

Views: 23843

Answers (5)

Noman Akhtar
Noman Akhtar

Reputation: 730

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

Upvotes: 1

Tom Anderson
Tom Anderson

Reputation: 47233

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.

Upvotes: 0

user2311125
user2311125

Reputation: 1

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.

Upvotes: 0

dira
dira

Reputation: 30594

See if this can help you...

Upvotes: 0

mdma
mdma

Reputation: 57757

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.

Upvotes: 0

Related Questions