JPA Criteria API: Multiple condition on LEFT JOIN

Question

I have a join reference like following for which the first join expression is constructed by the JPA API automatically.

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> c = cb.createTupleQuery();
Root<Demand> demands = c.from(Demand.class);
Join<Demand, Metadata> joinMetadata = demands.join("metadatas", JoinType.LEFT);

but, I would like to add an aditional condition to my joinMetadata like Metadata.type="AFFECTATION_KEY" but I don't know how.

Many thanks for any help

Answer 1

JPA

JPA 2.0 always joins by the mapping join columns only, as it does not support an ON clause. JPA 2.1 on the other hand does support this:

... 
Join<Demand, Metadata> joinMetadata = demands.join("metadatas", JoinType.LEFT);
joinMetadata.on(cb.equal(joinMetadata.get("type"), "AFFECTATION_KEY"));

---

EclipseLink

EclipseLink 2.4 has support for an ON clause,

See, http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#ON

It is also possible through the Criteria API, using the EclipseLink native Expression API that provides on clause support,

See, http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Criteria#JpaCriteriaBuilder_and_EclipseLink_Extensions

Answer 2

And Hibernate's Criteria API version 3.6 supports it too:

    Criteria c = session.createCriteria(Tuple.class);

    c.createAlias("demands", "d")
    .createAlias("d.metadata", "m", 
        Criteria.LEFT_JOIN, Restrictions.eq("m.type", "AFFECTATION_KEY"));

Or you may want Restrictions.eqProperty() for that last bit. Note the under-appreciated version of createAlias() with the fourth parameter. Here is a quote from the documentation

Parameters:

• associationPath - A dot-seperated property path
• alias - The alias to assign to the joined association (for later reference).
• joinType - The type of join to use.
• withClause - The criteria to be added to the join condition (ON clause)

Hibernate API 3.6