Initializing Our Database With the @Sql Annotation

Published: April 9, 2017

After we have finished this lesson, we:

Understand why we should use the @Sql annotation.
Can initialize the used database by using the @Sql annotation.

This lesson assumes that:

You can configure your end-to-end tests when you are using Spring Boot

Why Should We Use the @Sql Annotation?

You might remember that earlier I said that we should initialize our database by using a database migration tool when we are writing end-to-end tests. So, the big question is:

Why does this lesson talk about the @Sql annotation if we should use a database migration tool?

Well, one of the biggest reasons why I said that we should use a database migration tool is that it is the least worst choice if we have to deploy our application to a servlet container before we run our end-to-end tests.

However, if we use Spring Boot, we can start our web application by leveraging the testing support provided by Spring Boot. In other words, we can write our end-to-end tests by using the features provided by the Spring Test framework and Spring Boot. This means that we can initialize our database into a known state before each test method is run by using either DbUnit or the @Sql annotation.

Like I mentioned earlier , when I write end-to-end tests, I like to initialize my database by using SQL scripts because I tend to use large data sets and large DbUnit data sets are hard to read, write, and maintain. However, I think that it is important to understand that you can use DbUnit as well.

Additional Reading:

Let’s move on and create the SQL scripts that we use when we initialize our database.

Creating the Required SQL Scripts

We will insert the test data into our database by using SQL scripts. Because these files must be found by our end-to-end tests, we will put these files to the src/end-to-end-test/resources directory.

By the way, if you have “a lot of” SQL scripts, it’s a good idea to organize them by using different subdirectories. Because I want to demonstrate how you can use these SQL scripts, I will put our SQL scripts to the com/testwithspring/intermediate subdirectory.

We can create the required SQL scripts by following these steps:

First, we have to create the SQL script that deletes the existing data from the database. This script must delete all rows found from the tags, tasks, and user_accounts tables.

By the way, we don’t have to delete any rows from the tasks_tags table. These rows are deleted automatically because our foreign keys use CASCADE DELETE.

After we have created this SQL script, the source code of the cleandb.sql file looks as follows:

1DELETE FROM tags;
2DELETE FROM tasks;
3DELETE FROM user_accounts;

Second, we have to create the SQL script that contains all task related data. This script inserts two rows into the tasks, tags, and tasks_tags tables.

After we have created this SQL script, the source code of the tasks.sql file looks as follows:

 1-- Tasks
 2INSERT INTO tasks
 3  (id, assignee_id, closer_id, creation_time, creator_id, description, modification_time, modifier_id, resolution, status, title, version)
 4VALUES
 5  (1, 1, 1, now(), 1, 'This example contains end-to-end tests', now(), 1, 'DONE', 'CLOSED', 'Write example application', 0);
 6INSERT INTO tasks
 7(id, assignee_id, closer_id, creation_time, creator_id, description, modification_time, modifier_id, resolution, status, title, version)
 8VALUES
 9  (2, null, null, now(), 1, 'This lesson talks about end-to-end testing', now(), 1, null, 'OPEN', 'Write lesson', 0);
10
11-- Tags
12INSERT INTO tags
13(id, creation_time, modification_time, name, version)
14VALUES
15  (1, now(), now(), 'example', 0);
16INSERT INTO tags
17(id, creation_time, modification_time, name, version)
18VALUES
19  (2, now(), now(), 'lesson', 0);
20
21INSERT INTO tasks_tags (task_id, tag_id) VALUES (1, 1);
22INSERT INTO tasks_tags (task_id, tag_id) VALUES (2, 2);

Third, we have to create the SQL script that inserts the user data into our database. Because we want to create one normal user and one administrator, this script inserts two rows into the user_accounts table.

After we have created this SQL scripts, the source code of the users.sql file looks as follows:

1INSERT INTO user_accounts
2(id, creation_time, email_address, is_enabled, modification_time, name, password, role, version)
3VALUES
4  (1, now(), 'john.doe@gmail.com', true, now(), 'John Doe', 'user', 'ROLE_USER', 0);
5INSERT INTO user_accounts
6(id, creation_time, email_address, is_enabled, modification_time, name, password, role, version)
7VALUES
8  (2, now(), 'anne.admin@gmail.com"', true, now(), 'Anne Admin', 'admin', 'ROLE_ADMIN', 0);

Next, we will find out how we can initialize our database by using the @Sql annotation.

Initializing Our Database With the @Sql Annotation

If we want to use the @Sql annotation, we have to ensure that our configuration registers a test execution listener called: SqlScriptsTestExecutionListener. However, if we haven’t replaced the default test execution listeners, we don’t have to do anything because this test execution listener is enabled by default.

We can initialize our database into a known state before each end-to-end test is run by making the following changes to the configuration of our end-to-end test class:

First, we have ensure that the existing data is deleted from the database after a test method has been run. After we have made this change, the source code of our end-to-end test class looks as follows:

 1import org.junit.Test;
 2import org.junit.experimental.categories.Category;
 3import org.junit.runner.RunWith;
 4import org.openqa.selenium.WebDriver;
 5import org.openqa.selenium.chrome.ChromeDriver;
 6import org.springframework.beans.factory.annotation.Autowired;
 7import org.springframework.boot.test.context.SpringBootTest;
 8import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;
 9import org.springframework.test.context.ActiveProfiles;
10import org.springframework.test.context.jdbc.Sql;
11import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
12
13import static org.assertj.core.api.Assertions.assertThat;
14import static org.springframework.test.context.jdbc.Sql.ExecutionPhase.AFTER_TEST_METHOD;
15
16@RunWith(SpringJUnit4ClassRunner.class)
17@SpringBootTest(classes = MessageApplication.class,
18        webEnvironment = WebEnvironment.DEFINED_PORT
19)
20@SeleniumTest(driver = ChromeDriver.class)
21@Sql(
22        value = "classpath:/com/testwithspring/intermediate/cleandb.sql",
23        executionPhase = AFTER_TEST_METHOD
24)
25@ActiveProfiles(Profiles.END_TO_END_TEST)
26@Category(EndToEndTest.class)
27public class ShowMessagePageTest {
28
29    @Autowired
30    private WebDriver browser;
31
32    @Test
33    public void shouldOpenFrontPage() {
34        browser.get("http://localhost:8080");
35        assertThat(browser.getTitle()).isEqualTo("Hello World!");
36    }
37}

Second, we have to initialize our database into a known state before a test method is run. We can do this by following these steps:

Insert the user data into our database.
Insert the task data into our database.

After we have made the required changes to the configuration of our end-to-end test class, its source code looks as follows:

 1import org.junit.Test;
 2import org.junit.experimental.categories.Category;
 3import org.junit.runner.RunWith;
 4import org.openqa.selenium.WebDriver;
 5import org.openqa.selenium.chrome.ChromeDriver;
 6import org.springframework.beans.factory.annotation.Autowired;
 7import org.springframework.boot.test.context.SpringBootTest;
 8import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;
 9import org.springframework.test.context.ActiveProfiles;
10import org.springframework.test.context.jdbc.Sql;
11import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
12
13import static org.assertj.core.api.Assertions.assertThat;
14import static org.springframework.test.context.jdbc.Sql.ExecutionPhase.AFTER_TEST_METHOD;
15
16@RunWith(SpringJUnit4ClassRunner.class)
17@SpringBootTest(classes = MessageApplication.class,
18        webEnvironment = WebEnvironment.DEFINED_PORT
19)
20@SeleniumTest(driver = ChromeDriver.class)
21@Sql(value = {
22        "classpath:/com/testwithspring/intermediate/users.sql",
23        "classpath:/com/testwithspring/intermediate/tasks.sql"
24})
25@Sql(
26        value = "classpath:/com/testwithspring/intermediate/cleandb.sql",
27        executionPhase = AFTER_TEST_METHOD
28)
29@ActiveProfiles(Profiles.END_TO_END_TEST)
30@Category(EndToEndTest.class)
31public class ShowMessagePageTest {
32
33    @Autowired
34    private WebDriver browser;
35
36    @Test
37    public void shouldOpenFrontPage() {
38        browser.get("http://localhost:8080");
39        assertThat(browser.getTitle()).isEqualTo("Hello World!");
40    }
41}

If you are not using Java 8 (or newer), you cannot use the @Sql annotation as a repeatable annotation. Instead, you have to use the @SqlGroup annotation when you want to add multiple @Sql annotations to a test class.

Additional Reading:

We can now initialize our database into a known state by using the @Sql annotation. Let’s summarize what we learned from this lesson.

Summary

This lesson has taught us three things:

If we are using Spring Boot, we can initialize our database into a known state by using either DbUnit or the @Sql annotation.
We should initialize our database into a known state by using SQL scripts and the @Sql annotation because large DbUnit data sets are hard to read, write, and maintain.
The executionPhase attribute of the @Sql annotation specifies when the SQL scripts are invoked.

Get the source code from Github