Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
The moose likes Object Relational Mapping and the fly likes Basic Hibernate many-to-many mapping / query not right Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Basic Hibernate many-to-many mapping / query not right" Watch "Basic Hibernate many-to-many mapping / query not right" New topic
Author

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

Travis Prescott
Greenhorn

Joined: Sep 05, 2009
Posts: 24

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:
----------------------------------------------




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


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



ERROR:
-----

Thillakan Saba
Ranch Hand

Joined: May 15, 2007
Posts: 53


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





HTH


SCBCD, SCJP & MCP
HowToAskQuestions
Travis Prescott
Greenhorn

Joined: Sep 05, 2009
Posts: 24
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
Greenhorn

Joined: Sep 05, 2009
Posts: 24

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
Greenhorn

Joined: Sep 05, 2009
Posts: 24
here's the generated SQL:

Travis Prescott
Greenhorn

Joined: Sep 05, 2009
Posts: 24
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:



I now get this sql stmt generated:



and it generates the following results:

Thillakan Saba
Ranch Hand

Joined: May 15, 2007
Posts: 53
I think , You have to specify which part of the result you are going to use.

try




Travis Prescott
Greenhorn

Joined: Sep 05, 2009
Posts: 24
Thanks! That was the solution:



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

Thanks for the response, Thillakan!

Travis
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Basic Hibernate many-to-many mapping / query not right