Skip to main content

Chapter 3: Custom Queries with @Query

Introduction​

Naming conventions are powerful, but sometimes insufficient for complex queries.

This chapter teaches you how to write your own queries with @Query!

What You Will Learn​

  1. JPQL (Java Persistence Query Language) Syntax
  2. @Query with JPQL
  3. @Query with Native SQL
  4. Named Parameters with @Param
  5. Modification Queries (@Modifying)

Duration: 1h

Why @Query?​

Limitations of Conventions​

Try to create this query with conventions:

SELECT p FROM Product p 
WHERE p.price < :maxPrice 
AND p.stock > 0 
AND p.category.name IN ('Electronics', 'Computers')
ORDER BY p.price ASC

Resulting method name:

findByPriceLessThanAndStockGreaterThanAndCategoriesNameInOrderByPriceAsc(
    Double maxPrice, 
    Integer minStock, 
    List<String> categories
)

❌ Too long! Not readable!

Solution: @Query​

@Query("SELECT p FROM Product p " +
       "WHERE p.price < :maxPrice " +
       "AND p.stock > 0 " +
       "AND p.category.name IN :categories " +
       "ORDER BY p.price ASC")
List<Product> findAvailableProducts(
    @Param("maxPrice") Double maxPrice,
    @Param("categories") List<String> categories
);

✅ Clear! Readable! Powerful!

JPQL vs SQL​

Fundamental Differences​

AspectJPQLSQL
TargetJava EntitiesDB Tables
SyntaxFROM Product pFROM product
Fieldsp.nameproduct.name
Relationsp.category.nameJOIN required
Portable✅ Yes❌ DB dependent

Comparative Example​

JPQL (recommended):

@Query("SELECT p FROM Product p WHERE p.price < :max")
List<Product> findCheap(@Param("max") Double max);

Native SQL:

@Query(value = "SELECT * FROM product WHERE price < :max", 
       nativeQuery = true)
List<Product> findCheap(@Param("max") Double max);

Prefer JPQL: More portable, more object-oriented!

JPQL: The Basics​

1. Simple SELECT​

@Query("SELECT p FROM Product p")
List<Product> findAllProducts();

// Equivalent SQL:
// SELECT * FROM product

Key Points:

  • Product = Class name (not table name)
  • p = Alias for the entity

2. WHERE Conditions​

@Query("SELECT p FROM Product p WHERE p.price < :maxPrice")
List<Product> findByPriceLessThan(@Param("maxPrice") Double maxPrice);

@Query("SELECT p FROM Product p WHERE p.name LIKE %:keyword%")
List<Product> searchByName(@Param("keyword") String keyword);

@Query("SELECT p FROM Product p WHERE p.active = true AND p.stock > 0")
List<Product> findAvailable();

3. ORDER BY​

@Query("SELECT p FROM Product p ORDER BY p.price ASC")
List<Product> findAllOrderByPrice();

@Query("SELECT p FROM Product p ORDER BY p.price DESC, p.name ASC")
List<Product> findAllSorted();

4. Joins​

Without explicit JOIN (uses the relation):

@Query("SELECT e FROM Employee e WHERE e.department.name = :deptName")
List<Employee> findByDepartmentName(@Param("deptName") String deptName);

With JOIN:

@Query("SELECT e FROM Employee e JOIN e.department d WHERE d.name = :deptName")
List<Employee> findByDepartmentName(@Param("deptName") String deptName);

With JOIN FETCH (avoids N+1):

@Query("SELECT e FROM Employee e JOIN FETCH e.department")
List<Employee> findAllWithDepartment();

Parameters: Positional vs Named​

Positional Parameters (?1, ?2)​

@Query("SELECT p FROM Product p WHERE p.price BETWEEN ?1 AND ?2")
List<Product> findByPriceRange(Double min, Double max);

Problem: Order matters, not clear

Named Parameters (:name) - Recommended​

@Query("SELECT p FROM Product p WHERE p.price BETWEEN :min AND :max")
List<Product> findByPriceRange(
    @Param("min") Double minPrice,
    @Param("max") Double maxPrice
);

✅ Advantages:

  • Clearer
  • Order independent
  • Fewer errors

Complex Queries​

@Query("SELECT p FROM Product p " +
       "WHERE (:name IS NULL OR p.name LIKE %:name%) " +
       "AND (:minPrice IS NULL OR p.price >= :minPrice) " +
       "AND (:maxPrice IS NULL OR p.price <= :maxPrice) " +
       "AND (:category IS NULL OR p.category = :category)")
List<Product> searchProducts(
    @Param("name") String name,
    @Param("minPrice") Double minPrice,
    @Param("maxPrice") Double maxPrice,
    @Param("category") String category
);

Usage:

// All parameters
productRepo.searchProducts("Laptop", 500.0, 1500.0, "Electronics");

// Only name
productRepo.searchProducts("Laptop", null, null, null);

// Price and category
productRepo.searchProducts(null, 500.0, 1500.0, "Electronics");

Example 2: Aggregations​

// Count
@Query("SELECT COUNT(p) FROM Product p WHERE p.category = :category")
Long countByCategory(@Param("category") String category);

// Sum
@Query("SELECT SUM(p.price) FROM Product p WHERE p.category = :category")
Double sumPricesByCategory(@Param("category") String category);

// Average
@Query("SELECT AVG(p.price) FROM Product p")
Double getAveragePrice();

// Min/Max
@Query("SELECT MIN(p.price), MAX(p.price) FROM Product p")
Object[] getPriceRange();

Example 3: GROUP BY and HAVING​

@Query("SELECT p.category, COUNT(p) FROM Product p " +
       "GROUP BY p.category " +
       "HAVING COUNT(p) > :minCount")
List<Object[]> getCategoriesWithMinProducts(@Param("minCount") Long minCount);

Result:

List<Object[]> results = productRepo.getCategoriesWithMinProducts(5L);
for (Object[] row : results) {
    String category = (String) row[0];
    Long count = (Long) row[1];
    System.out.println(category + ": " + count + " products");
}

Projections: Returning DTOs​

Problem​

You don't want the entire entity, just a few fields:

// ❌ Loads the ENTIRE entity
@Query("SELECT p FROM Product p")
List<Product> findAll();

Solution 1: Object[]​

@Query("SELECT p.name, p.price FROM Product p")
List<Object[]> findNameAndPrice();

Usage:

List<Object[]> results = productRepo.findNameAndPrice();
for (Object[] row : results) {
    String name = (String) row[0];
    Double price = (Double) row[1];
}

❌ Not type-safe!

Solution 2: Interface Projection​

Create an interface:

public interface ProductSummary {
    String getName();
    Double getPrice();
}

Use in @Query:

@Query("SELECT p.name AS name, p.price AS price FROM Product p")
List<ProductSummary> findSummaries();

Usage:

List<ProductSummary> summaries = productRepo.findSummaries();
for (ProductSummary summary : summaries) {
    System.out.println(summary.getName() + ": " + summary.getPrice());
}

✅ Type-safe!

Solution 3: DTO with Constructor​

Create a DTO:

public class ProductDTO {
    private String name;
    private Double price;
    
    public ProductDTO(String name, Double price) {
        this.name = name;
        this.price = price;
    }
    
    // Getters
}

Use in @Query:

@Query("SELECT new com.example.dto.ProductDTO(p.name, p.price) FROM Product p")
List<ProductDTO> findDTOs();

✅ The best! Type-safe and performant!

Native SQL with nativeQuery = true​

When to Use Native SQL?​

Use native SQL for:

  • ✅ DB-specific functions (PostgreSQL, MySQL, etc.)
  • ✅ Very complex queries
  • ✅ Specific optimizations

Avoid for:

  • ❌ Simple queries (use JPQL)
  • ❌ Portability between DBs

Native SQL Syntax​

@Query(value = "SELECT * FROM product WHERE price < :maxPrice", 
       nativeQuery = true)
List<Product> findCheapNative(@Param("maxPrice") Double maxPrice);

Key Points:

  • nativeQuery = true
  • DB table and column names (not Java entities)

Example: PostgreSQL Function​

@Query(value = "SELECT * FROM product " +
               "WHERE LOWER(name) = LOWER(:name)", 
       nativeQuery = true)
List<Product> findByNameCaseInsensitive(@Param("name") String name);

Modification Queries: @Modifying​

UPDATE​

@Modifying
@Transactional
@Query("UPDATE Product p SET p.price = :newPrice WHERE p.id = :id")
int updatePrice(@Param("id") Long id, @Param("newPrice") Double newPrice);

Key Points:

  • @Modifying required
  • @Transactional required
  • Returns the number of rows modified

DELETE​

@Modifying
@Transactional
@Query("DELETE FROM Product p WHERE p.active = false")
int deleteInactiveProducts();

Warning: Cache Inconsistency​

Problem:

Product p = productRepo.findById(1L);  // Price = 100
productRepo.updatePrice(1L, 200.0);    // Updates in DB
// But p.getPrice() still returns 100! (cache)

Solution: Clear the cache

@Modifying(clearAutomatically = true)
@Transactional
@Query("UPDATE Product p SET p.price = :price WHERE p.id = :id")
int updatePrice(@Param("id") Long id, @Param("price") Double price);

Named Queries​

Define in the Entity​

@Entity
@NamedQuery(
    name = "Product.findExpensive",
    query = "SELECT p FROM Product p WHERE p.price > :minPrice"
)
public class Product {
    // ...
}

Use in the Repository​

public interface ProductRepository extends JpaRepository<Product, Long> {
    // Spring automatically finds the NamedQuery!
    List<Product> findExpensive(@Param("minPrice") Double minPrice);
}

Complete Examples​

Complete Product Repository​

public interface ProductRepository extends JpaRepository<Product, Long> {
    
    // 1. Simple search
    @Query("SELECT p FROM Product p WHERE p.name LIKE %:keyword%")
    List<Product> searchByName(@Param("keyword") String keyword);
    
    // 2. Search with join
    @Query("SELECT p FROM Product p JOIN FETCH p.category WHERE p.price < :max")
    List<Product> findCheapWithCategory(@Param("max") Double max);
    
    // 3. Aggregation
    @Query("SELECT p.category.name, COUNT(p), AVG(p.price) " +
           "FROM Product p GROUP BY p.category.name")
    List<Object[]> getStatsByCategory();
    
    // 4. DTO Projection
    @Query("SELECT new com.example.dto.ProductDTO(p.name, p.price) " +
           "FROM Product p WHERE p.active = true")
    List<ProductDTO> findActiveDTOs();
    
    // 5. Native SQL
    @Query(value = "SELECT * FROM product WHERE stock < 10", 
           nativeQuery = true)
    List<Product> findLowStock();
    
    // 6. Modification
    @Modifying
    @Transactional
    @Query("UPDATE Product p SET p.active = false WHERE p.stock = 0")
    int deactivateOutOfStock();
}

Best Practices​

1. Clear Method Names​

// ✅ GOOD
@Query("SELECT p FROM Product p WHERE p.price < :max")
List<Product> findByPriceLessThan(@Param("max") Double max);

// ❌ BAD
@Query("SELECT p FROM Product p WHERE p.price < :max")
List<Product> get(@Param("max") Double max);

2. Named Parameters​

// ✅ GOOD
@Query("... WHERE p.price BETWEEN :min AND :max")
List<Product> find(@Param("min") Double min, @Param("max") Double max);

// ❌ BAD
@Query("... WHERE p.price BETWEEN ?1 AND ?2")
List<Product> find(Double min, Double max);

3. JOIN FETCH for Performance​

// ❌ N+1 Problem
@Query("SELECT e FROM Employee e")
List<Employee> findAll();
// Then e.getDepartment() makes a query per employee!

// ✅ 1 single query
@Query("SELECT e FROM Employee e JOIN FETCH e.department")
List<Employee> findAllWithDepartment();

4. JPQL vs Native SQL​

// ✅ Prefer JPQL (portable)
@Query("SELECT p FROM Product p WHERE p.price < :max")

// ⚠️ Native SQL only if necessary
@Query(value = "SELECT * FROM product WHERE price < :max", nativeQuery = true)

Chapter Summary​

@Query Allows​

✅ Complex queries more readable
✅ JPQL (object-oriented, portable)
✅ Native SQL (DB-specific functions)
✅ Projections (DTOs, interfaces)
✅ Aggregations (COUNT, SUM, AVG)
✅ Modifications (@Modifying)

JPQL Syntax​

@Query("SELECT e FROM Entity e " +
       "WHERE e.field = :param " +
       "ORDER BY e.field ASC")
List<Entity> findMethod(@Param("param") Type param);

Native SQL​

@Query(value = "SELECT * FROM table WHERE column = :param", 
       nativeQuery = true)
List<Entity> findMethod(@Param("param") Type param);

Self-Assessment​

  1. What is the difference between JPQL and SQL?
  2. How to pass named parameters?
  3. What does JOIN FETCH do?
  4. When to use @Modifying?

Answers:

  1. JPQL uses Java entities, SQL uses DB tables
  2. With :paramName and @Param("paramName")
  3. Loads relations in a single query (avoids N+1)
  4. For UPDATE or DELETE

Next Step​

You have mastered @Query! Move on to Pagination and Performance.

➡️ Chapter 4: Pagination and Performance

⬅️ Previous Chapter: Relations

⬅️ Back to Module 6