Hibernate HQL: how to use a complex left join fetch

Question

Hi all,

I want to add a left join on TASK table when the following condition occurs: LEFT JOIN FETCH PROMPT p on (t.id = p.task.id and p.applicationName in ('XXX'))

Here is my hql query:

select
            distinct t        
        from
            TASK t
        LEFT JOIN FETCH
            SERVER ser 
                on t.id=ser.task_id 
        LEFT JOIN FETCH
            APPLICATION app 
                on ser.id=app.server_id         
        LEFT JOIN FETCH
            PROMPT p on (t.id  = p.task.id and p.applicationName in ('XXX'))
        where
            t.id=ser.task.id 
            and ser.id=app.server 
            and  app.name in ('XXX') 
        order by t.id

I get the following exception, probably due to "on" keyword:

java.lang.NoSuchMethodError: org.hibernate.hql.antlr.HqlBaseParser.recover(Lantlr/RecognitionException;Lantlr/collections/impl/BitSet;)V

      at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:771)

Any ideas?

class Task {
    private String taskId;
    private Set<ServerDetails> servers;
}

class ServerDetails {
    private String id;
    private Set<ApplicationDetails> applications;
}

class ApplicationDetails {
   private String id;
}

    Class Prompt {
      private String applicationName;
    }

How can i use left join fetch using my condition p.applicationName in ('XXX')?

Answer 1

There are several problems here:

1. Your HQL as you've posted it is not really HQL :-) It's straight up SQL. For HQL you need use mapped associations to join tables. See below for an improved version.
2. As far as I'm aware ON clause does not support multiple joined conditions in parenthesis.
3. NoSuchMethodError is not something Hibernate would throw :-) You likely have an older version of antlr.jar somewhere in your classpath and it gets picked up in place of the one expected by Hibernate. Find it and remove it.

Without seeing your mappings the following is likely inaccurate, but I'll take a stab at writing the more appropriate HQL:

select distinct t        
  from TASK t
  left join fetch t.server ser
  left join fetch ser.application app
  left join t.prompt p with p.applicationName in ('XXX')
 order by t.id

Note that prompt is not fetched because you can't use join fetch together with with condition. You can replace it with inner join fetch and where if the association is required.

If you're still having trouble after the classpath issue is resolved, feel free to post your mappings if you need help with actual HQL.