Hibernate JPA Integration Test issue

ISSUE

I’m trying to do some integration testing on my REST controller in Spring. I’ve set up a BEFORE_TEST sql script to run to create a table along with some default values before each test, but they don’t seem to be inserted as expected.

Here is my integration test setup…

 @SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
@AutoConfigureMockMvc

@Sql(scripts = { "classpath:specimen-schema.sql",
        "classpath:specimen-schema.sql" }, executionPhase = ExecutionPhase.BEFORE_TEST_METHOD)

public class SpecimenControllerIntegrationTest {

    @Autowired
    private MockMvc mockMVC;

    @Autowired
    private ObjectMapper mapper;

    @Test
    void testCreate() throws Exception {
        Specimen newSpecimen = new Specimen("Tyrannosaurus_Rex", "California, USA", "Container_A", null, "Jaw Bone");

        String newSpecimenAsJSON = this.mapper.writeValueAsString(newSpecimen);
        RequestBuilder mockRequest = post("/createSpecimen").contentType(MediaType.APPLICATION_JSON)
                .content(newSpecimenAsJSON);

        Specimen savedSpecimen = new Specimen(2L, "Tyrannosaurus_Rex", "California, USA", "Container_A", null,
                "Jaw Bone");
        String savedSpecimenAsJSON = this.mapper.writeValueAsString(savedSpecimen);

        ResultMatcher matchStatus = status().isCreated();
        ResultMatcher matchBody = content().json(savedSpecimenAsJSON);

        this.mockMVC.perform(mockRequest).andExpect(matchStatus).andExpect(matchBody);

    }

Here are my two SQL scripts…

Schema…

DROP TABLE IF EXISTS `specimen` CASCADE;
CREATE TABLE specimen (
    id BIGINT AUTO_INCREMENT NOT NULL,
    date_arrived TIMESTAMP,
    description VARCHAR(255),
    latin_name VARCHAR(255) NOT NULL,
    origin VARCHAR(255) NOT NULL,
    storage_location VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

Data…

INSERT INTO `specimen` (`date_arrived`, `description`, `latin_name`, `origin`, `storage_location`) VALUES ('2020-05-05', 'skeleton', 'archaeornithymimus', 'Peru', 'Container-C');

Here is my specimen class values and constructor…

package com.qa.museum.domain;

import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Specimen {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String latinName;
    private String origin;
    private String storageLocation;
    private Date dateArrived;
    private String description;

    public Specimen(String latinName, String origin, String storageLocation, Date dateArrived, String description) {
        super();
        this.latinName = latinName;
        this.origin = origin;
        this.storageLocation = storageLocation;
        this.dateArrived = dateArrived;
        this.description = description;
    }

Here’s the part of the controller I’m testing…

@RestController
public class SpecimenController {

    private SpecimenService service;

    public SpecimenController(SpecimenService service) {
        super();
        this.service = service;
    }

    @PostMapping("/createSpecimen")
    public ResponseEntity<Specimen> createSpecimen(@RequestBody Specimen specimen) {
        return new ResponseEntity<Specimen>(this.service.createSpecimen(specimen), HttpStatus.CREATED);
    }

TEST RESULT

Test result is saying id expected was 2, but it got 1. It should be 2, as the SQL scripts should be creating an instance of specimen in the DB and auto incrementing the id to 1, then my test should be creating a second, with an id of 2. However, the SQL script doesn’t seem to be inserting the initial DB entry.

Hibernate: insert into specimen (id, date_arrived, description, latin_name, origin, storage_location) values (null, ?, ?, ?, ?, ?)

Side note – I’ve not used dates much in Java, so could it be something to do with the date field? It was working fine when I tested in Postman, I’ve set the test date entry to null as a placeholder as I’m unsure of the java date syntax.

Thanks in advance.

Answer

You specified the same script name specimen-schema.sql twice:

@Sql(scripts = { "classpath:specimen-schema.sql",
        "classpath:specimen-schema.sql" }, executionPhase = ExecutionPhase.BEFORE_TEST_METHOD)

The sql script to insert the first record was never run.

specimen-data.sql :

INSERT INTO `specimen` (`date_arrived`, `description`, `latin_name`, `origin`, `storage_location`) VALUES ('2020-05-05', 'skeleton', 'archaeornithymimus', 'Peru', 'Container-C');

So the record new Specimen("Tyrannosaurus_Rex", "California, USA", "Container_A", null, "Jaw Bone"); has been saved with the id 1.

You should change the second "classpath:specimen-schema.sql" to "classpath:specimen-data.sql"