JPQL, Criteria API & Queries

Querying with JPQL

18 min Lesson 1 of 13

Querying with JPQL

Every application that persists data eventually needs to retrieve it in flexible, filtered, and sorted ways. You could drop down to native SQL — but that means coupling your code to specific table and column names, handling database-specific dialects, and working with raw ResultSet objects instead of the typed entity objects that Hibernate already manages for you. JPQL (Jakarta Persistence Query Language) solves this by letting you query your object model directly. The critical insight is: JPQL operates on entities and their mapped fields, not on database tables and columns.

JPQL vs SQL: The Conceptual Shift

When you write a native SQL query you address the physical database: table names, column names, join conditions between foreign keys. When you write JPQL you address your Java domain model: entity class names, field names, and the associations you declared with @OneToMany, @ManyToOne, and friends.

Consider a Product entity mapped to the table products, with a field unitPrice mapped to column unit_price. In SQL you write:

-- SQL: uses the physical table and column names
SELECT * FROM products WHERE unit_price > 50;

In JPQL you write:

-- JPQL: uses the entity class name and field name
SELECT p FROM Product p WHERE p.unitPrice > 50

Hibernate translates that JPQL string into the appropriate SQL for whichever database you are targeting — MySQL, PostgreSQL, Oracle, H2 — so your query code is portable across databases.

Entity name, not table name. By default the entity name is the simple class name (Product, not products). You can customise it with @Entity(name = "Prod"), but most teams leave it as the class name. JPQL identifiers are case-sensitive for entity and field names, case-insensitive for keywords like SELECT, FROM, WHERE.

Anatomy of a JPQL Statement

A JPQL SELECT statement has the same logical clauses as SQL but over entities:

SELECT — what to project (the whole entity, a field, or a constructor expression)
FROM — the entity to query, plus an identification variable (alias)
WHERE — filtering predicates on entity fields and relationships
ORDER BY — sorting by field name
GROUP BY / HAVING — aggregation (covered in Lesson 3)

Your First JPQL Query in Spring Boot 3

In a Spring Boot 3 / Hibernate 6 project you get a EntityManager injected by the container. Here is the minimal setup — a Product entity and a repository method that runs a JPQL query:

// Product.java
package com.example.store.entity;

import jakarta.persistence.*;
import java.math.BigDecimal;

@Entity
@Table(name = "products")
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String name;

    @Column(name = "unit_price", nullable = false)
    private BigDecimal unitPrice;

    @Column(nullable = false)
    private Integer stock;

    // getters / setters omitted for brevity
}

// ProductRepository.java
package com.example.store.repository;

import com.example.store.entity.Product;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.persistence.TypedQuery;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.util.List;

@Repository
public class ProductRepository {

    @PersistenceContext
    private EntityManager em;

    @Transactional(readOnly = true)
    public List<Product> findExpensive(BigDecimal minPrice) {
        String jpql = "SELECT p FROM Product p WHERE p.unitPrice > :minPrice ORDER BY p.unitPrice DESC";
        TypedQuery<Product> query = em.createQuery(jpql, Product.class);
        query.setParameter("minPrice", minPrice);
        return query.getResultList();
    }
}

Two things to notice:

em.createQuery(jpql, Product.class) returns a TypedQuery<Product>, which means getResultList() returns List<Product> — no casting needed.
The named parameter :minPrice is bound with setParameter. Never concatenate user input into the JPQL string — that is JPQL injection, analogous to SQL injection. Parameters are covered in depth in Lesson 4.

Projecting a Single Entity vs. Individual Fields

Selecting the whole entity (SELECT p FROM Product p) is convenient but loads every mapped column. If you only need the name and price for a price-list page, project just those fields:

// Returns List<Object[]> — each array is [String name, BigDecimal unitPrice]
TypedQuery<Object[]> q = em.createQuery(
    "SELECT p.name, p.unitPrice FROM Product p WHERE p.stock > 0",
    Object[].class
);
List<Object[]> rows = q.getResultList();
for (Object[] row : rows) {
    String name  = (String) row[0];
    BigDecimal price = (BigDecimal) row[1];
    System.out.printf("%s: $%.2f%n", name, price);
}

Prefer DTO projections for read-only views. Selecting individual fields returns Object[], which is fragile (wrong cast = ClassCastException at runtime). In Lesson 9 you will use constructor expressions — SELECT NEW com.example.store.dto.PriceDto(p.name, p.unitPrice) FROM Product p — to get a type-safe List<PriceDto> instead. Use whole-entity selection only when you intend to modify the returned objects within the same transaction.

Pagination with setFirstResult and setMaxResults

Loading thousands of rows in one query is a common performance mistake. Use pagination to fetch a page at a time — JPQL delegates to the database's native LIMIT / OFFSET syntax automatically:

@Transactional(readOnly = true)
public List<Product> findPage(int page, int pageSize) {
    TypedQuery<Product> query = em.createQuery(
        "SELECT p FROM Product p ORDER BY p.id ASC",
        Product.class
    );
    query.setFirstResult(page * pageSize);   // 0-based offset
    query.setMaxResults(pageSize);
    return query.getResultList();
}

Always define ORDER BY when paginating. Without an ORDER BY clause the database may return rows in any order, meaning different pages can return the same row or skip rows. Make the sort column stable and ideally unique — the primary key is a safe default.

Case Sensitivity and Naming Rules

JPQL has a few important naming rules that trip up developers coming from SQL:

Entity names are case-sensitive — FROM product will throw a IllegalArgumentException if your class is named Product.
Field names are case-sensitive — use the Java field name (unitPrice), not the column name (unit_price).
Keywords are case-insensitive — select, SELECT, and Select all work, but uppercase is the convention.
String literals use single quotes: WHERE p.status = \'ACTIVE\'.

How Hibernate Translates JPQL to SQL

When em.createQuery(...) is called, Hibernate's query parser compiles the JPQL into an internal AST, then generates SQL targeting your configured dialect. You can see the generated SQL — invaluable for debugging — by adding this to application.properties:

# application.properties
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.orm.jdbc.bind=TRACE

With those settings, a SELECT p FROM Product p WHERE p.unitPrice > :minPrice query on PostgreSQL generates something like:

SELECT p1_0.id, p1_0.name, p1_0.unit_price, p1_0.stock
FROM   products p1_0
WHERE  p1_0.unit_price > ?

Notice how Hibernate mapped Product to products, unitPrice to unit_price, and replaced the named parameter :minPrice with a JDBC ? placeholder — giving you a fully parameterised, injection-safe query.

Summary

JPQL is an object-oriented query language that operates on your entity model, not on database tables. You write queries in terms of class names and field names; Hibernate translates them to dialect-specific SQL at runtime. Use TypedQuery to avoid casting, always use parameters (never string concatenation), add ORDER BY when paginating, and enable SQL logging while developing to understand exactly what hits the database. The next lesson extends this foundation with JPQL joins and fetch joins for navigating entity relationships.