Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

jpql, join on?

 
zb cong
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
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac 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
 
Mike Keith
author
Ranch Hand
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
Ranch Hand
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic