Hibernate Community • View topic - Purely HQL LEFT JOIN

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Purely HQL LEFT JOIN

Page 1 of 1

[ 3 posts ]

Previous topic | Next topic

Author

Message

albartell

Post subject: Purely HQL LEFT JOIN

Posted: Tue Jul 05, 2005 10:50 am

Newbie

Joined: Sat Mar 12, 2005 1:42 pm
Posts: 7
Location: MN

Hi all,

I am trying to create a join between two tables 'Ordhdr' and 'Company' where Ordhdr has a column called companyuid. Companyuid in Ordhdr is a surrogate key for the uid column in table Company. In my situation I am using HQL to compose a query and want to include a join from Ordhdr to Company. The following native SQL details the join relationship I am trying to convey in HQL (very simple and straight forward).

Code:

SELECT oh.uid FROM ordhdr oh
LEFT JOIN company AS cmp ON oh.companyuid = cmp.uid
WHERE oh.totalprice > 1000

I have seen in the documentation and forum threads that it is favored to name your columns to be joined the same in both tables. Unfortunately that is not the route I have gone and have columns representing the same unique id but are named differently (i.e. ordhdr.companyuid is equal to company.uid) I have also seen forum threads that attempt to address what I am asking but it seems they go off on different tangents and don’t address the HQL join issue I am looking to resolve.

Is it possible to join two tables in HQL without modifying the hibernate-mappings of the tables/objects being joined? And if so could somebody take my example SQL above and my hibernate-mapping’s below and provide me an example of how to do just that? I would forever be indebted to you!

Thanks,
Aaron Bartell
http://mowyourlawn.com

Hibernate version:
2.1.3

Mapping documents:

Code:

<hibernate-mapping package="com.mowyourlawn.dao">
    <class name="Ordhdr" table="ordhdr">
        <id name="uid" column="uid" type="java.lang.Integer">
            <generator class="native"/>
        </id>
 
        <property name="useruid" column="useruid" type="java.lang.Integer"  not-null="true" />
        <property name="costgroupuid" column="costgroupuid" type="java.lang.Integer"  not-null="true" />
        <property name="delivermethoduid" column="delivermethoduid" type="java.lang.Integer"  not-null="true" />
        <property name="created" column="created" type="java.util.Date" />
        <property name="statusuid" column="statusuid" type="java.lang.Integer"  not-null="true" />
        <property name="totalprice" column="totalprice" type="java.lang.Double"  not-null="true" />
        <property name="deliverby" column="deliverby" type="java.util.Date" />
        <property name="note" column="note" type="java.lang.String"  not-null="true" />
        <property name="completedate" column="completedate" type="java.util.Date" />
        <property name="eventdate" column="eventdate" type="java.util.Date" />
        <property name="companyuid" column="companyuid" type="java.lang.Integer"  not-null="true" />
        <property name="submitdate" column="submitdate" type="java.util.Date" />
        <property name="finalprice" column="finalprice" type="java.lang.Double"  not-null="true" />
        <property name="admincomment" column="admincomment" type="java.lang.String"  not-null="true" />
    </class>
</hibernate-mapping>


<hibernate-mapping package="com.mowyourlawn.dao">
    <class name="Company" table="company">
        <id name="uid" column="uid" type="java.lang.Integer">
            <generator class="native"/>
        </id>
 
        <property name="name" column="name" type="java.lang.String"  not-null="true" />
        <property name="descr" column="descr" type="java.lang.String"  not-null="true" />
        <property name="created" column="created" type="java.util.Date" />
        <property name="createdby" column="createdby" type="java.lang.Integer"  not-null="true" />
    </class>   
</hibernate-mapping>

Name and version of the database you are using:
MySQL 4.1

Top

weazelb0y

Post subject: same problem - more complex mapping

Posted: Tue Jul 05, 2005 2:48 pm

Beginner

Joined: Thu Nov 20, 2003 1:30 pm
Posts: 27

I have the same problem...Using Hibernate 3.0.5

Is it possible to do join queries without changing the mapping?

I have legacy app with a mapping that would be complex.

I would change my mapping, but in my case I would have to create a composite many-to-one with a discriminator. Meaning, can I create a 'properties' element with one of the 'property' be a value? I don't want to create a subclass in the target reference for each value of the discriminator. It would get messy.

Is it possible to to do either one?

I hope there is an easy solution!

Thanks a lot.

Top

zepernick

Post subject:

Posted: Wed Jul 06, 2005 8:43 am

Beginner

Joined: Fri Oct 22, 2004 4:47 pm
Posts: 22

This is how we have done it in the past:

In this example, you are selecting from "ordhdr". In the mapping document for "ordhdr", you would need to have a, many to one, one to many, or many to many mapping to the company object. After this mapping is there you will be able to perform your join to the other object. When you perform the join, you will want to reference whatever name you gave to the property in the "ordhdr" mapping document.

Here is an example of a one to many mapping to your company objet:

Code:

      <set name="companys" table="COMPANY" lazy="true" inverse="true">
         <key column="COMPANYID" />
         <one-to-many class="com.mowyourlawn.dao"/>
      </set>   

You would then perform your join like this

Code:

SELECT oh.uid FROM ordhdr oh
LEFT JOIN oh.companys AS cmp 
WHERE oh.totalprice > 1000

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