Testing the DAO Layer in Java

A data access object, or DAO, contains code that reads and writes application data. A repository serves a similar purpose by exposing operations such as finding products or saving orders. When we test this layer, we want to know whether those operations work with our mappings, queries and database rules.

A mocked repository can help test the code that calls it, but it cannot tell us whether its real query works. To check queries, mappings and database constraints, we run a test against an isolated database. These are integration tests because they exercise our code together with database software.

1. Choose the Test for the Question

The distinction is about what executes. A test remains a database integration test when it runs in JUnit or uses an in-memory database. JUnit is the test runner, while the database is one of the real components involved.

Question	Useful test
Does a service handle a missing employee correctly?	A unit test with a repository that returns no employee
Does a custom query apply both quantity limits?	An integration test with records inside and outside the limits
Can an entity be saved and read back correctly?	An integration test that checks important persisted fields
Does a uniqueness constraint reject a duplicate?	A database test that attempts the duplicate write
Do transactions commit or roll back as required?	An integration test that observes the relevant transaction boundary

We do not need to retest every internal feature of Spring Data. We do need to test our entity configuration, query declarations, migrations and important persistence behavior. Even a basic save-and-read test can reveal an incorrect mapping or missing required field.

2. Choose an Isolated Database

We use a database created for tests, with no production or shared development data. It may run locally or in a container. A container packages a service and its dependencies so we can start an isolated instance for a test run.

Testcontainers can manage database containers from Java tests. It is useful when we need the same database engine used by the application, such as PostgreSQL or MongoDB. It requires a supported container runtime and adds startup work, so it is not a requirement for every unit test.

An in-memory database can be convenient for tests designed around it. However, H2 does not implement every PostgreSQL or MySQL behavior. SQL dialects, column types and constraints can differ. If a test is meant to prove behavior specific to the production engine, we use that engine and a compatible version.

The database also needs the schema or document setup the application expects. For a relational database, running the application’s migrations helps us test the schema changes as well as the Java code.

3. Define What the Query Should Return

The following example uses Spring Data MongoDB to test a quantity filter. It assumes a Java 17 or later application with Spring Data MongoDB configured. A record is a compact data carrier with generated accessors. Here, Item represents a stored product with a string identifier, a name, a quantity and a category. ItemRepository exposes the quantity search.

import java.util.List;
import org.springframework.data.annotation.Id;
import org.springframework.data.mongodb.core.mapping.Document;
import org.springframework.data.mongodb.repository.MongoRepository;
import org.springframework.data.mongodb.repository.Query;

@Document("items")
record Item(@Id String id, String name, int quantity, String category) {}

interface ItemRepository extends MongoRepository<Item, String> {
    @Query("{'quantity': {$gte: ?0, $lte: ?1}}")
    List<Item> findAllByQuantityBetween(int minimum, int maximum);
}

The query uses $gte for greater than or equal to and $lte for less than or equal to. Both limits are included. The Java method name alone does not prove that behavior, so our test data includes the exact boundary values.

For a search from 4 to 10, quantities 4, 7 and 10 must match. Quantities 3 and 11 must not match. Choosing these values helps us detect a wrong comparison or a missing limit.

4. Check the Records, Not Only Their Count

The following test method belongs in a MongoDB integration test with an injected ItemRepository. Spring creates the repository implementation, and the test configuration connects it to the isolated MongoDB instance. It assumes the test owns its database, starts with an empty items collection and cleans it between tests. It is the query check, rather than a complete database provisioning configuration.

@Test
void findsItemsWithinInclusiveQuantityLimits() {
    itemRepository.saveAll(List.of(
            new Item("below", "Below minimum", 3, "Test"),
            new Item("minimum", "At minimum", 4, "Test"),
            new Item("middle", "Between limits", 7, "Test"),
            new Item("maximum", "At maximum", 10, "Test"),
            new Item("above", "Above maximum", 11, "Test")));

    List<Item> matches = itemRepository.findAllByQuantityBetween(4, 10);

    Set<String> ids = matches.stream()
            .map(Item::id)
            .collect(Collectors.toSet());

    assertEquals(3, matches.size());
    assertEquals(Set.of("minimum", "middle", "maximum"), ids);
}

The method uses \@Test and assertEquals() from JUnit Jupiter, List and Set from java.util, and Collectors from java.util.stream.

Checking the count alone would allow three wrong records to pass. Checking the identifiers shows which records actually matched. We also keep the count assertion so an unexpected duplicate is visible.

The query does not declare a sort order, so the test does not assume one. If ordering is part of a repository method’s promise, we request it explicitly and assert the returned order. Comparing whole objects also requires an appropriate equality implementation; comparing stable identifiers keeps this query test focused.

5. Add the Cases That Matter to the Application

The boundary test is a useful start, but one test does not describe the whole method. We can add separate cases for no matches, an exact range where minimum equals maximum, and invalid input if the application defines how to handle it.

Other repository methods need different checks. A tenant filter needs records belonging to another tenant. Pagination needs enough records to cross a page boundary and a stable sort. A write operation may need checks for required fields, uniqueness or generated values.

If the application uses Jakarta Persistence, commonly called JPA, for relational database access, there is another consideration. In those tests, flush() sends pending changes to the database. clear() removes managed entities from the persistence context, the session’s collection of tracked objects. Reading after flushing and clearing helps avoid a test that only reads an object already held in memory. These operations do not apply to the MongoDB example above.

Some constraints are checked at commit, so flushing is not always enough. We test a real commit when the requirement depends on commit behavior.

6. Clean Up Without Hiding Test Dependencies

Each test prepares the data it needs and leaves no data that can change another test’s result. We establish an empty test collection before the first test too, because an earlier interrupted run may have left records behind. We can create a fresh database for each run or reset the collection before each test. Cleanup belongs in a test lifecycle method, such as \@AfterEach, when it must run after a failing assertion too.

Deleting a collection is appropriate only when the test exclusively owns it. For tests running in parallel, separate databases, schemas or another deliberate isolation strategy prevent one test’s cleanup from erasing another test’s data.

Transaction rollback can help in transactional tests, but we verify its scope. In Spring Boot tests that send HTTP requests to a real server, the server may use a different thread and transaction. The Spring Boot testing documentation explains this distinction. MongoDB cleanup also needs an explicit strategy unless transactions are actually configured and used.

7. Conclusion

We test database behavior by exercising the real repository against an isolated database with known data. We choose inputs that distinguish a correct query from a plausible wrong one, check the returned records and account for transaction and cleanup behavior. The general unit testing guide covers the separate tests for application logic around these repositories.

Happy Learning!