HQL join syntax

[Bear Bibeault]

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]

Well, one formation of the SQL code would be:

SELECT l.* FROM Customer c INNER JOIN Location l ON c.locationId=l.id WHERE c.ownerId=?

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

from Customer as customer inner join fetch customer.location with customer.ownerId=?

[Mark Spritzler]

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

public class Owner
   private Long id;

public class Location
   private Long id;
   private String name;
   private List<Customer> customers;

public class Customer
   private List<Location> locations;
   private Owner owner;

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]

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:

  @Required
  @ManyToOne(cascade = {CascadeType.REFRESH,CascadeType.MERGE,CascadeType.PERSIST})
  @JoinColumn(name = "ownerId")
  public Owner owner;

  @ManyToOne(cascade = {CascadeType.REFRESH,CascadeType.MERGE,CascadeType.PERSIST})
  @JoinColumn(name = "locationId")
  public Location Location;

There are no relations in the Location model itself.

Many customers can reference the same Owner and Location.