Custom Queries with Spring Data JPA [Java Spring Boot Mastery Series – Part 7]

Spring Data JPA allows you to define custom queries using method names or the @Query annotation.

🛠️ 1. Derived Query Methods

Spring Data JPA can derive queries based on method names:

public interface OrderRepository extends JpaRepository<Order, Long> {
    List<Order> findByOrderNumber(String orderNumber);
    List<Order> findByUserUsername(String username);
    List<Order> findByOrderNumberContainingIgnoreCase(String keyword);
}

🔍 Description:

• findByOrderNumber will generate: SELECT o FROM Order o WHERE o.orderNumber = ?1
• findByUserUsername navigates through a relation.
• ContainingIgnoreCase enables partial and case-insensitive search.

💡 Tip:

Use descriptive method names for clarity.

📜 2. Custom Queries with @Query

You can write JPQL or native SQL inside the @Query annotation.

JPQL Example:

@Query("SELECT o FROM Order o WHERE o.user.username = :username")
List<Order> getOrdersByUsername(@Param("username") String username);

Native SQL Example:

@Query(value = "SELECT * FROM orders WHERE user_id = ?1", nativeQuery = true)
List<Order> getOrdersByUserIdNative(Long userId);

✅ 3. Pagination and Sorting

Spring Data provides Pageable and Sort support.

Example

Page<Order> findByUserUsername(String username, Pageable pageable);

Usage in Service Layer:

Pageable pageable = PageRequest.of(0, 10, Sort.by("orderNumber"));
Page<Order> orders = orderRepository.findByUserUsername("john", pageable);

🧪 4. Dynamic Queries with Specifications (Advanced)

You can dynamically build queries using JpaSpecificationExecutor.

Example Setup:

public interface OrderRepository extends JpaRepository<Order, Long>, JpaSpecificationExecutor<Order> {}

Specification<Order> hasUser(String username) = (root, query, builder) ->
    builder.equal(root.get("user").get("username"), username);

➡️ Next Up: Part 8 – Integrating MySQL and Spring Boot