In Hibernate 2 the HQL parser allowed us to write something like:



using Oracles old notation for outer joins (+)

Now the new HQL parser in release 3 no longer allows that.

Is there any other way to write an outer join on two classes that have no mapped relation? The ANSI style outer join doesn't seem to work since HQL reserves that to navigate according the the mapping, e.g.



doesn't seem to work.

Also the subquery variant doesn't work:

HQL complains that inside the subquery "a" (i.e. the reference to the main query) is unknown.

I hope someone can tell me how to create an outer join "ad hoc" join/cross product. Thanks in advance,

Peter

The new parser is much more picky (as it should be) with what can be passed through the SQL generator. If you want outer joins you need the relationship mapped or you can use nativeSQL. In your case that should be fine since you are using an Oracle extension to the HQL anyway.

For full outer, you could get clever with simple cross selects:That, or something like it, will give you the equivalent of an full outer join. I think. Assuming that by "full outer" (which is a concept that doesn't enter into any code I've ever written) one would want all rows of both tables, joined correctly where possible, or listed with a null other-table-row where joining is not possible.

Well this is a normal cross product, i.e. you won't get a if there is no b for it.

OK, even if it doesn't solve the problem, at least this gives me a conclusive answer that what I want is not possible in HQL at this time. Maybe it would be possible (feature request) to allow it in one form or the other, e.g. to somehow add the variant I described above:

which is a ansi-style outer join from a to b.

"for the time being" I have solved it using two queries:


I execute the 2nd query, store the result tuples a,b in a Map from A to B.

Then I execute the first query and for each A I make a lookup in the map from A to B and either I get a B or I get null. It is slower and more cumbersome than before, but at least it still works. In our case, we retrieve 1000s of A, making a loop and executing a query for each A to see if a B exists for it would be a performance killer.

To use native sql: I'll try that too, but if possible I'd like to avoid it.