Module 3·Data & Persistence

Queries: Derived, JPQL & Native

Query methods generated from their names, custom @Query in JPQL, and native SQL when you need it.

16 min readIntermediate

On this page

findAll and findById cover the basics, but real features need real queries: "books by this author," "books with copies available," "search by title." Spring Data gives you three ways to express these, from zero-code to full control.

1. Derived query methods

The headline trick: Spring Data reads the method name and writes the query for you. Add a method to the repository interface - no body, no annotation:

interface BookRepository extends JpaRepository<Book, Long> {

    List<Book> findByAuthor(String author);              // WHERE author = ?

    List<Book> findByCopiesGreaterThan(int min);         // WHERE copies > ?

    Optional<Book> findByIsbn(String isbn);              // WHERE isbn = ?

    List<Book> findByTitleContainingIgnoreCase(String q); // WHERE lower(title) LIKE %?%
}

The grammar is composable: findBy + property + optional keyword (GreaterThan, Containing, Between, OrderBy…). Spring parses it and generates the query.

Naming the dish orders it

Derived queries are like a restaurant where naming the dish is placing the order. You don't write a recipe - you say "the grilled-salmon-with-lemon," and the kitchen knows exactly what to make. findByAuthorOrderByTitle is that spoken order: Spring "cooks" the SQL from the name.

2. @Query for anything more complex

When a method name would get absurd, write the query yourself in JPQL (a query language over your entities, not tables):

@Query("SELECT b FROM Book b WHERE b.copies > 0 AND b.author = :author")
List<Book> availableByAuthor(@Param("author") String author);

JPQL uses entity and field names (Book, b.copies), so it stays close to your object model and is portable across databases.

3. Native SQL when you must

For database-specific features or hand-tuned performance, drop to native SQL:

@Query(value = "SELECT * FROM book WHERE copies > 0", nativeQuery = true)
List<Book> availableNative();

Reach for the simplest that works

Prefer a derived method for straightforward finders; use @Query (JPQL) when the logic outgrows a readable name; drop to native SQL only for database-specific needs. Most of a repository is derived methods.

Pick the technique

For each BookVault query, choose derived / JPQL / native and sketch it:

Find a book by its ISBN.
Find books whose title contains a search term, case-insensitively.
Find books by a given author that still have copies available.

What does the repository method List<Book> findByAuthor(String author) require you to implement?

Key takeaways

Derived query methods generate queries from the method name (findByAuthor, findByTitleContainingIgnoreCase) - no body needed.
The name grammar composes properties with keywords like GreaterThan, Containing, Between, and OrderBy.
Use @Query with JPQL (entity/field names, database-portable) when a method name would be unwieldy.
Use native SQL (nativeQuery = true) only for database-specific features or hand-tuned performance.
Favor the simplest technique that reads clearly; most repositories are mostly derived methods.

Was this lesson helpful?

Edit this page on GitHub