• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

"JOIN" not working in JPA - Named Query

 
Anantha Raman
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 46
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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....
 
Anantha Raman
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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....}
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic