Query with intermediate table (set) problem

bromero · **Posted:** Wed Apr 04, 2007 1:25 pm

I have this code which was working with hibernate 3.1.3 but for other reasons i had to install 3.2.3 this morning and now i get a

Code:

org.hibernate.QueryException: illegal attempt to dereference collection

when i try to execute de code

Code:

int count = ((Integer) session.createQuery("select count(*) from Appellant ap where  (ap.sectors in (:sectorsIds))").setParameterList("sectorsIds", sectorsIdArray).iterate().next()).intValue();

where sectorsIdArray is an array that contains indexes of sectors in which i am interested.

The mapping files are:

Appellant.hbm.xml

Code:

<hibernate-mapping>
    <class name="org.ai.kandelo.beans.Appellant" table="APPELLANTS" lazy="false">
        <id name="id" column="APPELLANT_ID" unsaved-value="0">
            <generator class="native"/>
        </id>

        <property name="name" not-null="true"/>
        <property name="surname"/>
        <property name="address"/>
        [...]
     
        <set name="sectors" table="APPELLANTS_TO_SECTORS" lazy="false">
          <key column="APPELLANT_ID"/>
          <many-to-many column="SECTOR_ID"
              class="org.ai.kandelo.beans.Sector"/>
        </set>      
    </class>
</hibernate-mapping>

and

Sector.hbm.xml

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="org.ai.kandelo.beans.Sector" table="SECTORS" lazy="false">
        <id name="id" column="SECTOR_ID">
            <generator class="native"/>
        </id>
        <property name="nameKey" not-null="true"/>        
        <property name="visible"/>
    </class>
</hibernate-mapping>

The query i wanted to do is "give me all the appellants that have any on the indexes passed by me within it set sectors"

The strange thing is that it was working until i changed to version 3.2.3.

Thanks in advance and excuse me for my poor english. I promise to rank you if you give me any idea.

vpajjuri · **Posted:** Wed Apr 04, 2007 2:19 pm

Hey, why are you iterating over the result, if its just one count then use the uniqueResult() it will return you a long value. Hope this works.

Dont forget to rate.

bromero · **Posted:** Wed Apr 04, 2007 6:49 pm

I was following the directions in :

http://www.hibernate.org/hib_docs/v3/re ... tipstricks

Quote:

You can find the size of a collection without initializing it:

( (Integer) session.iterate("select count(*) from ....").next() ).intValue();

It didnt solve the problem, but thanks

bromero · **Posted:** Wed Apr 04, 2007 7:07 pm

This is the complete stacktrace

Code:

 00:56:04,153 ERROR [action]:253 - Servlet.service() para servlet action lanzó excepción
org.hibernate.QueryException: illegal attempt to dereference collection [appellant0_.APPELLANT_ID.sectors] with element property reference [id] [select count(*) from org.ai.kandelo.beans.Appellant ap where (ap.sectors.id in (:sectorsIds)]
at org.hibernate.hql.ast.tree.DotNode$1.buildIllegalCollectionDereferenceException(DotNode.java:46)
        at org.hibernate.hql.ast.tree.DotNode.checkLhsIsNotCollection(DotNode.java:512)
        at org.hibernate.hql.ast.tree.DotNode.resolve(DotNode.java:221)
        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:728)

dharmendra.pandey · **Posted:** Thu Apr 05, 2007 12:14 am

Hi bromero,

ap.sectors.id
This feature was in 3.0 version now you later version 3.1 or 3.2 it has been removed. basically it is problem with collection path .

To resolve this join it in from like

select count(*) from org.ai.kandelo.beans.Appellant ap , sectors s where (s.id in (:sectorsIds)

may be

select count(*) from org.ai.kandelo.beans.Appellant ap join ap.sectors as s where (s.id in (:sectorsIds)

would work

bromero · **Posted:** Thu Apr 05, 2007 10:09 am

Thanks dharmendra.pandey, your post help me.

Anyway, the final query has been (to avoid duplicates)

Code:

List list = session.createQuery("select distinct ap.id from Appellant ap join ap.sectors as s where (s.id in (:sectorsIds))")            
      .setParameterList("sectorsIds", getSectorsId(newUrgentAction))
      .setResultTransformer(Criteria.ROOT_ENTITY)      
      .list();

and then i return the list size. (with your select count(*) it returns the size of the inner join (resulting in duplicated appelants). Ive solved that with the "select distinct ap.id".

Thanks for your help