• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

"JOIN" not working in JPA - Named Query

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 ?
Ranch Hand
Posts: 47
  • 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
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....}
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
    Bookmark Topic Watch Topic
  • New Topic