Common Table Expressions (CTEs) are one of the most powerful SQL features for writing readable, reusable, and recursive queries. Prior to Hibernate 6, developers typically had to rely on native SQL whenever they wanted to use a CTE. Hibernate 6 introduced first-class support for Common Table Expressions directly in HQL (Hibernate Query Language), allowing developers to write complex queries while still benefiting from Hibernate’s ORM capabilities.
1. Overview
A Common Table Expressions (CTEs) is a temporary named result set that exists only for the duration of a single SQL statement. Instead of relying on temporary tables, views, or deeply nested subqueries, developers can define reusable intermediate result sets using the WITH clause. This makes complex SQL much easier to understand, maintain, and debug. Unlike a temporary table, a CTE is not physically stored in the database. It exists only while the query is executing and disappears once the statement completes. A CTE can also be referenced multiple times within the same query, avoiding duplicated query logic.
CTEs improve:
- Readability by breaking large queries into logical building blocks.
- Maintainability through reusable intermediate result sets.
- Query modularity by separating complex logic into smaller pieces.
- Recursive querying for hierarchical and graph-like data.
- Performance in scenarios where repeated subqueries can be simplified.
- Hierarchical data traversal without requiring application-side recursion.
Before Hibernate 6, developers typically had to write native SQL whenever a CTE was required, sacrificing database portability and some ORM features. Hibernate 6 introduces native CTE support in HQL, allowing developers to write expressive and type-safe queries while continuing to leverage Hibernate’s entity mapping, query parser, and session management.
1.1 What are Common Table Expressions?
A Common Table Expression is defined using the SQL WITH clause. It creates a temporary named result set that can be referenced by the main query or by other CTEs defined later in the same statement. Because the result is named, the overall query becomes much easier to read than deeply nested subqueries. The basic syntax is:
WITH employee_cte
AS (SELECT *
FROM employees
WHERE salary > 80000
)
SELECT *
FROM employee_cte;
In this example, the query first creates a temporary result set named employee_cte, which contains employees earning more than 80,000. The outer query then simply retrieves data from that result set. Think of a CTE as a temporary, read-only view that exists only for the lifetime of the SQL statement.
1.1.1 Types of CTEs
| Type | Description |
|---|---|
| Non-Recursive CTE | Defines a temporary result set from a standard SELECT statement. Commonly used for reporting, filtering, aggregations, and simplifying complex queries. |
| Recursive CTE | Uses an anchor query and a recursive query joined with UNION ALL to repeatedly process hierarchical or tree-structured data such as organizational charts or category hierarchies. |
| Multiple CTEs | Allows multiple CTEs to be declared in a single WITH clause, where later CTEs can reference earlier ones to build complex query pipelines. |
| Nested CTEs | Creates layered query logic where one CTE depends on another, making analytical and reporting queries much easier to organize. |
1.1.2 Advantages of CTE Support in Hibernate
Hibernate’s CTE support brings several benefits over using native SQL:
- Write complex queries directly in HQL without switching to native SQL.
- Improve readability by replacing deeply nested subqueries with named result sets.
- Reuse intermediate query results multiple times within a single statement.
- Support recursive queries for hierarchical entity relationships.
- Continue benefiting from Hibernate’s entity mappings and type safety.
- Reduce duplicated query logic, making maintenance easier.
- Keep business logic closer to the object model instead of database-specific SQL.
1.1.3 When Should You Use CTEs?
CTEs are an excellent choice whenever a query becomes difficult to understand or contains repeated logic. Common use cases include:
- Breaking large SQL statements into smaller logical steps.
- Replacing repeated or duplicated subqueries.
- Generating analytical or reporting queries.
- Traversing organizational hierarchies and manager-employee relationships.
- Working with category trees, folder structures, or bill-of-material hierarchies.
- Performing graph traversal and recursive searches.
- Building complex aggregations in a modular and readable manner.
1.1.4 Limitations
Although CTEs are powerful, they have a few considerations:
- Hibernate CTE support is available starting with Hibernate 6.
- Recursive CTE support depends on the capabilities of the underlying database.
- Different databases may optimize CTEs differently, so execution plans should be reviewed for performance-critical queries.
- Deep recursive queries should include appropriate stopping conditions to avoid excessive recursion.
- CTEs exist only for the duration of a single SQL statement and cannot be reused across multiple queries.
2. Code Example
In this section, we’ll build a simple employee hierarchy using PostgreSQL and query it using Hibernate 6 CTE support. The example demonstrates both a non-recursive CTE for filtering data and a recursive CTE for traversing an employee-manager hierarchy.
2.1 Project Dependencies
Hibernate 6 introduces first-class support for Common Table Expressions in HQL. To run the examples, include Hibernate Core and the PostgreSQL JDBC driver in your project.
<dependency>
<groupId>org.hibernate.orm</groupId>
<artifactId>hibernate-core</artifactId>
<version>stable__jar__version</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
</dependency>
The examples use PostgreSQL because it provides excellent support for both standard and recursive CTEs. The same Hibernate HQL can also be used with other databases that support CTEs.
2.2 Setting Up Sample Data in PostgreSQL
We’ll create a simple employee table where each employee optionally references another employee as their manager. This self-referencing relationship makes it an ideal candidate for demonstrating recursive CTEs.
CREATE TABLE employee
(
id BIGSERIAL PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL,
manager_id BIGINT
);
Next, insert some sample data representing a small organizational hierarchy.
INSERT INTO employee(name,salary,manager_id) VALUES
('John',120000,NULL),
('Alice',95000,1),
('Bob',90000,1),
('Charlie',70000,2),
('David',65000,2),
('Emma',60000,3),
('James',55000,3);
In this dataset, John is the top-level employee and has no manager. Alice and Bob report directly to John, Charlie and David report to Alice, while Emma and James report to Bob. This simple hierarchy is sufficient to demonstrate both standard CTEs for filtering data and recursive CTEs for traversing parent-child relationships. This hierarchy provides enough depth to demonstrate how recursive CTEs repeatedly discover child employees until no additional records are found.
2.3 Hibernate Entity
Next, create the Hibernate entity that maps to the employee table. Since an employee can report to another employee, the entity contains a self-referencing association that models the manager-employee relationship required for the recursive CTE example.
package com.example.hibernate.cte.entity;
import jakarta.persistence.*;
@Entity
@Table(name = "employee")
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false, length = 100)
private String name;
@Column(nullable = false)
private Double salary;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "manager_id")
private Employee manager;
public Employee() {
}
public Employee(String name, Double salary, Employee manager) {
this.name = name;
this.salary = salary;
this.manager = manager;
}
// getters and setters
@Override
public String toString() {
return "Employee{" +
"id=" + id +
", name='" + name + '\'' +
", salary=" + salary +
", manager=" + (manager != null ? manager.getId() : null) +
'}';
}
}
The entity is mapped to the employee table using the @Entity and @Table annotations. The id field is the primary key and is generated automatically, while the name and salary fields map directly to their corresponding database columns. The manager field is defined as a self-referencing @ManyToOne association using the manager_id foreign key, allowing each employee to reference another employee as their manager. This hierarchical mapping is the foundation for executing recursive CTE queries that traverse the organization structure.
2.4 Basic CTE Example in Hibernate
The following example demonstrates how to use a non-recursive Common Table Expression in Hibernate 6. The CTE filters employees whose salary is greater than or equal to 90,000 and exposes the filtered records as a temporary result set. The outer query then retrieves the required fields from this CTE instead of directly querying the employee table.
package com.example.hibernate.cte;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
public class BasicCTEExample {
public static void main(String[] args) {
SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
try (Session session = sessionFactory.openSession()) {
Transaction transaction = session.beginTransaction();
String hql = """
with HighSalaryEmployees as (
select
e.id as id,
e.name as name,
e.salary as salary
from Employee e
where e.salary >= 90000
)
select
h.name,
h.salary
from HighSalaryEmployees h
order by h.salary desc
""";
List < Object[] > employees = session.createQuery(hql, Object[].class).getResultList();
System.out.println("High Salary Employees");
for (Object[] row: employees) {
System.out.printf("%-10s %.2f%n", row[0], row[1]);
}
transaction.commit();
}
}
}
The query defines a non-recursive CTE named HighSalaryEmployees using the WITH clause. Inside the CTE, Hibernate selects employees whose salary is at least 90,000 and creates a temporary result set containing their identifier, name, and salary. The main query then reads data from the CTE and sorts the results by salary in descending order. The createQuery() method executes the HQL query through Hibernate, while the result is returned as a list of Object[] because the query selects multiple fields instead of a complete entity object. This approach keeps the query readable while avoiding native SQL.
High Salary Employees John 120000.00 Alice 95000.00 Bob 90000.00
2.5 Recursive CTE Example in Hibernate
Recursive CTEs are useful for querying hierarchical data such as employee-manager relationships, category trees, and organizational structures. A recursive CTE consists of two parts: an anchor query that identifies the root records and a recursive query that repeatedly finds related child records until the complete hierarchy is generated.
package com.example.hibernate.cte;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
public class RecursiveCTEExample {
public static void main(String[] args) {
SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
try (Session session = sessionFactory.openSession()) {
Transaction transaction = session.beginTransaction();
String hql = """
with recursive EmployeeHierarchy
(id, name, managerId, level) as (
select
e.id,
e.name,
e.manager.id,
1
from Employee e
where e.manager is null
union all
select
child.id,
child.name,
child.manager.id,
parent.level + 1
from Employee child
join EmployeeHierarchy parent
on child.manager.id = parent.id
)
select
eh.name,
eh.level
from EmployeeHierarchy eh
order by eh.level, eh.name
""";
List < Object[] > hierarchy = session.createQuery(hql, Object[].class).getResultList();
System.out.println("Employee Hierarchy");
for (Object[] row: hierarchy) {
System.out.printf("%-10s Level %s%n", row[0], row[1]);
}
transaction.commit();
}
}
}
The query defines a recursive CTE named EmployeeHierarchy that builds the complete employee hierarchy in multiple iterations. The anchor query executes first and selects the top-level employee whose manager is null, assigning the initial hierarchy level as 1. The recursive part then joins the Employee entity with the previously generated CTE results to find employees whose manager matches an existing employee in the hierarchy. For every recursive iteration, the level value is increased using parent.level + 1 until no additional child employees are found. The final query retrieves employee names and their hierarchy levels from the generated CTE, allowing the complete organizational structure to be fetched using a single Hibernate query.
Employee Hierarchy John Level 1 Alice Level 2 Bob Level 2 Charlie Level 3 David Level 3 Emma Level 3 James Level 3
3. Conclusion
Common Table Expressions (CTEs) significantly improve the readability, maintainability, and modularity of complex SQL queries by allowing developers to break large queries into smaller, reusable components. With Hibernate 6 introducing first-class CTE support in HQL, developers can leverage both non-recursive and recursive CTEs without resorting to native SQL, while continuing to benefit from Hibernate’s ORM capabilities. Non-recursive CTEs simplify reporting and replace deeply nested subqueries, whereas recursive CTEs provide an elegant solution for traversing hierarchical structures such as organizational charts, category trees, file systems, and bill-of-material hierarchies. By combining expressive HQL with powerful SQL features, Hibernate 6 makes complex data retrieval more intuitive, maintainable, and efficient, making CTEs an essential feature for modern enterprise applications.
