Hibernate version:
3.0

Two classes that have similar composite keys, with 2 properties mapped as many-to-one and the third key, a Date.

would like to do something like:


The key classes are different because the Date maps to a different column name, xDate vs. yDate.

Is this type of 'unrelated' join possible? (how or alteratives?).

this should work :
select X, Y
where
X.id.a = Y.id.a
and X.id.b = Y.id.b
and X.id.date <= Y.id.date

Thanks, that got me a few steps closer to what I'm trying to do. Now, I have a third relationship that is used to implement a "waterfall": get Z's property first if there is a z, otherwise get X's property.

Given this scenario:

select X, Y
where
X.id.a = Y.id.a
and X.id.b = Y.id.b
and X.id.date <= Y.id.date

need:

select X, Y, Z
where
X.id.a = Y.id.a
and X.id.b = Y.id.b
and X.id.date <= Y.id.date
and X.id.a *= Z.id.a
and X.id.b *= Z.id.b

My understanding is that theta-style outer joins are not implemented in hql. It would seem like the native sql query would be more complex than the jdbc sql. Any suggestions?

For this kind of situations I recommend supplementing Hibernate with I batis, or if you need heavy SQL optimization then just use iBatis

http://sandbox.sourcelabs.com/kosta/hb- ... batis.html

Konstantin,

Thanks for the link to the excellent site. I'm not sure if we'll be able to add ibatis at this point, but the solution sounds good. Hopefully hibernate will support what you suggest[url=http://sandbox.sourcelabs.com/kosta/hb-beyond-hw/java/com/sourcelabs/hibernate/bhw/sql/doc/cooperating_with_sql.html]
here[/url] in the future.

Thanks for your help!