Sunday, October 23, 2011

JPA join queries need foreign key relationship

I needed a SQL query which is a projection of fields from five tables and looks like this in native SQL: (I reduced the number of tables in the code below to 2 for clearity)
SELECT refpoint.refid,refpoint.x,result.y 
FROM refpoint
LEFT OUTER JOIN result on result.refid = refpoint.refid
WHERE refpoint.refid IN (?,?)
Using QueryDsl and JPA I wrote this equivalent query:
QRefpoint refpoint = QRefpoint.refpoint;
QResult result = QResult.result;
JPQLQuery query = new JPQLQuery(entityManager);
long count = query(refpoint)
.leftJoin(result)
.with(result.refid.eq(refpoint.refid)
.where(refpoint.refid in (1,2)).count();
Running this code gave me:
16:07:20.619 [main] DEBUG org.hibernate.hql.ast.ErrorCounter - Path expected for join! antlr.SemanticException: Path expected for join! at org.hibernate.hql.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:360) ~[hibernate-core-3.5.6-Final.jar:3.5.6-Final]
After much experimentation I found out that JPA (and Hibernate) do not support Joining without a mapped relationship. In this case the tables Refpoint and Result have a weak relationship based on refid and although a join in native sql is possible you cannot do this from JPA and/or Hibernate.
The solution I came up with was creating a Database View and corresponding JPA entity like this:
CREATE VIEW RefpointResult as 
SELECT refpoint.refid,refpoint.x,result.y 
FROM refpoint
LEFT OUTER JOIN result on result.refid = refpoint.refid
and in Java:
QRefpointResult refresult = QRefpointResult.refpointresult;
JPQLQuery query = new JPQLQuery(entityManager);
long count = query(refresult)
.where(refresult.refid in (1,2)).count();

No comments: