Win a copy of JDBC Workbook this week in the JDBC and Relational Databases forum
or A Day in Code in the A Day in Code forum!
  • 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
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

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: 17346
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.
 
I'd appreciate it if you pronounced my name correctly. Pinhead, with a silent "H". Petite ad:
Devious Experiments for a Truly Passive Greenhouse!
https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
    Bookmark Topic Watch Topic
  • New Topic