JPQL

About

JPQL is the object-oriented query language defined by the Java Persistence API (JPA). It’s similar in syntax to SQL but operates on the entity object model rather than directly on database tables.

• SQL works on tables, rows, columns.

• JPQL works on entities, fields, relationships.

Some of the Features of JPQL

• Queries entities and their relationships using object-based syntax.

• Supports CRUD operations using HQL-like statements.

• Abstracts underlying SQL and RDBMS-specific queries.

• Enables navigation across entity relationships like @OneToMany, @ManyToOne.

• Strongly integrated with JPA EntityManager.

JPQL Query Types

1. Basic SELECT

Fetch entities or specific fields from a table (entity).

TypedQuery<Employee> query = em.createQuery(
  "SELECT e FROM Employee e", Employee.class);
List<Employee> result = query.getResultList();

Fetch all employees from the database.

2. SELECT with WHERE Clause

TypedQuery<Employee> query = em.createQuery(
  "SELECT e FROM Employee e WHERE e.department = :dept", Employee.class);
query.setParameter("dept", "Finance");

Find all employees in the Finance department.

3. Selecting Specific Fields (Projection)

TypedQuery<String> query = em.createQuery(
  "SELECT e.name FROM Employee e", String.class);

Fetch just the names of all employees.

Or multiple fields:

TypedQuery<Object[]> query = em.createQuery(
  "SELECT e.name, e.salary FROM Employee e", Object[].class);

4. Using Aggregate Functions

Query query = em.createQuery(
  "SELECT AVG(e.salary) FROM Employee e");
Double averageSalary = (Double) query.getSingleResult();

Get average salary across all employees.

5. GROUP BY and HAVING

Query query = em.createQuery(
  "SELECT e.department, COUNT(e) FROM Employee e GROUP BY e.department HAVING COUNT(e) > 5");

Find departments with more than 5 employees.

6. JOINs (Entity Relationships)

JPQL handles joins using entity fields, not tables.

TypedQuery<Project> query = em.createQuery(
  "SELECT p FROM Project p JOIN p.employees e WHERE e.name = :name", Project.class);
query.setParameter("name", "John Doe");

Get projects where John Doe is assigned.

7. ORDER BY Clause

TypedQuery<Employee> query = em.createQuery(
  "SELECT e FROM Employee e ORDER BY e.salary DESC", Employee.class);

List employees sorted by salary (highest first).

8. IN Clause

List<String> departments = List.of("IT", "HR");
TypedQuery<Employee> query = em.createQuery(
  "SELECT e FROM Employee e WHERE e.department IN :departments", Employee.class);
query.setParameter("departments", departments);

Get employees from IT or HR.

9. LIKE Clause (Pattern Matching)

TypedQuery<Employee> query = em.createQuery(
  "SELECT e FROM Employee e WHERE e.name LIKE :name", Employee.class);
query.setParameter("name", "A%");

Find employees whose name starts with 'A'.

10. Subqueries

TypedQuery<Employee> query = em.createQuery(
  "SELECT e FROM Employee e WHERE e.salary > (SELECT AVG(e2.salary) FROM Employee e2)", Employee.class);

Get employees earning above the average salary.

11. UPDATE and DELETE

JPQL supports UPDATE and DELETE, but not INSERT.

UPDATE

Query query = em.createQuery(
  "UPDATE Employee e SET e.salary = e.salary * 1.1 WHERE e.department = :dept");
query.setParameter("dept", "IT");
int updated = query.executeUpdate();

Give 10% raise to IT department.

DELETE

Query query = em.createQuery(
  "DELETE FROM Employee e WHERE e.status = :status");
query.setParameter("status", "INACTIVE");
int deleted = query.executeUpdate();

Remove all inactive employees.

12. Constructor Expressions (DTO Projection)

TypedQuery<EmployeeDTO> query = em.createQuery(
  "SELECT new com.example.EmployeeDTO(e.name, e.salary) FROM Employee e", EmployeeDTO.class);

Return results in a custom DTO.

Limitations of JPQL

• No support for INSERT.

• Complex SQL (window functions, full joins, recursive CTEs) not supported.

• Limited database-specific syntax (must switch to native queries).