1

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

Blessed Geek
  • 21,058
  • 23
  • 106
  • 176
  • What is `n`? What is the exception (copy your stacktrace)? JOIN FETCH should work. – Anthony Accioly Jul 24 '12 at 22:35
  • Not n but e - corrected. – Blessed Geek Jul 24 '12 at 22:42
  • "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 Jul 24 '12 at 22:46
  • Did you double check your imports are JPA? e.g., make sure you imported `javax.persistence.OneToOne` and not `org.hibernate.mapping.OneToOne`? – Affe Jul 24 '12 at 22:48
  • 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 Jul 24 '12 at 23:06

1 Answers1

1

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

Community
  • 1
  • 1
Anthony Accioly
  • 21,918
  • 9
  • 70
  • 118
  • [This article](http://java-persistence-performance.blogspot.com.br/2010/08/batch-fetching-optimizing-object-graph.html) is also a really good source about join-fetch and batch hints. – Anthony Accioly Jul 24 '12 at 23:08
  • I don't understand how a hint works. Shouldn't the hint says "e.middle = 'M'" rather than just "e.middle". Otherwise, how do I "hint" eclipselink to filter on middle='M' only? – Blessed Geek Jul 24 '12 at 23:22
  • 1
    You will still use your filters in the query: `e.middle = :middle` and `e.address.state = :state`. Use a hint to join fetch `e.address`. [This article](http://java-persistence-performance.blogspot.com.br/2012/04/objects-vs-data-and-filtering-join.html) also advice to bypass cache. – Anthony Accioly Jul 25 '12 at 00:19
  • Thank you for saving my life/project - kowtow, kowtow. – Blessed Geek Jul 25 '12 at 01:17