Join unrelated entities in JPA

With SQL, you can join pretty much any two tables on almost any columns that have compatible type. This is not the possible in JPA as it relies heavily on relation mapping.

JPA relation between two entities must be declared, usually using @OnToMany or @ManyToOne or another annotation on some JPA entity's field. But sometimes you cannot simply introduce new relation into your existing domain model as it can also bring all sorts of troubles, like unnecessary fetches or lazy loads. Then ad hoc joining comes very handy.

JPA 2.1 introduced ON clause support, which is useful but not ground breaking. It only allows to use additional joining condition to one that implicitly exists because of entity relation mapping.

To cut story short, JPA specification still does not allow ad hoc joins on unrelated entities, but fortunately both two most used JPA implementation can do it now.

Of course, you still have to map columns (most likely numeric id columns) using @Id or @Column annotation, but you do not have to declare relation between entities to be able to join them.

EclipseLink since version 2.4 - User Guide and Ticket

Hibernate starting with version 5.1.0 released this February - Announcement and Ticket

Using this powerful tool, we can achieve unimaginable.

@Entity
@Table(name = "CAT")
class Cat {

    @Id
    @Column(name = "NAME")
    private String name;

    @Column(name = "KITTENS")
    private Integer kittens;
}

@Entity
@Table(name = "INSURANCE")
class Insurance {

    @Id
    @Column(name = "NUMBER")
    private Integer number;

    @Temporal(TemporalType.DATE)
    private Date startDate;
}

For example to join number of kittens your cat has with your insurance number!

String jpaql="SELECT Cat c JOIN Insurance i ON c.kittens = i.number";
entityManager.createQuery(jpaql);

If you try this with Hibernate version older than 5.1.0 you will get QuerySyntaxException

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Path expected for join!

Happy ad hoc joins!