Derived Query Methods

Queries from Method Names

Declare a method whose name follows the Spring Data rules, and the query is generated for you. No implementation, no SQL:

public interface ProductRepository extends JpaRepository<Product, Long> {

    Optional<Product> findBySku(String sku);

    List<Product> findByCategory(String category);

    List<Product> findByCategoryAndPriceLessThan(String category, BigDecimal maxPrice);

    List<Product> findByNameContainingIgnoreCase(String text);        // search box

    List<Product> findByPriceBetweenOrderByPriceAsc(BigDecimal min, BigDecimal max);

    List<Product> findByStatusIn(Collection<ProductStatus> statuses);

    List<Product> findTop5ByCategoryOrderByCreatedAtDesc(String category);   // "new arrivals"

    long countByCategory(String category);

    boolean existsBySku(String sku);

    List<Product> findByStockLessThanEqual(int threshold);            // low-stock alert
}

Generated SQL

Request and SQL

Calling findByNameContainingIgnoreCase("boat"):

curl "localhost:8080/api/products/search?q=boat"

Example output

Hibernate: select p1_0.id,p1_0.category,p1_0.created_at,p1_0.description,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where upper(p1_0.name) like upper(?) escape '\'
TRACE o.h.orm.jdbc.bind : binding parameter (1:VARCHAR) <- [%boat%]

HTTP/1.1 200 OK
[{"id":42,"name":"Boat Rockerz 450","price":1499.00},{"id":57,"name":"Boat Airdopes 141","price":1099.00}]

Keyword Reference

Keyword	Example	JPQL condition
`And`, `Or`	`findByCategoryAndStatus`	`category = ?1 and status = ?2`
`Is`, `Equals` (optional)	`findByNameIs`	`name = ?1`
`LessThan`, `LessThanEqual`	`findByPriceLessThan`	`price < ?1`
`GreaterThan`, `GreaterThanEqual`	`findByStockGreaterThan`	`stock > ?1`
`Between`	`findByPriceBetween`	`price between ?1 and ?2`
`IsNull`, `IsNotNull`	`findByDescriptionIsNull`	`description is null`
`Like`, `Containing`, `StartingWith`, `EndingWith`	`findByNameStartingWith`	`name like ?1%`
`In`, `NotIn`	`findByStatusIn`	`status in ?1`
`Not`	`findByStatusNot`	`status <> ?1`
`True`, `False`	`findByActiveTrue`	`active = true`
`IgnoreCase`	`findByNameIgnoreCase`	`upper(name) = upper(?1)`
`OrderBy...Asc/Desc`	`findByCategoryOrderByPriceDesc`	`order by price desc`
`First`, `Top`, `TopN`	`findFirstBy...`, `findTop10By...`	limit 1 / limit 10

Prefixes: findBy, readBy, getBy, queryBy (same meaning), countBy, existsBy, deleteBy.

When Method Names Get Too Long

findByCategoryAndStatusAndPriceBetweenAndStockGreaterThanOrderByPriceAsc works, but nobody wants to read it. For anything more than two or three conditions, write the query with @Query and JPQL — see Queries in the medium level.

Note: A typo in a property name (findByCatgory) is caught at startup: No property 'catgory' found for type 'Product'. The app will not start, which is much better than a runtime error.