Spring Data JPA & Hibernate
JPQL and @Query — Writing Custom Queries
Using JPQL objects references, native SQL queries, and modifying transactional statements.
Introduction
When standard CRUD or derived query methods are insufficient, you can write custom queries. JPQL (Java Persistence Query Language) targets your Java entity classes and properties instead of raw tables and columns. When database-specific queries are needed, Native SQL Queries can bypass JPQL.
Why It Matters
Complex queries involving multi-table joins, subqueries, bulk updates, and aggregate reporting require granular SQL control. JPQL provides database independence, while Native SQL grants access to platform-specific optimizations (like JSON operators in PostgreSQL or indexing hints in Oracle).
Real-World Analogy
Think of JPQL vs Native SQL like standard accounting software vs writing ledgers manually. Standard software (JPQL) uses abstract entities ("revenue", "expenses") and works similarly across different branches (databases). Writing manually in a ledger book (Native SQL) lets you use specific templates, but is bound to that physical ledger format (specific database).
Detailed Mechanics
1. JPQL vs Native SQL
| Feature | JPQL | Native SQL |
|---|---|---|
| Target | Java Entity Classes & Fields | Database Tables & Columns |
| Portability | Database-Agnostic | Tied to Specific Database |
| Validation | Checked at startup | Not checked until execution |
2. @Modifying Queries
For bulk updates or deletes, the query must be annotated with @Modifying alongside @Query. This tells Spring to execute the statement as an update operation instead of a SELECT query.
Practical Example
Quick Quiz
Q1: What is the risk of using @Modifying queries without setting clearAutomatically = true?
A) The update is not executed in the database.
B) The database rolls back the transaction automatically.
C) The Persistence Context (L1 Cache) contains outdated entity versions, leading to stale data bugs in subsequent code execution.
D) The query runs extremely slowly.
Answer: C — Bulk updates directly modify database tables. Hibernate does not automatically update matching entity instances in memory unless the persistence context is explicitly cleared.
Q2: In JPQL, which targets does a SELECT query inspect?
A) Physical database tables.
B) Java Entity Classes and their attributes.
C) XML schema definition mappings.
D) JSON web services payload keys.
Answer: B — JPQL operates entirely on the object graph schema, translating class fields to database columns at runtime.
Scenario-Based Challenge
Production Scenario:
You run a service method that loads an account, executes a bulk query update to modify its balance directly, and then prints the loaded account balance. The database value is modified, but the logs show the old balance in the object reference. How do you resolve this?
View SolutionThis happens because the loaded entity in the first-level cache is not refreshed after the bulk query runs. To fix this:
- Configure
@Modifying(clearAutomatically = true)on the repository method to clear the persistence context automatically. - Manually refresh the entity in the service using
entityManager.refresh(account)to fetch the updated state from the database.
Interview Questions
1. Conceptual: What are the trade-offs of using nativeQuery = true in Spring Data JPA?
Pros: Native SQL queries allow database-specific optimizations and functions. Cons: You lose database dialect portability, Spring cannot validate the query fields at startup, and you must handle manual projection mappings if the returned columns do not align with a declared entity model.
2. Coding: How do you pass list parameter collections into a @Query annotation?
Spring Data JPA natively expands collections within an IN clause:
@Query("SELECT u FROM User u WHERE u.status IN :statuses")
List<User> findByStatuses(@Param("statuses") List<String> statuses);
Production Considerations
Avoid passing unvalidated parameters to Native Queries. Spring SQL binds parameters safely, preventing SQL Injection. Never concatenate query parameters dynamically inside raw SQL strings.