• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

HQL join syntax

 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65216
95
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not sure what it is about HQL, but it seems to just make my mind go offline. (I suppose the same might be said for SQL).

I have a model with the following relevant fields:

Customer:
int ownerId; (many to one)
int locationId; (many to many)

Location:
int id;
String name;

I have a specific ownerId value. How do I obtain the List of all Locations that are related to all customers with that ownerId?

Thanks much!
 
Scott Selikoff
author
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, one formation of the SQL code would be:



I imagine the HQL syntax would be similar... Just a guess but perhaps:

 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Customer:
int ownerId; (many to one)
int locationId; (many to many)

Location:
int id;
String name;


Do you have the Customer object have a List<Location> or Customer have an Owner object, or is this legacy Jdbc code and therefore still have fks as properties?

If you have


And they are relational mapped then the query is really simple.
And you just want a List of locations.

HQL

"FROM Location loc JOIN loc.customers cust JOIN cust.owner owner WHERE owner.id = wnerId"

Typically you might just want a List of Customers, then loop through the List of Locations for the customers if the owner.id could be for many Customers

then HQL would be

"FROM Customers cust JOIN FETCH cust.locations WHERE cust.owner.id = wnerId"

(This is what Scott posted. Sorry, I didn't pay attention to his query because I wanted to think through the whole process, it was easier for me to do that.)

This one would return a list of Customers where all their locations Collection fully populated with data from the database.

I think though they won't work for you because you aren't using an OO model for your Domain objects.

Mark
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65216
95
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yeah, sorry. What I posted focuses on what's in the DB rather than the actual Model. Maybe that's my blind spot with HQL, I gravitate to thinking about the DB rather than the Model.

The actual fields in Customer are along the line of:



There are no relations in the Location model itself.

Many customers can reference the same Owner and Location.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic