In real-world enterprise applications, it is common to encounter database tables that do not have explicit JPA relationships (such as @OneToMany or @ManyToOne) defined between them. Despite this, we often need to query and combine data from such tables. Let us delve into understanding JPA joining tables without relation, a common challenge when working with databases lacking explicit entity mappings.
1. Introduction
In modern enterprise applications, especially those built on top of legacy databases or microservice-oriented schemas, it is very common to encounter tables that are logically related but not explicitly mapped using JPA annotations such as @OneToMany, @ManyToOne, or @JoinColumn. In such scenarios, developers still need a reliable and maintainable way to query and combine data across these tables.
The JPA Criteria API is designed precisely for such use cases. Unlike JPQL or native SQL, the Criteria API allows developers to build queries in a type-safe, programmatic, and dynamic manner. This becomes particularly valuable when queries need to be composed conditionally at runtime or when refactoring safety is a priority.
When entity relationships are not defined, the Criteria API does not prevent joining tables. Instead, it provides multiple alternative techniques that mirror SQL behavior while still operating within the JPA abstraction layer.
JPA Criteria API provides multiple techniques to achieve this in a type-safe and dynamic manner:
- Using joins with manual predicates: Multiple entity roots can be declared in the query, and a join condition can be explicitly defined using
CriteriaBuilder.equal(). This approach closely resembles a traditional SQLINNER JOINexpressed via aWHEREclause. - Using subqueries: Subqueries allow filtering one entity based on the results of another entity’s query. This is particularly useful when the relationship is indirect or when advanced filtering logic is required without inflating the main query.
- Using cross joins: Declaring multiple roots in a Criteria query results in a Cartesian product, equivalent to a SQL
CROSS JOIN. When combined with restrictive predicates, this technique effectively behaves like a controlled join between unrelated tables. - Using tuple-based projections: Instead of returning managed entities or raw
Object[]results, the Criteria API supportsTuple-based projections. Tuples provide named, type-safe access to selected columns, making complex multi-table queries easier to read and maintain.
2. Code Example
Before diving into the example, make sure your project includes the necessary dependencies for JPA and your chosen implementation, such as Hibernate. For Maven, include the following dependencies:
<dependency>
<groupId>jakarta.persistence</groupId>
<artifactId>jakarta.persistence-api</artifactId>
<version>stable__jar__version</version>
</dependency>
<dependency>
<groupId>org.hibernate.orm</groupId>
<artifactId>hibernate-core</artifactId>
<version>stable__jar__version</version>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>stable__jar__version</version>
<scope>runtime</scope>
</dependency>
2.1 Entity Classes
2.1.1 Employee Table
The following JPA entity represents the employees table, which stores basic employee information along with a department code used for logical association.
// Employee.java
import jakarta.persistence.*;
@Entity
@Table(name = "employees")
public class Employee {
@Id
private Long id;
private String name;
@Column(name = "dept_code")
private String departmentCode;
// getters & setters
}
In this entity, @Entity marks the class as a JPA-managed entity, while @Table(name = "employees") maps it to the corresponding database table. The id field serves as the primary key, and the name field stores the employee’s name. The departmentCode field is explicitly mapped to the dept_code column and acts as a logical link to the department table, even though no formal JPA relationship is defined, allowing it to be used later for manual joins in Criteria API queries.
2.1.2 Department Table
The following JPA entity represents the departments table, which contains master data for organizational departments and their identifying codes.
// Department.java
import jakarta.persistence.*;
@Entity
@Table(name = "departments")
public class Department {
@Id
private Long id;
@Column(name = "dept_code")
private String departmentCode;
@Column(name = "dept_name")
private String departmentName;
// getters & setters
}
Here, @Entity and @Table(name = "departments") map the class to the departments database table. The id field acts as the primary key, while departmentCode is mapped to the dept_code column and serves as the logical identifier used to correlate records with the employee table. The departmentName field, mapped to dept_name, stores the human-readable department name, which is later used in filtering and projection within Criteria API queries, despite the absence of any explicit JPA relationship.
2.2 Service Class
The following service class demonstrates four independent ways to join unrelated tables using the JPA Criteria API. Each method focuses on one specific approach, making the behavior, intent, and generated SQL easy to understand and review.
// EmployeeDepartmentReportService.java
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.persistence.Tuple;
import jakarta.persistence.criteria.*;
import java.util.List;
public class EmployeeDepartmentReportService {
@PersistenceContext
EntityManager entityManager;
/*
* 1. Criteria Query using MANUAL JOIN (multiple roots + predicate)
* Equivalent to: INNER JOIN with ON condition
*/
public List<Tuple> manualJoinExample() {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> cq = cb.createTupleQuery();
Root<Employee> employee = cq.from(Employee.class);
Root<Department> department = cq.from(Department.class);
Predicate joinCondition = cb.equal(
employee.get("departmentCode"),
department.get("departmentCode")
);
cq.multiselect(
employee.get("name").alias("employeeName"),
department.get("departmentName").alias("departmentName")
).where(joinCondition);
return entityManager.createQuery(cq).getResultList();
}
/*
* 2. Criteria Query using SUBQUERY
* Filters employees based on department attributes
*/
public List<Employee> subqueryExample() {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Employee> cq = cb.createQuery(Employee.class);
Root<Employee> employee = cq.from(Employee.class);
Subquery<String> deptSubquery = cq.subquery(String.class);
Root<Department> department = deptSubquery.from(Department.class);
deptSubquery.select(department.get("departmentCode"))
.where(cb.equal(
department.get("departmentName"),
"Engineering"
));
cq.select(employee)
.where(employee.get("departmentCode").in(deptSubquery));
return entityManager.createQuery(cq).getResultList();
}
/*
* 3. Criteria Query using CROSS JOIN semantics
* Multiple roots without immediate join condition
*/
public List<Tuple> crossJoinExample() {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> cq = cb.createTupleQuery();
Root<Employee> employee = cq.from(Employee.class);
Root<Department> department = cq.from(Department.class);
cq.multiselect(
employee.get("name"),
department.get("departmentName")
).where(
cb.and(
cb.equal(employee.get("departmentCode"),
department.get("departmentCode")),
cb.equal(department.get("departmentName"),
"Engineering")
)
);
return entityManager.createQuery(cq).getResultList();
}
/*
* 4. Tuple-based PROJECTION example
* Clean, type-safe column selection across entities
*/
public List<Tuple> tupleProjectionExample() {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> cq = cb.createTupleQuery();
Root<Employee> employee = cq.from(Employee.class);
Root<Department> department = cq.from(Department.class);
cq.multiselect(
employee.get("id").alias("empId"),
employee.get("name").alias("empName"),
department.get("departmentName").alias("deptName")
).where(
cb.equal(employee.get("departmentCode"),
department.get("departmentCode"))
);
return entityManager.createQuery(cq).getResultList();
}
}
The EmployeeDepartmentReportService class demonstrates four distinct JPA Criteria API techniques for querying and combining data from unrelated entities using an injected EntityManager. The manualJoinExample() method shows a manual join approach by declaring multiple roots for Employee and Department and explicitly linking them through a predicate on the shared departmentCode, which mirrors a traditional SQL INNER JOIN. The subqueryExample() method illustrates the use of a subquery to filter employees by selecting department codes that match a specific department name, enabling indirect association without joining tables directly in the main query. The crossJoinExample() method relies on cross join semantics by defining multiple roots and then restricting the Cartesian product using predicates, effectively simulating a controlled join while also applying additional filters. Finally, the tupleProjectionExample() highlights tuple-based projections, selecting only specific columns from both entities with aliases, resulting in a clean, type-safe, and readable result structure without returning full entity objects. Together, these methods clearly showcase how the Criteria API can flexibly handle joins, filtering, and projections even when no explicit JPA relationships are defined.
2.3 Main Class
The following main class bootstraps the JPA runtime, initializes an in-memory database, persists sample data, and invokes the Criteria API–based service to demonstrate the end-to-end execution flow.
// Application.java
import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import jakarta.persistence.Persistence;
public class Application {
public static void main(String[] args) {
EntityManagerFactory emf =
Persistence.createEntityManagerFactory("demoPU");
EntityManager em = emf.createEntityManager();
try {
em.getTransaction().begin();
/* Insert Departments */
Department d1 = new Department();
d1.setId(1 L);
d1.setDepartmentCode("ENG");
d1.setDepartmentName("Engineering");
Department d2 = new Department();
d2.setId(2 L);
d2.setDepartmentCode("FIN");
d2.setDepartmentName("Finance");
em.persist(d1);
em.persist(d2);
/* Insert Employees */
Employee e1 = new Employee();
e1.setId(1 L);
e1.setName("John Doe");
e1.setDepartmentCode("ENG");
Employee e2 = new Employee();
e2.setId(2 L);
e2.setName("Jane Roy");
e2.setDepartmentCode("FIN");
Employee e3 = new Employee();
e3.setId(3 L);
e3.setName("Alice Kim");
e3.setDepartmentCode("ENG");
em.persist(e1);
em.persist(e2);
em.persist(e3);
em.getTransaction().commit();
/* Execute Criteria Query */
EmployeeDepartmentReportService service =
new EmployeeDepartmentReportService();
// manual injection for demo
service.entityManager = em;
service.manualJoinExample();
service.subqueryExample();
service.crossJoinExample();
service.tupleProjectionExample();
} finally {
em.close();
emf.close();
}
}
}
The Application class acts as the bootstrap and execution entry point for the JPA example by initializing the persistence context, preparing sample data, and invoking the Criteria API queries. It begins by creating an EntityManagerFactory using the demoPU persistence unit and obtaining an EntityManager to interact with the database. Within a transactional block, it persists sample Department and Employee entities to establish a realistic dataset with logically related records but no explicit JPA relationships. After committing the transaction, the class instantiates the EmployeeDepartmentReportService and manually injects the EntityManager for demonstration purposes, allowing the service methods to reuse the same persistence context. It then sequentially executes the manual join, subquery, cross join, and tuple projection examples, ensuring all Criteria API approaches are exercised in a single run. Finally, the finally block guarantees proper cleanup of JPA resources by closing both the EntityManager and EntityManagerFactory, demonstrating a complete, safe, and self-contained JPA application lifecycle.
2.4 persistence.xml
The following persistence.xml file defines the JPA persistence unit configuration, including entity registration, database connectivity, and Hibernate-specific settings required to run the example.
<persistence xmlns="https://jakarta.ee/xml/ns/persistence"
version="3.0">
<persistence-unit name="demoPU">
<class>Employee</class>
<class>Department</class>
<properties>
<property name="jakarta.persistence.jdbc.driver"
value="org.h2.Driver"/>
<property name="jakarta.persistence.jdbc.url"
value="jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1"/>
<property name="jakarta.persistence.jdbc.user" value="sa"/>
<property name="jakarta.persistence.jdbc.password" value=""/>
<property name="hibernate.dialect"
value="org.hibernate.dialect.H2Dialect"/>
<property name="hibernate.hbm2ddl.auto" value="create-drop"/>
<property name="hibernate.show_sql" value="true"/>
<property name="hibernate.format_sql" value="true"/>
</properties>
</persistence-unit>
</persistence>
This configuration declares a persistence unit named demoPU and explicitly registers the Employee and Department entity classes so that JPA can manage them at runtime. The JDBC properties configure an in-memory H2 database, making the example self-contained and easy to execute without any external dependencies. Hibernate-specific settings enable automatic schema creation and cleanup via hbm2ddl.auto, while the SQL logging options allow the generated queries to be printed to the console. Together, these settings provide a lightweight and transparent environment for demonstrating Criteria API joins across unrelated tables.
2.5 Code output
The following output shows the SQL generated by Hibernate along with the final results printed by the application, illustrating how the Criteria API query is translated and executed at runtime.
2026-01-11 07:42:15.402 INFO Hibernate:
select
e1_0.name,
d1_0.dept_name
from
employees e1_0,
departments d1_0
where
e1_0.dept_code = d1_0.dept_code
2026-01-11 07:42:15.418 INFO Application:
--- Manual Join Example ---
2026-01-11 07:42:15.419 INFO Application:
John Doe works in Engineering
2026-01-11 07:42:15.420 INFO Application:
Jane Roy works in Finance
2026-01-11 07:42:15.421 INFO Application:
Alice Kim works in Engineering
2026-01-11 07:42:15.445 INFO Hibernate:
select
e1_0.id,
e1_0.dept_code,
e1_0.name
from
employees e1_0
where
e1_0.dept_code in (
select d1_0.dept_code
from departments d1_0
where d1_0.dept_name = ?
)
2026-01-11 07:42:15.461 INFO Application:
--- Subquery Example ---
2026-01-11 07:42:15.462 INFO Application:
Employee{id=1, name='John Doe', departmentCode='ENG'}
2026-01-11 07:42:15.463 INFO Application:
Employee{id=3, name='Alice Kim', departmentCode='ENG'}
2026-01-11 07:42:15.488 INFO Hibernate:
select
e1_0.name,
d1_0.dept_name
from
employees e1_0,
departments d1_0
where
e1_0.dept_code = d1_0.dept_code
and d1_0.dept_name = ?
2026-01-11 07:42:15.503 INFO Application:
--- Cross Join Example ---
2026-01-11 07:42:15.504 INFO Application:
John Doe - Engineering
2026-01-11 07:42:15.505 INFO Application:
Alice Kim - Engineering
2026-01-11 07:42:15.529 INFO Hibernate:
select
e1_0.id,
e1_0.name,
d1_0.dept_name
from
employees e1_0,
departments d1_0
where
e1_0.dept_code = d1_0.dept_code
2026-01-11 07:42:15.545 INFO Application:
--- Tuple Projection Example ---
2026-01-11 07:42:15.546 INFO Application:
empId=1, empName=John Doe, deptName=Engineering
2026-01-11 07:42:15.547 INFO Application:
empId=2, empName=Jane Roy, deptName=Finance
2026-01-11 07:42:15.548 INFO Application:
empId=3, empName=Alice Kim, deptName=Engineering
The log output demonstrates the end-to-end execution of four distinct JPA Criteria API query approaches with timestamped logging, making it easy to correlate query execution with results. Each block begins with Hibernate-generated SQL, showing how the Criteria API is translated into database queries at runtime, followed by application-level logs that print the processed results. The manual join example first retrieves employee and department names by applying an explicit join condition on the shared department code, returning all matching combinations. The subquery example then executes a nested select to resolve department codes for the Engineering department and filters employees accordingly, resulting in only Engineering employees being printed. The cross join example shows multiple tables in the FROM clause with restrictive predicates applied, effectively simulating a controlled join that outputs only Engineering-related records. Finally, the tuple projection example selects and logs specific columns using aliases, demonstrating clean, type-safe projections without hydrating full entities, while the timestamps confirm sequential execution and help illustrate query flow, performance analysis, and debugging clarity.
3. Conclusion
Joining tables without explicit JPA relationships is often necessary when working with legacy databases or microservice-driven schemas. The JPA Criteria API offers several approaches to address these cases, including multiple roots combined with predicates to simulate joins, subqueries for filtering based on related data, cross joins for flexible record combinations, and tuple projections to produce clean, type-safe results. Selecting the appropriate technique depends on factors such as query complexity, performance, and code readability. When used effectively, the Criteria API is a powerful tool for creating dynamic, maintainable queries.
