Joining two tables using an existing mapping

Cue · **Joined:** Tue May 04, 2004 6:15 am **Posts:** 51

Hi there,

Hibernate version: 2.1.4
Db: Microsoft SQL Server 2000 - 8.00.760

Code:

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

<hibernate-mapping package="gr.forthnet.openseas.models.shippingroutes">   

<class name="Port" table="ports">
        <id name="abbreviation" column="port_id" type="java.lang.String" unsaved-value="null" length="32"> 
            <generator class="hilo"/> 
        </id> 

        <property name="name" column="port_name" type="java.lang.String" not-null="true" /> 
              
        <set name="destinations" table="depdest" cascade="all" inverse="true" lazy="false">
            <key column="departureID" />
            <many-to-many column="destinationID" class="Port" />
        </set>          
</class>
</hibernate-mapping>

I'm trying to do the following select using

Code:

List result = session.find(hql, value, Hibernate.STRING);

Code:

"SELECT DISTINCT FROM Port JOIN ports_destinations Destination IN Port.port_id = Destination.pcode WHERE Destination.dabbr = ?";

and i get the following exception

Code:

net.sf.hibernate.QueryException: in expected: Destination [SELECT DISTINCT FROM gr.forthnet.openseas.models.shippingroutes.Port JOIN ports_destinations Destination IN Port.port_id = Destination.pcode WHERE Destination.dabbr = ?]
   at net.sf.hibernate.hql.FromParser.token(FromParser.java:102)
   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:1561)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1532)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1520)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1516)

Thanx in advance!

shl · **Posted:** Tue Jul 20, 2004 6:57 am

your HQL is not correct, try this:

Code:

SELECT DISTINCT port FROM Port port
JOIN port.destinations destination WHERE destination.dabbr = ?";[quote]

--
Leonid[/quote]

Cue · **Joined:** Tue May 04, 2004 6:15 am **Posts:** 51

Hey, thx for the reply.

However I get the same error.

AFAIK the two tables dont have any pk/fk so I think I have to specify the join explicity

Trying

Code:

SELECT DISTINCT port FROM Port port JOIN ports_destinations destination IN port.port_id = destination.pcode WHERE destination.dabbr = ?

did not work :(...

michael · **Posted:** Tue Jul 20, 2004 8:50 am

You can't do arbitary join conditions with HQL