Hibernate version:
2.1.3


Full stack trace of any exception that occurs:
net.sf.hibernate.QueryException: outer or full join must be followed by path expression [SELECT DISTINCT pjo.printersettinguid FROM com.loffler.copycenter.dao.Printjoboption pjo LEFT JOIN com.loffler.copycenter.dao.Printjob pj ON pjo.printjobuid=pj.uid]

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

The hibernate documentation doesn't seem to address the JOIN keyword as I expected (compared to when I use JOIN with traditional JDBC MySQL scenarios). I would like to join one table to another based on their unique keys but am not sure how to do it with HQL outside of the WHERE clause.

Here is an example of a MySQL query that I run and it works just fine:
SELECT DISTINCT pjo.printersettinguid
FROM Printjoboption pjo
LEFT JOIN Printjob pj ON pjo.printjobuid=pj.uid

Can anyone shed some light for me?
Aaron Bartell
http://mowyourlawn.com

I don't have your Hibernate model in front of me, but it's usually as easy as:


This assumes that the name of the com.loffler.copycenter.dao.Printjob property on the com.loffler.copycenter.dao.Printjoboption object is called Printjob. Understandable?

If not, please post your Hibernate mappings for the relevant objects and I'll try to illustrate.

Thanks for replying. Here are my mappings, though if I am hearing you right I need to have the "JOIN" columns named the same in both tables (which I don't), and I cannot rename the columns. I will make a suggestion to the hibernate to include that in the documentation in Chapter 11.3.

So now my question would be now do I JOIN two differently named columns from two tables (both columns mean the same thing and are the same datatype, just named differently). The two fields I am trying to join are in bold.



<hibernate-mapping package="com.loffler.copycenter.dao">

<class name="Printjob" table="printjob">
<id name="uid" column="uid" type="java.lang.Integer">
<generator class="native"/>
</id>

<property name="documentuid" column="documentuid" type="java.lang.Integer" not-null="true" />
<property name="useruid" column="useruid" type="java.lang.Integer" not-null="true" />
<property name="created" column="created" type="java.util.Date" />
<property name="copies" column="copies" type="java.lang.Integer" not-null="true" />
<property name="statusuid" column="statusuid" type="java.lang.Integer" not-null="true" />
<property name="note" column="note" type="java.lang.String" not-null="true" />
<property name="printeruid" column="printeruid" type="java.lang.Integer" not-null="true" />
<property name="jobid" column="jobid" type="java.lang.Integer" not-null="true" />
<property name="autoprint" column="autoprint" type="java.lang.String" not-null="true" />
<property name="ordhdruid" column="ordhdruid" type="java.lang.Integer" not-null="true" />
<property name="perdocprice" column="perdocprice" type="java.lang.Double" not-null="true" />
<property name="perpageprice" column="perpageprice" type="java.lang.Double" not-null="true" />
<property name="perdocextprice" column="perdocextprice" type="java.lang.Double" not-null="true" />
<property name="perpageextprice" column="perpageextprice" type="java.lang.Double" not-null="true" />
</class>

</hibernate-mapping>

<hibernate-mapping package="com.loffler.copycenter.dao">

<class name="Printjoboption" table="printjoboption">
<id name="uid" column="uid" type="java.lang.Integer">
<generator class="native"/>
</id>

<property name="printjobuid" column="printjobuid" type="java.lang.Integer" not-null="true" />
<property name="printeroptionuid" column="printeroptionuid" type="java.lang.Integer" not-null="true" />
<property name="perdocprice" column="perdocprice" type="java.lang.Double" not-null="true" />
<property name="perpageprice" column="perpageprice" type="java.lang.Double" not-null="true" />
<property name="printersettinguid" column="printersettinguid" type="java.lang.Integer" not-null="true" />
<property name="perdocextprice" column="perdocextprice" type="java.lang.Double" not-null="true" />
<property name="perpageextprice" column="perpageextprice" type="java.lang.Double" not-null="true" />
</class>

</hibernate-mapping>

No, the column doesn't have to be the same. As long as you have the relationship mapped properly, you are joining on the property name in the parent table. In your Printjoboption class mapping, you need to add relationship mappings (e.g. one-to-one, one-to-many) to the associated classes. e.g.

<one-to-one name="printjobuid" class="Printjob"/>

and remove the <property name="printjobuid"> - then you are simply using the join that I posted, Hibernate will automagically resolve the proper column names for each side of the relationship.

However, in your example HQL, you aren't even using any properties from the Printjob table - so why do you need to join it?

The paradigm shift for HQL that you need to get to is that you're not writing SQL - it's as though you're querying an OODBMS - the ORM is doing all the joining for you.

What do you _want_ to return in your query, and I can try to help you model it. IMHO the HQL language is very short-changed in the Hibernate documentation - it's quite powerful, but the concepts aren't well-described.

<one-to-one name="printjobuid" class="Printjob"/>

Just so I am understanding the above, that assumes class Printjob has a non-composite key, correct? And then it will map printjobuid to Printjob's uid? Makes sense.

Note that I use an IDE called MyEclipseIDE and each time I refresh my table from MySQL into the IDE it wipes out any custom hibernate configs out, so that is why I am trying to keep this in HQL.


However, in your example HQL, you aren't even using any properties from the Printjob table - so why do you need to join it?

I omitted a fairly large WHERE clause so it didn't confuse my original post. I check the Printjob.statusuid field to make sure it is a certain value, for instance. Sorry for the confusion.


What do you _want_ to return in your query, and I can try to help you model it. IMHO the HQL language is very short-changed in the Hibernate documentation - it's quite powerful, but the concepts aren't well-described.

Let's keep it simple and modify the following HQL to JOIN Printjoboption.printjobuid to Printjob.uid. All I really need is to know how to JOIN to differently named columns in HQL.

SELECT DISTINCT pjo.printersettinguid
FROM com.loffler.copycenter.dao.Printjoboption pjo
LEFT JOIN com.loffler.copycenter.dao.Printjob pj
ON pjo.printjobuid=pj.uid
WHERE pj.statusuid=1

Thank you so much for your time pmularien, it is much appreciated.
Aaron Bartell

No, this will work with a composite key as well - check the documentation on composite keys.



Agreed, you can do what you need to do in HQL.



Clarifying question - is statusuid also supposed to be a foreign key reference to another object? If so, can you include that?



Well, remember - HQL doesn't care about columns - that's the shift in thinking. You're already modeling the mapping between tables at the XML mapping level.

You can write it as follows:



Assuming your Hibernate mappings are set up with the right relationships, that should just magically "work", joining the tables as required. Two further questions:

1. Is "statusuid = 1" referring to a specific object instance? If so, there are better ways to write that (see my question about where this is an FK reference, above)
2. Are you trying to return an object reference from this query? If so, you would be better to set up the relationship to printersettinguid as a one-to-one (or as appropriate) relationship and return the whole PrinterSetting object.