Joining Tables Without Relation Using JPA Criteria

Joining Tables Without Relation Using JPA Criteria

JPA typically uses relationship annotations like @OneToMany to join entities, simplifying queries. However, practical scenarios such as legacy database design or performance considerations often mean these relationships aren’t explicitly defined in the JPA model.

This tutorial details constructing JPA criteria queries to join tables lacking defined relationships, providing functionality when the ideal model of fully-mapped entities isn’t available.

Why This Matters

Defining relationships in JPA simplifies querying but can impact performance or be impractical with existing databases. Without defined relationships, direct joins using standard JPA features become impossible; attempting to use joined attributes throws org.hibernate.query.sqm.PathElementException. Alternatives like subqueries and cross joins provide ways to circumvent this limitation, impacting query complexity and potentially affecting scalability.

Key Insights

• Subqueries bridge identifiers: Using Subquery and IN predicates allows linking tables based on shared key values without a defined relationship.
• Cross Joins require conditions: Explicitly defining multiple Root instances leverages cross joins, but requires careful filtering to avoid Cartesian products and performance issues.
• Tuple selections offer flexibility: Tuple enables retrieving data from multiple entities in a single query when a cross join is used, facilitating more complex data retrieval.

Working Example

@Entity
@Table(name = "school")
public class School {
    @Id
    @Column(name = "id")
    private int id;
    @Column(name = "name")
    private String name;
    // constructors, setters and getters
}

@Entity
@Table(name = "student")
public class Student {
    @Id
    @Column(name = "id")
    private int id;
    @Column(name = "school_id")
    private int schoolId;
    @Column(name = "name")
    private String name;
    // constructors, setters and getters
}

//Criteria Query with Subquery
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<School> query = cb.createQuery(School.class);
Root<School> schoolRoot = query.from(School.class);
Subquery<Long> subquery = query.subquery(Long.class);
Root<Student> studentRoot = subquery.from(Student.class);
subquery.select(studentRoot.get("schoolId"))
        .where(cb.equal(studentRoot.get("name"), "Benjamin Lee"));
query.select(schoolRoot)
        .where(schoolRoot.get("id").in(subquery));
List<School> schools = em.createQuery(query).getResultList();

Practical Applications

• Legacy Systems: Integrating with databases where relationships aren’t explicitly defined, enabling querying without major schema changes.
• Pitfall: Cross Join Performance: Using cross joins without careful predicates can lead to exponential increases in result set size and drastically reduce query performance.