> For the complete documentation index, see [llms.txt](https://www.pranaypourkar.co.in/the-programmers-guide/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://www.pranaypourkar.co.in/the-programmers-guide/spring/spring-features/spring-persistence/jpa-java-persistence-api/querying-data/jpql.md).

# 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).

```java
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

```java
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)

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

*Fetch just the names of all employees.*

Or multiple fields:

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

### 4. Using Aggregate Functions

```java
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

```java
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.**

```java
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

```java
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

```java
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)

```java
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

```java
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**

```java
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**

```java
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)

```java
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).


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://www.pranaypourkar.co.in/the-programmers-guide/spring/spring-features/spring-persistence/jpa-java-persistence-api/querying-data/jpql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.