Query Features

🎯 JPA Query Features: Your Magic Toolkit for Finding Data

Imagine you’re a detective with a super-powered magnifying glass. You can ask ANY question about your data, and get the EXACT answers you need!

🌟 The Big Picture

Think of a database like a giant toy box with millions of toys. JPA Query Features are your special powers to:

1. Count toys (Aggregate Functions)
2. Ask tricky questions (Subqueries)
3. Build custom search robots (Criteria API)

Let’s unlock each power, one by one!

📊 JPQL Aggregate Functions

What Are They?

Aggregate functions are like math helpers. Instead of looking at each toy one-by-one, they look at ALL toys and give you ONE answer.

Simple Analogy: Imagine counting ALL your crayons instead of saying “red, blue, green…” You just say “I have 24 crayons!”

The 5 Super Powers

Code Examples

COUNT - Count all employees:

SELECT COUNT(e)
FROM Employee e

Returns: 150 (one number!)

SUM - Total of all salaries:

SELECT SUM(e.salary)
FROM Employee e

Returns: 5000000

AVG - Average salary:

SELECT AVG(e.salary)
FROM Employee e

Returns: 33333.33

MAX & MIN together:

SELECT MAX(e.salary), MIN(e.salary)
FROM Employee e

Returns: 100000, 20000

GROUP BY Magic ✨

Want to count employees per department? Use GROUP BY!

SELECT e.department, COUNT(e)
FROM Employee e
GROUP BY e.department

Returns:

• Sales → 50
• Engineering → 80
• HR → 20

🔍 JPQL Subqueries

What Are They?

A subquery is a question inside a question. Like asking:

“Show me employees who earn MORE than the AVERAGE salary”

First, you need to find the average. Then, compare everyone to it!

The Nesting Trick

graph TD
    A["Main Query"] --> B["Subquery"]
    B --> C["Answer from Subquery"]
    C --> D["Main Query Uses Answer"]

Real Examples

Find employees earning above average:

SELECT e FROM Employee e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM Employee e2
)

Find departments with more than 10 employees:

SELECT d FROM Department d
WHERE (
    SELECT COUNT(e)
    FROM Employee e
    WHERE e.department = d
) > 10

WHERE vs HAVING with Subqueries

-- HAVING example
SELECT e.department, AVG(e.salary)
FROM Employee e
GROUP BY e.department
HAVING AVG(e.salary) > 50000

🏗️ Criteria API: Build Queries Like LEGO!

Why Criteria API?

JPQL is like writing a letter. Criteria API is like building with LEGO blocks!

When to Use Criteria API:

• ✅ Search forms with optional filters
• ✅ Dynamic sorting
• ✅ Type-safe queries

🔧 CriteriaBuilder: Your Tool Factory

What Is It?

CriteriaBuilder is like a toolbox factory. It creates all the tools you need to build queries.

Getting Your Builder

EntityManager em = ...;
CriteriaBuilder cb = em.getCriteriaBuilder();

Tools CriteriaBuilder Creates

graph TD
    CB["CriteriaBuilder"] --> CQ["CriteriaQuery"]
    CB --> P["Predicates"]
    CB --> E["Expressions"]
    CB --> O["Orders"]

📋 CriteriaQuery: Your Query Blueprint

What Is It?

CriteriaQuery is the blueprint of your query. You tell it:

• WHAT to select
• FROM which table
• WHERE conditions

Building a Query Step-by-Step

// Step 1: Get the builder
CriteriaBuilder cb = em.getCriteriaBuilder();

// Step 2: Create the blueprint
CriteriaQuery<Employee> cq =
    cb.createQuery(Employee.class);

// Step 3: Define the "FROM"
Root<Employee> emp = cq.from(Employee.class);

// Step 4: Define "SELECT"
cq.select(emp);

// Step 5: Execute!
List<Employee> results =
    em.createQuery(cq).getResultList();

Visual Flow

graph TD
    A["CriteriaBuilder"] -->|creates| B["CriteriaQuery"]
    B -->|defines| C["Root - FROM clause"]
    C -->|add| D["Select"]
    D -->|add| E["Where/Predicates"]
    E -->|execute| F["Results!"]

🎭 Predicates: Your Filter Conditions

What Are Predicates?

Predicates are true/false questions about your data.

“Is salary > 50000?” → TRUE or FALSE

Creating Predicates

CriteriaBuilder cb = em.getCriteriaBuilder();
Root<Employee> emp = cq.from(Employee.class);

// Simple predicate: salary > 50000
Predicate salaryCheck =
    cb.gt(emp.get("salary"), 50000);

// Apply it!
cq.where(salaryCheck);

Combining Predicates

AND - Both must be true:

Predicate highSalary =
    cb.gt(emp.get("salary"), 50000);
Predicate inSales =
    cb.equal(emp.get("dept"), "Sales");

// salary > 50000 AND dept = 'Sales'
cq.where(cb.and(highSalary, inSales));

OR - Either can be true:

// dept = 'Sales' OR dept = 'Marketing'
cq.where(cb.or(inSales, inMarketing));

NOT - Opposite:

// NOT (salary < 30000)
cq.where(cb.not(lowSalary));

Common Predicate Methods

🔗 Joins in Criteria: Connecting Tables

What Are Joins?

Joins connect related tables. Like connecting puzzle pieces!

graph LR
    E["Employee"] -->|works in| D["Department"]
    E -->|has| P["Projects"]

Types of Joins

Creating Joins in Criteria

Basic Join:

Root<Employee> emp =
    cq.from(Employee.class);

// Join to Department
Join<Employee, Department> dept =
    emp.join("department");

// Now filter on department!
cq.where(
    cb.equal(dept.get("name"), "Sales")
);

Left Join (keep all employees):

Join<Employee, Department> dept =
    emp.join("department", JoinType.LEFT);

Multiple Joins:

Root<Employee> emp =
    cq.from(Employee.class);

Join<Employee, Department> dept =
    emp.join("department");

Join<Employee, Project> proj =
    emp.join("projects");

// Filter: Sales dept AND active projects
cq.where(
    cb.and(
        cb.equal(dept.get("name"), "Sales"),
        cb.equal(proj.get("active"), true)
    )
);

Fetch Joins (Performance Boost!)

Fetch joins load related data in ONE query:

// Without fetch: N+1 queries problem!
// With fetch: 1 query only!

Root<Employee> emp =
    cq.from(Employee.class);

emp.fetch("department", JoinType.LEFT);

🎯 Complete Example: Dynamic Search

Let’s build a real search form!

public List<Employee> search(
    String name,
    Double minSalary,
    String department
) {
    CriteriaBuilder cb =
        em.getCriteriaBuilder();

    CriteriaQuery<Employee> cq =
        cb.createQuery(Employee.class);

    Root<Employee> emp =
        cq.from(Employee.class);

    // Collect predicates dynamically
    List<Predicate> predicates =
        new ArrayList<>();

    // Add conditions only if provided
    if (name != null) {
        predicates.add(
            cb.like(emp.get("name"),
                "%" + name + "%")
        );
    }

    if (minSalary != null) {
        predicates.add(
            cb.ge(emp.get("salary"),
                minSalary)
        );
    }

    if (department != null) {
        Join<Employee, Department> dept =
            emp.join("department");
        predicates.add(
            cb.equal(dept.get("name"),
                department)
        );
    }

    // Apply all predicates with AND
    cq.where(
        cb.and(predicates.toArray(
            new Predicate[0]))
    );

    return em.createQuery(cq)
        .getResultList();
}

🚀 Quick Reference

graph LR
    A["JPA Query Features"] --> B["Aggregate Functions"]
    A --> C["Subqueries"]
    A --> D["Criteria API"]

    B --> B1["COUNT/SUM/AVG"]
    B --> B2["MAX/MIN"]
    B --> B3["GROUP BY"]

    C --> C1["Nested SELECT"]
    C --> C2["WHERE subquery"]
    C --> C3["HAVING subquery"]

    D --> D1["CriteriaBuilder"]
    D --> D2["CriteriaQuery"]
    D --> D3["Predicates"]
    D --> D4["Joins"]

💡 Remember These!

1. Aggregates = Math on groups (COUNT, SUM, AVG, MAX, MIN)
2. Subqueries = Questions inside questions
3. CriteriaBuilder = Your tool factory
4. CriteriaQuery = Your query blueprint
5. Predicates = True/false filters
6. Joins = Connecting related tables

🎉 You did it! You now have the power to ask ANY question about your data. Go build something amazing!