• 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 ...
Marshals:
  • Campbell Ritchie
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Knute Snortum
  • Rob Spoor
Saloon Keepers:
  • Tim Moores
  • Ron McLeod
  • Piet Souris
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Ganesh Patekar

jpql, join on?

 
Ranch Hand
Posts: 416
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
a simplified example:

i have two tables, dog and cat, there are not foreign key reference between them. i use openJPA to implement jpa mapping, and two entities are generated: Catty and Doggy. and there are not reference between these two objects too.

but in my app, i want to implement join query. something like following in NATIVE SQL:

select * from dog d join cat c on d.age = c.age.

it works on my db2.

so i use jpql like following:
SELECT d FROM Doggy d JOIN Catty c ON d.age = c.age.

unfortunately, the jpql throw exception. similar with following:


org.apache.openjpa.persistence.ArgumentException: Encountered "JOIN Catty c" at character xx, but expected: [".", "FETCH", "INNER", "JOIN", "LEFT", <IDENTIFIER>].

question:
1 what is the problem of my jpql?
2 while i use join in jpql, is it mandatory to define a reference attribute to other object in first object?
3 does jpql support "ON" key word. i have checked jpql manual, it seem it is not preserved?

thanks
 
ranger
Posts: 17344
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try

SELECT d FROM Doggy d, Catty c ON d.age = c.age.

Mark
 
author
Posts: 304
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There is no "ON" in JP QL, but you can do an implicit join and use the WHERE clause:

SELECT d FROM Doggy d, Catty c WHERE d.age = c.age
 
zb cong
Ranch Hand
Posts: 416
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks for answer, i have tried every possibilities. all fail. i conclude the first problem is the usage of join key word:

org.apache.openjpa.persistence.ArgumentException: Encountered "JOIN Catty c at character xx, but expected: [".", "FETCH", "INNER", "JOIN", "LEFT", <IDENTIFIER>].


my current position is the there are not any reference between the two tables and there are also not any references between the two entities. i have checked some samples, all of them require the reference between the two entities if you want to join them.
so i conclude that it is MANDATORY that there MUST be reference between two entities if i want to join them, is my understanding correct?
 
Mike Keith
author
Posts: 304
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

zb cong wrote:thanks for answer, i have tried every possibilities. all fail. i conclude the first problem is the usage of join key word:


That is correct. You can't use JOIN unless you have a relationship attribute between the two entities.

so i conclude that it is MANDATORY that there MUST be reference between two entities if i want to join them, is my understanding correct?


That is incorrect. As I said above, you join without using the JOIN keyword. The solution I gave you above (SELECT d FROM Doggy d, Catty c WHERE d.age = c.age) should work. If you get an error then it is a bug in OpenJPA. Plug in another provider and try it.
 
Don't get me started about those stupid light bulbs.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!