Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Hibernate HQL: how to use a complex left join fetch

Tags:

hibernate

hql

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')?

like image 549
Keren Avatar asked Nov 05 '09 11:11

Keren


People also ask

Can we use join in HQL query?

HQL Join : HQL supports inner join, left outer join, right outer join and full join. For example, select e.name, a. city from Employee e INNER JOIN e.

What is left join fetch in hibernate?

JOIN FETCH (or LEFT JOIN FETCH ) will collect all the associations along with their owner object. Meaning that the collection will be retrieved in the same select. This can be shown by enabling Hibernate's statistics. A (left/outer) join fetch is great for *ToOne (many-to-one or one-to-one) associations.

Is Left join and join same?

The LEFT JOIN statement is similar to the JOIN statement. The main difference is that a LEFT JOIN statement includes all rows of the entity or table referenced on the left side of the statement.


1 Answers

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.

like image 80
ChssPly76 Avatar answered Oct 21 '22 07:10

ChssPly76