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
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