Derived queries: tests

1. Derived queries: when do you need tests

If you have already worked with Spring Data, you have probably felt this: “I wrote a method named findBySomethingAndSomethingElse — and it magically worked. That means I do not need to test it, because the ‘framework did it’.” This is where the framework gets a chance to quietly laugh. Because a generated query is still part of your application’s behavior, which means it can still break.

First, a derived method is a reading contract in your domain. In ContentHub, such reads directly affect article visibility: for example, “find an article by slug only if it is published” is already about safety and business meaning, not “just a query.” Second, a method name is a string (albeit typed through Java symbols), and a mistake in a field name may surface only when the context starts or at the first call. Third, even if the method is assembled correctly, it may return something other than what you expect because of data nuances, sorting, statuses, and “almost matching” records.

To avoid staying at the level of philosophy, let us remember a practical idea: we test derived queries not because we doubt Spring Data, but because we doubt our task definition and how it is encoded in the method name.

A small diagram of what we actually verify:

flowchart TD
    A["Derived method name as a reading contract"] --> B["Prepare contrasting data (matches / almost matches / extra)"]
    B --> C["flush + clear (make DB behavior visible)"]
    C --> D["Call the repository"]
    D --> E["Assert: contents + order + empty/present"]

2. Derived method: reading the name

The word “derived” here means “derived.” Spring Data takes the method name and “derives” a query from it. To a beginner, this looks like “Hogwarts for Java” magic, but in practice it is just a naming convention that works predictably enough. And precisely because it is predictable, it is convenient to test.

When you see a name like findAllByAuthorUsernameAndStatusOrderByUpdatedAtDesc, this is not just a long string that makes you want to cry. It is a whole set of promises: which fields take part in filtering, what type of result is expected, whether there is sorting, and which kind. The test should verify exactly those promises — no more, no less.

Here is a small “cheat sheet” in table form so we do not turn this into a list of spells:

A particularly important nuance from the ContentHub domain: an article’s slug is unique. This means that “contrasting data” for a query by slug will not be built via “a second article with the same slug, but a different status” (the database will not allow that), but rather via “there is an article with this slug, but the status is different — so the query must return empty.”

3. Minimal ContentHub context

We are not building new production code right now; we are reading and testing what already exists. But to make the examples understandable, we need to agree on which fields we use in queries. In ContentHub, these are completely real-world fields: slug, status, authorUsername, updatedAt. This set is already enough for filtering and sorting, and for public listings we will also add publishedAt and paginated reads.

Below is a compact repository example that reflects today’s topic. For this topic, the method signatures are what matter: Spring Data uses them to build the query, and the test then proves that this contract is actually fulfilled.

import java.util.List;
import java.util.Optional;
import org.springframework.data.repository.Repository;

public interface ArticleRepository extends Repository<Article, Long> {

    // Contract: by (slug + status) we return at most one article.
    // Important for "public" reads: the slug may match, but the status must fit.
    Optional<Article> findBySlugAndStatus(String slug, ArticleStatus status);

    // Contract: filtering by author and status + sorting is part of the method behavior.
    // The test must prove both filters and order.
    List<Article> findAllByAuthorUsernameAndStatusOrderByUpdatedAtDesc(
            String authorUsername,
            ArticleStatus status
    );
}

Pay attention to two things. The first method returns Optional<Article>, and the test must honestly verify “found/not found,” not “well, it should be there somewhere.” The second method returns a list and already contains sorting in its name (OrderByUpdatedAtDesc), so checking only the list size is, to put it mildly, not enough. It is like checking that a train arrived, but not checking whether it arrived at the right station.

4. Contrasting data for the test

The most common reason for “useless” repository tests is poor test data. The test looks serious, the annotations are correct, @DataJpaTest shines like a New Year garland, but in fact you put exactly one record into the database, ran a query, and were surprised that “it was found.” Such a test checks almost nothing. It mostly checks that Java has not forgotten how to work with strings.

The practical strategy is simple: for a derived query test, you must create a small but contrasting world. It must definitely contain records that should be included in the result, and records that should be filtered out. Among the “extra” ones, it is useful to have “almost matching” records: those that differ by exactly one filter criterion. They prove that the criterion really works and did not match by accident.

In ContentHub terms, this is easy to formulate: if the method filters by author and status, then the database should contain articles by the same author but with another status, and articles by another author but with the same status. If the method sorts by time, then there must be different time values, otherwise the test will check an order that is “already the only one.” In short, the test data must be such that the repository could fail — and then the test will catch it.

5. Example #1: testing findBySlugAndStatus

Let us now do a simple but very characteristic check: a query by slug and status. At first glance it seems strange, because slug is unique. But from the application’s point of view, this is an important safeguard: a public read by slug must not return an article that is not published yet. Even if someone guesses the slug, we do not want to “accidentally” show a DRAFT.

Let us start with the test skeleton. It is typical for @DataJpaTest: we start the slice, autowire the repository and TestEntityManager.

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.boot.test.autoconfigure.orm.jpa.TestEntityManager;

@DataJpaTest
class ArticleRepositoryDerivedQueriesDataJpaTest {

    // The repository is what we are testing (derived queries live in its signatures).
    @Autowired ArticleRepository repository;

    // EntityManager for data preparation (Arrange): persist/flush/clear.
    @Autowired TestEntityManager entityManager;

    @Test
    void findBySlugAndStatus_returnsEmpty_whenStatusDoesNotMatch() {
        // test body
    }
}

Now the most important part is data. To avoid listing dozens of required Article fields in every example, let us assume we have a small test factory (or at least a helper method) that fills in the “minimum required set” with defaults. This is normal practice: right now we need to test query behavior, not do the “write setTitle 200 times” exercise.

Here is an example of such a factory directly in the test (yes, it is a bit longer than 10 lines, but you write it once and do not torture your brain in every test):

import java.time.Instant;

private Article article(String slug, ArticleStatus status, String author, Instant updatedAt, Category category) {
    Article a = new Article();

    // Fields that actually participate in the derived queries from the examples.
    a.setSlug(slug);
    a.setStatus(status);
    a.setAuthorUsername(author);
    a.setUpdatedAt(updatedAt);

    // The "minimum required set" so the entity is valid for saving.
    a.setTitle("Test title");
    a.setSummary("Test summary");
    a.setBody("Test body");
    a.setCategory(category);

    return a;
}

And now the test itself. The idea: there is an article with "spring-boot-testing" in the database, but its status is DRAFT. We ask for PUBLISHED and expect empty. Plus we will add one more “extra” article so the test is not “a world of one record.”

import java.time.Instant;
import java.util.Optional;
import static org.assertj.core.api.Assertions.assertThat;

@Test
void findBySlugAndStatus_returnsEmpty_whenStatusDoesNotMatch() {
    // Arrange: create a category and several articles, including one that is "almost matching".
    Category java = entityManager.persist(new Category("java", "Java"));

    // slug matches, but status does not -> must be filtered out by the query
    entityManager.persist(article(
            "spring-boot-testing",
            ArticleStatus.DRAFT,
            "alice",
            Instant.parse("2026-01-10T10:00:00Z"),
            java
    ));

    // a fully "extra" record, so the test does not pass in an empty world
    entityManager.persist(article(
            "jpa-basics",
            ArticleStatus.PUBLISHED,
            "bob",
            Instant.parse("2026-01-11T10:00:00Z"),
            java
    ));

    // Make the database write happen and read "as from the database", not from the persistence context.
    entityManager.flush();
    entityManager.clear();

    // Act: call the derived method exactly as it will be called in the application.
    Optional<Article> result = repository.findBySlugAndStatus("spring-boot-testing", ArticleStatus.PUBLISHED);

    // Assert: on a status mismatch — empty.
    assertThat(result).isEmpty();
}

Notice how the status filter is “proven” here. We are not trying to create a second article with the same slug (the database would rightfully punish us with a unique constraint). Instead, we create a situation where the slug matches, but the status does not — and verify that the result is empty. Such a test protects public reads from “draft leakage,” even if someone makes a mistake in the code and starts using the wrong repository method.

6. Example #2: OrderByUpdatedAtDesc

Now let us take a slightly more “grown-up” derived method: filtering by author and status plus sorting by updatedAt descending. Such queries in real projects break more often than you might think: sometimes updatedAt is not updated, sometimes the sorting field is misunderstood, sometimes another criterion is added and no one notices that the order has become nondeterministic.

The main idea of this section: if a method name contains OrderBy..., then order checking is not “optional perfectionism,” but part of the contract. And the test must be strict: not “contains such-and-such slugs,” but “contains them in this exact order.”

Let us create contrasting data. We need articles by author "alice" in DRAFT status with different updatedAt values so the order is meaningful. We also need “almost matching” ones: articles by "alice" with another status, and articles by another author with the same status.

import java.time.Instant;
import java.util.List;
import static org.assertj.core.api.Assertions.assertThat;

@Test
void findAllByAuthorAndStatus_ordersByUpdatedAtDesc() {
    // Arrange
    Category java = entityManager.persist(new Category("java", "Java"));

    // Two matching records: the order should be determined only by updatedAt.
    entityManager.persist(article("a-1", ArticleStatus.DRAFT, "alice", Instant.parse("2026-01-10T10:00:00Z"), java));
    entityManager.persist(article("a-2", ArticleStatus.DRAFT, "alice", Instant.parse("2026-01-12T10:00:00Z"), java));

    // "Almost matches": same author, but different status.
    entityManager.persist(article("a-3", ArticleStatus.PUBLISHED, "alice", Instant.parse("2026-01-13T10:00:00Z"), java)); // extra by status

    // "Almost matches": same status, but different author.
    entityManager.persist(article("b-1", ArticleStatus.DRAFT, "bob", Instant.parse("2026-01-14T10:00:00Z"), java));       // extra by author

    // Make the DB the "source of truth" for reading.
    entityManager.flush();
    entityManager.clear();

    // Act
    List<Article> result = repository.findAllByAuthorUsernameAndStatusOrderByUpdatedAtDesc("alice", ArticleStatus.DRAFT);

    // Assert: both filtering and order are part of the contract.
    assertThat(result)
            .extracting(Article::getSlug)
            .containsExactly("a-2", "a-1");
}

Here the test proves three things at once, and it does so quite briefly. It proves that the author filter works (article "b-1" did not get included), that the status filter works (article "a-3" did not get included), and that sorting by updatedAt desc is actually applied (the order is strictly "a-2", then "a-1"). If someone changes the method to OrderByUpdatedAtAsc, or changes the field, or suddenly removes sorting, the test will fail immediately and honestly.

7. flush() and clear() — test honesty

After a couple of examples, you may think: “Okay, I get it, flush and clear are a mantra. But what actually happens if we remove them?” Great question, because this is exactly the place where many data tests start turning green by accident and create false confidence.

In JPA, you have the persistence context — the first-level cache where EntityManager keeps managed entities. When you call entityManager.persist(article), the object becomes managed. After that, you can read its fields and compare them — and that will work even if the database has not received SQL yet. Many queries will still go to the database, but results may come back as the same managed instances, and you risk checking “the state of the object in memory” rather than the fact of correct interaction with the database.

flush() forces Hibernate to send changes to the database right now, and clear() evicts managed entities from the persistence context. After clear(), you can no longer “accidentally” compare an object to itself. You are forced to obtain the result from the repository and check it as “what came from the database.” That makes the test more evidence-based.

If you want to remember it in one sentence, here it is: flush makes writing to the DB unavoidable, and clear makes reading from the DB unavoidable. Together, they turn a test slice from an imitation into a real persistence-boundary check.

8. Assertions for derived query tests

In data tests, it is easy to fall into “assert by list size.” It is fast, it is pretty, it does not require thinking… and it almost always does a poor job of protecting against regressions. If the method returned the wrong elements but by chance the same number, the test will stay green. If the method returned elements in the wrong order, the test will stay green. And then you will spend a long time looking at a production bug and saying: “Strange, but the tests were there...”

In derived query tests, you usually need two types of checks. The first is checking the composition of the result: specific slugs (or ids) should be in the result, not just “something.” The second is checking the order if order is part of the contract (and in our case it is explicitly written in the name). AssertJ with extracting(...) and containsExactly(...) is perfect for this: it expresses the idea “these exact values, and in this exact order.”

For Optional, it is important not to forget to check both worlds: isPresent() in the positive scenario and isEmpty() in the negative one. And if you do extract the value, it is better to do it through an AssertJ chain so you do not get a NoSuchElementException and turn the test into a mystery.

An example of a “proper” check for Optional looks like this:

import static org.assertj.core.api.Assertions.assertThat;

// Act: read according to the "slug + status" contract.
Optional<Article> result = repository.findBySlugAndStatus("jpa-basics", ArticleStatus.PUBLISHED);

// Assert: first prove that the record is actually found, and only then verify the meaning.
assertThat(result)
        .isPresent()
        .get()
        .extracting(Article::getSlug)
        .isEqualTo("jpa-basics");

This check is not the shortest one, but it is very honest: first we prove presence, then we prove the meaning of the result. And yes, it is a bit like bureaucracy — but bureaucracy in tests is usually cheaper than bureaucracy after production falls over.

9. Typical mistakes when testing derived query methods

Mistake #1: test “one record in an empty database” and call it a filter check.
If there is one article in the database and you call findAllByAuthorUsernameAndStatus..., you have proved almost nothing. The method could ignore status, ignore author, return everything — and the test would still pass. Contrasting data is not about “beauty,” but about the minimum way to prove that filter conditions really work.

Mistake #2: ignore OrderBy... in the method name and check only the contents.
When a method promises order, and the test does not check it, you leave an opening for a regression that shows up especially painfully in lists. In the UI, in the API, in “latest updates” — order is often part of the contract. If order matters in the name, it matters in the asserts too.

Mistake #3: do not call flush() and wonder why a constraint error does not appear, or why the behavior is “kind of strange.”
JPA can delay SQL until a convenient moment. In a test, this turns into the effect “the error appeared not where we expected it.” flush() fixes the moment of truth: if the database should complain, let it complain right in the Arrange part, not at the end of the test somewhere inside the repository call.

Mistake #4: do not call clear() and accidentally check managed objects instead of reading.
When you compare fields on an object you just persisted, you are mostly testing the object itself. In complex scenarios this creates false confidence: it seems like “we read from the database,” but in reality we just held the same instance in memory. clear() forces the test to actually read.

Mistake #5: check only hasSize(n) and celebrate a green test.
Size is a weak characteristic. Two wrong elements can still give size 2. If the query matters for application behavior, fix specific values: slug/id, and if necessary, the order. In this sense, AssertJ chains extracting(...).containsExactly(...) are one of the best “antidotes” to meaningless green tests.