5 Efficient Ways to Check Record Existence in Spring Boot 3

Environment

Spring Boot 3.5.0, JPA, and Hibernate are used for all examples.

1. Introduction

Checking whether a database row exists is a frequent requirement. Five approaches are demonstrated:

Custom findBy method – returns the full entity, causing unnecessary data transfer.

Query‑by‑Example (QBE) – builds a dynamic query from an example entity; flexible but not optimal for existence checks.

existsBy method – Spring Data native support that translates to a minimal SELECT … LIMIT 1 (or EXISTS) query, offering the best performance.

COUNT aggregation query – counts rows and compares to 1; slightly more work but still fast for a single record.

Native SQL CASE WHEN EXISTS – uses the database’s EXISTS semantics directly; performance depends on index availability and database compatibility.

Recommendation: Use existsBy for most scenarios; resort to QBE for complex dynamic conditions, and consider COUNT or native SQL when an exact count or database‑specific optimization is required.

2. Implementations

2.1 Custom findBy

Repository definition returning an Optional<Account>:

public interface AccountRepository extends JpaRepository<Account, Long> {
    Optional<Account> findByName(String name);
}

Unit test:

assertTrue(accountRepository.findByName("Pack_xg").isPresent());

The generated SQL selects all columns (e.g., SELECT * FROM t_account WHERE name = ?), which is wasteful when only existence is needed.

2.2 Query‑by‑Example (QBE)

Example‑based existence check:

@Test
public void testUsingQBE() {
    Account probe = new Account();
    probe.setName("Pack_xg");
    Example<Account> example = Example.of(probe,
        ExampleMatcher.matching()
            .withIgnorePaths("id")
            .withMatcher("name", ExampleMatcher.GenericPropertyMatchers.exact()));
    assertTrue(accountRepository.exists(example));
}

QBE creates a full‑column SELECT and then evaluates existence, incurring the same overhead as findBy.

2.3 existsBy

Repository method returning a boolean:

public interface AccountRepository extends JpaRepository<Account, Long> {
    boolean existsByName(String name);
}

Unit test:

@Test
public void testExistsBy() {
    assertTrue(accountRepository.existsByName("Pack_xg"));
}

Hibernate translates this to a minimal query such as SELECT 1 FROM t_account WHERE name = ? LIMIT 1, making it the fastest option.

2.4 COUNT aggregation

Custom JPQL that counts rows and compares to 1:

public interface AccountRepository extends JpaRepository<Account, Long> {
    @Query(value = """
        SELECT COUNT(a.id) = 1
        FROM Account a
        WHERE a.name = :name
        """)
    boolean existsByNameWithCount(@Param("name") String name);
}

Unit test:

@Test
public void testExistsByNameWithCount() {
    assertTrue(accountRepository.existsByNameWithCount("Pack_xg"));
}

The aggregation adds a slight overhead, but counting a single row is still very fast.

2.5 Native CASE WHEN EXISTS

Native SQL returning true/false via a CASE expression:

public interface AccountRepository extends JpaRepository<Account, Long>, JpaSpecificationExecutor<Account> {
    @Query(value = """
        SELECT CASE WHEN EXISTS (
            SELECT 1 FROM t_account WHERE name = :name
        ) THEN 'true' ELSE 'false' END
        """, nativeQuery = true)
    boolean existsByNameWithCase(@Param("name") String name);
}

Unit test:

@Test
public void testExistsByNameWithCase() {
    assertTrue(accountRepository.existsByNameWithCase("Pack_xg"));
}

Execution plan without an index shows a table scan; with an index on name it becomes a covering index lookup. Performance is comparable to the existsBy approach, but database‑specific differences should be verified.

3. Performance comparison

Custom findBy – retrieves full entity → higher I/O and memory usage.

QBE – also selects all columns; not optimal for pure existence checks. existsBy – translates to SELECT … LIMIT 1 or EXISTS; minimal data transfer and fastest.

COUNT – adds aggregation but remains fast for a single row.

Native CASE WHEN EXISTS – efficient when proper indexes exist; otherwise similar to a table scan.

4. Recommendation

Prefer existsBy for its simplicity and efficiency. Use QBE only when you need complex, dynamically built conditions. Choose COUNT or native CASE WHEN EXISTS when you require an exact count or want to leverage specific database features.