Joining Tables Without Relation Using JPA Criteria

1. Overview

In JPA, we define an association between two entities using relationship annotations such as @OneToMany. These annotations allow us to join them using JPQL or the criteria builder. However, this relationship is not always clearly defined due to considerations such as legacy database design or performance requirements.

This tutorial will explore how to construct a JPA criteria query to join tables without explicitly defining the JPA relationship.

2. Data Preparation

To illustrate the idea, let’s prepare two JPA entity classes, School and Student. These entity classes represent two database tables with a one-to-many relationship, where a school could have many students:

@Entity
@Table(name = "school")
public class School {
    @Id
    @Column(name = "id")
    private int id;

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

    @OneToMany
    @JoinColumn(name = "school_id", referencedColumnName = "id")
    private List students;

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

In the subsequent sections, we configure EntityManagerFactory to enable JPA properties hibernate.show_sql and hibernate.format_sql.

These settings enable Hibernate to print out the SQL generated in the console log. This allows us to understand how our criteria queries are translated into SQL at runtime:

private EntityManagerFactory createEntityManagerFactory() {
    return new PersistenceConfiguration("SchoolData")
      .jtaDataSource("java:comp/env/jdbc/SchoolData")
      .managedClass(School.class)
      .managedClass(Student.class)
      .property("hibernate.show_sql", true)
      .property("hibernate.format_sql", true)
      .createEntityManagerFactory();
}

3. Criteria Query with Join

When the one-to-many relationship is defined on the School entity class, we could use the following criteria query to get the School that has the Student with the name Benjamin Lee:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<School> query = cb.createQuery(School.class);

Root<School> schoolRoot = query.from(School.class);
Join<School, Student> studentJoin = schoolRoot.join("students");
cq.select(schoolRoot)
  .where(cb.equal(studentJoin.get("name"), "Benjamin Lee"));

List<School> schools = em.createQuery(query).getResultList();

This basically defines the School entity as the root and joins to the Student entity via the Join class based on our relationship definition. We could find the SQL generated by this criteria query when we run it in the console:

select
    s1_0.id,
    s1_0.name 
from
    school s1_0 
join
    student s2_0 
        on s1_0.id=s2_0.school_id 
where
    s2_0.name=?

However, the Join works only when the relationship is defined. Let’s remove the relationship definition, including the annotation and the instance variable students from the School class, and see how it goes:

@Entity
@Table(name = "school")
public class School {
    @Id
    @Column(name = "id")
    private int id;

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

    // constructors, setters and getters
}

This time, we could see an exception thrown when we run it:

org.hibernate.query.sqm.PathElementException: Could not resolve attribute 'students' of 'com.baeldung.criteria.School'

4. Criteria Query with Subquery

There are different approaches to getting rid of the relationship definition. One of them is to define a subquery via the SubQuery class to select the School based on the schoolId of the target Student:

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();

In the subquery, we select the schoolId from the Student entity that has the name Benjamin Lee, and we select the School entity based on this schoolId.

The in(…) predicate acts as a bridge between these two entities and links them through their identifiers without any JPA relationship.

We could see JPA generating the following SQL for the corresponding criteria query:

select
    s1_0.id,
    s1_0.name 
from
    school s1_0 
where
    s1_0.id in ((select
        s2_0.school_id 
    from
        student s2_0 
    where
        s2_0.name=?))

5. Criteria Query with Cross Join

We may ask ourselves whether we could use a more natural way to link up these two entities. Indeed, we could define a cross join in a criteria query to achieve the same result:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<School> query = cb.createQuery(School.class);

Root<School> schoolRoot = query.from(School.class);
Root<Student> studentRoot = query.from(Student.class);
Predicate joinCondition = cb.equal(schoolRoot.get("id"), studentRoot.get("schoolId"));
Predicate studentName = cb.equal(studentRoot.get("name"), "Benjamin Lee");
query.select(schoolRoot)
  .where(cb.and(joinCondition, studentName));

List<School> schools = em.createQuery(query).getResultList();

The key component here is to define two Root instances: the School root and the Student Root. By explicitly defining multiple roots, JPA will create a cross join between the corresponding database tables.

Upon execution, we could see the criteria query translates to the following SQL from the console log:

select
    s1_0.id,
    s1_0.name 
from
    school s1_0,
    student s2_0 
where
    s1_0.id=s2_0.school_id 
    and s2_0.name=?

Similar to writing a SQL query, we should explicitly put conditions to limit the number of records returned. In our example, we restrict the result set using the student’s name. Since a cross join produces a Cartesian product, it returns a massive result set if both tables contain many data rows.

6. Criteria Query with Tuple Select

In the previous cross-join criteria query, we only returned the School entity. However, this may be insufficient, as we may want to retrieve the data from the Student entity as well.

To return the Student entity in the same query, we could call the createTupleQuery() method on CriteriaBuilder. This allows us to select Tuple instead of School, which could hold multiple entities:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> query = cb.createTupleQuery();

Root<School> schoolRoot = query.from(School.class);
Root<Student> studentRoot = query.from(Student.class);
Predicate joinCondition = cb.equal(schoolRoot.get("id"), studentRoot.get("schoolId"));
Predicate studentName = cb.equal(studentRoot.get("name"), "Benjamin Lee");
query.select(cb.tuple(schoolRoot,studentRoot))
  .where(cb.and(joinCondition, studentName));

List<Tuple> tuples = em.createQuery(query).getResultList();

The tuple query enables us to select multiple entities from a CriteriaQuery. In this example, we get both the School and the Student entities.

By analyzing the generated SQL in the console log, we see that JPA includes columns from the student table in the select clause, showing that it also retrieves data from the Student entity:

select
    s1_0.id,
    s1_0.name,
    s2_0.id,
    s2_0.name,
    s2_0.school_id 
from
    school s1_0,
    student s2_0 
where
    s1_0.id=s2_0.school_id 
    and s2_0.name=?

Each Tuple returned a single result row and contains the selected entities in the same order we specified in the criteria query. To get the entities from the first result row, we could simply:

Tuple firstTuplpe = tuples.get(0);
School school = firstTuplpe.get(0, School.class);
Student student = firstTuplpe.get(0, Student.class);

7. Conclusion

In this article, we explored several ways using subqueries, cross joins, and tuple selections to construct a JPA criteria query that joins database tables without explicitly defining the JPA relationship. These approaches provide alternatives for working with legacy schemas where entity relationships are not defined.

As usual, our complete code examples are available over on GitHub.