How to Preload H2 Database For Integration Testing

The cool thing about spring boot is that we don’t have to worry about database configuration for our testing. Because we added the h2 dependency at the begining, it configures an in memeory database for us, all ready for our testing purposes. Now all I have to do is to pre-load it with my test data. There are a few ways to do this.

Three options to preload the database for testing purposes

1. Inject the repository in to the test class and save values directly through that.

2. Use @Sql or @SqlGroup annotation to execute a set of sql statements. These annotaitons can be used on both class and method levels

3. Create a script file in src/integration-test/resources folder and add all the insert statements there, it will be executed during applciation loading.

For my testing, I’ll use a combination of the last two methods.

Using a Script file to preload the database

• Create a new source folder, in src/integration-test called resources.

• In there, create a file named test-data.sql

• Add some insert statements in this file.

• Use the @Sql(executionPhase = ExecutionPhase.BEFORE_TEST_METHOD, scripts = "/test-data.sql") annotation over the test method or class to run the script before the test.

If you named this file import.sql, it will be picked up by hibernate automatically to pre-load the database, and you don’t have to use the @Sql annotation.

Example test-data.sql file

insert into `comment` (post_id, message) values (1, 'test comment1');

insert into `comment` (post_id, message) values (1, 'test comment2');

insert into `comment` (post_id, message) values (1, 'test comment3');

insert into `comment` (post_id, message) values (1, 'test comment4');

Code example - using the script file to pre-load the database

    @Test
    @Sql(executionPhase = ExecutionPhase.BEFORE_TEST_METHOD, scripts = "/test-data.sql")
    public void getCommentsByPostId_whenDataPresent_returnsAllvalues() throws Exception {

        mvc.perform(get("/posts/1/comments")).andExpect(jsonPath("$").isArray())
                .andExpect(jsonPath("$.[0].postId", is(1))).andExpect(jsonPath("$.[0].message", is("test comment1")))
                .andDo(print());
    }

Using @Sql and @SqlGroup annotations to execute a set of sql statements

This is best done with an example. In the above example I have used the @Sql annotation to run the script file to load some data, but after the test execution, I want to remove all the data to clear the database for my other tests. This can be done with a single sql statement. So I would use another @Sql annotation to run the delete statement like so, @Sql(executionPhase = ExecutionPhase.AFTER_TEST_METHOD, statements = "delete from comment;").

I would also use the @SqlGroup annotation to group these together.

Code example - using the @Sql and @SqlGroup annotation to execute a set of sql statements

@Test
    @SqlGroup({ @Sql(executionPhase = ExecutionPhase.BEFORE_TEST_METHOD, scripts = "/test-data.sql"),
            @Sql(executionPhase = ExecutionPhase.AFTER_TEST_METHOD, statements = "delete from comment;") })
    public void getCommentsByPostId_whenDataPresent_returnsAllvalues() throws Exception {

        mvc.perform(get("/posts/1/comments")).andExpect(jsonPath("$").isArray())
                .andExpect(jsonPath("$.[0].postId", is(1))).andExpect(jsonPath("$.[0].message", is("test comment1")))
                .andDo(print());
    }

How to test the REST API response

The response is a JSON, so I will use json path expressions to check that I’m getting the correct response. The documentation provide all the expresstions that can be used.

Following are some exapmles that I used in my testing.

jsonPath("$").isArray()
jsonPath("$.[0].postId", is(1))
jsonPath("$.[0].message", is("test comment1"))
jsonPath("$.id", is(greaterThan(0)))
jsonPath("$.length()", is(1))

Please find the complete source code @ https://github.com/JavaCodeHouse/comments-application