Except the initial schema of our database, neither XML nor SQL files will be needed, only pure Java code. Which will ensure a less error prone and clearer code.

Summary

Set up the project
Add the DataSource configuration
Set up the database
Inject SQL Data
Write tests
Conclusion

Set up the project

We will use:

JUnit 5 to run our tests and create Extensions
AssertJ to have fluent assertions
H2 as the memory database
DbSetup to populate the database with our test data, which we will name fixtures

Why DbSetup ? Because it’s very simple to setup, fast, reliable, Java only, no XML/YAML/SQL (see DbSetup’s Approach for more details).

Here is our Maven pom.xml file:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.blebail.blog.sample</groupId>
    <artifactId>java-sql-junit5-h2</artifactId>
    <packaging>jar</packaging>
    <version>1.0-SNAPSHOT</version>
    <name>java-sql-juni5-h2</name>
    <url>https://github.com/baptistelebail/samples/tree/master/java-sql-junit5-h2</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <!-- JUnit -->
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>5.6.0</version>
            <scope>test</scope>
        </dependency>

        <!-- AssertJ -->
        <dependency>
            <groupId>org.assertj</groupId>
            <artifactId>assertj-core</artifactId>
            <version>3.15.0</version>
            <scope>test</scope>
        </dependency>

        <!-- H2 Database -->
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.4.200</version>
            <scope>test</scope>
        </dependency>

        <!-- DbSetup -->
        <dependency>
            <groupId>com.ninja-squad</groupId>
            <artifactId>DbSetup</artifactId>
            <version>2.1.0</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>8</source>
                    <target>8</target>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>2.22.2</version>
            </plugin>
        </plugins>
    </build>
</project>

Add the DataSource configuration

We create an application.properties file in src/test/resources/
and fill it with the following DataSource configuration:

datasource.url=jdbc:h2:mem:test;DATABASE_TO_UPPER=false;DB_CLOSE_DELAY=-1
datasource.username=sa
datasource.password=

We set DATABASE_TO_UPPER to false to tell H2 not to put the schema and tables names in uppercase, the default is true

(see DATABASE_TO_UPPER for more details).

We set DB_CLOSE_DELAY to -1 because:

By default, closing the last connection to a database closes the database. For an in-memory database, this means the content is lost. To keep the database open, add ;DB_CLOSE_DELAY=-1 to the database URL

(see DB_CLOSE_DELAY for more details)

Set up the database

We create a db_schema.sql file in src/test/resources which will initialize the schema of the database, with for example, an account table:

DROP TABLE IF EXISTS account;

CREATE TABLE account
(
    id            VARCHAR(36)  NOT NULL PRIMARY KEY,
    username      VARCHAR(255) NOT NULL UNIQUE,
    active        BOOLEAN      NOT NULL DEFAULT FALSE
);

Now we need a JUnit extension to setup our database, before all our tests run (by implementing JUnit BeforeAllCallback), which will:

load the database configuration
create the DataSource
read our db_schema.sql file and execute it

(The DbSetup tool can execute an Operation, which can be any SQL operation, or several SQL operations, that’s what we will use to create the schema).

We create the SqlMemoryDatabase extension in src/test/java/:

import com.ninja_squad.dbsetup.DbSetup;
import com.ninja_squad.dbsetup.Operations;
import com.ninja_squad.dbsetup.destination.DataSourceDestination;
import org.h2.jdbcx.JdbcDataSource;
import org.junit.jupiter.api.extension.BeforeAllCallback;
import org.junit.jupiter.api.extension.ExtensionContext;

import javax.sql.DataSource;
import java.io.IOException;
import java.net.URISyntaxException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.Properties;

public final class SqlMemoryDatabase implements BeforeAllCallback {

    private static final String APPLICATION_PROPERTIES_PATH = "/application.properties";

    private static final String DB_SCHEMA_SQL_PATH = "/db_schema.sql";

    private DataSource dataSource;

    @Override
    public void beforeAll(ExtensionContext extensionContext) throws Exception {
        loadDataSource();
        createSchema();
    }

    private void loadDataSource() throws Exception {
        Properties properties = new Properties();
        properties.load(getClass().getResourceAsStream(APPLICATION_PROPERTIES_PATH));

        JdbcDataSource jdbcDataSource = new JdbcDataSource();
        jdbcDataSource.setURL(properties.getProperty("datasource.url"));
        jdbcDataSource.setUser(properties.getProperty("datasource.username"));
        jdbcDataSource.setPassword(properties.getProperty("datasource.password"));

        dataSource = jdbcDataSource;
    }

    private void createSchema() throws IOException, URISyntaxException {
        Path schemaPath = Paths.get(getClass().getResource(DB_SCHEMA_SQL_PATH).toURI());
        String createSchemaSql = new String(Files.readAllBytes(schemaPath));

        new DbSetup(
                new DataSourceDestination(dataSource),
                Operations.sql(createSchemaSql)
        ).launch();
    }

    public DataSource dataSource() {
        return dataSource;
    }
}

Inject SQL data

Now we need a JUnit extension to load some SQL fixtures which:

injects fixtures into our database before each tests (by implementing JUnit BeforeEachCallback), but only if needed, because some tests only read the database, so there is no need to set up the whole database again before the next test
allows us to inject fixtures into our database on demand, for example for a single test

We create the SqlFixture extension in src/test/java/:

import com.ninja_squad.dbsetup.DbSetup;
import com.ninja_squad.dbsetup.destination.DataSourceDestination;
import com.ninja_squad.dbsetup.operation.Operation;
import org.junit.jupiter.api.extension.BeforeEachCallback;
import org.junit.jupiter.api.extension.ExtensionContext;

import java.util.Objects;
import java.util.Optional;

public final class SqlFixture implements BeforeEachCallback {

    private final SqlMemoryDatabase sqlMemoryDatabase;
    
    private final Operation initialOperation;

    private final DbSetupTracker dbSetupTracker;

    public SqlFixture(SqlMemoryDatabase sqlMemoryDatabase) {
        this(sqlMemoryDatabase, null);
    }

    public SqlFixture(SqlMemoryDatabase sqlMemoryDatabase, Operation initialOperation) {
        this.sqlMemoryDatabase = Objects.requireNonNull(sqlMemoryDatabase);
        this.initialOperation = Objects.requireNonNull(initialOperation);
        this.dbSetupTracker = new DbSetupTracker();
    }

    @Override
    public void beforeEach(ExtensionContext extensionContext) {
        Optional.ofNullable(initialOperation)
                .map(this::dbSetup)
                .ifPresent(dbSetupTracker::launchIfNecessary);
    }

    public void inject(Operation operation) {
        dbSetup(operation).launch();
    }

    public DbSetup dbSetup(Operation operation) {
        return new DbSetup(new DataSourceDestination(sqlMemoryDatabase.dataSource()), operation);
    }

    /**
     * Avoids initializing the database in the next test.
     * Use only if the test does not make any modification, such as INSERT, UPDATE or DELETE, to the database.
     */
    public void readOnly() {
        dbSetupTracker.skipNextLaunch();
    }
}

Write tests

We have fully functional and reusable extensions for any SQL test. Now let’s say we have an AccountRepository interface who fetches some Account from the database.

Here is the Account class:

import java.util.Objects;

public final class Account {

    private final String id;

    private final String username;

    private final boolean active;

    public Account(String id, String username, boolean active) {
        this.id = Objects.requireNonNull(id);
        this.username = Objects.requireNonNull(username);
        this.active = active;
    }

    public String id() {
        return id;
    }

    public String username() {
        return username;
    }

    public boolean isActive() {
        return active;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Account account = (Account) o;
        return active == account.active &&
                Objects.equals(id, account.id) &&
                Objects.equals(username, account.username);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, username, active);
    }
}

Here is the AccountRepository interface:

import java.util.Optional;
import java.util.Set;

public interface AccountRepository {

    Set<Account> findAll();

    Optional<Account> findByUsername(String username);

    Set<Account> findInactives();
}

We need to create the SQL fixtures that we will inject in our tests.

Firstly, we will need shared fixtures, the ones we will share with ALL of our tests. Which are, for example, cleaning the account table and insert a few default accounts. (With the help of Operations.sequenceOf(Operation...) which allows us to execute sequentially several Operation).

I personally think that sharing fixtures across several tests is acceptable if they are minimal and stable (because changing these fixtures could break several tests). Ideally we would have a small portion of shared fixtures and a lot of custom fixtures tailored for most of our tests.

We create SharedFixtures in src/test/java:

import com.ninja_squad.dbsetup.Operations;
import com.ninja_squad.dbsetup.operation.Operation;

public final class SharedFixtures {

    public static final Account adminAccount = 
            new Account("admin_id", "admin", true);

    public static final Account userAccount = 
            new Account("user_id", "user", true);

    public static Operation insertDefaultAccounts() {
        return Operations.sequenceOf(
                Operations.deleteAllFrom("account"),
                Operations.insertInto("account")
                        .columns("id", "username", "active")
                        .values(adminAccount.id(), adminAccount.username(), adminAccount.isActive())
                        .values(userAccount.id(), userAccount.username(), userAccount.isActive())
                        .build()
        );
    }
}

Secondly, we will need custom fixtures, the ones we will inject in some of our tests. Some special cases, which are, for example, an inactive account.

We create CustomFixtures in src/test/java:

import com.ninja_squad.dbsetup.Operations;
import com.ninja_squad.dbsetup.operation.Operation;

public final class CustomFixtures {

    public static final Account inactiveAccount = 
            new Account("inactive_id", "johndoe", false);

    public static Operation insertInactiveAccount() {
        return Operations.insertInto("account")
                .columns("id", "username", "active")
                .values(inactiveAccount.id(), inactiveAccount.username(), inactiveAccount.isActive())
                .build();
    }
}

We now have everything we need to write tests for AccountRepository.

All we need is to:

register our SqlMemoryDatabase extension
register our SqlFixture extension
use SqlFixture.inject() to inject our custom fixtures in specific tests

We create AccountRepositoryTest in src/test/java:

import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.RegisterExtension;

import static org.assertj.core.api.Assertions.assertThat;

public final class AccountRepositoryTest {

    private AccountRepository accountRepository;

    @RegisterExtension
    public static SqlMemoryDatabase sqlMemoryDatabase = new SqlMemoryDatabase();

    @RegisterExtension
    public SqlFixture sqlFixture = new SqlFixture(
            sqlMemoryDatabase,
            SharedFixtures.insertDefaultAccounts()
    );

    @BeforeEach
    void setUp() {
        // Here JdbcAccountRepository is a plain simple implementation of AccountRepository,
        // nothing interesting in the context of this post
        accountRepository = new JdbcAccountRepository(sqlMemoryDatabase.dataSource());
    }

    @Test
    void shouldFindAllAccounts() {
        sqlFixture.readOnly();

        assertThat(accountRepository.findAll())
                .containsOnly(SharedFixtures.adminAccount, SharedFixtures.userAccount);
    }

    @Test
    void shouldFindAccountByUsername() {
        sqlFixture.readOnly();

        assertThat(accountRepository.findByUsername("admin"))
                .contains(SharedFixtures.adminAccount);
    }

    @Test
    void shouldFindInactiveAccounts() {
        sqlFixture.inject(CustomFixtures.insertInactiveAccount());

        assertThat(accountRepository.findInactives())
                .containsOnly(CustomFixtures.inactiveAccount);
    }
}

In this case we inject only the fixture SharedFixtures.insertDefaultAccounts() before each test, but if we would need more than one, we would simply do as follows:

@RegisterExtension
public SqlFixture sqlFixture = new SqlFixture(
        sqlMemoryDatabase,
        Operations.sequenceOf(
                SharedFixtures.insertDefaultAccounts(), 
                SharedFixtures.insertXXX(),
                ...
        )
);

Summary

This is my favorite way of testing SQL code with Java. That way, we can tie directly our Java fixtures to the SQL data in one place, without handling SQL files. I find it simpler, faster, more explicit and more convenient than other tools, such as DBUnit.

(The whole project sources are available here)