File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Object Relational Mapping and the fly likes Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark ""JOIN" not working in JPA - Named Query" Watch ""JOIN" not working in JPA - Named Query" New topic

"JOIN" not working in JPA - Named Query

Anantha Raman

Joined: Mar 27, 2010
Posts: 2
Could any one kindly let me know how to use JOIN in "JPA - Named Query". The DB am using is Informix.

Below is the query ,

"select la.internet_ord_nbr,c.cust_first_name,c.cust_last_name, c.primary_phone_nbr,ac.cust_first_name,ac.cust_last_name from account la inner join cust:cust c on la.customer_nbr = c.customer_nbr left outer join cust:cust ac on la.alt_customer_nbr = ac.customer_nbr where upper(c.cust_first_name) like upper(?1)"

Following are my doubts,
1. Do we need to use "column_name" (or) the "string declared in place of col name" in the query
For example -> @Id
@Column(name = "customer_nbr")
private Long customerNbr;
Which one to use in t Query -> customer_nbr (or) customerNbr.....

2. To access the table in INFORMIX db, we need to refer the table as "customer:customer". Will this work in JPA ?
Krum Bakalsky
Ranch Hand

Joined: Mar 14, 2010
Posts: 46
The @Column annotation belongs to the so called "physical metadata" of the entity. It is entirely related to the relational database representation of your entity.
It has nothing to do with your Java object domain model.

And JPQL is intended to abstract you 100% from the database domain artifacts. In JPQL you use Java object domain model concepts only!
So, if you are talking about classical JPQL query, you should use entity names and persistent attribute names only (which names are derived from the instance variable names in case of field access types, and from the getter/setter method names in case of property access type).

But if you are using native queries, aka native SQL, then this is another story....

SCJP 6 (86% - the hard way), SCBCD 5 (81% - the hard way)
Anantha Raman

Joined: Mar 27, 2010
Posts: 2
Thanks for your reply.
Am talking about JPQL only....So, i need to use the Local instances declared for the Column Names in the query, right ?

But any inputs on JOIN...i tried the above query, but getting an error that,
--> Encountered JOIN @ line no... but expected {".","WHEN","HAVING" etc....}
I agree. Here's the link:
subject: "JOIN" not working in JPA - Named Query
It's not a secret anymore!