Want to do the join myself

mmehrwald · **Joined:** Sun Jul 06, 2008 9:47 am **Posts:** 3

Hello,

I have got two tables which I want to join. The column where the join occures can be null so I won't get results if I do the join like A.Bid = B.id. So my statement should look like this: A.Bid = B.id OR A.Bid IS NULL but Hibernate creates A.Bid = B.id automatically and of course does not use OR to connect the next where clause.
How can I say Hibernate not to join the tables as it wants but as I want?

Statement:
SELECT DISTINCT ap, ap.Price_Period_Type.timeUnits, ap.id FROM com.example.Article_Price ap, com.example.Price_Period_Type as ppt WHERE ap.Price_Period_Type.id = ppt.id OR ap.Price_Period_Type.id IS NULL AND ap.Price_Season.id = :psid ORDER BY ap.Price_Period_Type.timeUnits, ap.id DESC")

Thank you!
Markus

Cameron McKenzie · **Posted:** Mon Jul 07, 2008 9:10 am

So, is there a natural join between the tables? I mean, a foreign key relationship? And from there, you have your objects mapped?

A criteria query would be a snap in this scenario. Also, if we knew your object model, a simple HQL query would do it too.

What have you tried?

mmehrwald · **Joined:** Sun Jul 06, 2008 9:47 am **Posts:** 3

I posted the HQL above. Here are the mapping files:

Code:

<hibernate-mapping>
   <class name="com.example.Article_Price" table="Article_Price" lazy="false">
      <id name="id" column="id" type="integer" unsaved-value="0">
         <generator class="increment">
         </generator>
      </id>
      <many-to-one name="Price_Period_Type" cascade="lock" column="Price_Period_Typeid" class="com.example.Price_Period_Type" not-null="false" lazy="no-proxy" access="field">
      </many-to-one>
      <many-to-one name="Price_Season" cascade="lock" column="Price_Seasonid" class="com.example.Price_Season" not-null="true" lazy="no-proxy" access="field">
      </many-to-one>
      <many-to-one name="Currency" cascade="lock" column="Currencyid" class="com.example.Currency" not-null="true" lazy="no-proxy" access="field">
      </many-to-one>
      <property name="value" column="value" type="float" not-null="false" lazy="false"/>
      <property name="validFrom" column="validFrom" type="timestamp" not-null="true" lazy="false"/>
      <property name="validUntil" column="validUntil" type="timestamp" not-null="false" lazy="false"/>
   </class>
</hibernate-mapping>

and

Code:

<hibernate-mapping>
   <class name="com.example.Price_Period_Type" table="Price_Period_Type" lazy="true">
      <meta attribute="class-description">
         Kapselt Zeitspannen (pro Tag/pro Woche/pro Monat)...
      </meta>
      <id name="id" column="id" type="integer" unsaved-value="0">
         <generator class="increment">
         </generator>
      </id>
      <property name="name" column="name" type="string" length="255" not-null="true" lazy="false"/>
      <property name="timeUnits" column="timeUnits" type="integer" not-null="true" lazy="false">
         
      </property>
      <set name="Article_Price" lazy="extra" cascade="save-update,lock" inverse="true">
         <key column="Price_Period_Typeid" not-null="false"/>
         <one-to-many class="com.example.Article_Price"/>
      </set>
   </class>
</hibernate-mapping>

So as you can see the Price_Period_Type may be NULL but I also want to select the Article_Price if this is so and the Price_Seasonid is correct. That's why I used the OR in the HQL but Hibernate creates another join itself which leads to not returning the Article_Prices where the Price_Period_Type is not specified.

mmehrwald · **Joined:** Sun Jul 06, 2008 9:47 am **Posts:** 3

What I forgot to say. The DISTINCT is not the problem. I tried the folloing HQL as well:

SELECT ap FROM com.example.Article_Price ap, com.example.Price_Period_Type as ppt WHERE (ap.Price_Period_Type.id = ppt.id OR ap.Price_Period_Type.id IS NULL) AND ap.Price_Season.id = :psid ORDER BY ap.Price_Period_Type.timeUnits DESC

and it does not work too.