Search code examples
joinjpqlopenjpa

OpenJPA How to use WHERE condition on collection with join fetch?

I use OpenJPA 2.1 on WebSphere Application Server 8. I have a bidirectional oneToMay relation like this:

@Entity(name = "table1")
public class Table1  {
    @Id
    @Column(columnDefinition = "DATE")
    private Date date;
    @OneToMany(mappedBy = "date", targetEntity = Table2.class, fetch = FetchType.LAZY, cascade = CascadeType.PERSIST)
    private List<Table2> table2List;
}

@Entity(name = "table2")
public class Tabl2{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "fk_date", nullable = false)
    private Table1 table1;

    @Column(name = "value", nullable = false, columnDefinition = "INT")
    private int value;

}

Now I want to select the whole table1 entity with a condition on table2 like this:

TypedQuery<WeekBean> q = em.createQuery(
    "SELECT t1 from table1 t1 JOIN FETCH t1.table2List t2 WHERE t2.value = :value",
    t1.class);

But this does not work, beacause I get an ArgumentException:

Exception in thread "main" <openjpa-2.1.1-SNAPSHOT-r422266:1141200 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: " Encountered "f" at character 50, but expected [",", ".", "GROUP", "HAVING", "INNER", "JOIN", "LEFT", "ORDER", "WHERE", <EOF>]." 
    at org.apache.openjpa.kernel.jpql.JPQLParser.parse(JPQLParser.java:51)
    at org.apache.openjpa.kernel.ExpressionStoreQuery.newCompilation(ExpressionStoreQuery.java:154)
    at org.apache.openjpa.datacache.QueryCacheStoreQuery.newCompilation(QueryCacheStoreQuery.java:262)
    at org.apache.openjpa.kernel.QueryImpl.newCompilation(QueryImpl.java:672)
    at org.apache.openjpa.kernel.QueryImpl.compilationFromCache(QueryImpl.java:654)
    at org.apache.openjpa.kernel.QueryImpl.compileForCompilation(QueryImpl.java:620)
    at org.apache.openjpa.kernel.QueryImpl.compileForExecutor(QueryImpl.java:682)
    at org.apache.openjpa.kernel.QueryImpl.compile(QueryImpl.java:589)
    at org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:996)
    at com.ibm.ws.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:107)
    at com.ibm.ws.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:86)
    at com.ibm.ws.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:34)
    at org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:974)

How can I use a JOIN FETCH with a condition on the collection?

Best Regards Veote

Solution

  • According JPA 2.0 specification such a construct is not supported, and looks like OpenJPA also does not provide functionality as vendor extension:

    The association referenced by the right side of the FETCH JOIN clause must be an association that belongs to an entity that is returned as a result of the query. It is not permitted to specify an identification variable for the entities referenced by the right side of the FETCH JOIN clause, and hence references to the implicitly fetched entities cannot appear elsewhere in the query.

    In your case this means that you cannot define t2 and as a consequence you cannot use it in the WHERE clause.