Win a copy of Zero to AI - A non-technical, hype-free guide to prospering in the AI era this week in the Artificial Intelligence and Machine Learning forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Paul Clapham
  • Bear Bibeault
  • Jeanne Boyarsky
Sheriffs:
  • Ron McLeod
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Jj Roberts
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • salvin francis
  • Scott Selikoff
  • fred rosenberger

HQL join syntax

 
Marshal
Posts: 67463
173
Mac Mac OS X IntelliJ IDE jQuery Java
  • 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!
 
author
Posts: 4226
33
jQuery Eclipse IDE Java
  • 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:

 
ranger
Posts: 17346
11
Mac IntelliJ IDE 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
Marshal
Posts: 67463
173
Mac Mac OS X IntelliJ IDE jQuery Java
  • 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.
 
If you were a tree, what sort of tree would you be? This tiny ad is a poop beast.
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
reply
    Bookmark Topic Watch Topic
  • New Topic