skip to content

What do the existsBy, countBy, and deleteBy derived prefixes do, and what must you add for derived deletes to work correctly?

level: seniorimportance: should knowfreq 55%

answer

  1. existsBy = cheap SELECT 1 probe
  2. countBy = SELECT count(*)
  3. deleteBy needs @Transactional
  4. derived delete = load-then-remove (callbacks fire)
  5. bulk @Modifying skips lifecycle/cascades

basics

~10 s

existsBy returns a boolean (efficient existence check), countBy returns a long count, and deleteBy/removeBy delete matching rows. Derived deletes are modifying queries, so they must run inside a transaction (@Transactional).

solid answer

~50 s

Beyond `findBy`, the derivation engine supports operation prefixes: `existsBy...` returns `boolean` via a cheap existence probe (SELECT 1 / limited count), avoiding loading the entity; `countBy...` returns `long`; `deleteBy...` / `removeBy...` delete matching rows and return either `void` or the deleted count. The crucial difference: `deleteBy` is a **modifying** operation and requires an active transaction — call it from a `@Transactional` service, or annotate the repository method. There are also two DIFFERENT delete strategies. A derived `deleteBy...` (or the `deleteAll`-style calls) **loads the entities and removes them one by one**, so JPA lifecycle callbacks (`@PreRemove`) and cascades fire and the deleted count is accurate. A bulk `@Modifying @Query("delete ...")` issues a single SQL DELETE that bypasses the persistence context, callbacks, and cascades — faster but skips lifecycle and can leave the first-level cache stale. Choose based on whether cascades/callbacks matter.

code

java · 23 lines
java
public interface AccountRepository extends JpaRepository<Account, Long> {

    boolean existsByEmail(String email);          // SELECT 1 ... LIMIT 1
    long    countByStatus(Status status);         // SELECT count(*)

    // Derived delete: loads matching entities, fires @PreRemove + cascades.
    // Returns how many were deleted. MUST run in a transaction.
    long deleteByStatus(Status status);

    // Bulk delete: one SQL DELETE, skips callbacks/cascades.
    @Modifying(clearAutomatically = true)
    @Query("delete from Account a where a.status = :s")
    int bulkDeleteByStatus(@Param("s") Status status);
}

@Service
class AccountService {
    private final AccountRepository repo;
    AccountService(AccountRepository repo) { this.repo = repo; }

    @Transactional               // required for the derived delete
    public long purge(Status s) { return repo.deleteByStatus(s); }
}

go deeper

for a junior

Know existsBy returns boolean, countBy returns a count, deleteBy deletes.

for a middle

Add that deleteBy requires @Transactional and existsBy avoids loading the entity.

for a senior

Contrast derived load-then-remove (callbacks + cascades) with bulk @Modifying delete (single SQL, skips lifecycle) and the stale-context / clearAutomatically issue.

for a principal

Reason about mass-delete performance, FK ordering, cascade correctness, and choosing per-operation between derived and bulk semantics.

## The operation prefixes Query derivation isn't only `findBy`. The subject keyword selects the operation: ### existsBy `boolean existsByEmail(String email)` — returns whether any row matches. Spring implements it as an **existence probe** (e.g. `SELECT 1 ... LIMIT 1` or a limited count), so it does **not** hydrate the entity. Prefer it over `findBy...().isPresent()`, which loads the whole row. ### countBy `long countByStatus(Status s)` — emits `SELECT count(*) ... WHERE`. Returns a `long`. ### deleteBy / removeBy `deleteByStatus(Status s)` — deletes matching rows. `remove` and `delete` are synonyms. Return type may be `void`, `long` (number deleted), or a `List<T>`/`int` in some versions. ## Transactions are mandatory for delete Repository **read** methods get a default read-only transaction from `SimpleJpaRepository`, but a **derived delete** mutates data. If no transaction is active you get `TransactionRequiredException` (or `InvalidDataAccessApiUsageException`). Fixes: - Call it from a `@Transactional` service method (normal case), OR - Put `@Transactional` on the repository method itself. ## Two very different delete mechanics This is the senior-level point: | | Derived `deleteBy...` | `@Modifying @Query("delete ...")` | |---|---|---| | Mechanism | Load matching entities, then `EntityManager.remove` each | Single bulk SQL `DELETE` | | Lifecycle callbacks (`@PreRemove`/`@PostRemove`) | Fire | **Skipped** | | Cascades / orphan removal | Applied | **Skipped** | | Performance | N selects + N deletes | One statement, fastest | | Persistence context | Kept consistent | Can go **stale**; add `clearAutomatically`/`flushAutomatically` | So a derived `deleteByAuthorId` on an `Author` with cascading children will delete children too and run callbacks; a bulk `@Modifying` delete will violate FK constraints unless you also delete children explicitly. ## Return-count nuance Derived deletes return the **actual number of entities removed** (because they load-then-remove). Bulk `@Modifying` deletes return the JDBC row-update count. They usually agree but can differ when cascades add rows the derived path counts differently. ## Gotchas - `existsBy` returning `Boolean` vs primitive `boolean` — both fine, but null never happens (exists is always true/false). - Combining `@Modifying(clearAutomatically = true)` matters so the L1 cache doesn't serve deleted entities afterward. - Derived delete of a large table is slow (loads every row) — for mass deletes prefer bulk `@Modifying`. - `deleteBy` still needs the criteria to resolve to real properties, same fail-fast rules as `findBy`.

  • Why prefer existsByX over findByX().isPresent()?
    existsBy runs a lightweight existence probe (SELECT 1 / limited count) and never hydrates the entity, while findBy loads the full row and maps it into a managed object just to check presence.
  • Your derived deleteByAuthorId ran and cascaded to child comments, but a colleague's @Modifying delete threw a foreign-key violation. Why?
    The derived delete loads each Author and removes it via the EntityManager, so JPA cascade/orphanRemoval deletes the children first. The bulk @Modifying DELETE bypasses the persistence context and cascades, hitting the FK constraint because children still reference the row.
  • After a bulk @Modifying delete, a subsequent find returns a stale entity. Fix?
    The bulk delete didn't touch the first-level cache. Use @Modifying(clearAutomatically = true) (and often flushAutomatically = true) so the persistence context is cleared.

saying these in an interview costs you the question

  • Saying existsBy loads the entity
  • Forgetting derived deletes need a transaction
  • Claiming @Modifying bulk delete fires @PreRemove and cascades
  • Assuming derived delete and bulk delete are interchangeable for entities with cascades

context

resolving links…