-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 
Author Message
 Post subject: Newbee having problem crafting a master-detail HQL query
PostPosted: Mon Jan 09, 2006 11:47 am 
Newbie

Joined: Mon Dec 12, 2005 10:45 am
Posts: 12
I'm a newbee in HQL (and in SQL also) and I have dificulties crafting the HQL query named "LRUDAO.find_by_name_for_sil28" below.

Could someone help me ?

TIA.



Hibernate version:
3.1

Mapping documents:

<?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" >

<hibernate-mapping package="com.airbus.bimat.domain.lru">

<class name="LRU" table="LRUS">
<id name="id" column="LRU_ID" access="field">
<generator class="native" />
</id>

<natural-id mutable="true">
<property name="name" column="LRU_NAME" type="string" not-null="true" length="60" />
<property name="ATA" column="LRU_ATA" type="string" not-null="true" length="6" />
</natural-id>

<set name="parts" cascade="all-delete-orphan" inverse="true">
<key column="LRU_ID" not-null="true" />
<one-to-many class="Part" />
</set>
</class>

<class name="Part" table="PARTS">
<id name="id" column="PART_ID" access="field">
<generator class="native" />
</id>

<natural-id>
<property name="pn" column="PART_PN" type="string" not-null="true" length="40" />
</natural-id>

<many-to-one name="LRU" column="LRU_ID" class="LRU" not-null="true" lazy="false" />

<property name="comments" column="PART_COMMENTS" type="string" length="100" />

<property name="A318" column="PART_a318" type="boolean" not-null="true" />
<property name="A319" column="PART_a319" type="boolean" not-null="true" />
<property name="A320" column="part_a320" type="boolean" not-null="true" />
<property name="A321" column="PART_a321" type="boolean" not-null="true" />
<property name="A330" column="PART_a330" type="boolean" not-null="true" />
<property name="A340" column="PART_a340" type="boolean" not-null="true" />
<property name="A3456" column="PART_a3456" type="boolean" not-null="true" />
<property name="A380" column="PART_a380" type="boolean" not-null="true" />
</class>

<!-- queries -->

<!--
Select from the LRUS table all records having at least one child record in
the PARTS table where either the A318 or the A319 or the A320 or A321 is true.
In SQL :
select
distinct LRUS.lru_id
from
LRUS, PARTS
where
LRUS.lru_id = PARTS.lru_id
and (PART_A318 = 1 OR PART_A319 = 1 OR PART_A320 = 1 OR PART_A321 = 1)
-->
<query name="LRUDAO.find_by_name_for_sil28">
<![CDATA[
select
l
from
LRU as l, PART as p
where
l.id = p.LRU.id
and (p.A318 = 1 OR p.A319 = 1 OR p.A320 = 1 OR p.A321 = 1)
]]>
</query>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

java.lang.ExceptionInInitializerError
at com.airbus.bimat.persistance.HibernateUtil.startup(HibernateUtil.java:72)
at com.airbus.bimat.persistance.HibernateUtil.getSessionFactory(HibernateUtil.java:103)
at com.airbus.bimat.persistance.HibernateUtil.getCurrentSession(HibernateUtil.java:119)
at com.airbus.bimat.persistance.HibernateUtil.closeSession(HibernateUtil.java:163)
at com.airbus.bimat.persistance.SQLScriptRunner.executeSQLScript(SQLScriptRunner.java:98)
at com.airbus.bimat.persistance.SQLScriptRunner.executeSQLScript(SQLScriptRunner.java:56)
at com.airbus.bimat.service.lru.TestLruService.setUp(TestLruService.java:47)
at junit.framework.TestCase.runBare(TestCase.java:125)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: org.hibernate.HibernateException: Errors in named queries: LRUDAO.find_by_name_for_sil28
at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:338)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1154)
at com.airbus.bimat.persistance.HibernateUtil.startup(HibernateUtil.java:66)
... 16 more


Name and version of the database you are using:

Oracle 9i

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jan 09, 2006 1:07 pm 
Expert
Expert

Joined: Fri Aug 19, 2005 2:11 pm
Posts: 628
Location: Cincinnati
Quote:
select
l
from
LRU as l, PART as p
where
l.id = p.LRU.id
and (p.A318 = 1 OR p.A319 = 1 OR p.A320 = 1 OR p.A321 = 1)


at first glance, I'm not sure if having select I from LRU as I... is valid sql. change to

select
*
from
LRU as l, PART as p
where
l.id = p.LRU.id
and (p.A318 = 1 OR p.A319 = 1 OR p.A320 = 1 OR p.A321 = 1)

_________________
Chris

If you were at work doing this voluntarily, imagine what you'd want to see to answer a question.


Top
 Profile  
 
 Post subject: Re: Newbee having problem crafting a master-detail HQL query
PostPosted: Mon Jan 09, 2006 1:22 pm 
Regular
Regular

Joined: Tue Nov 16, 2004 6:36 pm
Posts: 62
Location: Zürich
query is case sensitive, your class is named Part but the query uses PART.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jan 09, 2006 1:46 pm 
Newbie

Joined: Mon Dec 12, 2005 10:45 am
Posts: 12
I finally solve the problem with the following HQL:

Code:
      select
         distinct l
      from
         LRU as l left join l.parts as p
      where
         l.id = p.LRU.id
         and (p.A318 = 1 or p.A319 = 1 or p.A320 = 1 or p.A321 = 1)


PLM you are right, in the previous query version, Part was misspelled as PART.

HQL syntax and semantic is realy obscure to me right now as I'm just begining to write my first queries, where can I found a deep and didactic tutorial about HQL ?

Thancks.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jan 09, 2006 1:52 pm 
Senior
Senior

Joined: Tue Aug 23, 2005 8:52 am
Posts: 181
Pascal jacob wrote:
I finally solve the problem with the following HQL:

Code:
      select
         distinct l
      from
         LRU as l left join l.parts as p
      where
         l.id = p.LRU.id
         and (p.A318 = 1 or p.A319 = 1 or p.A320 = 1 or p.A321 = 1)



You dont need the l.id = p.LRU.id as thats how the internal mapping is done according to your hbm.xml. The join is only keyed off the LRU.id.
Code:
select distinct l from
LRU as l left join l.parts as p
where p.A318 = 1 or p.A319 = 1 or p.A320 = 1 or p.A321 = 1

should suffice.

The UserGuide at http://www.hibernate.org/hib_docs/v3/re ... ml_single/
contains various examples of HQL although you probably have to try a few before you master them.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 

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

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.