Basic Hibernate many-to-many mapping / query not right

[Travis Prescott]

I am trying to map a many to many relationship as below. A straight "select *" query works but when I try to limit the results I'm getting errors that are difficult to understand. In the example below, I'm attempting to get all of the Song objects that have a SoundsLike value with a particular soundsLikeSearchId. For the record, I get the same error if I were to attempt to filter on the SoundsLike.id value too. I've seen lots of documentation on the mapping but not much on the query. What is a good way to do this? I thought the way below was rational...is it not?

table definition for many-to-many relationship:
----------------------------------------------

SONG:
+-------------+--------------+------+-----+---------+-------+
| song_id     | char(22)     | NO   | PRI | NULL    |       |
| tempo_id    | char(22)     | NO   |     | NULL    |       |
| style_id    | char(22)     | NO   |     | NULL    |       |
| feel_id     | char(22)     | NO   |     | NULL    |       |
| description | varchar(255) | NO   |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

SOUNDS_LIKE:
+-----------------------+--------------+------+-----+---------+-------+
| sounds_like_id        | char(22)     | NO   | PRI | NULL    |       |
| artist_name           | varchar(255) | NO   |     | NULL    |       |
| sounds_like_search_id | char(22)     | NO   |     | NULL    |       |
| active_yn             | char(1)      | NO   |     | NULL    |       |
+-----------------------+--------------+------+-----+---------+-------+

SONG_SOUNDS_LIKE:
+---------------------+----------+------+-----+---------+-------+
| song_sounds_like_id | char(22) | NO   | PRI | NULL    |       |
| song_id             | char(22) | NO   |     | NULL    |       |
| sounds_like_id      | char(22) | NO   |     | NULL    |       |
+---------------------+----------+------+-----+---------+-------+

hibernate mapping for tables:
----------------------------

<class name="Song" table="song">
    <id name="id" type="string" column="SONG_ID">
        <generator class="com.tuba.common.dao.KeyGenerator" />
    </id>
    <property name="tempoId" type="string" column="TEMPO_ID" />
    <property name="styleId" type="string" column="STYLE_ID" />
    <property name="feelId" type="string" column="FEEL_ID" />
    <property name="description" type="string" column="DESCRIPTION" />
    <set name="soundsLike" table="SONG_SOUNDS_LIKE" cascade="save-update" fetch="join" lazy="false">
        <key column="SONG_ID" />
        <many-to-many column="SOUNDS_LIKE_ID"  class="com.tuba.license.bean.SoundsLike" />
    </set>
</class>

<class name="SoundsLike" table="SOUNDS_LIKE">
    <id name="id" type="string" column="SOUNDS_LIKE_ID">
        <generator class="com.tuba.common.dao.KeyGenerator" />
    </id>
    <property name="artistName" type="string" column="ARTIST_NAME" />
    <property name="soundsLikeSearchId" type="string" column="SOUNDS_LIKE_SEARCH_ID" />
    <property name="activeYn" type="yes_no" column="ACTIVE_YN" />
</class>

fields in objects:
-----------------

Song.java:
    private String id;
    private String tempoId;
    private String styleId;
    private String feelId;
    private String description;
    private Set<SoundsLike> soundsLike;
SoundsLike.java:
    private String id;
    private String artistName;
    private String soundsLikeSearchId;
    private Boolean activeYn;

HQL that works:
    String hql = "from Song";
    results = getHibernateTemplate().find(hql);

HQL that does NOT work:
    String hql = "from Song where soundsLike.id = ?";
    Object[] params = { "1" };
    List<Song> results = getHibernateTemplate().find(hql, params);

ERROR:
-----

22:02:10,359 ERROR [STDERR] Caused by: org.springframework.orm.hibernate3.HibernateQueryException: illegal attempt to dereference collection [{synthetic-alias}{non-qualified-property-ref}soundsLike] with element property reference [id] [from com.tuba.license.bean.Song where soundsLike.id = ?]; nested exception is org.hibernate.QueryException: illegal attempt to dereference collection [{synthetic-alias}{non-qualified-property-ref}soundsLike] with element property reference [id] [from com.tuba.license.bean.Song where soundsLike.id = ?]
22:02:10,359 ERROR [STDERR] 	at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:657)
22:02:10,359 ERROR [STDERR] 	at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
22:02:10,359 ERROR [STDERR] 	at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424)
22:02:10,359 ERROR [STDERR] 	at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
22:02:10,359 ERROR [STDERR] 	at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:921)
22:02:10,359 ERROR [STDERR] 	at com.tuba.license.dao.LicenseDaoImpl.getSoundsLike(LicenseDaoImpl.java:48)
22:02:10,359 ERROR [STDERR] 	... 41 more
22:02:10,359 ERROR [STDERR] Caused by: org.hibernate.QueryException: illegal attempt to dereference collection [{synthetic-alias}{non-qualified-property-ref}soundsLike] with element property reference [id] [from com.tuba.license.bean.Song where soundsLike.id = ?]
22:02:10,359 ERROR [STDERR] 	at org.hibernate.hql.ast.tree.DotNode$1.buildIllegalCollectionDereferenceException(DotNode.java:69)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.hql.ast.tree.DotNode.checkLhsIsNotCollection(DotNode.java:536)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.hql.ast.tree.DotNode.resolve(DotNode.java:244)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:117)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:113)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.hql.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:750)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.hql.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:1216)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.hql.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:4041)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.hql.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:3525)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1762)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.hql.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:776)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:577)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:251)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:183)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:134)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
22:02:10,359 ERROR [STDERR] 	at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1650)
22:02:10,359 ERROR [STDERR] 	at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:923)
22:02:10,359 ERROR [STDERR] 	at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:419)

[Thillakan Saba]

String hql = "from Song where soundsLike.id = ?";

You can not use like this because soundsLike is a Set. you have to use JOIN or IN statement.

String hql = "from Song s, IN (s.soundsLike) sl where sl.id = ?";

or
String hql = "from Song s join s.soundsLike sl where sl.id = ?";

HTH

[Travis Prescott]

it appears that my limitation is SQL...not hibernate ;-)

That worked - it's returning the correct number of objects too. The only problem is that the app is failing when I try to cast the returning List to List<Song>. After playing with it, it is definitely not a list of Song objects. When I debugged & did an object.getClass(), it tells me that it's actually an Object. How can this be? The query even indicates "from Song". I did try to cast it to other objects that I have mapped here (both the join table & the SoundsLike table) and they fail as well. Do you have any idea what's happening here?

Travis

[Travis Prescott]

If I comment out the query and do just a select * (see below) rather than the join, the query returns a List<Song> as a result. With the join, it returns a List<Object>. This makes no sense b/c they are both "...from Song...". Why is the return different? Also, I have toString() methods in all of my beans but when I just print the results out, it prints the Object reference ID rather than the toString() value...so it's not even returning my bean objects.


String hql = "from Song s join s.soundsLike sl where sl.soundsLikeSearchId = ?";
List result = getHibernateTemplate().find(hql, params);
//result is a List<Object> ... not List<Song> but returns the proper number of results

String hql = "from Song s where s.id = ?";
List result = getHibernateTemplate().find(hql, params);
//result is a List<Song>

[Travis Prescott]

here's the generated SQL:

20:43:23,656 INFO  [LoggingInterceptor] Finishing execution stack for action //testSoundsLike
20:43:25,468 INFO  [STDOUT] Hibernate: 
    /* 
from
    Song s 
join
    s.soundsLike sl 
where
    sl.soundsLikeSearchId = ? */ select
        song0_.SONG_ID as SONG1_3_0_,
        soundslike2_.SOUNDS_LIKE_ID as SOUNDS1_8_1_,
        song0_.TEMPO_ID as TEMPO2_3_0_,
        song0_.STYLE_ID as STYLE3_3_0_,
        song0_.FEEL_ID as FEEL4_3_0_,
        song0_.DESCRIPTION as DESCRIPT5_3_0_,
        soundslike2_.ARTIST_NAME as ARTIST2_8_1_,
        soundslike2_.SOUNDS_LIKE_SEARCH_ID as SOUNDS3_8_1_,
        soundslike2_.ACTIVE_YN as ACTIVE4_8_1_ 
    from
        SONG song0_ 
    inner join
        SONG_SOUNDS_LIKE soundslike1_ 
            on song0_.SONG_ID=soundslike1_.SONG_ID 
    inner join
        SOUNDS_LIKE soundslike2_ 
            on soundslike1_.SOUNDS_LIKE_ID=soundslike2_.SOUNDS_LIKE_ID 
    where
        soundslike2_.SOUNDS_LIKE_SEARCH_ID=?
20:43:25,609 INFO  [STDOUT] Hibernate: 
    /* load collection com.tuba.license.bean.Song.soundsLike */ select
        soundslike0_.SONG_ID as SONG1_1_,
        soundslike0_.SOUNDS_LIKE_ID as SOUNDS2_1_,
        soundslike1_.SOUNDS_LIKE_ID as SOUNDS1_8_0_,
        soundslike1_.ARTIST_NAME as ARTIST2_8_0_,
        soundslike1_.SOUNDS_LIKE_SEARCH_ID as SOUNDS3_8_0_,
        soundslike1_.ACTIVE_YN as ACTIVE4_8_0_ 
    from
        SONG_SOUNDS_LIKE soundslike0_ 
    left outer join
        SOUNDS_LIKE soundslike1_ 
            on soundslike0_.SOUNDS_LIKE_ID=soundslike1_.SOUNDS_LIKE_ID 
    where
        soundslike0_.SONG_ID=?
20:43:25,625 INFO  [STDOUT] Hibernate: 
    /* load collection com.tuba.license.bean.Song.soundsLike */ select
        soundslike0_.SONG_ID as SONG1_1_,
        soundslike0_.SOUNDS_LIKE_ID as SOUNDS2_1_,
        soundslike1_.SOUNDS_LIKE_ID as SOUNDS1_8_0_,
        soundslike1_.ARTIST_NAME as ARTIST2_8_0_,
        soundslike1_.SOUNDS_LIKE_SEARCH_ID as SOUNDS3_8_0_,
        soundslike1_.ACTIVE_YN as ACTIVE4_8_0_ 
    from
        SONG_SOUNDS_LIKE soundslike0_ 
    left outer join
        SOUNDS_LIKE soundslike1_ 
            on soundslike0_.SOUNDS_LIKE_ID=soundslike1_.SOUNDS_LIKE_ID 
    where
        soundslike0_.SONG_ID=?

[Travis Prescott]

after further experimentation with this issue, I changed to the other suggestion of the "in" statement for the query. This gives me good data & eliminates a secondary query to load the collection (see the information below).

PROBLEM: I am still getting a List of objects back from my "from Song" query. What is Hibernate returning to me? I have never seen this before & am about out of ideas. I can't cast them to anything meaninful. What do I do?

Travis


Using this sql stmt:

String hql = "from Song s, IN (s.soundsLike) sl where sl.id = ?";

I now get this sql stmt generated:

select
    song0_.SONG_ID as SONG1_3_0_,
    soundslike2_.SOUNDS_LIKE_ID as SOUNDS1_8_1_,
    song0_.TEMPO_ID as TEMPO2_3_0_,
    song0_.STYLE_ID as STYLE3_3_0_,
    song0_.FEEL_ID as FEEL4_3_0_,
    song0_.DESCRIPTION as DESCRIPT5_3_0_,
    soundslike2_.ARTIST_NAME as ARTIST2_8_1_,
    soundslike2_.SOUNDS_LIKE_SEARCH_ID as SOUNDS3_8_1_,
    soundslike2_.ACTIVE_YN as ACTIVE4_8_1_ 
from
    SONG song0_ 
inner join
    SONG_SOUNDS_LIKE soundslike1_ 
        on song0_.SONG_ID=soundslike1_.SONG_ID 
inner join
    SOUNDS_LIKE soundslike2_ 
        on soundslike1_.SOUNDS_LIKE_ID=soundslike2_.SOUNDS_LIKE_ID 
where
    soundslike2_.SOUNDS_LIKE_ID='1';

and it generates the following results:

[size=7]
------------------------------------------------------------------------------------------------------------------
 SONG1_3_0_ SOUNDS1_8_1_ TEMPO2_3_0_ STYLE3_3_0_ FEEL4_3_0_ DESCRIPT5_3_0_ ARTIST2_8_1_ SOUNDS3_8_1_ ACTIVE4_8_1_ 
------------------------------------------------------------------------------------------------------------------
 1          1            1           1           1          song one       Madonna      1             Y            
 4          1            4           4           4          song four      Madonna      1             Y            
 4          1            4           4           4          song four      Madonna      1             Y            
------------------------------------------------------------------------------------------------------------------
[/size]

[Thillakan Saba]

I think , You have to specify which part of the result you are going to use.

try

String hql = " select s from Song s, IN (s.soundsLike) sl where sl.id = ?";  

or

String hql = " select distict s from Song s, IN (s.soundsLike) sl where sl.id = ?";  

[Travis Prescott]

Thanks! That was the solution:

select s from Song s, IN (s.soundsLike) sl where sl.soundsLikeSearchId = ?

One thing to note...the above was (very) slightly quicker than the join...but mostly negligable.

Thanks for the response, Thillakan!

Travis