Complete Guide to JPQL Query Types in JPA
JPQL (Java Persistence Query Language) is an object-oriented query language used by JPA implementations such as Hibernate to query database entities.
Unlike SQL, JPQL operates on Java entity classes and their properties, rather than database tables and columns.
This guide covers JPQL query types, joins, filtering, aggregation, subqueries, projections, updates, deletes, pagination, and advanced Hibernate features, with examples suitable for Spring Boot, Jakarta Persistence, and Hibernate.
1. Example entity model
We’ll use four entities throughout the examples.
Conceptual relationships: Department–Employee (one-to-many), Employee–Task (one-to-many), and Employee–Project (many-to-many). Dashed relationships indicate the Employee–Project association.
Department entity
@Entity
public class Department {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
@OneToMany(mappedBy = "department")
private List<Employee> employees;
}
Employee entity
@Entity
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private Double salary;
private Integer age;
@ManyToOne
@JoinColumn(name = "department_id")
private Department department;
@OneToMany(mappedBy = "employee")
private List<Task> tasks;
@ManyToMany
@JoinTable(
name = "employee_project",
joinColumns = @JoinColumn(name = "employee_id"),
inverseJoinColumns = @JoinColumn(name = "project_id")
)
private List<Project> projects;
}
Project and Task entities
@Entity
public class Project {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
@ManyToMany(mappedBy = "projects")
private List<Employee> employees;
}
@Entity
public class Task {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String title;
@ManyToOne
@JoinColumn(name = "employee_id")
private Employee employee;
}
All examples assume an injected EntityManager:
@PersistenceContext
private EntityManager em;
For brevity, entity getters, setters, and imports are omitted.
2. SELECT queries
SELECT retrieves entities, individual properties, or calculated values.
2.1 Select all entities
List<Employee> employees = em.createQuery(
"SELECT e FROM Employee e",
Employee.class
).getResultList();
Equivalent SQL:
SELECT * FROM employee;
2.2 Select one entity
Employee employee = em.createQuery(
"SELECT e FROM Employee e WHERE e.id = :id",
Employee.class
)
.setParameter("id", 10L)
.getSingleResult();
getSingleResult() throws NoResultException if no result exists and NonUniqueResultException if multiple results exist.
2.3 Select specific fields
List<Object[]> results = em.createQuery(
"SELECT e.name, e.salary FROM Employee e",
Object[].class
).getResultList();
for (Object[] row : results) {
String name = (String) row[0];
Double salary = (Double) row[1];
}
2.4 SELECT DISTINCT
Removes duplicate results.
List<Department> departments = em.createQuery(
"""
SELECT DISTINCT d
FROM Department d
JOIN d.employees e
WHERE e.salary > 5000
""",
Department.class
).getResultList();
2.5 SELECT with entity aliases
SELECT employee
FROM Employee employee
WHERE employee.age > 30
Aliases such as e, d, and employee refer to entities or joined objects.
3. WHERE queries and filtering
3.1 Comparison operators
JPQL supports:
| Operator | Meaning |
|---|---|
= |
Equal |
<> |
Not equal |
> |
Greater than |
< |
Less than |
>= |
Greater than or equal |
<= |
Less than or equal |
Example:
SELECT e
FROM Employee e
WHERE e.salary >= 5000
3.2 AND, OR, NOT
SELECT e
FROM Employee e
WHERE e.salary > 5000
AND (e.age < 30 OR e.age > 50)
3.3 BETWEEN
SELECT e
FROM Employee e
WHERE e.salary BETWEEN 3000 AND 7000
BETWEEN includes both boundaries.
3.4 IN
List<Employee> employees = em.createQuery(
"SELECT e FROM Employee e WHERE e.id IN :ids",
Employee.class
)
.setParameter("ids", List.of(1L, 2L, 3L))
.getResultList();
3.5 LIKE
SELECT e
FROM Employee e
WHERE e.name LIKE 'John%'
Common wildcards:
%— zero or more characters_— exactly one character
Case-insensitive search:
SELECT e
FROM Employee e
WHERE LOWER(e.name) LIKE LOWER(:name)
3.6 IS NULL / IS NOT NULL
SELECT e
FROM Employee e
WHERE e.department IS NULL
3.7 IS EMPTY / IS NOT EMPTY
Used for collection relationships.
SELECT d
FROM Department d
WHERE d.employees IS EMPTY
Returns departments with no employees.
4. JPQL JOIN types
Joins are especially important because JPQL uses entity relationships, rather than requiring explicit foreign-key comparisons.
4.1 INNER JOIN
Returns only records where the relationship has matching objects.
SELECT e
FROM Employee e
INNER JOIN e.department d
WHERE d.name = 'IT'
Equivalent SQL:
SELECT e.*
FROM employee e
INNER JOIN department d
ON e.department_id = d.id
WHERE d.name = 'IT';
JOIN and INNER JOIN are equivalent.
SELECT e
FROM Employee e
JOIN e.department d
Behavior: Employees without a department are excluded.
4.2 LEFT JOIN / LEFT OUTER JOIN
Returns all employees, including those without a department.
SELECT e
FROM Employee e
LEFT JOIN e.department d
Equivalent SQL:
SELECT e.*
FROM employee e
LEFT JOIN department d
ON e.department_id = d.id;
Employees without departments are included.
Find employees without departments
SELECT e
FROM Employee e
LEFT JOIN e.department d
WHERE d.id IS NULL
For this particular relationship, the simpler query is:
SELECT e
FROM Employee e
WHERE e.department IS NULL
4.3 RIGHT JOIN / RIGHT OUTER JOIN
Returns all departments, even if they have no employees.
SELECT d
FROM Employee e
RIGHT JOIN e.department d
However, RIGHT JOIN support is provider- and version-dependent, and Hibernate has broader support than some other implementations.
Recommended portable alternative:
SELECT d
FROM Department d
LEFT JOIN d.employees e
This expresses the same outer-join direction using a LEFT JOIN.
4.4 FULL OUTER JOIN
A FULL JOIN returns all records from both sides, including unmatched records.
Important: FULL OUTER JOIN is not part of standard portable JPQL. Hibernate HQL supports full outer joins, depending on the dialect and database.
Hibernate HQL example:
SELECT e, d
FROM Employee e
FULL JOIN e.department d
For portable JPQL, use alternative queries or native SQL.
4.5 JOIN FETCH
One of the most useful JPQL features.
A fetch join loads associated entities as part of the query.
Without fetch join:
SELECT e
FROM Employee e
Accessing e.getDepartment() may trigger additional SQL queries when the association is lazy.
With fetch join:
SELECT e
FROM Employee e
JOIN FETCH e.department
Hibernate retrieves employees and their departments together.
LEFT JOIN FETCH
SELECT e
FROM Employee e
LEFT JOIN FETCH e.department
Includes employees without departments.
Fetch collections
SELECT DISTINCT d
FROM Department d
LEFT JOIN FETCH d.employees
Retrieves departments and their employees.
Important considerations:
- Fetch joins help avoid the N+1 query problem.
- Collection fetch joins can multiply SQL rows.
- Avoid paginating queries that fetch-join collections.
- Fetch joins should not be used to filter the fetched collection with
ON. - Standard JPQL fetch joins do not declare an alias for the fetched association.
4.6 Multiple JOINs
SELECT DISTINCT e
FROM Employee e
JOIN e.department d
JOIN e.projects p
WHERE d.name = 'IT'
AND p.name = 'Website'
This retrieves employees in the IT department who work on the Website project.
Equivalent SQL conceptually:
SELECT DISTINCT e.*
FROM employee e
JOIN department d
ON e.department_id = d.id
JOIN employee_project ep
ON ep.employee_id = e.id
JOIN project p
ON p.id = ep.project_id
WHERE d.name = 'IT'
AND p.name = 'Website';
4.7 JOIN with ON
The ON clause adds conditions to the join.
SELECT d, e
FROM Department d
LEFT JOIN d.employees e
ON e.salary > 5000
This returns every department, with employees matched only when their salary exceeds 5000.
Compare:
SELECT d, e
FROM Department d
LEFT JOIN d.employees e
WHERE e.salary > 5000
The second query removes rows where no employee satisfies the condition, effectively losing the unmatched departments.
This distinction is particularly important with outer joins.
4.8 Implicit JOIN
JPQL automatically navigates single-valued relationships.
SELECT e
FROM Employee e
WHERE e.department.name = 'IT'
This implicitly joins Employee with Department.
It is similar to:
SELECT e
FROM Employee e
JOIN e.department d
WHERE d.name = 'IT'
Implicit joins through associations generally have INNER JOIN semantics.
For nullable relationships, use explicit LEFT JOIN when necessary.
4.9 Multiple roots / CROSS JOIN
JPQL supports multiple entities in the FROM clause.
SELECT e, d
FROM Employee e, Department d
This produces a Cartesian product.
If there are 100 employees and 10 departments, the result contains 1,000 combinations.
You can restrict combinations:
SELECT e, d
FROM Employee e, Department d
WHERE e.department = d
Hibernate HQL also supports explicit CROSS JOIN.
4.10 JOIN many-to-many relationships
For Employee and Project:
SELECT DISTINCT e
FROM Employee e
JOIN e.projects p
WHERE p.name = 'Migration'
Hibernate automatically handles the intermediate employee_project join table.
To retrieve projects assigned to a particular employee:
SELECT p
FROM Employee e
JOIN e.projects p
WHERE e.id = :employeeId
4.11 JOIN unrelated entities
Standard JPQL association joins require a mapped relationship.
If entities are unrelated, portable JPQL can use multiple roots:
SELECT e, d
FROM Employee e, Department d
WHERE e.name = d.name
Hibernate HQL also supports explicit entity joins with ON:
SELECT e, d
FROM Employee e
JOIN Department d ON e.name = d.name
This is useful for entities that do not have a mapped association.
JOIN comparison
| Join type | Includes unmatched rows? | Standard JPQL |
|---|---|---|
| INNER JOIN | No | Yes |
| LEFT JOIN | Left side | Yes |
| RIGHT JOIN | Right side | Version/provider dependent |
| FULL JOIN | Both sides | No |
| JOIN FETCH | Depends on inner/outer join | Yes |
| JOIN ON | Depends on join type | Yes |
| Implicit JOIN | No | Yes |
| Multiple roots | All combinations before filtering | Yes |
| Unrelated entity JOIN | Depends on join type | Hibernate HQL extension |
5. Aggregate queries
JPQL supports aggregate functions.
5.1 COUNT
SELECT COUNT(e)
FROM Employee e
Java:
Long count = em.createQuery(
"SELECT COUNT(e) FROM Employee e",
Long.class
).getSingleResult();
5.2 SUM
SELECT SUM(e.salary)
FROM Employee e
5.3 AVG
SELECT AVG(e.salary)
FROM Employee e
5.4 MIN and MAX
SELECT MIN(e.salary), MAX(e.salary)
FROM Employee e
5.5 COUNT DISTINCT
SELECT COUNT(DISTINCT e.department)
FROM Employee e
Counts distinct non-null departments referenced by employees.
6. GROUP BY and HAVING
6.1 GROUP BY
Calculate average salary per department:
SELECT d.name, AVG(e.salary)
FROM Employee e
JOIN e.department d
GROUP BY d.id, d.name
Result example:
| Department | Average salary |
|---|---|
| IT | 6,500 |
| HR | 4,500 |
| Finance | 7,200 |
6.2 HAVING
Filter aggregated results.
SELECT d.name, COUNT(e)
FROM Department d
JOIN d.employees e
GROUP BY d.id, d.name
HAVING COUNT(e) > 5
Returns departments with more than five employees.
6.3 GROUP BY with LEFT JOIN
Include departments with no employees:
SELECT d.name, COUNT(e)
FROM Department d
LEFT JOIN d.employees e
GROUP BY d.id, d.name
For departments without employees, COUNT(e) returns zero.
6.4 GROUP BY with multiple aggregates
SELECT d.name,
COUNT(e),
AVG(e.salary),
MIN(e.salary),
MAX(e.salary)
FROM Department d
LEFT JOIN d.employees e
GROUP BY d.id, d.name
7. ORDER BY queries
7.1 Ascending
SELECT e
FROM Employee e
ORDER BY e.salary ASC
7.2 Descending
SELECT e
FROM Employee e
ORDER BY e.salary DESC
7.3 Multiple ordering fields
SELECT e
FROM Employee e
ORDER BY e.department.name ASC, e.salary DESC
Sorts by department name, then salary descending.
8. Subqueries
JPQL supports subqueries in WHERE and HAVING.
Standard JPQL does not generally allow subqueries in FROM.
8.1 Scalar subquery
Find employees earning more than the company average.
SELECT e
FROM Employee e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM Employee e2
)
8.2 IN subquery
SELECT e
FROM Employee e
WHERE e.department.id IN (
SELECT d.id
FROM Department d
WHERE d.name LIKE 'IT%'
)
8.3 EXISTS subquery
Find departments containing employees earning more than 5000.
SELECT d
FROM Department d
WHERE EXISTS (
SELECT e
FROM Employee e
WHERE e.department = d
AND e.salary > 5000
)
8.4 NOT EXISTS
Find departments without employees.
SELECT d
FROM Department d
WHERE NOT EXISTS (
SELECT e
FROM Employee e
WHERE e.department = d
)
8.5 Correlated subquery
Find employees earning more than the average salary in their own department.
SELECT e
FROM Employee e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM Employee e2
WHERE e2.department = e.department
)
The inner query refers to the outer query’s employee.
8.6 ALL
Find employees earning more than every employee in the HR department.
SELECT e
FROM Employee e
WHERE e.salary > ALL (
SELECT e2.salary
FROM Employee e2
WHERE e2.department.name = 'HR'
)
8.7 ANY / SOME
Find employees earning more than at least one employee in HR.
SELECT e
FROM Employee e
WHERE e.salary > ANY (
SELECT e2.salary
FROM Employee e2
WHERE e2.department.name = 'HR'
)
SOME is a synonym for ANY.
9. Collection queries
JPQL has operators specifically designed for entity collections.
9.1 MEMBER OF
Find employees belonging to a given project.
SELECT e
FROM Employee e
WHERE :project MEMBER OF e.projects
Java:
List<Employee> employees = em.createQuery(
"""
SELECT e
FROM Employee e
WHERE :project MEMBER OF e.projects
""",
Employee.class
)
.setParameter("project", project)
.getResultList();
The parameter must contain a managed or identifiable Project entity instance.
9.2 IS EMPTY
SELECT e
FROM Employee e
WHERE e.projects IS EMPTY
Returns employees without projects.
9.3 SIZE
SELECT e
FROM Employee e
WHERE SIZE(e.projects) > 3
Returns employees assigned to more than three projects.
9.4 INDEX
For indexed list mappings, JPQL supports INDEX().
For example, if a collection is mapped with @OrderColumn:
SELECT t
FROM Employee e
JOIN e.tasks t
WHERE INDEX(t) = 0
This requires tasks to have an indexed list mapping; the original example entity would need an appropriate @OrderColumn.
9.5 KEY and VALUE
For map-valued relationships:
SELECT KEY(entry), VALUE(entry)
FROM Department d
JOIN d.employeeMap entry
This assumes a mapped employeeMap property on Department.
10. Constructor expressions / DTO projections
Instead of returning entire entities, JPQL can create DTOs.
Example DTO:
package com.example.dto;
public record EmployeeDTO(
String name,
Double salary
) {}
JPQL:
List<EmployeeDTO> employees = em.createQuery(
"""
SELECT NEW com.example.dto.EmployeeDTO(
e.name,
e.salary
)
FROM Employee e
""",
EmployeeDTO.class
).getResultList();
Advantages include reduced data transfer, avoiding unnecessary entity loading, and convenient REST API responses.
DTO with JOIN and aggregate
public record DepartmentStatisticsDTO(
String department,
Long employeeCount,
Double averageSalary
) {}
SELECT NEW com.example.dto.DepartmentStatisticsDTO(
d.name,
COUNT(e),
AVG(e.salary)
)
FROM Department d
LEFT JOIN d.employees e
GROUP BY d.id, d.name
11. UPDATE queries
JPQL supports bulk updates.
int updated = em.createQuery(
"""
UPDATE Employee e
SET e.salary = e.salary * 1.10
WHERE e.department.name = :department
"""
)
.setParameter("department", "IT")
.executeUpdate();
However, JPQL bulk UPDATE queries do not support JOIN clauses, and portable JPQL does not allow association navigation in an update’s WHERE clause in the same way as SELECT queries.
A portable alternative is:
int updated = em.createQuery(
"""
UPDATE Employee e
SET e.salary = e.salary * 1.10
WHERE e.department IN (
SELECT d
FROM Department d
WHERE d.name = :department
)
"""
)
.setParameter("department", "IT")
.executeUpdate();
executeUpdate() returns the number of affected entities.
Bulk updates bypass the persistence context’s normal entity synchronization and lifecycle callbacks. They should be executed within a transaction, and already-loaded entities may need to be refreshed or the persistence context cleared.
12. DELETE queries
Delete employees with no department:
int deleted = em.createQuery(
"""
DELETE FROM Employee e
WHERE e.department IS NULL
"""
).executeUpdate();
Delete employees in a department:
int deleted = em.createQuery(
"""
DELETE FROM Employee e
WHERE e.department IN (
SELECT d
FROM Department d
WHERE d.name = :department
)
"""
)
.setParameter("department", "Temporary")
.executeUpdate();
Bulk DELETE does not automatically apply normal entity lifecycle cascades and callbacks.
Like bulk UPDATE, JPQL DELETE does not support JOIN clauses directly.
13. Named queries
Named queries are defined on entity classes.
@Entity
@NamedQuery(
name = "Employee.findByDepartment",
query = """
SELECT e
FROM Employee e
WHERE e.department.name = :name
"""
)
public class Employee {
// ...
}
Usage:
List<Employee> employees = em.createNamedQuery(
"Employee.findByDepartment",
Employee.class
)
.setParameter("name", "IT")
.getResultList();
Benefits include reusable queries, centralized definitions, and validation at application startup by many JPA providers.
14. Parameterized queries
JPQL supports named and positional parameters.
Named parameters
SELECT e
FROM Employee e
WHERE e.name = :name
query.setParameter("name", "John");
Positional parameters
SELECT e
FROM Employee e
WHERE e.name = ?1
query.setParameter(1, "John");
Recommendation: Prefer named parameters for readability and always bind user-supplied values rather than concatenating them into JPQL strings.
15. Pagination
JPQL does not use SQL’s LIMIT and OFFSET syntax in portable queries.
Instead, use the JPA API.
List<Employee> employees = em.createQuery(
"""
SELECT e
FROM Employee e
ORDER BY e.id
""",
Employee.class
)
.setFirstResult(20)
.setMaxResults(10)
.getResultList();
This retrieves the third page when each page contains 10 records.
Use a deterministic ORDER BY for stable pagination.
16. CASE expressions
JPQL supports conditional expressions.
SELECT e.name,
CASE
WHEN e.salary >= 8000 THEN 'High'
WHEN e.salary >= 4000 THEN 'Medium'
ELSE 'Low'
END
FROM Employee e
CASE with aggregation
SELECT d.name,
SUM(
CASE
WHEN e.salary > 5000 THEN 1
ELSE 0
END
)
FROM Department d
LEFT JOIN d.employees e
GROUP BY d.id, d.name
Counts employees earning more than 5000 per department.
17. JPQL functions
JPQL includes standard string, numeric, date, and null-handling functions.
| Function | Example | Purpose |
|---|---|---|
LOWER |
LOWER(e.name) |
Lowercase |
UPPER |
UPPER(e.name) |
Uppercase |
LENGTH |
LENGTH(e.name) |
String length |
CONCAT |
CONCAT(e.name, ' Jr') |
Concatenate |
SUBSTRING |
SUBSTRING(e.name, 1, 3) |
Extract substring |
TRIM |
TRIM(e.name) |
Remove whitespace |
LOCATE |
LOCATE('a', e.name) |
Find substring |
ABS |
ABS(e.salary) |
Absolute value |
MOD |
MOD(e.age, 2) |
Remainder |
SQRT |
SQRT(e.salary) |
Square root |
COALESCE |
COALESCE(e.name, 'Unknown') |
First non-null value |
NULLIF |
NULLIF(e.age, 0) |
Return null if equal |
CURRENT_DATE |
CURRENT_DATE |
Current date |
CURRENT_TIME |
CURRENT_TIME |
Current time |
CURRENT_TIMESTAMP |
CURRENT_TIMESTAMP |
Current timestamp |
Example:
SELECT e
FROM Employee e
WHERE LOWER(e.name) LIKE '%john%'
Database-specific functions
JPQL also supports invoking database functions through FUNCTION().
SELECT FUNCTION('REPLACE', e.name, 'John', 'Jack')
FROM Employee e
Support depends on the database and JPA provider.
18. TYPE and TREAT for inheritance
These are useful when entities use JPA inheritance.
Suppose Manager extends Employee.
TYPE
SELECT e
FROM Employee e
WHERE TYPE(e) = Manager
Returns employees whose entity type is Manager.
TREAT
Assuming Manager has a bonus property:
SELECT e
FROM Employee e
WHERE TREAT(e AS Manager).bonus > 1000
TREAT allows access to subclass-specific attributes.
19. Advanced Hibernate HQL queries
Hibernate supports several features beyond standard JPQL.
These are useful but reduce portability.
19.1 Common Table Expressions (CTEs)
Hibernate HQL supports CTEs.
WITH HighEarners AS (
SELECT e.id AS id, e.salary AS salary
FROM Employee e
WHERE e.salary > 7000
)
SELECT h.id, h.salary
FROM HighEarners h
This is Hibernate HQL, not standard JPQL.
19.2 UNION
Modern Hibernate HQL supports set operations such as UNION, UNION ALL, INTERSECT, and EXCEPT.
SELECT e.name
FROM Employee e
WHERE e.age < 25
UNION
SELECT e.name
FROM Employee e
WHERE e.salary > 10000
Support for set operations in Jakarta Persistence depends on the specification version; they are not available in older JPQL versions.
19.3 Window functions
Hibernate HQL supports SQL-style window functions.
Example: Rank employees by salary within each department.
SELECT
e.name,
e.salary,
ROW_NUMBER() OVER (
PARTITION BY e.department.id
ORDER BY e.salary DESC
)
FROM Employee e
Other useful window functions include RANK(), DENSE_RANK(), LAG(), LEAD(), and aggregate functions with OVER.
These are Hibernate-specific HQL capabilities rather than portable traditional JPQL.
19.4 Native SQL queries
When JPQL cannot express a database-specific operation, use native SQL.
List<Employee> employees = em.createNativeQuery(
"""
SELECT e.*
FROM employee e
JOIN department d
ON e.department_id = d.id
WHERE d.name = :name
""",
Employee.class
)
.setParameter("name", "IT")
.getResultList();
Native SQL supports database-specific constructs but queries tables and columns rather than entity properties.
20. Practical JOIN examples
Here are common real-world scenarios.
Find employees and their departments
SELECT e, d
FROM Employee e
JOIN e.department d
Find departments without employees
SELECT d
FROM Department d
LEFT JOIN d.employees e
WHERE e.id IS NULL
Find employees without tasks
SELECT e
FROM Employee e
WHERE e.tasks IS EMPTY
Find employees assigned to multiple projects
SELECT e
FROM Employee e
WHERE SIZE(e.projects) > 1
Find departments with at least five employees
SELECT d
FROM Department d
JOIN d.employees e
GROUP BY d
HAVING COUNT(e) >= 5
Find employees who work on a specific project
SELECT DISTINCT e
FROM Employee e
JOIN e.projects p
WHERE p.name = :projectName
Find employees earning more than their department average
SELECT e
FROM Employee e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM Employee e2
WHERE e2.department = e.department
)
Retrieve departments and employees in one query
SELECT DISTINCT d
FROM Department d
LEFT JOIN FETCH d.employees
Find employees with tasks whose titles contain a word
SELECT DISTINCT e
FROM Employee e
JOIN e.tasks t
WHERE LOWER(t.title) LIKE LOWER(:keyword)
Find employees in IT with no projects
SELECT e
FROM Employee e
JOIN e.department d
WHERE d.name = 'IT'
AND e.projects IS EMPTY
21. JPQL vs SQL vs Hibernate HQL
| Feature | JPQL | Hibernate HQL | Native SQL |
|---|---|---|---|
| Entity-based queries | Yes | Yes | No |
| INNER JOIN | Yes | Yes | Yes |
| LEFT JOIN | Yes | Yes | Yes |
| RIGHT JOIN | Version dependent | Yes | DB dependent |
| FULL JOIN | No | Yes | DB dependent |
| JOIN FETCH | Yes | Yes | No equivalent entity-loading syntax |
| GROUP BY / HAVING | Yes | Yes | Yes |
| Subqueries | Yes | Yes | Yes |
| UPDATE / DELETE | Yes | Yes | Yes |
| INSERT | No portable traditional JPQL | Yes | Yes |
| CTE | No portable traditional JPQL | Yes | DB dependent |
| Window functions | No portable traditional JPQL | Yes | DB dependent |
| UNION | Specification-version dependent | Yes | DB dependent |
| Database-specific functions | Via FUNCTION() |
Yes | Yes |
The precise feature set depends on the Jakarta Persistence version and Hibernate version.
22. JPQL best practices
- Prefer explicit JOINs for complex queries because they make relationships and join semantics easier to understand.
- Use LEFT JOIN when related entities may be absent and you still need the parent entity.
- Use JOIN FETCH selectively to avoid N+1 queries, especially for
@ManyToOnerelationships. - Avoid fetching multiple large collections simultaneously, because the result set can grow dramatically.
- Use DTO projections when you only need a few properties rather than entire entities.
- Use named parameters to safely bind user input.
- Use EXISTS for relationship-existence checks when joins would introduce duplicate results.
- Use pagination for large datasets, but avoid paginating collection fetch joins.
- Be careful with bulk UPDATE and DELETE, since they bypass normal persistence-context synchronization.
- Use native SQL or Hibernate HQL extensions only when standard JPQL cannot meet your requirements.
23. Quick reference: choosing the right query
| Requirement | Recommended JPQL technique |
|---|---|
| Retrieve all entities | SELECT e FROM Employee e |
| Filter entities | WHERE |
| Match related entities | INNER JOIN |
| Include entities without relationships | LEFT JOIN |
| Load relationships eagerly for one query | JOIN FETCH |
| Filter join matches | JOIN ... ON |
| Remove duplicate results | DISTINCT |
| Calculate totals or averages | COUNT, SUM, AVG |
| Group and filter aggregates | GROUP BY, HAVING |
| Compare against another query | Subquery |
| Check related records exist | EXISTS |
| Check a collection is empty | IS EMPTY |
| Check collection membership | MEMBER OF |
| Return custom DTOs | SELECT NEW |
| Update many entities | UPDATE |
| Delete many entities | DELETE |
| Sort results | ORDER BY |
| Retrieve a page | setFirstResult, setMaxResults |
| Reuse predefined queries | @NamedQuery |
| Handle conditional values | CASE, COALESCE |
| Use advanced SQL features | Hibernate HQL or native SQL |
For a typical Spring Boot + Hibernate application, the most important JPQL features to master are SELECT, WHERE, INNER JOIN, LEFT JOIN, JOIN FETCH, GROUP BY, HAVING, EXISTS, subqueries, and DTO projections. Together, these cover the vast majority of application database queries.