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 Hibernate - Query Issues With Many-To-One Relationships Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate - Query Issues With Many-To-One Relationships" Watch "Hibernate - Query Issues With Many-To-One Relationships" New topic
Author

Hibernate - Query Issues With Many-To-One Relationships

Leonard Bedner
Greenhorn

Joined: Oct 04, 2009
Posts: 5
Hello all.



So, I currently have 2 tables that have a many-to-one relationship, named Ability and AbilityType. Here are the sql commands used to create the tables:



Here is my hibernate config file:



Here are the XML mappings for the tables:



Here are the Java files for the tables:



Now, when I run a normal join on these tables to get all Abilities by a specific AbilityType, I am expecting to get 5 results of type com.brc.roe.db.ability.Abilityin the result set. What happens however is, I get 5 results of type java.lang.Object arrays. Within the 2 slots of these arrays are a reference to both a com.brc.roe.db.ability.Ability AND a com.brc.roe.db.ability.AbilityType.

Now, the results in of themselves are correct, but is there any way to just get the com.brc.roe.db.ability.Ability's? Here is my code:



If any more information is needed, let me know.

Thank you.
David Newton
Author
Rancher

Joined: Sep 29, 2008
Posts: 12617

I might have mapped this differently if I knew I'd be going from type -> ability... by making an inverse relationship you can get an ability type and its associated abilities w/o having to do a join like that. I'm not sure if that's what's causing the behavior you're seeing--I've never really done what you're doing the way you're doing it. I make those kinds of relationships explicit. Something to think about, anyway.
Leonard Bedner
Greenhorn

Joined: Oct 04, 2009
Posts: 5
David Newton wrote:I might have mapped this differently if I knew I'd be going from type -> ability... by making an inverse relationship you can get an ability type and its associated abilities w/o having to do a join like that. I'm not sure if that's what's causing the behavior you're seeing--I've never really done what you're doing the way you're doing it. I make those kinds of relationships explicit. Something to think about, anyway.


Wow... Okay, first off, I definitely put in the wrong test code. My apologies... Here is the stuff I was using when I initially posted this topic:



And here is the native SQL I was using in the command line, which returns exactly what I want (or so I thought...):



After sitting and thinking about what you said, I realized I was looking at this the wrong way. I really should be using this query:



I now realize that the first query did return what I wanted, plus more, since it is taking the results from BOTH tables. That explains why I was getting back BOTH Ability AND AbilityType objects in the result set. In the 2nd query, it's only taking the results from the Ability table, which is really what I need. So I guess I now have 2 followup questions:

1) How can I replicate the 2nd query using HQL? I tried using this:



And got this stack trace:



I then tried this method:



It worked, but when I got the list, it was a list of Object arrays (that included all the data in said arrays). Obviously, I want Ability objects.

2) All of the above said, do you still think I am going across this wrong, starting way back with how I created the AbilityType and Ability tables, and the relationship between them? Or was that just because of how I originally wrote the HQL statement (which obviously was showing something I didn't want).

Thanks for your time. It is much appreciated!
Leonard Bedner
Greenhorn

Joined: Oct 04, 2009
Posts: 5
Wouldn't you believe it. I was just informed that I needed to change this:



With this:



So, I just needed to use "a" instead of "a.*"... You live and you learn, I guess... Thanks, David. You definitely got me thinking, which made me realize my initial query wasn't any good to begin with.
David Newton
Author
Rancher

Joined: Sep 29, 2008
Posts: 12617

I like it when I can mumble some barely-cognizant noises and have it lead someone else to actually do the work ;)
Ben Narendren
Greenhorn

Joined: Oct 01, 2009
Posts: 19
I believe what you need might be something like this


from Ability as ability
inner join fetch ability.abilityType

This way you don't have to deal with object arrays.

If you don't want to fetch the abilityType , you need something like

from Ability as ability
inner join ability.abilityType

 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Hibernate - Query Issues With Many-To-One Relationships