Chapter 4.3☕ 16 min read

Custom Queries: Derived Methods & @Query

Name your method right, and Spring writes the SQL for you.

01The Concept: Method Name as a Query

JpaRepository gives us findById and findAll. But what if we want to find all books written by "Deva"? Or books with "Spring" in the title? Spring Data JPA allows us to write custom queries without writing SQL, using Derived Query Methods.

The Hyderabad Irani Cafe Order Analogy:

At an Irani Cafe, you don't go to the kitchen and write a recipe for your chai. You just shout: "Ek chai, less sugar!" The waiter understands the instruction and brings exactly what you want.

In Spring Data JPA, your method name is the instruction. If you name your method findByAuthor, Spring parses the English words, understands you want to SELECT * FROM Book WHERE author = ?, and writes the SQL for you automatically!

02Technical Explanation

Derived Methods: You create methods inside your interface starting with findBy, readBy, or getBy. You append the exact field name (e.g., Author). Spring writes the SQL.
Keywords: You can use And, Or, Between, LessThan, GreaterThan, Like.
• findByTitleContaining(String keyword) → WHERE title LIKE '%keyword%'
@Query: If the query gets too complex for a method name, you can write custom JPQL or Native SQL using the @Query annotation.

03Full Working Code: Derived Methods and @Query

Let's add custom finder methods to our BookRepository.

package com.devinhyderabad;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;

public interface BookRepository extends JpaRepository<Book, Long> {

    // 1. Derived Method: Spring translates this to SELECT * FROM Book WHERE author = ?
    List<Book> findByAuthor(String author);

    // 2. Derived Method with multiple conditions: WHERE author = ? AND title LIKE ?
    List<Book> findByAuthorAndTitleContaining(String author, String titleKeyword);

    // 3. Custom JPQL Query using @Query (More flexible)
    // JPQL uses Entity names (Book), not table names (book)
    @Query("SELECT b FROM Book b WHERE b.author = :authorName")
    List<Book> findBooksByAuthorUsingJPQL(@Param("authorName") String author);
}

04Controller Usage

@RestController
@RequestMapping("/api/books")
class BookController {

    private final BookRepository bookRepository;
    public BookController(BookRepository bookRepository) { this.bookRepository = bookRepository; }

    // Using Derived Method
    @GetMapping("/search/author/{author}")
    public List<Book> searchByAuthor(@PathVariable String author) {
        return bookRepository.findByAuthor(author);
    }

    // Using @Query JPQL
    @GetMapping("/search/jpql/{author}")
    public List<Book> searchByJPQL(@PathVariable String author) {
        return bookRepository.findBooksByAuthorUsingJPQL(author);
    }
}

05Why It Matters / Interview Note

Interview Question: "What is the difference between Derived Methods and @Query in Spring Data JPA?"

Answer: Derived methods parse the method name to generate SQL. They are great for simple queries. However, for complex queries involving JOINs or subqueries, method names become unreadable (findByUserAddressCityName). For these, we use @Query to write explicit JPQL or native SQL, which is more readable and performant.

Enterprise Note: While derived methods are easy, they can sometimes generate inefficient SQL. In performance-critical enterprise applications, developers prefer @Query or Spring Data JPA Projections to fetch only the exact columns needed, reducing network traffic between the database and the Java app.

Key Takeaways

✅ Derived methods parse English method names to generate SQL automatically
✅ Keywords like And, Or, Containing, LessThan enable complex queries without SQL
✅ @Query annotation allows custom JPQL or native SQL for complex needs
✅ Prefer @Query for performance-critical queries to avoid inefficient generated SQL

PreviousJpaRepository CRUD Setup 📋 All Chapters Next ChapterEntity Relationships (JPA) →