Spring Boot Custom Queries

Built-in repository methods such as findAll and save handle simple tasks. Real applications need sharper questions: books under a certain price, customers from one city, orders placed last week. Custom queries answer those questions with very little code.

Spring Data JPA offers three ways to write them: derived method names, the @Query annotation with JPQL, and native SQL.

The Librarian Idea

A visitor walks up to a librarian and asks for "all novels by Mia Stone priced under 20, cheapest first." The librarian understands the request and returns the matching books. A custom query works the same way. The developer states the request, and Spring Data fetches the matching rows.

 Request in plain words
 "Books by Mia Stone under 20, cheapest first"
          |
          v
 findByAuthorAndPriceLessThanOrderByPriceAsc("Mia Stone", 20)
          |
          v
 SELECT * FROM book WHERE author = ? AND price < ? ORDER BY price ASC

Derived Query Methods

Spring Data reads a method name, splits it into keywords, and builds the query. No SQL is required.

public interface BookRepository extends JpaRepository<Book, Long> {

    List<Book> findByAuthor(String author);

    List<Book> findByPriceLessThan(double price);

    List<Book> findByTitleContaining(String word);

    List<Book> findByAuthorAndPriceLessThanOrderByPriceAsc(String author, double price);

    boolean existsByTitle(String title);

    long countByAuthor(String author);
}

Common Keywords

Keyword	Meaning	SQL Idea
And / Or	Join conditions	AND / OR
LessThan / GreaterThan	Compare numbers	< / >
Between	Range check	BETWEEN
Containing	Partial text match	LIKE %word%
IgnoreCase	Ignore capital letters	UPPER()
OrderBy	Sort results	ORDER BY
IsNull	Find empty values	IS NULL

Derived names work best for short queries. A name with five or six keywords becomes hard to read, and the @Query annotation fits better.

The @Query Annotation with JPQL

JPQL looks like SQL but uses class and field names instead of table and column names.

@Query("SELECT b FROM Book b WHERE b.author = :author AND b.price < :maxPrice")
List<Book> findCheapBooks(@Param("author") String author,
                          @Param("maxPrice") double maxPrice);

The colon marks a named parameter, and @Param links it to the method argument. Named parameters read clearly and avoid position mistakes.

Searching with LIKE

@Query("SELECT b FROM Book b WHERE LOWER(b.title) LIKE LOWER(CONCAT('%', :keyword, '%'))")
List<Book> search(@Param("keyword") String keyword);

Native SQL Queries

Native queries send plain SQL straight to the database. Use them for database-specific features that JPQL cannot express.

@Query(value = "SELECT * FROM book WHERE price BETWEEN :low AND :high", nativeQuery = true)
List<Book> findInRange(@Param("low") double low, @Param("high") double high);

Native SQL ties the code to one database product. Prefer JPQL when both options work.

Update and Delete Queries

Queries that change data need two extra annotations.

@Modifying
@Transactional
@Query("UPDATE Book b SET b.price = b.price * 0.9 WHERE b.author = :author")
int applyDiscount(@Param("author") String author);

@Modifying tells Spring the query changes rows. The method returns the number of rows affected.

Choosing Fields with Projections

A projection returns only the columns a screen needs. An interface lists the getters to fetch.

public interface BookSummary {
    String getTitle();
    double getPrice();
}

List<BookSummary> findByAuthor(String author);

Smaller results use less memory and travel faster over the network.

Choosing the Right Option

Situation	Best Choice
One or two simple conditions	Derived method name
Joins, long conditions, readable intent	@Query with JPQL
Database-specific functions	Native query
Only a few columns needed	Projection

Reading Query Method Names

Read a derived method name from left to right. The word find starts the query. The word By opens the condition list. Each property name becomes a column check, and keywords such as And, Or, and OrderBy join or arrange the checks. Spring Data validates the name at startup, so a misspelled property stops the application early with a clear message. Rename methods carefully, because the name itself holds the query.

Best Practices

• Pass values through parameters and never join user text into the query string, which blocks SQL injection.
• Test each query against sample data before release.
• Turn on SQL logging to inspect generated statements.
• Add database indexes for columns used often in conditions.
• Keep method names meaningful, such as findCheapBooks, so other developers grasp the goal quickly.

Custom queries turn business questions into short, safe code. Mastering the three styles gives full control over data retrieval.