File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
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 Android Security Essentials Live Lessons this week in the Android 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
 
Don't get me started about those stupid light bulbs.
 
subject: Basic Hibernate many-to-many mapping / query not right
 
Similar Threads
sub query in HQL
null pointer exception
org.hibernate.QueryException: could not resolve property: ObjectId of: test.Objtablee
Hibernate/Spring/Jboss Annotations + Entity Mapping problems
error in running HQL Query