Question about JPQL, @NamedQuery

Question

If I have something like this

@Entity
public class Facility {
    ...
    @ManyToOne
    @JoinColumn(name="CUSTOMER_FK")
    private Customer customer;
    ...
}

does my @NameQuery like this

@NamedQuery(name="Facility.findByCustomerId", query="select c from Facility c where c.customer=:customer_fk")

or like this

@NamedQuery(name="Facility.findByCustomerId", query="select c from Facility c where c.CUSTOMER_FK=:customer_fk")

Answer 1

In JPQL you use the names of the properties, not the database columns. So - the first option.

But when you pass parameters, you pass the whole objects, not their IDs. In your case, you either pass a Customer instance, or make the where clause look for c.customer.id (and you'd better name the alias f, not c)

Answer 2

You need to stop thinking foreign keys and to start thinking object when using JPQL. Let's take a closer look:

select c from Facility c where c.CUSTOMER_FK=:customer_fk

Does your Facility have a CUSTOMER_FK property? No, so the above query is incorrect. Next one:

select c from Facility c where c.customer=:customer_fk

Syntacticly, this one is correct. But you're still not thinking object. Here, the query expect you to pass an instance of customer, not a FK. I'd thus rewrite it like this (it is the same query but it communicates IMO the intention much better and I'd actually avoid any foo_fk convention, you don't really manipulate FK with JPA):

select c from Facility c where c.customer = :customer

And if you really want to find by id, you should navigate through the association and write:

select c from Facility c where c.customer.id = :id