illegal attempt to dereference collection

vinicius.carvalho · **Joined:** Thu May 18, 2006 1:46 pm **Posts:** 10

Hibernate version: 3.2.3
Entity Manager version: 3.3.1
Hibernate Annotations version: 3.3.0

Hello there! I'm porting an already functional project using Hibernate 3.1 and SessionFactory to a JPA approach.

After changing all my xdoclets annotations to my new JPA annotations, one query is not working anymore. It's a many-to-many bi-directional relationship.

Here's the snippet from the hbm, and my mapped version with annotations:

Code:

From Neighborhood:
<set
            name="sections"
            table="sections_neighborhoods"
            lazy="true"
            inverse="true"
            cascade="none"
            sort="unsorted"
        >
            <key
                column="id_neighborhood"
            >
            </key>

            <many-to-many
                class="x.x.x.Section"
                column="id_section"
                outer-join="auto"
             />

        </set> 

From Section:

<set
            name="neighborhoods"
            table="sections_neighborhoods"
            lazy="true"
            cascade="none"
            sort="unsorted"
            mutable="false"
            
        >
            <key
                column="id_section"
            >
            </key>

            <many-to-many
                class="x.x.x.x.Neighborhood"
                column="id_neighborhood"
                outer-join="auto"
             />

        </set>

New mapping:

Section:
@ManyToMany(targetEntity=Neighborhood.class)
   @JoinTable(name="sections_neighborhoods",joinColumns={@JoinColumn(name="id_section")},inverseJoinColumns={@JoinColumn(name="id_neighborhood")})
   public Set<Neighborhood> getNeighborhoods() {
      return  neighborhoods;
   }

Neighborhood:
@ManyToMany(targetEntity=Section.class)
   @JoinTable(name="sections_neighborhoods",joinColumns={@JoinColumn(name="id_neighborhood")},inverseJoinColumns={@JoinColumn(name="id_section")})
   public Set<Section> getSections() {
      return  sections;
   }

And the following query used to work:

from Neighborhood n where n.sections.id = ? order by n.name

I was selecting all neighborhoods from a given section. All was ok, now that I've changed I get the exception:
org.hibernate.QueryException: illegal attempt to dereference collection [neighborhood0_.id_neighborhood.sections] with element property reference [id].

What am I missing here? I tried to add a mappedBy to the Neighborhood side of the association but did not help :( (the inverse was the only thing that I noticed that was missing)

Regards

himawan · **Joined:** Sat Aug 19, 2006 6:31 pm **Posts:** 139

You can't dereference a collection like that.

Code:

n.sections.id

sections is a collection in n.

You have to join or use IN.

FROM Neighborhood n, IN (n.sections) AS s WHERE s.id=?

mk · **Joined:** Sat Jan 29, 2005 8:49 pm **Posts:** 20

After upgrading to the latest Hibernate version I got this error. In the documentation it is still mentioned that this kind of reference is possible (and it makes very much sense to use it).

I try to use a statement like this

Code:

from cat c where c.mate.id = 13

the resulting SQL should be something like

Code:

SELECT * FROM CAT C WHERE C.MATE_ID = 13

which is much more performant than making a join

Code:

from cat c join c.mate m where m.id = 13

which would result in

Code:

SELECT * FROM CAT C INNER JOIN MATE M ON C.MATE_ID = M.ID WHERE M.ID = 13

A downgrade to 3.2.2.ga works for me right now and I guess it will be working again in future versions.

It doesn't work with version 3.2.3.ga and with 3.2.4.sp1.

I have created a jira entry for this
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2667

Nopalin · **Joined:** Sat Oct 07, 2006 2:48 pm **Posts:** 17

i am having this problem too, but after all i could got this working. My original query was:

Code:

from Library lb where lb.disabled = false and lb.books.id = :bId

i wanted to fetch libraries for specific book. In jboss 4.0.3SP1 (not sure what hibernate version it has) this query works perfectlu, but in jboss 4.2.0GA this shows me the illegal attempt to dereference collection exception.

I changed to this query:

Code:

from Library lb left join lb.books b where lb.disabled = false and b.id = :bId

and it works!

But the problem is: result list is not of my Entity Type.
The first query returns a list of Libraries, so i can iterate it with the form:

Code:

for(Library lb: em.createQuery(query).getResultList())
{
}

the second query returns a list the Libraries (at least i would guess that), but when i iterate it at the same form, i get this exception:

java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to entity.Library

i also tried with this query:

Code:

from Library lb, IN (lb.books) b where lb.disabled = false and b.id = :bId

but i get the same exception..

what am i doing wrong? do i need some special configuration or something?

Thanks

mk · **Joined:** Sat Jan 29, 2005 8:49 pm **Posts:** 20

Well the thing is that

Code:

from Library lb where lb.disabled = false and lb.books.id = :bId

does not generate the same SQL as

Code:

from Library lb left join lb.books b where lb.disabled = false and b.id = :bId

In the first case you use only one table because lb.books.id only take the id and does not need a join. In the second case you make an inner join which takes much longer. This is why I have opened a case in jira.

Although you can use the join statement but of course you have to specify what part of the result you want to use.

This should work for you. But again it is slower than the first query.

Code:

select distinct lb from Library lb left join lb.books b where lb.disabled = false and b.id = :bId

Don't forget to vote ;-)

Nopalin · **Joined:** Sat Oct 07, 2006 2:48 pm **Posts:** 17

i have to do left join because books is a collection property, first query or second implies a join.

these are my entities:

Code:

@Entity
class Library{
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
int id;

@NotNull
String name;

@NotNull
boolean disabled;

@ManyToMany
Set<Book> books;
}


@Entity
class Book{
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
int id;

@NotNull
String name;

@NotNull
boolean disabled;

@NotNull
String description;
}

as you can see, i use a manytomany relationship, so library tables does not contain book properti, them are in middle table between library and book. I am trying to get libraries for specific book, so this implies a join anyway.

But the problem is not the query i guess, because it works, and it returns the correct result lit size depending on the criteria, the problem is the type of objects returned in the list.. i would suppose them are of Library type, but they aren't. hmm i dont know why.

Thanks

PD. i also tried your query and it fails for me =(

Nopalin · **Joined:** Sat Oct 07, 2006 2:48 pm **Posts:** 17

Please delete this post

mk · **Joined:** Sat Jan 29, 2005 8:49 pm **Posts:** 20

you are right you need a join anyway.

actually in this case it also does not make a difference if you use a left join or an inner join because you reference a column of the other table.

saying: you can't get a library as a result that doesn't have any books. because you want to get libraries that contain the book with a given id.

a left join would allow the result set to contain records where there are no book records. an inner join would not allow that.

anyhow, the point is not the join. the point is that you have to define what you want to get as a result. that is why you need to define a select something. the distinct statement helps to get rid of duplicate entries that might be produced by the join. in case of this join there shouldn't be any duplicates (as long as you do not search for 2 books at a time) so you can omit it.

so this should work

Code:

select lb from Library lb join lb.books b where lb.disabled = false and b.id = :bId 

if you user that query what is the result? is there an error message?

Nopalin · **Joined:** Sat Oct 07, 2006 2:48 pm **Posts:** 17

mk wrote:

the point is that you have to define what you want to get as a result.
so this should work

Code:

select lb from Library lb join lb.books b where lb.disabled = false and b.id = :bId 

if you user that query what is the result? is there an error message?

yes man it worked!!

thank you so much for your help. sorry for this late reply =)

PD. as i am not the creator of this topic, i can not rate you, =(

ysoen · **Joined:** Wed May 27, 2009 7:43 pm **Posts:** 4

mk - you rule!
i know its two years after you posted your reply... but your post has ended a three-day struggle!!!

thanks!

sihan · **Joined:** Mon Sep 07, 2009 4:34 pm **Posts:** 1

Hi,
Sorry to reopen the thread after a long time.
However I am trying to convert the following sql to HQL:

Quote:

select * from users u where
(Select count(id) From videos v where v.uploader=u.username and v.status='Accepted') > 100;

I am trying to get it like this:

Quote:

"select u from users u where u.videosCollection.size>=200 and u.videosCollection.status='Accepted'" ;

But I am getting this exception:
" illegal attempt to dereference collection"

The relation between users and videos is OneToMany.
My intention is to get all the users from users table who have uploaded not less than 200 'Accepted' videos.

Any helpful ideas is much appreciated.

Thanks

micmic2000 · **Joined:** Tue Jul 17, 2007 9:05 am **Posts:** 9

You can try this instead:
"select u from users u, u.videosCollection v where v.size>=200 and v.status='Accepted'"

Nicola · **Joined:** Tue Mar 23, 2010 10:33 am **Posts:** 12

I have the problem of "illegal attempt to dereference collection" but my case is a little bit complex

I have 3 tables A, B, C

C has foreign key to B
B has foreign key to A

I mapped in this way

A has a collection ( Set<B> ) of B
B has a collection ( Set<C> ) of C

I would like to select A, B and C in the same query

If I do

"select distinct a from A a left join fetch a.b"

It works but I think if I can fetch the C associations it should be be faster.

How can I fetch the C associations to B?

I have tried:

"select distinct a from A a left join fetch a.b left join fetch a.b.c"

But this istructions give me the error "illegal attempt to dereference collection"

Please help me

dandan · **Joined:** Mon Dec 25, 2006 4:59 am **Posts:** 15

Hei Nicola

I am stuck with same problem you have experienced.
if you have solved it please share it with me.

thanks

tnagarjuna · **Joined:** Thu Jul 26, 2012 2:38 am **Posts:** 1

Hello Dandan/Nicola,

I am also facing the same issue, If you guys found the solution can you please share it me.
I have three tables(Three DTOs) A,B and C.
A has List of B objects
B has List of C objects
My Query is like this:

Code:

select a from A a
join a.b as b
join b.c as c
Where 
a.name ='' and 
c.id = ''

Thanks & Regards,
Nagarjuna.