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)