• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Jesse Silverman
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Al Hobbs
  • salvin francis

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

 
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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

 
Ranch Hand
Posts: 53
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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





HTH
 
Travis Prescott
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
here's the generated SQL:

 
Travis Prescott
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 53
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think , You have to specify which part of the result you are going to use.

try




 
Travis Prescott
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic