Hibernate Community • View topic - How do I query an object having a collection

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

How do I query an object having a collection

Page 1 of 1

[ 12 posts ]

Previous topic | Next topic

Author

Message

peebrayne

Post subject: How do I query an object having a collection

Posted: Tue Apr 11, 2006 9:45 pm

Newbie

Joined: Thu Apr 06, 2006 6:41 pm
Posts: 6

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hi everyone,

I've been working on this the past few days with little progress. I'm finally frustrated so I am posting hoping someone can help me out. I'm using native SQL rather than HQL because HQL doesn't have "left outer join... on" which I need. Right now, I'm just trying to do this simple query. There is a one-to-many relationship between StarViInvoice and StarViLineItem. I'm trying to retrieve each StarViInvoice object and its collection of StarViLineItems. I've tried the code below, but it returns a list of objects and I get a ClassCastException if i try to cast it into a StarViInvoice item.

I've also just tried to do a "Select {s.*} from.." but it returns every property except for the list of StarViLineItems. Does someone have a non HQL solution? Thanks in advance.

Hibernate version:
3.1

Mapping documents:

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

Code:

String sqlSelect = "select {s.*},{li.*} " +
            "from StarViInvoice s inner join StarViLineItem li on s.headerOrderReference = li.headerOrderReference " +
            "where s.identDealerNumber =:dealerNbr " +
            "and s.identDealerNumber = li.identDealerNumber";

      
      List results = null;
      results = session.createSQLQuery(sqlSelect)
         .addEntity("s",DtsVi.class)
         .addEntity("li",DtsViLineItem.class)
         .setString("dealerNbr",dealerNbr).list();

Full stack trace of any exception that occurs:

Name and version of the database you are using:
mysql 4.1
The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

Top

pepelnm

Post subject:

Posted: Wed Apr 12, 2006 3:38 am

Expert

Joined: Thu Sep 22, 2005 10:29 am
Posts: 285
Location: Almassera/Valencia/Spain/EU/Earth/Solar system/Milky Way/Local Group/Virgo Supercluster

In HQL you can use:
left outer join... with

instead of SQL:
left outer join... on

Top

peebrayne

Post subject:

Posted: Wed Apr 12, 2006 12:47 pm

Newbie

Joined: Thu Apr 06, 2006 6:41 pm
Posts: 6

If I use HQL using "left outer join... with" I get the error below. Its probably because my Vehicle object isn't referenced in my DtsVi object. I'm afraid of creating a one-to-one mapping between DtsVi and Vehicle because I'm not sure if this will cause any slowdown whenever I retrieve a Vehicle object.

Code:

[junit] Testcase: testGetVehicles(org.appfuse.dao.ViVehicleDAOTest):   Caused an ERROR
    [junit] Path expected for join! [from com.sumware.model.DtsVi d left outer join Vehicle v with d.dtsViPK.headerOrderReference = v.prodNbr]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: Path expected for join! [from com.sumware.model.DtsVi d left outer join Vehicle v with d.dtsViPK.headerOrderReference = v.prodNbr]
    [junit] org.springframework.orm.hibernate3.HibernateQueryException: Path expected for join! [from com.sumware.model.DtsVi d left outer join Vehicle v with d.dtsViPK.headerOrderReference = v.prodNbr]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: Path expected for join! [from com.sumware.model.DtsVi d left outer join Vehicle v with d.dtsViPK.headerOrderReference = v.prodNbr]
    [junit] org.hibernate.hql.ast.QuerySyntaxException: Path expected for join! [from com.sumware.model.DtsVi d left outer join Vehicle v with d.dtsViPK.headerOrderReference = v.prodNbr]
    [junit] at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
    [junit] at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:225)
    [junit] at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:158)
    [junit] at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:105)
    [junit] at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:74)
    [junit] at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:53)
    [junit] at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
    [junit] at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:108)
    [junit] at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:88)
    [junit] at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1540)
    [junit] at org.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.java:819)
    [junit] at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:365)
    [junit] at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:817)
    [junit] at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:809)
    [junit] at org.appfuse.dao.hibernate.ViVehicleDAOHibernate.getVehicles(ViVehicleDAOHibernate.java:64)
    [junit] at org.appfuse.dao.ViVehicleDAOTest.testGetVehicles(ViVehicleDAOTest.java:26)
    [junit] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    [junit] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    [junit] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    [junit] Caused by:  Path expected for join!
    [junit] at org.hibernate.hql.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:305)
    [junit] at org.hibernate.hql.antlr.HqlSqlBaseWalker.joinElement(HqlSqlBaseWalker.java:3258)
    [junit] at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3050)
    [junit] at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:2928)
    [junit] at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:686)
    [junit] at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:542)
    [junit] at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:279)
    [junit] at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:227)
    [junit] at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:218)
    [junit] ... 29 more
    [junit] TEST org.appfuse.dao.ViVehicleDAOTest FAILED
    [junit] Testsuite: org.appfuse.dao.ViewDAOTest
    [junit] Tests run: 1, Failures: 0, Errors: 0, Time elapsed: 0.422 sec
    [junit] Tests FAILED

Top

pepelnm

Post subject:

Posted: Wed Apr 12, 2006 2:54 pm

Expert

Joined: Thu Sep 22, 2005 10:29 am
Posts: 285
Location: Almassera/Valencia/Spain/EU/Earth/Solar system/Milky Way/Local Group/Virgo Supercluster

I don't understand the utility of a left join query if you don't use the second table in the select.

Maybe you wanted to use an inner join, didn't you?

Top

peebrayne

Post subject:

Posted: Wed Apr 12, 2006 5:15 pm

Newbie

Joined: Thu Apr 06, 2006 6:41 pm
Posts: 6

Well, I've changed my code to use HQL and I've stripped my query down. It is retrieving a DtsVi object which contains a Set of DtsViLineItems. There is a one-to-many relationship. DtsVi and DtsViLineItems contain composite keys. I want to return a list of DtsVi objects along with their corresponding DtsViLineItems, but I still get an empty set of DtsViLineItems. Why?

Code:

String hqlSelect ="select d from DtsVi d left outer join d.lineItems li where d.dtsViPK.identDealerNumber = ? ";
results = getHibernateTemplate().find(hqlSelect,dealerNbr);

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

<hibernate-mapping

>

    <class
            name="com.sumware.model.DtsVi"
            table="starViInvoice"
    >

        <!-- Use of @hibernate.id for composite IDs is deprecated, use @hibernate.composite-id instead -->
        <composite-id
            name="dtsViPK"
            class="com.sumware.model.DtsViPK"
                unsaved-value="any"
        >

               <!-- Defining the key-property element from @hibernate.property tags is deprecated, use @hibernate.key-property instead -->
                <key-property
                    name="headerOrderReference"
                    type="java.lang.String"
                >

                </key-property>

               <!-- Defining the key-property element from @hibernate.property tags is deprecated, use @hibernate.key-property instead -->
                <key-property
                    name="identDealerNumber"
                    type="java.lang.String"
                >

                </key-property>

               <!-- Defining the key-property element from @hibernate.property tags is deprecated, use @hibernate.key-property instead -->
                <key-property
                    name="manufacturer"
                    type="java.lang.Integer"
                >

                </key-property>

        </composite-id>

        <many-to-one
            name="vehicle"
                    class="org.appfuse.model.Vehicle"
                    column="headerOrderReference"
                unique="true"
                insert="false"
                update="false"
                property-ref="prodNbr"
        >

        </many-to-one>

        <set
            name="lineItems"
            lazy="true"
            cascade="all"
        >

            <!-- @hibernate.collection-key tag is deprecated, use @hibernate.key instead -->
            <key
            >

    </class>    

</hibernate-mapping>

Code:

<hibernate-mapping

>

    <class
            name="com.sumware.model.DtsViLineItem"
            table="starViLineItem"
    >

        <!-- Use of @hibernate.id for composite IDs is deprecated, use @hibernate.composite-id instead -->
        <composite-id
            name="dtsViLineItemPK"
            class="com.sumware.model.DtsViLineItemPK"
                unsaved-value="any"
        >

               <!-- Defining the key-property element from @hibernate.property tags is deprecated, use @hibernate.key-property instead -->
                <key-property
                    name="lineNumber"
                    type="java.lang.Integer"
                >

                </key-property>

               <!-- Defining the key-property element from @hibernate.property tags is deprecated, use @hibernate.key-property instead -->
                <key-property
                    name="headerOrderReference"
                    type="java.lang.String"
                >

                </key-property>

               <!-- Defining the key-property element from @hibernate.property tags is deprecated, use @hibernate.key-property instead -->
                <key-property
                    name="identDealerNumber"
                    type="java.lang.String"
                >

                </key-property>

               <!-- Defining the key-property element from @hibernate.property tags is deprecated, use @hibernate.key-property instead -->
                <key-property
                    name="manufacturer"
                    type="java.lang.Integer"
                >

                </key-property>

        </composite-id>

        <property
            name="description"
                    column="description"
                length="30"
        >

        </property>

        <property
            name="optionPackage"
        >

            <column
                name="optionPackage" 
                length="7"
            />

        </property>

    </class>    

</hibernate-mapping>

Top

pepelnm

Post subject:

Posted: Wed Apr 12, 2006 5:54 pm

Expert

Joined: Thu Sep 22, 2005 10:29 am
Posts: 285
Location: Almassera/Valencia/Spain/EU/Earth/Solar system/Milky Way/Local Group/Virgo Supercluster

two things:

1.- these two queries return the same result

Code:

select
   d
from
   DtsVi d
   left outer join d.lineItems li 
where 
   d.dtsViPK.identDealerNumber = ?

Code:

select
   d
from
   DtsVi d
where 
   d.dtsViPK.identDealerNumber = ?

can you understand it

2.- this mapping is incomplete, maybe a copy paste problem.

Code:

<set
   name="lineItems"
   lazy="true"
   cascade="all"
>
   <!-- @hibernate.collection-key tag is deprecated, use @hibernate.key instead -->
   <key
   >

Top

peebrayne

Post subject:

Posted: Wed Apr 12, 2006 8:11 pm

Newbie

Joined: Thu Apr 06, 2006 6:41 pm
Posts: 6

Yes I can see that they return the same result. As for the mapping code, yes part of it was deleted in an attempt to shorten the text. So I guess the question still is how can I return a collection along with an object?

I've done something like this before which returned a collection within an object. It was many-to-many. Surprisingly, this worked.

Code:

select v from Vehicle v left outer join v.options vo left outer join v.dealerOptions vdo

Top

pepelnm

Post subject:

Posted: Thu Apr 13, 2006 8:13 am

Expert

Joined: Thu Sep 22, 2005 10:29 am
Posts: 285
Location: Almassera/Valencia/Spain/EU/Earth/Solar system/Milky Way/Local Group/Virgo Supercluster

There's nothing surprisingly. Hibernate works that way.

Code:

select v from Vehicle

has to return all the Vehicle objects and then, if the mappings are correct, when you access every collection member, it will be populated. There's not need to explict it in the query, it has been done before in the map files.

This is why asked you for the lineItems mapping. If the map and the data are correct you don't need to ask for them in any query, they will be in every DtsVi (invoice?).

Maybe you should check your tables and their data.

Top

peebrayne

Post subject:

Posted: Thu Apr 13, 2006 1:52 pm

Newbie

Joined: Thu Apr 06, 2006 6:41 pm
Posts: 6

Thanks for the help. Indeed something is up with the mapping. Seems to be a problem with the composite key. Here is the relevent part of DtsVi hibernate mapping file.

Code:

<set
            name="lineItems"
            lazy="true"
        >

            <!-- @hibernate.collection-key tag is deprecated, use @hibernate.key instead -->
            <key
            >

                <!-- @hibernate.collection-key-column tag is deprecated, use @hibernate.column instead -->
                <column
                    name="identDealerNumber" 
                />

                <!-- @hibernate.collection-key-column tag is deprecated, use @hibernate.column instead -->
                <column
                    name="headerOrderReference" 
                />

                <!-- @hibernate.collection-key-column tag is deprecated, use @hibernate.column instead -->
                <column
                    name="manufacturer" 
                />

            </key>

            <!-- @hibernate.collection-one-to-many tag is deprecated, use @hibernate.one-to-many instead -->
            <one-to-many
                  class="DtsViLineItem"
            />

        </set>

Top

pepelnm

Post subject:

Posted: Thu Apr 13, 2006 5:43 pm

Expert

Joined: Thu Sep 22, 2005 10:29 am
Posts: 285
Location: Almassera/Valencia/Spain/EU/Earth/Solar system/Milky Way/Local Group/Virgo Supercluster

I'm not sure but I think that if youd don't use package attribute in <hibernate-mapping> then you must qualify with fullname every class name
you have

Code:

<one-to-many class="DtsViLineItem"/>

instead of

Code:

<one-to-many class="com.sumware.model.DtsViLineItem"/>

Try changing it.

Top

peebrayne

Post subject:

Posted: Fri Apr 14, 2006 3:17 pm

Newbie

Joined: Thu Apr 06, 2006 6:41 pm
Posts: 6

Well I finally went out and got the book Hibernate In Action, where I found that I needed to match the ORDER of the composite keys. I would have never figured it out. Composite keys are such a pain. Thanks for your help pepelnm.

Code:

 <composite-id
            name="dtsViPK"
            class="com.sumware.model.DtsViPK"
                unsaved-value="any"
        >
                <key-property
                    name="headerOrderReference"
                    type="java.lang.String"
                        column="headerOrderReference"
                >
                </key-property>

                <key-property
                    name="identDealerNumber"
                    type="java.lang.String"
                        column="identDealerNumber"
                >
                </key-property>

                <key-property
                    name="manufacturer"
                    type="java.lang.Integer"
                        column="manufacturer"
                >
                </key-property>

        </composite-id>


<set
            name="lineItems"
            lazy="false"
        >

            <key
            >
            <column
                name="headerOrderReference" 
            />

            <column
                name="identDealerNumber" 
            />

            <column
                name="manufacturer" 
            />

            </key>          

            <one-to-many
                class="com.sumware.model.DtsViLineItem"
            />

        </set>

Top

DWright

Post subject:

Posted: Fri Apr 14, 2006 7:07 pm

Regular

Joined: Wed Jul 07, 2004 2:00 pm
Posts: 64

If you want to retrieve both the parent and children, why not use

select
d
from
DtsVi d
left outer join fetch d.lineItems li
where
d.dtsViPK.identDealerNumber = ?

Top

Page 1 of 1

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