Spring Data JPA & Hibernate · Chapter 02

Repository Methods (Derived Queries)

How Spring Data parses method names into queries, prefixes like findBy, existsBy, countBy and deleteBy, Top and First, Distinct, IgnoreCase, And and Or, supported return types, and the pitfalls of derived queries.

◷ 6 min read§ 19 sections? 6 interview questions{ } 18 code examples

#Introduction

A large share of everyday database access in Spring applications is written as repository method names instead of JPQL or SQL. Spring Data reads the method name and builds the query for you.

Instead of writing:

SQL

SELECT * FROM employee WHERE department = 'IT';

you declare:

Java

List<Employee> findByDepartment(String department);

#How It Works

Application starts

Spring scans repository interfaces

Parses each method name into a query — fails fast if a property doesn't exist

Creates a repository proxy

At call time: the JPQL query runs through Hibernate

Hibernate generates SQL for the database

findByDepartment is split into find (the subject), By (the separator) and Department (the criteria) → select e from Employee e where e.department = ?1.

#The Sample Entity

Java

@Entity
public class Employee {
    @Id
    @GeneratedValue
    private Integer id;
    private String name;
    private String email;
    private String department;
    private BigDecimal salary;
    private Integer age;
    private Boolean active;
    private LocalDate joiningDate;
}

#Naming Rules

Text

<prefix>[Distinct|TopN|FirstN]By<Property><Keyword>[And|Or<Property><Keyword>…][OrderBy<Property>Asc|Desc]

Java

findByName(String name)
findBySalaryGreaterThan(BigDecimal salary)
findByAgeBetween(Integer min, Integer max)
findByDepartmentAndSalary(String dept, BigDecimal salary)
findByNameIgnoreCase(String name)

#Prefixes

Prefix	Purpose
`findBy`, `readBy`, `getBy`, `queryBy`, `searchBy`, `streamBy`	Retrieve data — functionally equivalent
`existsBy`	`boolean` — does any row match?
`countBy`	`long` — how many rows match?
`deleteBy`, `removeBy`	Delete matching entities

#findBy

Java

List<Employee> findByDepartment(String department);

Java

List<Employee> list = repository.findByDepartment("IT");

For a collection return type, no matching rows → an empty list — never null. One row gives a list with one element.

#findById and Optional

Java

Optional<Employee> emp = repository.findById(10);   // built into CrudRepository
emp.ifPresent(System.out::println);
Employee e = repository.findById(10).orElseThrow(() -> new EmployeeNotFoundException(10));

Optional makes "the row might not exist" explicit.

Single-Result Methods

Java

Optional<Employee> findByEmail(String email);   // ✅ preferred
Employee findByEmail(String email);              // returns null if nothing matches

#existsBy

Java

boolean existsByEmail(String email);

Java

if (repository.existsByEmail(email)) {
    throw new DuplicateEmailException(email);
}

Spring generates an efficient existence check (it selects a single row with a limit) rather than loading entities. Used for registration, duplicate checks and validation.

#countBy

Java

long countByDepartment(String department);

Generated SQL

select count(e1_0.id) from employee e1_0 where e1_0.department=?

#deleteBy and removeBy

Java

@Transactional
long deleteByDepartment(String department);   // returns how many were deleted

removeBy… is identical to deleteBy….

#Top and First

Java

Optional<Employee> findTopByDepartment(String department);       // LIMIT 1
Optional<Employee> findFirstByOrderByJoiningDateAsc();            // earliest joiner
Optional<Employee> findTopByOrderBySalaryDesc();                  // highest paid
List<Employee> findTop3ByOrderBySalaryDesc();                     // top 3 salaries
List<Employee> findFirst5ByOrderByAgeAsc();                       // 5 youngest

Generated SQL for findTop3ByOrderBySalaryDesc

select … from employee e1_0 order by e1_0.salary desc fetch first ? rows only

Top and First are interchangeable. Without an OrderBy, "first" is whatever the database returns first — always add ordering.

#Distinct

Java

List<Employee> findDistinctByDepartment(String department);

Produces select distinct e from Employee e where …. Most useful when joining collections (which can return duplicate parent rows).

#IgnoreCase

Java

List<Employee> findByNameIgnoreCase(String name);
List<Employee> findByDepartmentAllIgnoreCase(String department);   // AllIgnoreCase = every String criterion

Spring generates upper(e.name) = upper(?), matching John, JOHN, john and JoHn.

#And / Or

Java

List<Employee> findByDepartmentAndSalary(String dept, BigDecimal salary);   // WHERE department=? AND salary=?
List<Employee> findByDepartmentOrSalary(String dept, BigDecimal salary);    // WHERE department=? OR salary=?
List<Employee> findByDepartmentAndSalaryAndActive(String dept, BigDecimal salary, Boolean active);

Parameters are bound in the order they appear in the method name.

#Return Types

Return type	When
`Employee` / `Optional<Employee>`	At most one result
`List<Employee>`, `Set<Employee>`, `Collection<Employee>`	Many results
`Page<Employee>` / `Slice<Employee>`	With a `Pageable` parameter (see Pagination & Sorting)
`Stream<Employee>`	Process large results incrementally — must run in a transaction and be closed (try-with-resources)
A projection interface, record or DTO	Only some columns (see Projections, Specifications & Auditing)
`long`, `boolean`	`countBy`, `existsBy`

Java

@Transactional(readOnly = true)
public void export() {
    try (Stream<Employee> stream = repository.streamAllByActiveTrue()) {
        stream.forEach(csvWriter::write);
    }
}

#Property Names Must Match the Entity

Java

private String department;   // entity field

findByDepartment(...)  // ✅
findByDept(...)        // ❌ startup error

Output

No property 'dept' found for type 'Employee'

Errors appear at startup, not at runtime — a nice safety net.

#Interview Questions

Q1How does Spring Data JPA generate repository methods?

At startup it creates a proxy for each repository interface, parses each method name into a query (validating property names), and at runtime executes it through the JPA provider (Hibernate), which generates the SQL.

Q2What happens if a method references a non-existent property?

The application fails to start with an exception saying no property with that name exists on the entity.

Q3What's the difference between findBy, readBy and getBy?

For derived queries they're functionally equivalent — it's a naming preference.

Q4findTopBy vs findFirstBy?

They're the same; both limit the results, and accept a number (findTop3By…) and ordering.

Q5How does a derived deleteBy method work?

It loads the matching entities and removes them one by one (so cascades and callbacks run), returning the count — it isn't a single bulk DELETE. It must run in a transaction.

Q6When should you avoid derived query methods?

When names become long and hard to read, or you need joins across several entities, aggregations, subqueries or database-specific SQL — use @Query, native queries or Specifications instead.

#Best Practices

Use derived methods for simple lookups.
Return Optional<T> for at most one result.
Use Page/Slice with Pageable for large results.
Switch to @Query or Specifications when names get unwieldy.
Back existsBy checks with unique constraints; use bulk @Modifying queries for mass deletes.

#Key Takeaways

Spring Data builds queries from method names: prefix + By + property + keyword.
Collection results are never null; single results can be Optional.
existsBy and countBy are efficient; derived deleteBy deletes entity by entity.
Top/First limit results — combine with OrderBy.
Property names are validated at startup.