Setup and preload database for spring integration/functional tests

Much of the code you write can be tested using mocking frameworks such as mockito or easy mock but there is certain instances where you want to execute code and validate behavior against a database. It is important when running integration and functional tests that the database is in a known state before every test in order to have reproducible tests without side effects. Prior to Spring 4.1 if you were looking for your domains to interact with a database you could seed data with a tool like DBUnit prior to ApplicationContext loading but it was fairly intensive. In this tutorial lets find out how spring's testing improvements has simplified executing SQL scripts and inserting test data into an embedded database.

Project set up

[0:38]

Setting up a spring boot project through the starter initializer selecting spring-boot-starter-jdbc and spring-boot-starter-web we will generate and download a maven project that we will import into eclipse. Next adding the hyper SQL dependency will allow us to connect and interact with an embedded database.

<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <scope>runtime</scope>
</dependency>

Configure embedded database with boot

[0:56]

You are probably thinking we need to configure the database url, username, password and driver class. Luckily spring boot will recognize HSQLDB classes on the classpath and will autoconfigure an embedded database which saves us some work! If you are interested in the lower level classes that make that happen look at EmbeddedDatabaseConnection and EmbeddedDataSourceConfiguration.

If you want to connect to an external datasource such as oracle or mysql you can specify spring.datasource.* in application.properties or application.yml.

spring.datasource.url=jdbc:oracle:thin:@<server>:1521:<database_name>
spring.datasource.username=me
spring.datasource.password=me
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver

Walking through test class

[1:17]

Let's explore the ApplicationTests class that the initializer created for us. First the @RunWith(SpringJUnit4ClassRunner.class) states instead of using the default junitrunner use SpringJUnit4ClassRunner instead. Next the @SpringApplicationConfiguration will load a javaconfig class to configure the ApplicationContext. Our configuration is light but this is where you could include multiple configuration classes to set up your tests. Last is @WebAppConfiguration which just says to load it with a WebApplicationContext which has some underlying behavior. Let's run the default test to validate that the set up is functional thus far.

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = Application.class)
@WebAppConfiguration
public class ApplicationTests {

    @Test
    public void contextLoads() {

    }

}

Creating scripts

[1:50]

Next we will create two scripts. The first script will contain sql statements to create a table named PERSON and an insert statement to load "Fred".

CREATE TABLE PERSON (PERSONID INT PRIMARY KEY, FIRSTNAME VARCHAR(15) NOT NULL, MIDDLENAME VARCHAR(15), LASTNAME VARCHAR(15) NOT NULL, USERID VARCHAR(15) NOT NULL, PASSWORD VARCHAR(10) NOT NULL);

INSERT INTO PERSON VALUES(1, 'Fred', 'F', 'Flintstone', 'flintstonef', 'pebbles');

The second script will be executed after tests and will drop the PERSON table.

DROP TABLE PERSON;

Exploring @Sql and @SqlGroup

[2:2]

As mentioned we want to execute the beforeTestRun.sql prior to any tests running and afterTestRun.sql after each test method has executed. We can do this by using the @Sql and @SqlGroup. The @Sql annotation can be declared on a test class or test method and allows you to specify a file to be executed against the database. The default behavior is to run it before every test but if we want to be explicit we can add executionPhase = ExecutionPhase.BEFORE_TEST_METHOD.

@Sql(executionPhase = ExecutionPhase.BEFORE_TEST_METHOD, scripts = "classpath:beforeTestRun.sql")

Next we want to execute our second script. Since we are running prior to java 8 we need to use the @SqlGroup to declare multiple instances of @Sql otherwise we could take advantage of the repeating annotations. Let's wrap our first statement with @SqlGroup and add our second @Sql annotation. You will notice we included executionPhase = ExecutionPhase.AFTER_TEST_METHOD so that this script will be execute after each test.

@SqlGroup({
    @Sql(executionPhase = ExecutionPhase.BEFORE_TEST_METHOD, scripts = "classpath:beforeTestRun.sql"),
    @Sql(executionPhase = ExecutionPhase.AFTER_TEST_METHOD, scripts = "classpath:afterTestRun.sql")
})

Executing the class and viewing the logs we can see the scripts being executed.

o.e.j.i.junit.runner.RemoteTestRunner : Started RemoteTestRunner in 1.657 seconds (JVM running for 2.063)
o.s.jdbc.datasource.init. ScriptUtils : Executing SQL script from class path resource [beforeTestRun.sql]
o.s.jdbc.datasource.init.ScriptUtils : Executed SQL script from class path resource [beforeTestRun.sql] in 2 ms.

Writing a query to validate

[3:0]

To validate the code lets write a simple query and use JdbcTemplate to execute.

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = Application.class)
@WebAppConfiguration
@SqlGroup({
        @Sql(executionPhase = ExecutionPhase.BEFORE_TEST_METHOD, scripts = "classpath:beforeTestRun.sql"),
        @Sql(executionPhase = ExecutionPhase.AFTER_TEST_METHOD, scripts = "classpath:afterTestRun.sql") })
public class ApplicationTests {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    public void contextLoads() {

        String selectQuery = "SELECT * from PERSON WHERE PERSONID = 1";

        List<Map<String, Object>> resultSet = jdbcTemplate
                .queryForList(selectQuery);

        System.out.println(resultSet);
    }
}

Output

[{PERSONID=1, FIRSTNAME=Fred, MIDDLENAME=F, LASTNAME=Flintstone, USERID=flintstonef, PASSWORD=pebbles}]

You can see @Sql and @SqlGroup help simplify populating your database when your application starts and throw away changes when test end. If you are extracting DDL or SQL from an existing source there might be compatibility issues running against an embedded database such as h2 or a derby database. As your tests grow pay attention on how long it is taking scripts to execute in your continuous integration environment to keep builds running fast.

Thanks for joining today's level up, have a great day!