JPA NamedQuery to obtain a referenced object without executing join

Question

Say I have three entities: Customer, Product and Order. Each Order has a ManyToOne association to a Customer and a ManyToOne association to a Product. Associations are unidirectional from Order to Customer and to Product respectively. So the database looks like:

Customer
+----+-----+
| ID | ... |
+----+-----+

Product
+----+-----+
| ID | ... |
+----+-----+

Order
+----+-------------+------------+-----+
| ID | CUSTOMER_ID | PRODUCT_ID | ... |
+----+-------------+------------+-----+

Now I want to write a NamedQuery to retrieve all the Customer who ordered a particular Product. Can I refer the Customer object in the query without executing a join between Customer and Order? Can I write something like:

@NamedQuery(
name =  "GetCustomersByProduct",
query = "SELECT o.customer"
        "FROM Order o "+
        "WHERE  o.productId = :productId"
)

Or is it necessary to execute a join? What's the best way to do this?

score 0 · Answer 1 · answered Jul 10 '13 at 08:22

0

Good practice to avoid JOIN is usage of exists (see oracle doc: http://docs.oracle.com/html/E24396_01/ejb3_langref.html#ejb3_langref_exists ), most databases optimize EXISTS in compare with JOIN.

answered Jul 10 '13 at 08:22

Dewfy

23,277
13
73
121

score 0 · Accepted Answer · answered Jul 10 '13 at 08:27

0

Your query is right and only thing you need to get distinct customer since same product customer may have ordered more than once

query = "SELECT distinct(o.customer)"
    "FROM Order o "+
    "WHERE  o.productId = :productId"

Get Customer :

    Query qry = em.createNamedQuery("GetCustomersByProduct");
    List<Customer> resultList = qry.getResultList();

answered Jul 10 '13 at 08:27

vels4j

11,208
5
38
63

Not only I can use `SELECT o.customer`, but I can also use `WHERE o.product = :product`. Indeed, using `o.productId` gave me an error like `unrecognized field`. I have to say that my entities are a little bit more complex compared with the example. More precisely I use composite ids embedded in the entities (and `Serializable` entities). I don't know if this have some relation with the found solution. – user1781028 Jul 10 '13 at 10:08

JPA NamedQuery to obtain a referenced object without executing join

2 Answers2