Hibernate Community • View topic - Left Outer Join on multiple columns allowing nulls

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Left Outer Join on multiple columns allowing nulls

Page 1 of 1

[ 3 posts ]

Previous topic | Next topic

Author

Message

fidster

Post subject: Left Outer Join on multiple columns allowing nulls

Posted: Fri Dec 31, 2004 8:28 am

Newbie

Joined: Thu Dec 30, 2004 3:51 pm
Posts: 14

greetings,

I need a sugguestion for joining 2 tables on multiple fields

SELECT TABLE.KEY1, TABLE1.KEY2, TABLE1.COL1, TABLE1.COL2, TABLE1.COL3, TABLE2.COLX, TABLE2.COLY
FROM TABLE1
LEFT OUTER JOIN TABLE2 ON
TABLE1.FIELD1 = TABLE2.FIELD2
AND TABLE1.FIELD2 = TABLE2.FIELD2
AND TABLE1.FIELD3 = TABLE2.FIELD3

the catch is there doesn't have to be an associated record in TABLE2. returning all null values is fine.
and of course this is a legacy database, so there is no real key to declare in TABLE2.

i was trying to use a many-to-one, which means i have to declare FIELD1 FIELD2 and FIELD3 in TABLE2 as a composite-id, which it really isn't. the result is that hibernate generates the correct SQL statement, but if it returns all nulls for TABLE2, it tries running another query to find it, and throws an error. i guess what i need is a many-to-oneOrNull! it read issue HB-785 and tried the patch, but it doesn't work when the entire record is null. hibernate really wants to fill the object associated with TABLE2.

is there another way to map a query like this?

thanks for your help

Top

fidster

Post subject:

Posted: Fri Dec 31, 2004 8:38 am

Newbie

Joined: Thu Dec 30, 2004 3:51 pm
Posts: 14

er sorry screwed up my columns in my little sample

SELECT TABLE.KEY1, TABLE1.KEY2, TABLE1.COL1, TABLE1.COL2, TABLE1.COL3, TABLE2.COLX, TABLE2.COLY
FROM TABLE1
LEFT OUTER JOIN TABLE2 ON
TABLE1.COL1= TABLE2.COL1
AND TABLE1.COL2= TABLE2.COL2
AND TABLE1.COL3= TABLE2.COL3

Top

fidster

Post subject:

Posted: Fri Dec 31, 2004 9:02 am

Newbie

Joined: Thu Dec 30, 2004 3:51 pm
Posts: 14

heres my hbm examples for i am trying

TABLE1

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
   <class 
      name="Table1Model" 
      table="TABLE1"
   >
   
      <composite-id name="table1Key">
         <key-property name="key1" type="java.lang.String" column="KEY1" />
         <key-property name="key2" type="java.lang.String" column="KEY2" />
      </composite-id>

      <property name="col1" type="java.lang.String" column="COL1" />
      <property name="col2" type="java.lang.Integer" column="COL2" />
      <property name="col3" type="java.lang.String" column="COL3" />
   
      <many-to-one  
         name="table2key"
         class="Table2Model"
         insert="false" update="false" not-null="false"
         cascade="none"
         >
         <column name="COL1" />
         <column name="COL2" />
         <column name="COL3" />
      </many-to-one>

   </class>
</hibernate-mapping>

TABLE2

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
   <class 
      name="Table2Model" 
      table="TABLE2"
   >
      <composite-id name="table2key">
         <key-property name="col1" type="java.lang.String" column="COL1" />
         <key-property name="col2" type="java.lang.Integer" column="COL2" />
         <key-property name="col3" type="java.lang.String" column name="COL3" />
      </composite-id>

      <property name="colY" type="java.lang.Integer">
         <column name="COLY" sql-type="decimal(10,0)" />
      </property>

      <property name="colZ" type="java.lang.String">
         <column name="COLZ" sql-type="char(6)" />
      </property>

   </class>
</hibernate-mapping>

the resulting SQL statement hibernate generates is:

Code:

select this.KEY1 as KEY1_, this.KEY2 as KEY21_, this.COL1 as COL1_, this.COL2 as COL2_, this.COL3 as COL3_, table2_.COLY as COLY_, table2_.COLZ as COLZ_, from TABLE1 this left outer join TABLE2 table2_
on this.COL1=table2_.COL1 and this.COL2=table2_.COL2 and this.COL3=table2_.COL3 

which is exactly what i need, but if there is nothing for table2 then hibernate tries getting it with another query something like

Code:

select table2_.COL1 as COL1_, table2_.COL2 as COL2_, table2_.COL3 from TABLE2 table2_ where table2_.COL1=? and table2_.COL2=? and table2_.COL3=?

this generates a No row with the given identifier exists error
i simply do not need the 2nd query. a null object for table2 is fine

thanks

Top

Page 1 of 1

[ 3 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum