I have some integration tests in my spring boot tests. Around 20 of them. Before each integration test I am using @Sql annotation to inject some data and after the test I run a clean up script to truncate all the tables. So, my 20 tests case runs perfectly when I run them together. No issues. Now I have added a new integration test. Using the same process here too. Injecting some data at startup and cleaning the data with script. This test case runs perfectly but some how the data conflicts with other test cases.@AfterEach test I am running the clean up script similiar to the other testcases, but in this testcase some how the data starts persisting and conflict with other tests. If I comment this 21th test case, then again everything starts working just fine. I using the default surfire plugin that comes with springboot-maven-plugin. So, far the only solution I found is using @DirtiesContext with the test case that is causing the issue. But I don’t understand why is it necessary. My clean up scripts works fine(I can see that it is running via show-sql). Then why would my test case conflict with another.

I am using Mysql as my database with testcontainers, Spring data jpa to connect to database. I am providing the test case that is causing the issue below

@AfterEach
    public void cleanDb() {
        dbCleaner.clearDatabase();
        entityManager.clear();
    }

@Test
    @Sql(value = "/test/appointments/addUserAndDoctor.sql", executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)
    public void registerNewAppointmentTest() throws Exception {
        Long userId = 3L;
        Long doctorId = 2L;

        String registerRequest = """
                {
                    "patientId": 3,
                    "doctorId": 2,
                    "locationId": 1,
                    "date": "2024-08-08",
                    "isOnline": true
                }
                """;
        String token = mint(userId, List.of(Roles.ROLE_PATIENT));
        String location = mockMvc.perform(post("/v1/appointments")
                .contentType(MediaType.APPLICATION_JSON)
                .header("Authorization", String.format("Bearer %s", token))
                .content(registerRequest)).andExpect(status().isCreated())
                .andReturn().getResponse().getHeader("Location");

        log.info("New resources is created at: {}", location);

        Optional<Appointment> app = appointmentRepository.findById(1L);

        if(app.isEmpty()){
            fail("Appointment was not created");
        }

        RegisterAppointmentReq req = objectMapper.readValue(registerRequest, RegisterAppointmentReq.class);
        log.info("New appointment req: {}", req.toString());
        log.info("New appointment is created at: {}", app.get());

        assertEquals(req.getDoctorId(), app.get().getDoctor().getUserId());
        assertEquals(req.getPatientId(), app.get().getUser().getId());
        assertEquals(req.getLocationId(), app.get().getLocation().getId());
        assertEquals(req.getDate(), app.get().getDate());
        assertEquals(req.getIsOnline(), app.get().getIsOnline());
        assertEquals(req.getTime(), app.get().getTime());

        assertEquals(AppointmentStatus.REQUESTED, app.get().getStatus());
        assertEquals(false, app.get().getIsPaymentComplete());
    }

Here is the code for DBCleaner

@Profile("test")
@Component
public class DbCleaner {
    @PersistenceContext
    private EntityManager entityManager;

    @Transactional
    public void clearDatabase() {
        List<String> tables = entityManager.createNativeQuery(
                        "SELECT table_name FROM information_schema.tables " +
                                "WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE' " +
                                "AND table_name <> 'flyway_schema_history'")
                .getResultList();

        entityManager.createNativeQuery("SET FOREIGN_KEY_CHECKS = 0").executeUpdate();

        for (String table : tables) {
            entityManager.createNativeQuery("TRUNCATE TABLE " + table).executeUpdate();
        }

        entityManager.createNativeQuery("SET FOREIGN_KEY_CHECKS = 1").executeUpdate();
    }
}

I am very new to Spring boot. I would be highly grateful if any one could give my any idea why @DirtiesContext became necessary here. And is there anyway to avoid dirtiesContext here.

Thanks in advance

I already tried using Transcational and manually writing and using a script. But nothing worked. I also checkout the generated cleanup script which seems to include all the tables I have in the database. But only using dirtiesContext solves the problem.