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.refidand in Java:
QRefpointResult refresult = QRefpointResult.refpointresult; JPQLQuery query = new JPQLQuery(entityManager); long count = query(refresult) .where(refresult.refid in (1,2)).count();