I am using Hibernate 3.2

I am testing out conditional / formula based join with a traditional example.
an Item -> Bid, with 1-many relationship. But with conditional join, I want to map 1 item to only 1 bid (the bid which has biddate column as null, i assumed just for example)

Item.hbm.xml
____________________________________________________

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
<class name="com.lfg.em.domain.enrollment.Item" table="ITEM" schema="GROUPNET">
<id name="itemkey" type="java.lang.Long">
<column name="ITEMKEY" precision="22" scale="0" />
<generator class="native" />
</id>
<property name="name" type="java.lang.String">
<column name="NAME" length="30" />
</property>
<many-to-one name="currentBidder" property-ref="currentBidder" fetch="join">
<column name="itemkey" />
<formula>'1'</formula>
</many-to-one>
</class>
</hibernate-mapping>


Bid.hbm.xml
________________________________________________________

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
<class name="com.lfg.em.domain.enrollment.Bid" table="BID" schema="GROUPNET">
<id name="bidkey" type="java.lang.Long">
<column name="BIDKEY" precision="22" scale="0" />
<generator class="native" />
</id>

<property name="biddate" column="BIDDATE"/>

<properties name="currentBidder">
<property name="itemkey" />
<property name="currentRecord" type="java.lang.String">
<formula>case when biddate is null then '1' else '0' end</formula>
</property>
</properties>
<property name="maxbid"></property>
<property name="amount" type="java.lang.Long">
<column name="AMOUNT" precision="22" scale="0" />
</property>
</class>
</hibernate-mapping>


Hibernate generated query when I am trying to find an Item with an Id and then corresponding currentBid (Bid, which has biddate as null in database, just like that)

select item0_.ITEMKEY as ITEMKEY0_1_, item0_.NAME as NAME0_1_, item0_.itemkey as itemkey0_1_, '1' as formula0_1_, bid1_.BIDKEY as BIDKEY1_0_, bid1_.BIDDATE as BIDDATE1_0_, bid1_.itemkey as itemkey1_0_, bid1_.maxbid as maxbid1_0_, bid1_.AMOUNT as AMOUNT1_0_, case when bid1_.biddate is null then '1' else '0' end as formula1_0_ from GROUPNET.ITEM item0_ left outer join GROUPNET.BID bid1_ on item0_.itemkey=bid1_.itemkey and '1'=bid1_.null where item0_.ITEMKEY=1

This query is wrong, oracle does not run this query at all. I can obviously see what should the correct query look like, how come Hibernate generates this wrong syntax.(for example, observe bid1_.null in the above query, come on there is no column with name 'null' in my database!!!) What I am doing wrong>?? any ideas?


Please answer, credits waiting for you! Thanks.

Well the problem obviously lies here:

I assume that you want to make the join with bid entries where the bid date is null.



But how should hibernate know that you want to compare 1 with bid.currentRecord. So it assumes you want to join using the id and since the id only consists of 1 column the 2nd column is null.

I don't know if there is a sleek way to put that in the many-to-one definition. But you can use a hql statement selecting the current bidder.

and get rid of the formulas ;-)