JPQL Basics

Intermediate

Querying

JPQL is an entity-oriented query language; queries reference entity and field names (not table/column names), making them portable across DB vendors.

Overview

JPQL (Java Persistence Query Language) is an object-oriented query language defined by the JPA specification. Unlike SQL which operates on tables and columns, JPQL operates on entity classes and their field names. This makes JPQL queries portable across database vendors — the same JPQL string generates correct SQL for MySQL, PostgreSQL, Oracle, or H2. JPQL supports SELECT, UPDATE, and DELETE statements, JOIN with entity relationships, aggregate functions, subqueries, named parameters, and ordering. In Spring Data JPA, JPQL is used inside @Query annotations and is the underlying language of derived query method generation. Understanding JPQL is essential for writing any query beyond simple findBy methods.

JPQL Syntax — Entity Names, Not Table Names

The critical rule: JPQL uses the entity class name (as declared in @Entity or the value attribute) and Java field names — never the SQL table or column names. The from clause uses the entity class name; path expressions navigate associations using field names, not JOINs.

JPQL parameters are named (:paramName) or positional (?1). Named parameters are preferred for readability and are required in Spring Data @Query.

Java — JPQL EntityManager

// SQL reference for comparison:
// SELECT id, total, status FROM orders o WHERE o.customer_id = 42 AND o.status = 'SHIPPED'

// Equivalent JPQL — uses class name "Order", field names "customerId", "status"
// Entity:  @Entity public class Order { Long id; Long customerId; String status; BigDecimal total; }
// "o" is an alias (like SQL AS)
String jpql = "SELECT o FROM Order o WHERE o.customerId = :cid AND o.status = :status";

// Executing with EntityManager
TypedQuery<Order> query = em.createQuery(jpql, Order.class);
query.setParameter("cid",    42L);
query.setParameter("status", "SHIPPED");
List<Order> orders = query.getResultList();

// SELECT single field (returns List<BigDecimal>)
TypedQuery<BigDecimal> totalsQuery =
    em.createQuery("SELECT o.total FROM Order o WHERE o.customerId = :cid", BigDecimal.class);
totalsQuery.setParameter("cid", 42L);
List<BigDecimal> totals = totalsQuery.getResultList();

// Aggregate functions
TypedQuery<Long> countQuery =
    em.createQuery("SELECT COUNT(o) FROM Order o WHERE o.status = :s", Long.class);
countQuery.setParameter("s", "PENDING");
long count = countQuery.getSingleResult();

JPQL in Spring Data @Query

In Spring Data JPA, @Query annotates repository methods with JPQL. The return type can be an entity, a primitive, a DTO (using the SELECT new constructor expression), or a projection interface. @Modifying + @Transactional is required for UPDATE and DELETE.

Java — @Query JPQL

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    // Basic filter
    @Query("SELECT o FROM Order o WHERE o.customerId = :cid AND o.total > :min")
    List<Order> findByCustomerAndMinTotal(@Param("cid") Long cid, @Param("min") BigDecimal min);

    // Constructor expression — maps result to a DTO without loading full entity
    @Query("SELECT new com.example.dto.OrderSummary(o.id, o.status, o.total) " +
           "FROM Order o WHERE o.customerId = :cid ORDER BY o.createdAt DESC")
    List<OrderSummary> findSummariesByCustomer(@Param("cid") Long cid);

    // JOIN with related entity (uses field name "customer", not FK column)
    @Query("SELECT o FROM Order o JOIN o.customer c WHERE c.email = :email")
    List<Order> findByCustomerEmail(@Param("email") String email);

    // Aggregate with GROUP BY
    @Query("SELECT o.status, COUNT(o) FROM Order o GROUP BY o.status")
    List<Object[]> countByStatus();

    // Bulk UPDATE — requires @Modifying + @Transactional
    @Modifying
    @Transactional
    @Query("UPDATE Order o SET o.status = 'CANCELLED' WHERE o.customerId = :cid AND o.status = 'PENDING'")
    int cancelPendingByCustomer(@Param("cid") Long cid);
}

JPQL vs SQL — Key Differences

Understanding the differences prevents common mistakes:

- **Table vs Entity**: JPQL uses `Order` (class name), SQL uses `orders` (table name). - **Column vs Field**: JPQL uses `o.customerId` (Java field), SQL uses `o.customer_id` (column). - **JOIN navigation**: JPQL can traverse associations with `JOIN o.items i`; SQL needs explicit JOIN with ON clause. - **No SELECT ***: JPQL `SELECT o` selects the entire entity; there is no wildcard syntax. - **Pagination**: JPQL uses setFirstResult/setMaxResults; Spring Data Pageable handles this automatically. - **JPQL does NOT support**: window functions, CTEs (WITH clauses), INSERT, DB-specific functions → use nativeQuery=true for those.

Java — JPQL vs Native SQL

// JPQL navigates associations — no explicit ON clause needed
// "o.items" uses the @OneToMany field "items" in the Order entity
@Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.items WHERE o.customerId = :cid")
List<Order> findWithItemsByCustomer(@Param("cid") Long cid);

// Hibernate generates the SQL JOIN automatically:
// SELECT DISTINCT o.*, i.* FROM orders o
// JOIN order_items i ON i.order_id = o.id
// WHERE o.customer_id = ?

// JPQL cannot do window functions — use native SQL
@Query(value = """
    SELECT id, total,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn
    FROM orders
    WHERE status = 'SHIPPED'
    """, nativeQuery = true)
List<Object[]> findRankedByCustomer();

Key Points to Remember

1JPQL uses entity class names and Java field names — never SQL table or column names.
2Use named parameters (:paramName) in JPQL — @Param("name") maps the argument; positional parameters (?1) are fragile.
3SELECT new com.example.MyDto(o.id, o.name) constructor expression maps results to a DTO without loading full entities.
4@Modifying + @Transactional is required for @Query UPDATE and DELETE; omitting either causes an exception.
5JPQL JOIN automatically follows entity relationships (mappedBy, @JoinColumn) — no ON clause needed.
6For DB-specific features (window functions, CTEs, MATCH AGAINST) use nativeQuery=true — JPQL cannot express everything SQL can.

Interview Questions

What is the difference between JPQL and SQL?

EasyTCS

How do you write a JPQL query that loads an Order with all its OrderItems in one query?

MediumAmazon

What is the SELECT new constructor expression in JPQL and when would you use it?

MediumFlipkart

Why does a @Query UPDATE or DELETE need @Modifying and @Transactional?

MediumUber

JPQL returns Object[] for a GROUP BY count query. How would you map this to a strongly-typed result?

HardGoldman Sachs

Ask Aria about JPQL Basics

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Inheritance — Joined Strategy

JPQL Joins & Fetch Joins