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>].
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?
I found someone that encountered the same exception when dealing with jpql and join. This is the answer he got, hope it will be of help:
The exception stack seems to indicate it is trying to parse your query string as if it were JPQL, but it is a native SQL query. Try using em.createNativeQuery or @NamedNativeQuery if you intend to use direct SQL. If you want to use JPQL, you will need to use the Java object attribute names the fields are mapped to, not the database field names. You also have to decide what you want back - raw data or Entity objects. Assuming the application is fine getting back the collection of A objects, I would use:
"select a from A a where a.customer_email =:param"
this would then return A entities, where the a.bcollection can be traversed to get required data from B entities and so on. This allows you to use the mapped entities directly rather than query later on to get entities to manipulate the data, and allows you to take advantage of caching. If you just want the raw data try JPQL:
"select b.cd, b.ind, c.bng_id, c.tod_cd, d.seg_cd, d.seg from A a join a.bcollection b join b.ccollection c join c.dcollection d where a.customer_email = :param"