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