How to do two joins in same query?

nerotnt · **Joined:** Wed Oct 08, 2003 10:31 am **Posts:** 247

I'm trying to join MpLote with Mp and ArmazemMp in the same query.
As the mapping file shows, MpLote has a "many-to-one" relation with Mp and a "one-to-one" relation with ArmazemMp.

I've tried diferent ways but it keeps giving me:

Code:

net.sf.hibernate.QueryException
( ... )

Can someone please help me on this?

------------------
Testing Code
------------------

Code:

( ... )
SessionFactory sessionFactory = HibernateFactory.createFactory();
session = sessionFactory.openSession();
         
res = session.find("select ml " +
         "from vo.MpLote as ml join ml.mp as mp, " +
         "join ml.armazemMp " +
         "where mp.referencia = '" + ref + "' order by ml.referencia asc");
( ... )

----------------------------------------
Mapping files (generated with R3)
----------------------------------------

MpLote

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
    
<hibernate-mapping>
<!-- 
    Created by Middlegen Hibernate plugin

    http://boss.bekk.no/boss/middlegen/
    http://hibernate.sourceforge.net/
-->

<class 
    name="vo.MpLote" 
    table="mp_lote"
>

    <id
        name="id"
        type="long"
        column="id"
    >
        <generator class="increment" />
    </id>

    <property
        name="referencia"
        type="java.lang.String"
        column="referencia"
        not-null="true"
        length="-1"
    />

    <!-- associations -->
    <!-- bi-directional many-to-one association to Mp -->
    <many-to-one
        name="mp"
        class="vo.Mp"
        not-null="true"
    >
        <column name="mp_fk" />
    </many-to-one>
    
    <!-- bi-directional one-to-one association to ArmazemMp -->
    <one-to-one
        name="armazemMp"
        class="vo.ArmazemMp"
        outer-join="auto"
        constrained="false"
    />

</class>
</hibernate-mapping>

Mp

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
    
<hibernate-mapping>
<!-- 
    Created by Middlegen Hibernate plugin

    http://boss.bekk.no/boss/middlegen/
    http://hibernate.sourceforge.net/
-->

<class 
    name="vo.Mp" 
    table="mp"
>

    <id
        name="id"
        type="long"
        column="id"
    >
        <generator class="increment" />
    </id>

    <property
        name="referencia"
        type="java.lang.String"
        column="referencia"
        not-null="true"
        length="-1"
    />

    <!-- associations -->
    <!-- bi-directional one-to-many association to MpLote -->
    <set
        name="mpLotes"
        lazy="true"
        inverse="true"
    >
        <key>
            <column name="mp_fk" />
        </key>
        <one-to-many 
            class="vo.MpLote"
        />
    </set>

</class>
</hibernate-mapping>

ArmazemMp

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
    
<hibernate-mapping>
<!-- 
    Created by Middlegen Hibernate plugin

    http://boss.bekk.no/boss/middlegen/
    http://hibernate.sourceforge.net/
-->

<class 
    name="vo.ArmazemMp" 
    table="armazem_mp"
>

    <id
        name="mpLoteFk"
        type="long"
        column="mp_lote_fk"
    >
        <generator class="assigned" />
    </id>

    <property
        name="localizacao"
        type="java.lang.String"
        column="localizacao"
        length="10"
    />

    <!-- associations -->
    <!-- bi-directional one-to-one association to MpLote -->
    <one-to-one
        name="mpLote"
        class="vo.MpLote"
        outer-join="auto"
    />

</class>
</hibernate-mapping>

drj · **Posted:** Thu Jan 08, 2004 8:53 am

Could you provide the full stack trace in DEBUG mode please.

Thanks

Justin

nerotnt · **Joined:** Wed Oct 08, 2003 10:31 am **Posts:** 247

drj wrote:

Could you provide the full stack trace in DEBUG mode please.

Thanks

Justin

Where's the stack trace:

Code:

net.sf.hibernate.QueryException: unexpected token: join [select ml from vo.MpLote as ml join ml.mp as mp, join ml.armazemMp where mp.referencia like upper('%ref%') order by ml.referencia asc]
   at net.sf.hibernate.hql.FromParser.token(FromParser.java:53)
   at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
   at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:123)
   at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
   at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
   at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
   at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:293)
   at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1493)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1464)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1454)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1446)

christian · **Posted:** Thu Jan 08, 2004 10:27 am

Remove the comma between "mp, join".

nerotnt · **Joined:** Wed Oct 08, 2003 10:31 am **Posts:** 247

christian wrote:

Remove the comma between "mp, join".

THANKS very much Christian!
It worked.