Hi to everyone,
i've this database scenario:

i've this five table with this field on my db:
- hib_person(id, firstName, lastName, email);
- hib_firm(id, name, address);
- hib_country(id, name);
- hib_firm_person(person_id, firm_id);
- hib_firm_country (firm_id, country_id)

the hbm files are this:

Person.hbm.xml:

<class name="thecat.test.hibernate.Person" table="hib_PERSON">
<id name="id" type="long" column="ID" >
<generator class="assigned"/>
</id>

<property name="firstName">
<column name="FIRSTNAME" />
</property>
<property name="lastName">
<column name="LASTNAME"/>
</property>
<property name="email">
<column name="EMAIL"/>
</property>

<set name="firms" table="hib_FIRM_PERSON" lazy="false">
<key column="PERSON_ID"/>
<many-to-many column="FIRM_ID" class="thecat.test.hibernate.Firm"/>
</set>

Country.hbm.xml

<class name="thecat.test.hibernate.Country" table="hib_COUNTRY">
<id name="id" type="long" column="ID" >
<generator class="assigned"/>
</id>

<property name="name">
<column name="NAME" />
</property>
<set name="firms" table="hib_FIRM_PERSON" lazy="false">
<key column="PERSON_ID"/>
<many-to-many column="FIRM_ID" class="thecat.test.hibernate.Firm"/>
</set>
</class>

firm.hbm.xml:

<class name="thecat.test.hibernate.Firm" table="hib_FIRM">
<id name="id" type="long" column="ID" >
<generator class="assigned"/>
</id>

<property name="name">
<column name="NAME" />
</property>
<property name="address">
<column name="ADDRESS"/>
</property>

<set name="persons" table="hib_FIRM_PERSON">
<key column="FIRM_ID"/>
<many-to-many column="PERSON_ID" class="thecat.test.hibernate.Person"/>
</set>

<set name="countries" table="hib_FIRM_COUNTRY" lazy="false">
<key column="FIRM_ID"/>
<many-to-many column="COUNTRY_ID" class="thecat.test.hibernate.Country"/>
</set>

</class>


I'm trying this HQL query:

from Person as personwhere person.firms.name='suzuki' and person.firms.countries.name = 'nippon'

the result is the following exception:

12-feb-2007 13.50.28 org.hibernate.hql.ast.ErrorCounter reportError
GRAVE: illegal syntax near collection: countries
java.lang.NullPointerException
at org.hibernate.hql.ast.tree.DotNode.getColumns(DotNode.java:97)
at org.hibernate.hql.ast.tree.DotNode.initText(DotNode.java:210)
at org.hibernate.hql.ast.tree.DotNode.resolve(DotNode.java:204)
at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:94)
at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:90)
at org.hibernate.hql.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:725)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:1215)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:4032)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:3518)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1758)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1686)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:776)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:577)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:218)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:158)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:109)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:75)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:54)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
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:1583)
at thecat.test.hibernate.FirmPersonTest.main(FirmPersonTest.java:56)

Can someone tell me where i'm wrong?

Thanks in advance,
fabio.

Hi fabio, try something like:

from Person person join person.firms firm where firm.name = 'suzuki' and 'nippon' in element(firm.countries)

This solution cause this exception:

12-feb-2007 17.25.31 org.hibernate.hql.ast.ErrorCounter reportError
GRAVE: line 1:117: unexpected token: (
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 117 [from thecat.test.hibernate.Person person join person.firms firm where firm.name = 'suzuki' and 'giappone' in element(firm.countries.name)]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:244)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:155)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:109)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:75)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:54)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
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:1583)
at thecat.test.hibernate.FirmPersonTest.main(FirmPersonTest.java:58)
Caused by: line 1:117: unexpected token: (
at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:743)
at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:296)
at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:159)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:238)
... 9 more

Any idea,
thanks,
Fabio.

You also need to mark one side of your many-to-many relation as the inverse, something like:

<set name="firms" table="hib_FIRM_PERSON" lazy="false" inverse="true">

And, I'm probably wrong about the use of "in element()" here since it's not a element mapping, try without.

Also have a look at the hibernate docs, maybe they can help, especially chapter 11 (http://www.hibernate.org/hib_docs/refer ... ryhql.html). :)

Thaks for aswers,
but the problem is not solved, i'm read the cap 11 of the hibernate tutorial, but when i'm using a join clausole, the query returns 2 entyties (Person and firm) but i want only Person entity, with the correct value.

If someone have the same problem, please write me, i don't think i'm the only one using the join on more tables with many to many relations.

Thenks,
bye,
Fabio.

No problem!

Just use a "select person" before the "from" clause! :)

I've solved the problem, the correct HQL is:
from Person person join fetch person.firms firm join fetch firm.countries as countries where firm.name = 'suzuki' and countries.name = 'nippon'"

If anyone have more suggestions, please send a feedback.
Thanks to everyone have read this topic and thanks to daijavad for the suggesion.

Bye, Fabio.

You're welcome, please rate! :)