Spring Boot JPA (Part 9) | Native Queries and Criteria API

The Typewriter vs Precision CAD Drafting Analogy

Imagine preparing an engineering architectural drawing. If you use a traditional mechanical typewriter to type out structural measurements, the process is straightforward. But the typewriter has no awareness of geometric physics: if you accidentally type a negative dimension or misspell a beam thickness, the typewriter prints it anyway with zero warnings. You only discover the bridge collapses after it is built.

Now imagine using modern computer aided design (CAD) software. In CAD, you assemble 3D objects programmatically using typed parameters. If you try to attach a square joint to a circular pipe, the compiler halts immediately: the types are incompatible. The design is guaranteed to be mathematically and syntactically sound before the first steel beam is ordered.

In database querying, raw string queries (JPQL and Native SQL) are that mechanical typewriter: they are plain strings compiled at runtime. A misspelled column name crashes your application only when a user clicks the endpoint. The Criteria API is that precision CAD drafting system: it is a strongly typed, programmatic Java API for building queries dynamically with compile time type safety, preventing syntax errors and SQL injection vulnerabilities.

This lecture covers Native SQL queries with @Query(nativeQuery = true), the @Modifying annotation for updates and deletes, the Criteria API architecture (CriteriaBuilder, CriteriaQuery, Root), and dynamic query construction.

---

1. Native SQL Queries in Spring Data JPA

While JPQL covers most requirements, certain scenarios require writing raw SQL tailored to a specific database:

• Database specific functions (e.g. PostgreSQL JSONB operators, Oracle CONNECT BY, full text search).
• Complex Window functions (ROW_NUMBER() OVER (PARTITION BY...)).
• Performance critical stored procedures and Common Table Expressions (CTEs).

You write native SQL by setting nativeQuery = true inside @Query:

package com.example.orderservice.repository;

import com.example.orderservice.entity.Order;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

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

    // Native SQL targets physical table 'orders' and column 'order_total'
    @Query(
        value = "SELECT * FROM orders WHERE order_status = :status AND order_total > :minTotal",
        nativeQuery = true
    )
    List<Order> findLargeOrdersByStatus(
        @Param("status") String status,
        @Param("minTotal") double minTotal
    );
}

Trade Offs of Native SQL Queries:

• Portability Loss: Query syntax is coupled to that specific database vendor. Migrating from MySQL to PostgreSQL may require rewriting queries.
• No Automatic Pagination Count Optimization: Complex native queries require manual countQuery definitions.

---

2. Modifying Queries (@Modifying)

By default, Spring Data JPA expects @Query methods to be read only SELECT statements.

If your query performs an UPDATE or DELETE, you must annotate the method with @Modifying and @Transactional:

package com.example.orderservice.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    @Modifying
    @Transactional
    @Query("UPDATE User u SET u.status = :status WHERE u.lastLoginDate < :cutoffDate")
    int deactivateInactiveUsers(@Param("status") String status, @Param("cutoffDate") LocalDate cutoffDate);

    @Modifying
    @Transactional
    @Query(value = "DELETE FROM user_tokens WHERE expiry_time < NOW()", nativeQuery = true)
    void purgeExpiredTokens();
}

The First Level Cache Synchronization Trap:

When you execute a bulk @Modifying query, Hibernate executes the SQL directly against the database, bypassing the First Level Cache (L1). If your active session contains managed entity instances that were modified in the database by this bulk query, their in memory state is now out of date!

To fix this, configure clearAutomatically = true:

@Modifying(clearAutomatically = true)
@Query("UPDATE User u SET u.status = 'INACTIVE' WHERE u.id = :id")
void deactivateUser(@Param("id") Long id);

This instructs Hibernate to automatically clear the L1 cache after query execution, forcing subsequent reads to fetch fresh data from the database.

---

3. The Criteria API: Type Safe Programmatic Queries

The Criteria API (jakarta.persistence.criteria.*) builds queries using Java objects rather than strings:

[ CriteriaBuilder ]  ---> Factory for creating query components (predicates, expressions)
       |
[ CriteriaQuery ]    ---> Represents the query structure (SELECT, WHERE, ORDER BY)
       |
    [ Root ]         ---> Represents the base entity being queried (FROM User u)

Basic Criteria API Example:

package com.example.orderservice.dao;

import com.example.orderservice.entity.User;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.persistence.criteria.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class UserCriteriaDao {

    @PersistenceContext
    private EntityManager entityManager;

    public List<User> findActiveUsersByAge(int minAge) {
        // 1. Obtain CriteriaBuilder from EntityManager
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();

        // 2. Create typed CriteriaQuery
        CriteriaQuery<User> cq = cb.createQuery(User.class);

        // 3. Define the FROM clause (Root)
        Root<User> user = cq.from(User.class);

        // 4. Construct Predicates
        Predicate agePredicate = cb.greaterThanOrEqualTo(user.get("age"), minAge);
        Predicate statusPredicate = cb.equal(user.get("status"), "ACTIVE");

        // 5. Apply WHERE clause combining predicates with AND
        cq.where(cb.and(agePredicate, statusPredicate));

        // 6. Execute query
        return entityManager.createQuery(cq).getResultList();
    }
}

---

4. Dynamic Query Construction with Criteria API

The greatest strength of the Criteria API is constructing queries dynamically where search criteria are optional.

Imagine an ecommerce search form where a customer can optionally enter a product title, minimum price, maximum price, and category:

package com.example.orderservice.dao;

import com.example.orderservice.entity.Product;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.persistence.criteria.*;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

@Repository
public class ProductSearchDao {

    @PersistenceContext
    private EntityManager entityManager;

    public List<Product> searchProducts(String title, Double minPrice, Double maxPrice, String category) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Product> cq = cb.createQuery(Product.class);
        Root<Product> product = cq.from(Product.class);

        List<Predicate> predicates = new ArrayList<>();

        // Conditionally append predicates only if parameter was provided
        if (title != null && !title.isBlank()) {
            predicates.add(cb.like(cb.lower(product.get("title")), "%" + title.toLowerCase() + "%"));
        }

        if (minPrice != null) {
            predicates.add(cb.greaterThanOrEqualTo(product.get("price"), minPrice));
        }

        if (maxPrice != null) {
            predicates.add(cb.lessThanOrEqualTo(product.get("price"), maxPrice));
        }

        if (category != null && !category.isBlank()) {
            predicates.add(cb.equal(product.get("category"), category));
        }

        // Apply all active predicates
        cq.where(predicates.toArray(new Predicate[0]));

        // Add sorting
        cq.orderBy(cb.desc(product.get("price")));

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

Notice the elegance: if all parameters are null, zero predicates are appended, and the query executes as SELECT * FROM products. If two parameters are present, only those two are included. There is zero fragile SQL string concatenation (WHERE 1=1 AND ...).

---

Interview Questions & Pitfalls

Q1: What is the primary purpose of the @Modifying annotation in Spring Data JPA?

@Modifying informs Spring Data JPA that the annotated query is an UPDATE, DELETE, or DDL operation rather than a SELECT query. Without @Modifying, executing an update or delete query throws an InvalidDataAccessApiUsageException.

Q2: What is the purpose of clearAutomatically = true on @Modifying?

Bulk update and delete queries execute directly against the database, bypassing Hibernate's First Level (L1) session cache. If the current session contains managed entities updated by that bulk query, their in memory state becomes stale. Setting clearAutomatically = true clears the persistence context after execution, ensuring subsequent lookups fetch fresh database records.

Q3: What are the main trade offs between JPQL and the Criteria API?

JPQL is concise, human readable, and resembles standard SQL, but it is written as plain strings without compile time syntax checking. The Criteria API is strongly typed, validated at compile time, and ideal for building dynamic search queries with variable predicates, but it requires significantly more verbose boilerplate code.

Q4: Can native SQL queries return non entity projections or DTOs?

Yes. Native queries can map results to entity classes, Spring Data JPA Interface based projections, or custom DTOs using @SqlResultSetMapping and @ConstructorResult.

Q5: What problem led to the creation of the Spring Data JPA Specification API?

The standard JPA Criteria API requires extensive boilerplate: setting up EntityManager, CriteriaBuilder, CriteriaQuery, Root, and manually building query objects in separate DAO classes. The Spring Data JPA Specification API builds on top of the Criteria API, providing a clean, reusable functional interface (Specification<T>) that integrates directly with standard Spring Data repository methods.