This week's book giveaway is in the Java 8 forum.
We're giving away four copies of Java 8 in Action and have Raoul-Gabriel Urma, Mario Fusco, and Alan Mycroft on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes JPA join fetch query with many to many Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "JPA join fetch query with many to many" Watch "JPA join fetch query with many to many" New topic
Author

JPA join fetch query with many to many

Panos Ath
Greenhorn

Joined: Aug 21, 2012
Posts: 4
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.
Bill Gorder
Bartender

Joined: Mar 07, 2010
Posts: 1632
    
    7

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?


[How To Ask Questions][Read before you PM me]
Panos Ath
Greenhorn

Joined: Aug 21, 2012
Posts: 4
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

Joined: Mar 07, 2010
Posts: 1632
    
    7

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

Joined: Aug 21, 2012
Posts: 4
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

Joined: Mar 07, 2010
Posts: 1632
    
    7

The first one. The member of one is a known bug in hibernate 3.x
Panos Ath
Greenhorn

Joined: Aug 21, 2012
Posts: 4
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

Joined: Mar 07, 2010
Posts: 1632
    
    7

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JPA join fetch query with many to many
 
Similar Threads
equals() and hashcode() from K&B
hibernate mysql "Like"
Recursive fetch join not recursively fetching all children
How to import classes in a subpackage from another sub-package?
want algorithm to generate 100+ distinct colors