Custom join condition in JPA

Question

Is it possible to specify custom join criteria in JPA (either jpql or via the criteria api?

select .. from .. join ... on (... <custom criteria> )   

The reason i need this is because i would like to join a set of tables on a date range (one is a historical table with fact stacking)

** update **

It is possible to specify additional join conditions/ criteria in JPA (2.1 >). See accepted answer (zxcf).

Hibernate note: Although it is possible to specify additional join criteria either using JOIN .. ON or programmatic-ally using javax.persistence.criteria.JOIN, you cannot using criteria which references a different table, only criteria that reference the same table (and not higher in the hierarchy) are supported please see : https://hibernate.atlassian.net/browse/HHH-7321

Answer 1

You can't use JOIN keyword unless you have explicit relationship between two entities - if you have relationship, you can use:

SELECT e FROM Employee e JOIN e.projects p

You can additionaly narrow your JOIN by using ON keyword (please note that this applies to JPA 2.1):

SELECT e FROM Employee e JOIN e.projects p ON p.name LIKE 'As%'

If you want to define JOIN which connects two entities which does not have defined relationship you should use additional conditions in WHERE clause:

SELECT e FROM Employee e, Projects p WHERE e.projectId = p.id

update

If you use EclipseLink (or it's your JPA provider) in version 2.4 or higher then you can use custom conditions in JOIN .. ON clause as EclipseLink also supports usage of the ON clause between two root level objects.

SELECT e FROM Employee e LEFT JOIN MailingAddress a ON e.address = a.address

Here's reference

Answer 2

I think Dobrowolski's answer needs to be updated for future reference, as that is a feature no more exclusively provided by EclipseLink (at least for what concerns unrelated entities). If you are using Hibernate (5.1 or higher), it's now possible to declare explicit joins on unrelated entities (i.e without an explicit mapping) using JPQL/HQL, as you can see from this example:

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

List<Object[]> results = em.createQuery("SELECT p.firstName, p.lastName, n.phoneNumber FROM Person p JOIN PhoneBookEntry n ON p.firstName = n.firstName AND p.lastName = n.lastName").getResultList();

for (Object[] result : results) {
    log.info(result[0] + " " + result[1] + " - " + result[2]);
}

em.getTransaction().commit();
em.close();