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 HQL join syntax Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "HQL join syntax" Watch "HQL join syntax" New topic
Author

HQL join syntax

Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60045
    
  65

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!

[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3697
    
    5

Well, one formation of the SQL code would be:



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



My Blog: Down Home Country Coding with Scott Selikoff
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6


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


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60045
    
  65

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: HQL join syntax
 
Similar Threads
A question about HQL cascade select
Grails criteria with operations between fields
Find Top X Algorithm
new to hibernate, spring.. criteria queries/joins
UrlyBird 1.3.2 locking with no cookie