• 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:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

JPA join fetch query with many to many

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have an object Favourite. This object has a many to many connection with Colors. Lets say that some favourites have many colors, 2-3 etc. When I search for a favourite like:

.

The resulting object contains all the colors related to this favourite. My problem is when I want to search for a favourite that has a certain color. For example:



Then the resulting object contains only the red color. I want to get as a result the fav objects that contain the "red" color, but also show all the related colors. Any suggestions? Thanks in advance.
 
Bartender
Posts: 1682
7
Android Mac OS X IntelliJ IDE Spring Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

but also show all the related colors.



Sorry I am not following you. You might need to give more details of what you mean by all related colors. Your query clearly states WHERE cl.name = "red". Why would you expect to get others?
 
Panos Ath
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Bill Gorder wrote:

but also show all the related colors.



Sorry I am not following you. You might need to give more details of what you mean by all related colors. Your query clearly states WHERE cl.name = "red". Why would you expect to get others?



I want to search for ALL the favourite objects that CONTAIN the color red. Now I have it the way I mentioned and it brings only color"red".

When a favourite object with id = 5 has colors = [red, green, yellow] and I search for favourite item with color "red", now I get the object with id =5 and colors = [red]. I want to get the full list of colors, object id = 5, colors = [red, green, yellow].
 
Bill Gorder
Bartender
Posts: 1682
7
Android Mac OS X IntelliJ IDE Spring Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well I assume you have something similar to this in your Favorite class



Now if you are using hibernate (at least hibernate 3.x not sure about 4) there is a bug with ELEMENT OF so you will need something like



Otherwise if you are using a different persistence provider you can try this (which should work in hibernate as well but does not)

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

Bill Gorder wrote:Well I assume you have something similar to this in your Favorite class



Now if you are using hibernate (at least hibernate 3.x not sure about 4) there is a bug with ELEMENT OF so you will need something like



Otherwise if you are using a different persistence provider you can try this (which should work in hibernate as well but does not)



I am using JPA over Hibernate 3.3.1 . Which should I use?
 
Bill Gorder
Bartender
Posts: 1682
7
Android Mac OS X IntelliJ IDE Spring Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The first one. The member of one is a known bug in hibernate 3.x
 
Panos Ath
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Bill Gorder wrote:The first one. The member of one is a known bug in hibernate 3.x



It almost worked!! The problem is that fav.colors in not a List of Strings, but a list of Objects "Color" which cantains Id, and Name. I want to search by the name of the color. If I do it like



It tries to equal the string "Red" with the Object Color and it throws exception.
 
Bill Gorder
Bartender
Posts: 1682
7
Android Mac OS X IntelliJ IDE Spring Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Why not use an Enum to represent Color as I showed above. All it has is a name. If you wanted a print friendly name you could add a label to the enum.

Anyway if you want color to be its own Entity then you will need to compare entity to entity.




In this case color is a parameter of type Color.
 
reply
    Bookmark Topic Watch Topic
  • New Topic