data.sql vs @Sql annotation has inconsistent behaviour in Spring Boot-CodePudding

I have noticed something very strange. Maybe it's designed to be like this.

I am running integrated tests against a local MySQL database.

I wanted to create my own data.sql file so that data could be loaded and I wanted to start with the database in the same state.

In a nutshell, it seems like the @Sql annotation runs before every test method but the data.sql file is only loaded once before all tests are ran?

I am running an integrated test for an Entity Employee that has employeeNumber as a unique column.

There are a couple of tests which try to insert the same employee twice and they should fail.

Now, this is what is happening.

If I keep an @Sql annotation over the class deleting everything before it works fine. If I rely on the data.sql file without the annotation then the database does not clear the state and I am getting the duplicate field errors. Which means clearly the data.sql file is not clearing the state properly.

The funny thing is, those same tests that fail, if I run them individually immediately after, then they pass. So to me it is quiet obvious what is happening. data.sql is not running after every test.

Question is, is this a bug or is it normal?

Here is the code:

application.properties (inside test/resources):

spring.datasource.url=jdbc:mysql://localhost:3306/StraightWallsTest
spring.datasource.username=root
spring.datasource.password=root

# Get rid of JPA Hibernate's counter intuitive confusing & ridiculous underscore when no one asked for it
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
# Do not generate mappings by default
spring.jpa.hibernate.use-new-id-generator-mappings=false
# Do not print any stack trace to the client
server.error.include-stacktrace=never
# Do not generate schema
spring.jpa.hibernate.ddl-auto=none

spring.sql.init.mode=always

This is the data.sql file. I have all the delete statements on top to ensure that everything is deleted.

-- MySQL dump 10.13  Distrib 8.0.28, for macos12.2 (arm64)
--
-- Host: localhost    Database: StraightWallsLocal
-- ------------------------------------------------------
-- Server version   8.0.28

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=' 00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Dumping data for table `Department`
--
DELETE FROM`Employee`;
ALTER TABLE `Employee` AUTO_INCREMENT=1;
DELETE FROM`Department`;
ALTER TABLE `Department` AUTO_INCREMENT=1;
DELETE FROM`Role`;
ALTER TABLE `Role` AUTO_INCREMENT=1;
DELETE FROM `HolidayRequest`;
ALTER TABLE `HolidayRequest` AUTO_INCREMENT=1;
DELETE FROM `Period`;
ALTER TABLE `Period` AUTO_INCREMENT=1;


LOCK TABLES `Department` WRITE;
/*!40000 ALTER TABLE `Department` DISABLE KEYS */;
INSERT INTO `Department` VALUES (1,'Engineering'),(2,'Plumbing'),(3,'Roofing'),(4,'Carpentry'),(5,'Bricklaying'),(6,'Office');
/*!40000 ALTER TABLE `Department` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping data for table `Employee`
--

LOCK TABLES `Employee` WRITE;
/*!40000 ALTER TABLE `Employee` DISABLE KEYS */;
/*!40000 ALTER TABLE `Employee` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping data for table `HolidayRequest`
--

LOCK TABLES `HolidayRequest` WRITE;
/*!40000 ALTER TABLE `HolidayRequest` DISABLE KEYS */;
/*!40000 ALTER TABLE `HolidayRequest` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping data for table `Period`
--

LOCK TABLES `Period` WRITE;
/*!40000 ALTER TABLE `Period` DISABLE KEYS */;
/*!40000 ALTER TABLE `Period` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping data for table `Role`
--

LOCK TABLES `Role` WRITE;
/*!40000 ALTER TABLE `Role` DISABLE KEYS */;
INSERT INTO `Role` VALUES (1,'Deputy Head'),(2,'Head'),(3,'Manager'),(4,'Senior Employee'),(5,'Junior Employee');
/*!40000 ALTER TABLE `Role` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-04-20 23:35:41

The test cases:

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@Sql(statements = {"DELETE FROM Employee; ALTER TABLE Employee AUTO_INCREMENT=1"})
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@IfProfileValue(name = "spring.profiles.active", value = "test")
public class EmployeeAccountApiIntegratedTest {

    @LocalServerPort
    private int port;

    @Autowired
    private TestRestTemplate rest;

    @Test
    @DisplayName("My First integrated test with Test rest template, seems to be easier than i thought")
    public void whenPostingToDbEverythingWorksAndRealDbDoesNotHit(){

        RegistrationRequest registrationRequest = new RegistrationRequest();
        registrationRequest.setFirstName("Crow");
        registrationRequest.setLastName("Ice");
        registrationRequest.setEmployeeNumber("38476");
        registrationRequest.setPassword("abcdef");
        registrationRequest.setTermsAccepted(true);

        HttpEntity<RegistrationRequest> request = new HttpEntity<RegistrationRequest>(registrationRequest);

        ResponseEntity<String> response = rest.postForEntity(
                "/register",
                request,
                String.class
        );

        assertEquals(HttpStatus.CREATED, response.getStatusCode());
    }


    @Test
    @DisplayName("given employee doesnt exist, when authentication request is made, it should return forbidden even with valid credentials")
    public void shouldReturnForbiddenBecauseEmployeeDoesntExist(){
        HttpHeaders headers = new HttpHeaders();
        headers.set("Content-Type", "application/json");
        headers.set("Accept", "text/plain");

        LoginRequest loginRequestBody = new LoginRequest();
        loginRequestBody.setEmployeeNumber("abcde");
        loginRequestBody.setPassword("abcde");

        HttpEntity<LoginRequest> loginRequest = new HttpEntity<LoginRequest>(loginRequestBody, headers);


        ResponseEntity<String> loginResponse = rest.postForEntity(
                "/login",
                loginRequestBody,
                String.class
        );

        assertEquals(HttpStatus.FORBIDDEN, loginResponse.getStatusCode());
    }

    @Test
    @DisplayName("Given employee exists and valid credentials, when authentication is requested, then it should return status ok/200")
    public void returnsOkayWhenEverythingIsValid(){
        HttpHeaders registerHeaders = new HttpHeaders();
        registerHeaders.set("Content-Type", "application/json");
        registerHeaders.set("Accept", "application/json");

        RegistrationRequest registrationRequest = new RegistrationRequest();
        registrationRequest.setFirstName("Crow");
        registrationRequest.setLastName("Ice");
        registrationRequest.setEmployeeNumber("mnopq");
        registrationRequest.setPassword("abcdef");
        registrationRequest.setTermsAccepted(true);

        HttpEntity<RegistrationRequest> request = new HttpEntity<RegistrationRequest>(registrationRequest, registerHeaders);

        ResponseEntity<String> registrationResponse = rest.postForEntity(
                "/register",
                request,
                String.class
        );

        assertEquals(HttpStatus.CREATED, registrationResponse.getStatusCode());

        HttpHeaders loginHeaders = new HttpHeaders();
        loginHeaders.set("Content-Type", "application/json");

        LoginRequest loginRequestBody = new LoginRequest();
        loginRequestBody.setEmployeeNumber(registrationRequest.getEmployeeNumber());
        loginRequestBody.setPassword(registrationRequest.getPassword());

        HttpEntity<LoginRequest> loginRequest = new HttpEntity<LoginRequest>(loginRequestBody, loginHeaders);

        ResponseEntity<String> loginResponse = rest.postForEntity(
                "/login",
                loginRequestBody,
                String.class
        );

        assertEquals(HttpStatus.OK, loginResponse.getStatusCode());
    }

    @Test
    @DisplayName("Given valid employee number but invalid password, when authentication is requested, then the employee should be forbidden")
    public void returnsForbiddenWhenEmployeeNumberIsInValid(){
        HttpHeaders registerHeaders = new HttpHeaders();
        registerHeaders.set("Content-Type", "application/json");
        registerHeaders.set("Accept", "application/json");

        RegistrationRequest registrationRequest = new RegistrationRequest();
        registrationRequest.setFirstName("Crow");
        registrationRequest.setLastName("Ice");
        registrationRequest.setEmployeeNumber("gh78q");
        registrationRequest.setPassword("abcdef");
        registrationRequest.setTermsAccepted(true);

        HttpEntity<RegistrationRequest> request = new HttpEntity<RegistrationRequest>(registrationRequest, registerHeaders);

        ResponseEntity<String> registrationResponse = rest.postForEntity(
                "/register",
                request,
                String.class
        );

        assertEquals(HttpStatus.CREATED, registrationResponse.getStatusCode());

        HttpHeaders loginHeaders = new HttpHeaders();
        loginHeaders.set("Content-Type", "application/json");

        LoginRequest loginRequestBody = new LoginRequest();
        loginRequestBody.setEmployeeNumber("kdjfi");
        loginRequestBody.setPassword(registrationRequest.getPassword());

        HttpEntity<LoginRequest> loginRequest = new HttpEntity<LoginRequest>(loginRequestBody, loginHeaders);

        ResponseEntity<String> loginResponse = rest.postForEntity(
                "/login",
                loginRequestBody,
                String.class
        );

        assertEquals(HttpStatus.FORBIDDEN, loginResponse.getStatusCode());
    }

    @Test
    @DisplayName("Given an employee already exists, when an attempt is made to register with the same employee number, then an exception should be thrown")
    public void noDuplicateEmployeeNumber(){
        HttpHeaders registerHeaders = new HttpHeaders();
        registerHeaders.set("Content-Type", "application/json");
        registerHeaders.set("Accept", "application/json");

        RegistrationRequest registrationRequest = new RegistrationRequest();
        registrationRequest.setFirstName("Crow");
        registrationRequest.setLastName("Ice");
        registrationRequest.setEmployeeNumber("gh78q");
        registrationRequest.setPassword("abcdef");
        registrationRequest.setTermsAccepted(true);

        HttpEntity<RegistrationRequest> request = new HttpEntity<RegistrationRequest>(registrationRequest, registerHeaders);

        ResponseEntity<String> registrationResponse = rest.postForEntity(
                "/register",
                request,
                String.class
        );

        assertEquals(HttpStatus.CREATED, registrationResponse.getStatusCode());


        HttpHeaders registerHeaders2 = new HttpHeaders();
        registerHeaders2.set("Content-Type", "application/json");
        registerHeaders2.set("Accept", "application/json");

        RegistrationRequest registrationRequest2 = new RegistrationRequest();
        registrationRequest2.setFirstName("Crow");
        registrationRequest2.setLastName("Ice");
        registrationRequest2.setEmployeeNumber("gh78q");
        registrationRequest2.setPassword("abcdef");
        registrationRequest2.setTermsAccepted(true);

        HttpEntity<RegistrationRequest> request2 = new HttpEntity<RegistrationRequest>(registrationRequest, registerHeaders);

        ResponseEntity<String> registrationResponse2 = rest.postForEntity(
                "/register",
                request,
                String.class
        );

        assertEquals(HttpStatus.CONFLICT, registrationResponse2.getStatusCode());

    }

    /**
     * Ideally this should be throwing a 409, But there isnt enough info about this, so leaving it as a genetic error
     */
    @Test
    @DisplayName("Given integrity constraint violation, when employee tries to register, then throw an exception")
    public void returnsHttpConflict(){
        HttpHeaders registerHeaders = new HttpHeaders();
        registerHeaders.set("Content-Type", "application/json");
        registerHeaders.set("Accept", "application/json");

        RegistrationRequest registrationRequest = new RegistrationRequest();
        registrationRequest.setFirstName("Crow");
        registrationRequest.setLastName("Ice");
        registrationRequest.setEmployeeNumber("gh78q");
        registrationRequest.setPassword("abcdef");
        registrationRequest.setTermsAccepted(true);

        HttpEntity<RegistrationRequest> request = new HttpEntity<RegistrationRequest>(registrationRequest, registerHeaders);

        ResponseEntity<String> registrationResponse = rest.postForEntity(
                "/register",
                request,
                String.class
        );

        assertEquals(HttpStatus.CREATED, registrationResponse.getStatusCode());

        ResponseEntity<String> registrationResponse2 = rest.postForEntity(
                "/register",
                request,
                String.class
        );

        assertEquals(HttpStatus.CONFLICT, registrationResponse2.getStatusCode());

    }





}

Just to reiterate what is happening.

If I keep this annotation: Everything works perfectly

@Sql(statements = {"DELETE FROM Employee; ALTER TABLE Employee AUTO_INCREMENT=1"})

If I delete that annotation, then run all the tests in the class I get the duplicate errors for few methods that maybe be using the same employee number.

Here is what is confusing. When I comment out the @Sql annotation like so: Two of the tests fail

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
//@Sql(statements = {"DELETE FROM Employee; ALTER TABLE Employee AUTO_INCREMENT=1"})
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@IfProfileValue(name = "spring.profiles.active", value = "test")
public class EmployeeAccountApiIntegratedTest {

Funny thing is if I go to that same test that failed and run that test method in isolation it works:

So the main question is, is this normal, is it supposed to be like this? Or is it a bug? If it is the former, then it should be properly documented. It's not intuitive and not great architecture IMHO

Any advice would be greatly appreciated

CodePudding user response：

@Sql annotation has the execution phase:

Sql.ExecutionPhase executionPhase() default Sql.ExecutionPhase.BEFORE_TEST_METHOD;

public static enum ExecutionPhase {
    BEFORE_TEST_METHOD,
    AFTER_TEST_METHOD;

    private ExecutionPhase() {
    }
}

As you can see the default value is BEFORE_TEST_METHOD. Hence SQL within this annotation will be run before every test. When you commented it out, your delete statements will not be executed as you noticed, and you have conflicts/collisions between the particular tests. This is normal, no SQL annotation, and no SQL statements will be executed.

Now data.sql file. From docs.spring.io:

Script-based DataSource initialization is performed, by default, before any JPA EntityManagerFactory beans are created. schema.sql can be used to create the schema for JPA-managed entities and data.sql can be used to populate it.

EntityManagerFactory is not created for every test, but rather for every data source. Hence data.sql is executed only once somewhat similar to @BefereClass or @BeforeAll. It is not a bug, it is expected behaviour, however, I agree not very well documented.