Asserting against DB state in Spring Boot integration test

Question

How do I check the DB state in an integration test? Is injecting a JdbcTemplate the only option?

For example, you test a PATCH that, if successful, should update certain table cells.

I can't use a repository. This microservice can't select the entities it modifies. Adding select methods just for tests feels wrong.

import com.example.pixel_money_transfer_api.data.dto.request.TransferRequestDto;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.web.servlet.AutoConfigureMockMvc;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.testcontainers.service.connection.ServiceConnection;
import org.springframework.http.MediaType;
import org.springframework.test.context.jdbc.Sql;
import org.springframework.test.web.servlet.MockMvc;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;

import java.math.BigDecimal;

import static org.springframework.security.test.web.servlet.request.SecurityMockMvcRequestPostProcessors.jwt;
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.patch;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.status;

@Testcontainers
@AutoConfigureMockMvc
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
class TransferControllerIntegrationTest {

    @Container
    @ServiceConnection
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16.0");
    @Autowired
    MockMvc mockMvc;
    @Autowired
    ObjectMapper objectMapper;

    @Test
    @Sql(statements = "TRUNCATE \"user\" CASCADE;")
    @Sql(statements = """
            INSERT INTO "user" (id, name) VALUES (15, 'Sasha');
            INSERT INTO "user" (id, name) VALUES (16, 'Alex');
            INSERT INTO account (id, user_id, balance) VALUES (1, 15, 10);
            INSERT INTO account (id, user_id, balance) VALUES (2, 16, 0);
            """)
    void performTransfer_ifUserHasSufficientBalance_recipientHasAccount_returns200() throws Exception {
        TransferRequestDto transferDto = new TransferRequestDto();
        transferDto.setRecipientId(16L);
        transferDto.setAmount(BigDecimal.valueOf(10L));

        mockMvc.perform(patch("/api/money-transfer")
                        .with(jwt().jwt(jwt -> jwt
                                .claim("userid", 15L)))
                        .contentType(MediaType.APPLICATION_JSON)
                        .content(objectMapper.writeValueAsString(transferDto)))
                .andExpect(status().isOk());
        // now I should somehow assert against the DB state
    }
}

JdbcTemplate is fine. You can also read the DB using your own repository object (presumably the same object that wrote to the DB during the test). You could even read the DB using whatever business API you have for reads. — jaco0646
– jaco0646, Commented Jun 2 at 0:44
@jaco0646 "You can also read the DB using your own repository object" this microservice can't select the entities it modifies. Adding select methods just for tests feels wrong — Sergey Zolotarev
– Sergey Zolotarev, Commented Jun 2 at 3:19
Is DbUnit still a think? You can write expectations on the dataset with DbUnit (so you can write the whole set you expect to be in there and DbUnit will do the verification and validation). — M. Deinum
– M. Deinum, Commented Jun 2 at 8:54
What technologies are you using to write to DB in your application? Presumably it can handle simple reads. For the type of test you show, a simple select * from user would do, and you may not need much custom code to achieve that. — julaine
– julaine, Commented Jun 2 at 8:59
@julaine what would execute that select * from user? An autowired EntityManager? — Sergey Zolotarev
– Sergey Zolotarev, Commented Jun 2 at 9:05

Diman · Accepted Answer · 2025-06-02 10:02:32Z

-1

You're right to avoid adding select methods just for testing — doing so can introduce unnecessary coupling and violate encapsulation.

In Spring Boot integration tests, injecting JdbcTemplate is a practical and acceptable way to verify DB state when repositories aren't available or appropriate.

@Autowired
JdbcTemplate jdbcTemplate;

@Test
void performTransfer_ifUserHasSufficientBalance_recipientHasAccount_returns200() throws Exception {
    // Arrange
    TransferRequestDto transferDto = new TransferRequestDto();
    transferDto.setRecipientId(16L);
    transferDto.setAmount(BigDecimal.valueOf(10L));

    // Act
    mockMvc.perform(patch("/api/money-transfer")
                    .with(jwt().jwt(jwt -> jwt.claim("userid", 15L)))
                    .contentType(MediaType.APPLICATION_JSON)
                    .content(objectMapper.writeValueAsString(transferDto)))
            .andExpect(status().isOk());

    // Assert
    BigDecimal senderBalance = jdbcTemplate.queryForObject(
            "SELECT balance FROM account WHERE user_id = ?",
            new Object[]{15L},
            BigDecimal.class
    );

    BigDecimal recipientBalance = jdbcTemplate.queryForObject(
            "SELECT balance FROM account WHERE user_id = ?",
            new Object[]{16L},
            BigDecimal.class
    );

    assertEquals(new BigDecimal("0.00"), senderBalance);
    assertEquals(new BigDecimal("10.00"), recipientBalance);
}

answered Jun 2 at 10:02

Diman

12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sergey Zolotarev Jun 2 at 10:09

Why not EntityManager?

julaine Jun 3 at 7:28

I don't get it. You don't want to add selects just for testing, but then you add selects in your test.

Sergey Zolotarev Jun 3 at 7:53

I was talking about expanding the business API for the sake of tests (adding select methods in the repository). That is not happening if I inject an EntityManager

julaine Jun 4 at 10:38

Using entityManager does sound cleaner then whipping out custom sql. If that is your solution, please post it as an answer for others to find.

Collectives™ on Stack Overflow

Asserting against DB state in Spring Boot integration test

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related