Tutorial :Hibernate Join with OR clause



Question:

I'm trying to figure out how to generate a simple join that uses an 'or' clause contained within it, using HQL or Hibernate annotations. Here is an example of what I want the SQL to look like:

select *  from tableA  left outer join tableB      on tableA.id1 = tableB.id1      or tableA.id2 = tableB.id2  where ...  

I know I can do straight SQL code for this, however, I want to take advantage of the Hibernate abstraction level. I saw @JoinColumns however this seems to be an 'and' in the join. I'm taking advantage of this join with a typed HQL query, so I also have the ability to deviate a little from the annotations and use it in there... any ideas?


Solution:1

I've got to say that this is a bit esoteric; if it were possible to map your association thusly it would most likely perform rather abysmally (unless both tables involved are pretty small).

But, as far as I know, it's not possible:

  1. You can't map an association over OR'ed columns directly
  2. You can't use any join type other than cross join over entities not connected by association.
  3. You can't specify custom loader for anything other than collection - does your tableB hold collection elements for tableA rows? Even if that's the case, you would only be able to use this Loader for straight-up collection retrieval; it won't work as part of more complex HQL query.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »