Custom Query

Introduction

When the built-in findBy* queries are not enough, Spring Data JPA provides the @Query annotation for writing custom queries using JPQL or native SQL.

@Query with JPQL

JPQL (Java Persistence Query Language) uses entity names, not table names.

public interface ProductRepository extends JpaRepository<Product, Long> {

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

    @Query("SELECT p FROM Product p WHERE p.name LIKE %:keyword% OR p.description LIKE %:keyword%")
    Page<Product> searchProducts(@Param("keyword") String keyword, Pageable pageable);

    @Query("SELECT p FROM Product p WHERE p.stock > 0 ORDER BY p.name ASC")
    List<Product> findAvailableProducts();
}

Native Queries

Use native queries when you need database-specific features.

public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query(value = "SELECT * FROM product WHERE MATCH(name, description) AGAINST (:keyword IN BOOLEAN MODE)",
           nativeQuery = true)
    List<Product> fullTextSearch(@Param("keyword") String keyword);

    @Query(value = "SELECT p.*, k.name as category_name FROM product p " +
           "INNER JOIN category k ON p.category_id = k.id " +
           "WHERE p.price > :minPrice",
           nativeQuery = true)
    List<Object[]> findProductWithCategory(@Param("minPrice") double minPrice);
}

Parameter Binding

@Query("SELECT p FROM Product p WHERE p.name = :name AND p.price <= :maxPrice")
List<Product> findByNameAndMaxPrice(
    @Param("name") String name,
    @Param("maxPrice") double maxPrice);

// Using SpEL
@Query("SELECT p FROM Product p WHERE p.stock > :#{#minStock}")
List<Product> findStockGreaterThan(@Param("minStock") int minStock);

@Modifying

Required for UPDATE and DELETE operations.

public interface ProductRepository extends JpaRepository<Product, Long> {

    @Modifying
    @Query("UPDATE Product p SET p.stock = p.stock - :quantity WHERE p.id = :id")
    int reduceStock(@Param("id") Long id, @Param("quantity") int quantity);

    @Modifying
    @Query("DELETE FROM Product p WHERE p.stock = 0")
    int deleteOutOfStock();

    @Modifying
    @Query("UPDATE Product p SET p.price = p.price * :percentage WHERE p.category.id = :categoryId")
    int updatePriceByCategory(@Param("categoryId") Long categoryId, @Param("percentage") double percentage);
}

Important: @Modifying must be combined with @Transactional in the service layer.

@Service
@Transactional
public class ProductService {

    public void decreaseStock(Long id, int quantity) {
        productRepository.reduceStock(id, quantity);
    }
}

Named Queries

A query is defined on the entity and referenced from the repository.

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

public interface ProductRepository extends JpaRepository<Product, Long> {

    List<Product> findByName(@Param("name") String name);
}

Complex Queries

public interface ProductRepository extends JpaRepository<Product, Long> {

    // Aggregation
    @Query("SELECT p.category.name, COUNT(p), AVG(p.price) " +
           "FROM Product p GROUP BY p.category.name")
    List<Object[]> statisticsPerCategory();

    // Subquery
    @Query("SELECT p FROM Product p WHERE p.price > " +
           "(SELECT AVG(p2.price) FROM Product p2)")
    List<Product> findAboveAveragePrice();

    // JOIN
    @Query("SELECT p FROM Product p JOIN p.category k WHERE k.name = :category")
    List<Product> findByCategoryName(@Param("category") String category);
}

Summary

• @Query with JPQL uses entity names.
• nativeQuery = true for raw SQL.
• @Modifying is required for UPDATE/DELETE operations.

By mastering these 10 chapters, you have a solid foundation for building REST APIs with Spring Boot — from controllers to database access. Next, explore advanced topics such as security, testing, and deployment!