JPQL table join query

Question

How do I get JPA & JPQL to pass a complete join query to the RDBMS? For example,

SELECT e
FROM Employee e
WHERE a.runkey = e.runkey
  AND e.middle = 'M'
  AND a.state = 'MA'

With the following Employee class

@Entity
public class Employee implements Serializable {

  blah ... blah

  @OneToOne
  @JoinColumn(
    name = "runkey",
    referencedColumnName = "runkey",
    insertable=false, updatable=false)
  private Address address;
}

and the JPQL,

SELECT e
FROM Employee e
INNER JOIN FETCH e.address AS a
WHERE a.state = :state
 AND e.middle = :middle

I am able to get Hibernate JPA to pull the data as expected.

However, eclipselink croaks that it cannot traverse associated field "address".

If so, how then should I design the Employee entity and how should I phrase the JPQL in order to get eclipselink to execute a table join with WHERE filters on both tables?

(Rant: Otherwise Eclipselink JPA is no better than JDO!!!)

~

Further edit: Does this post mean anything to my case ....
https://forums.oracle.com/forums/thread.jspa?threadID=1568659

What is n? What is the exception (copy your stacktrace)? JOIN FETCH should work. — Anthony Accioly
"JOIN FETCH should work" - True only for Hibernate JPA or unfiltered eclipselink. When I changed provider to eclipselink, it failed because eclipselink does not allow me to filter on associated field. — Blessed Geek
Did you double check your imports are JPA? e.g., make sure you imported javax.persistence.OneToOne and not org.hibernate.mapping.OneToOne? — Affe
Yes, only javax.persistence in the entity - because the entity dto is shared between GWT/RestyGWT and JPA (and JAXB and JAX-RS) and GWT would not allow me to have hibernate imports in the dto. FYI, GWT requires the inheritance chain of all classes to have Java source - and I am unable to provide that for Hibernate. Therefore, definitely no hibernate imports in the entity dto. — Blessed Geek

Anthony Accioly Anthony Accioly · Accepted Answer · 2012-07-24T22:59:35

The problem is that you are trying to alias a join fetch which is not allowed according to the JPQL specs. Hibernate allows this anyway. You can still obtain the desired behavior with EclipseLink using query hints.

Take a look at the following posts:

The following link can also be useful:

http://wiki.eclipse.org/EclipseLink/Examples/JPA/QueryOptimization

JPQL table join query

1 Answers