Why is this HQL join fetch not returning expecting rows

Question

I have two tables: 'publication' and 'publication_translations'.

In hibernate I use an annotation to map the translations to the publication object with a @ManyToOne.

In the DAO layer I use a query to get all the publications like this:

from publication p where application_id = :applicationId

Now I want to fetch join the translations into the application object like:

from publication p left outer join fetch p.translations pt where application_id = :applicationId

This results in a SQL query like this:

  select
        publicatio0_.id as id25_0_,
        translatio1_.id as id26_1_,
        publicatio0_.application_id as applica17_25_0_,
        publicatio0_.url as url25_0_,
        translatio1_.language as language26_1_,
        translatio1_.name as name26_1_,
        translatio1_.publication_id as publicat4_26_1_,
        translatio1_.id as id0__ 
    from
        publication publicatio0_ 
    left outer join
        publication_translation translatio1_ 
            on publicatio0_.id=translatio1_.id 
    where
        and application_id=180;

When I run this query directly in MySQL it returns rows as I expect. But somehow the List returned by the session.list() stays empty.

It seems like Hibernate throws away the rows when the translation is not existent. (as you would expect with an inner join, but not a left outer join..

Why does this happen? Am I missing something?

Btw. the reason i'm doing this is because I want to add a where or with to the hql clause stating a specific language, so you can fetch the translations for a specific language. But I simplified the case to illustrate the problem. The end result will be something like:

from publication p join fetch p.translations pt where (pt.language = :language or pt.language is null) and application_id = :applicationId

Publication.java:

@OneToMany(mappedBy = "publication")
private List<PublicationTranslation> translations;

PublicationTranslation.java

@ManyToOne
private Publication publication;

Edit: added the annotations on the classes: Edit2: changed mistake in the annotation on Publication.java

Your query seens fine to me. Could you please post your `@OneToMany` and `@ManyToOne` mappings. Also, just to be sure, does `select p from publication p left join fetch p.translations pt where p.application_id = :applicationId` do any better? — Anthony Accioly, Jul 24 '13 at 12:58
Nope, I tried with 'select', with 'left join', 'join fetch' and without 'fetch'. — Marcel Panse, Jul 24 '13 at 13:12
Why session.list()? I thougth You would get an publication object and retieve translations by call publication.getTranslation() — Yugang Zhou, Jul 24 '13 at 13:13
So, your are joining through a primary key column right? Foreign key is something like `ALTER TABLE publication_translation ADD FOREIGN KEY (id) REFERENCES publication (id)`? — Anthony Accioly, Jul 24 '13 at 13:31
@Hippoom: I want a list of publications in the correct language. Not a single object. — Marcel Panse, Jul 25 '13 at 07:28
Really sorry, I thought "application_id" is the private key of publication. My mistake. — Yugang Zhou, Jul 25 '13 at 07:40

Anthony Accioly · Answer 1 · 2013-07-24T14:17:04.907

0

Try this:

// Publication.java
@OneToMany(mappedBy = "publication")
private List<PublicationTranslation> translations; 

// PublicationTranslation.java
@ManyToOne
@JoinColumn(columnName="publication_id", referencedColumnName="id")
private Publication publication;

Or, if you really need to join by pk (It looks like a mistake):

// PublicationTranslation.java
@ManyToOne
@PrimaryKeyJoinColumn(name="id", referencedColumnName="id")
private Publication publication;

public void setPublication(Publication publication) {
   this.publication= publication;
   this.id = publication.getId();
}

Then the following HQL should do the trick:

select p from publication p 
  left join fetch p.translations pt 
 where p.application_id = :applicationId

edited Jul 24 '13 at 14:17

answered Jul 24 '13 at 14:06

Anthony Accioly

19,883
7
62
102

My mistake, it is indeed mapped on the FK. Unfortunately it doesn't solve the problem though. The mapping is working correctly both ways as long as you leave out the join fetch part of the query. – Marcel Panse Jul 25 '13 at 07:27
So you are saying that you have changed your original mapping to what I've described here and it still does not work? Would you mind updating your question with the SQL query generated after the changes on the mapping? – Anthony Accioly Jul 25 '13 at 17:35

score 0 · Accepted Answer · answered Jul 25 '13 at 12:46

I finally figured it out. I'll share the answer.

The problem was in the extra properties in the query that I left out. The full query looked like this:

from publication p left join fetch p.translations pt where (pt.language = :language or pt.language is null) and p.application.id = :applicationId and state = :state and active = true and spsUpload = false

query.setParameter("state", PublicationState.DONE);

This worked fine without the 'left join fetch', but stopped working after I added it, so I concluded the 'left join fetch' was not working.

I printed the generated SQL in the console. Unfortunately Hibernate does not print the parameters with it, so I substituted the params in the query to see if the query was ok.

In fact it was the value of the parameter 'state' which was messed up, but there was no way to actually see it (is there?).

By joining the two tables, Hibernate somehow does not know which table you want to map the state field on. Hibernate automatically assumes it is just another column not known in your DO, so it assumes it is a String. Because it is a String, the setParameter which passes in an Object fails and probably outputs the [Object] instead of the actual toString() value.

So what did I learn: 1. Hibernate does not print the substitutes in the query, so you can't assume their values. 2. When sharing a simplified piece of code, run exactly that to see the result, even when you are totally convinced the problem lies somewhere else.

Anyway, thanks for helping, otherwise I wouldn't have found it..

The two lessons to learn here is: 1. always precede the field with the entity alias `p.state = :state and p.active = true and p.spsUpload = false`, 2. Use specify parameter types. `query.setString("state", PublicationState.DONE.toString())` — Anthony Accioly, Jul 26 '13 at 11:58
This would would have avoided the trouble (which is not at all something silly, hibernate can use enum types in queries, and by default it maps the Enum type to its ordinal, unless you map the field with `@Enumerated(EnumType.STRING)`. — Anthony Accioly, Jul 26 '13 at 12:11
About printing the parameters (Yes there is!): http://stackoverflow.com/questions/1710476/print-query-string-in-hibernate-with-parameter-values — Anthony Accioly, Jul 26 '13 at 12:16

Why is this HQL join fetch not returning expecting rows

2 Answers2