Hibernate Community • View topic - HQL question

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

HQL question - why doesn't this work?

Page 1 of 1

[ 5 posts ]

Previous topic | Next topic

Author

Message

burferd

Post subject: HQL question - why doesn't this work?

Posted: Fri Feb 10, 2012 10:39 pm

Newbie

Joined: Thu Jan 19, 2012 1:21 pm
Posts: 12

I'm trying to create a query to fetch information from one table TbDevice based on conditions in that table and another related table TbCustDevice in the database.
Basically, the TbDevice table holds the device information and the TbCustDevice table holds additional information if the device has been assigned to a customer.
I suspect I need to use an inner join, based on this example I found:

Quote:

Hibernate's HQL language is not capable for handling the "inner join on" clauses. If our domain entity model includes relationships defined between the related objects then the query like this

Query query = session.createQuery("from Car car inner join Owner owner where owner.Name ='Vinod'");

will work.

Here is the query I tried in the NetBeans HQL query option:

from TbDevice dev inner join TbCustDevice cdev where dev.biDeviceId=cdev.biDeviceId and dev.tiDeviceStatusId=2 and cdev.biAccountId=33

But I get the following error message and do not understand why.
Can anyone give me some insight on what I am doing wrong?

Thanks.

Error:

Code:

org.hibernate.QueryException: outer or full join must be followed by path expression [from hibernate.TbDevice dev inner join TbCustDevice cdev where dev.biDeviceId=cdev.biDeviceId and dev.tiDeviceStatusId=2 and cdev.biAccountId=33]
   at org.hibernate.hql.classic.FromParser.token(FromParser.java:170)
   at org.hibernate.hql.classic.ClauseParser.token(ClauseParser.java:86)
   at org.hibernate.hql.classic.PreprocessingParser.token(PreprocessingParser.java:108)
   at org.hibernate.hql.classic.ParserHelper.parse(ParserHelper.java:28)
   at org.hibernate.hql.classic.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:216)
   at org.hibernate.hql.classic.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:185)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
   at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
   at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
   at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
   at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)

Mapping files for:
TbDevice:

Code:

<?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">
<!-- Generated Feb 6, 2012 5:09:07 PM by Hibernate Tools 3.2.1.GA -->
<hibernate-mapping>
  <class catalog="ROVRDEMO" name="hibernate.TbDevice" schema="dbo" table="tbDevice">
    <id name="biDeviceId" type="long">
      <column name="biDeviceId"/>
      <generator class="assigned"/>
    </id>
    <many-to-one class="hibernate.TbUser" fetch="select" name="tbUser">
      <column name="iUpdUserId" not-null="true"/>
    </many-to-one>
    <many-to-one class="hibernate.StbDeviceStatus" fetch="select" name="stbDeviceStatus">
      <column name="tiDeviceStatusId" not-null="true"/>
    </many-to-one>
    <property name="vcImei" type="string">
      <column length="20" name="vcIMEI" not-null="true" unique="true"/>
    </property>
    <property name="dtStatusUpdTime" type="timestamp">
      <column length="23" name="dtStatusUpdTime"/>
    </property>
    <property name="dtUpdDate" type="timestamp">
      <column length="23" name="dtUpdDate" not-null="true"/>
    </property>
    <property name="sdtRetiredDate" type="timestamp">
      <column length="16" name="sdtRetiredDate"/>
    </property>
    <property name="ipegscriptVersion" type="java.lang.Integer">
      <column name="iPEGScriptVersion"/>
    </property>
    <property name="iconfigVersion" type="java.lang.Integer">
      <column name="iConfigVersion"/>
    </property>
    <property name="vcLmufirmwareVersion" type="string">
      <column length="3" name="vcLMUFirmwareVersion"/>
    </property>
    <property name="iapplicationProgramId" type="java.lang.Integer">
      <column name="iApplicationProgramId"/>
    </property>
    <property name="vcUim" type="string">
      <column length="20" name="vcUIM"/>
    </property>
    <property name="iuimfirmwareVersion" type="java.lang.Integer">
      <column name="iUIMFirmwareVersion"/>
    </property>
    <set inverse="true" name="tbEventTrxns">
      <key>
        <column name="biDeviceId" not-null="true"/>
      </key>
      <one-to-many class="hibernate.TbEventTrxn"/>
    </set>
    <set inverse="true" name="tbCustDevices">
      <key>
        <column name="biDeviceId"/>
      </key>
      <one-to-many class="hibernate.TbCustDevice"/>
    </set>
  </class>
</hibernate-mapping>

TbCustDevice:

Code:

<?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">
<!-- Generated Feb 6, 2012 5:09:07 PM by Hibernate Tools 3.2.1.GA -->
<hibernate-mapping>
  <class catalog="ROVRDEMO" name="hibernate.TbCustDevice" schema="dbo" table="tbCustDevice">
    <id name="biCustDeviceId" type="long">
      <column name="biCustDeviceID"/>
      <generator class="assigned"/>
    </id>
    <many-to-one class="hibernate.TbUser" fetch="select" name="tbUser">
      <column name="iUpdUserId"/>
    </many-to-one>
    <many-to-one class="hibernate.TbDevice" fetch="select" name="tbDevice">
      <column name="biDeviceId"/>
    </many-to-one>
    <many-to-one class="hibernate.StbCustDeviceStatus" fetch="select" name="stbCustDeviceStatus">
      <column name="tiCustDeviceStatusId" not-null="true"/>
    </many-to-one>
    <many-to-one class="hibernate.TbCustVehicle" fetch="select" name="tbCustVehicle">
      <column name="biCustVehicleId"/>
    </many-to-one>
    <many-to-one class="hibernate.TbCustAccount" fetch="select" name="tbCustAccount">
      <column name="biAccountId"/>
    </many-to-one>
    <property name="dtStartEffTime" type="timestamp">
      <column length="23" name="dtStartEffTime" not-null="true"/>
    </property>
    <property name="dtEndEffTime" type="timestamp">
      <column length="23" name="dtEndEffTime"/>
    </property>
    <property name="dtUpdTime" type="timestamp">
      <column length="23" name="dtUpdTime" not-null="true"/>
    </property>
    <property name="vcImei" type="string">
      <column length="20" name="vcIMEI"/>
    </property>
    <property name="tiLastKnownNomination" type="java.lang.Byte">
      <column name="tiLastKnownNomination"/>
    </property>
    <property name="dcDeviceCost" type="big_decimal">
      <column name="dcDeviceCost" precision="9"/>
    </property>
    <property name="dtStatusUpdTime" type="timestamp">
      <column length="23" name="dtStatusUpdTime" not-null="true"/>
    </property>
    <set inverse="true" name="tbCustDevicesSubscribeToServiceses">
      <key>
        <column name="biCustDeviceID" not-null="true"/>
      </key>
      <one-to-many class="hibernate.TbCustDevicesSubscribeToServices"/>
    </set>
    <set inverse="true" name="tbCustDevicesHaveServiceses">
      <key>
        <column name="biCustDeviceID" not-null="true"/>
      </key>
      <one-to-many class="hibernate.TbCustDevicesHaveServices"/>
    </set>
    <set inverse="true" name="tbEventTrxns">
      <key>
        <column name="biCustDeviceID" not-null="true"/>
      </key>
      <one-to-many class="hibernate.TbEventTrxn"/>
    </set>
  </class>
</hibernate-mapping>

Top

tomyun

Post subject: Re: HQL question - why doesn't this work?

Posted: Mon Feb 13, 2012 3:43 am

Newbie

Joined: Thu Feb 02, 2012 11:28 pm
Posts: 13
Location: shenzhen china

Remember, HQL is not SQL and works on Entities, not tables. You must
use property names, not column names, and you must join on objects
paths, not on relations.

Top

burferd

Post subject: Re: HQL question - why doesn't this work?

Posted: Mon Feb 13, 2012 2:32 pm

Newbie

Joined: Thu Jan 19, 2012 1:21 pm
Posts: 12

That's nice, but I still do not understand what I am doing wrong.
I can create the join, but can't figure out how to do the conditions.
I don't see the difference between what I was trying to do and the example I posted.
Also, I want to just return the data from the first table, when I do a join, I get columns from both tables.

Top

tomyun

Post subject: Re: HQL question - why doesn't this work?

Posted: Mon Feb 13, 2012 9:43 pm

Newbie

Joined: Thu Feb 02, 2012 11:28 pm
Posts: 13
Location: shenzhen china

Read the documentation!

If you still don't find WHy!

If necessary, you can increase my MSN.

MSN:goldfish_sun@sina.com.cn

Top

burferd

Post subject: Re: HQL question - why doesn't this work?

Posted: Mon Feb 13, 2012 11:13 pm

Newbie

Joined: Thu Jan 19, 2012 1:21 pm
Posts: 12

Saying RTFM does not always help when someone has a question that they don't understand.
I did RTFM, but still had issues as to why I could not make the query work.
That is why I asked on the forum, hoping that someone who has experience with Hibernate could explain what I was doing wrong.

BTW, I finally figured out what the problem was.
It was a simple syntax problem that someone who was more familiar with Hibernate probably would have seen.
This is my first hibernate project and I am on the steep side of the learning curve.

Thanks for the helpful feedback.

Top

Page 1 of 1

[ 5 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