Introduction to DbChange JUnit extension

Easy and declarative way to execute sql queries in JUnit tests.

Introduction

JUnit test structure follows to test case model:

PreConditions
The actions that set system in partucular state required for performing test case.

Test case
The actions that change system state in order to compare system behavior with expected result.

PostConditions
The actions that set system into origin state, namely state before PreConditions.

JUnit provides correspond annotations to test case model:

1. PreConditions = @BeforeEach
2. Test case = @Test
3. PostConditions = @AfterEach

This structure in Java code:

JUnit test structure

Imagine, we need to test some back-end application that connect to relation database management system (RDBMS) like Postgresql. And we need to insert some values in database before start execute testCase() method:

Insert/Delete some data required for test case

Developer have to write some code in such case in order to implement execution of sql query against RDBMS. And we have to reuse this code in methods marked by@BeforeEach and @AfterEach annotations.

Such approach has following drawbacks:

• requires additional effort to implement execution of sql queries to database
• requires to test new sql query executor
• difficult to reuse code in other projects

And there is one more drawback that breaks all efforts to implement solution described above. Let’s see…

What is the problem?

Let’s add new testCase:

New test case added into test class.

Methods setUp() and setUp2() would be executed for both test methods: testCase() and testCase2().

Why?

So, it is design of JUnit. There is no information about which @BeforeEach method correspond to which @Test method. And JUnit executes all methods marked by @BeforeEach before executing each test method.

Point to notice:

JUnit provides TestInfo object. You can put this object as method arguments into setUp() and setUp2() and then use if statement in order to execute sql queries depends on test method name.

How to deal with it?

JUnit provides only one capability: enclose each testCase() method by inner class:

Solution of issue with execution corresponding @BeforeEach and @AfterEach for particular test method

Such approach solves issue with execution corresponding @BeforeEach and @AfterEach for particular test method. But it brings complexity into code especially to work with test class dependencies and also increase efforts and time to support code base.

Motivation

With JUnit out of box functionality we have a lot of issues to implement test cases with executing sql queries at PreConditions and PostConditions stages.

And there is handy solution for all this issues:

DbChange goals

1. Provide rich API to code sql queries that are executed in tests written on JUnit 5.
2. Simplify sql queries maintaining in code base.
3. Provide library independent of various frameworks (Uses only standard Java library and Junit 5 compile dependency)

DbChange core concept

There are three annotations:

1. DbChange
2. DbChangeOnce
3. SqlExecutorGetter

DbChange

Provide meta information about RDBMS changes before/after each test execution in class.

DbChangeOnce

Provide meta information about RDBMS changes before/after all tests execution in class.

SqlExecutorGetter

Set default sql executor for all tests in class. Value in this annotation should be the name of public method in test class that returns instance of DefaultSqlExecutor.

Annotations position in code:

DbChange annotatoins positions in code

How to plug library into project

Gradle

1. Open to edit build.gradle.kts (or build.gradle for groovy)
2. Add Dbchange dependency to project (example uses Kotlin)

Define DbChange dependency in Gradle project

Maven

1. Open to edit your project pom.xml
2. Add Dbchange dependency to dependecies section

Define DbChange dependency in Maven project

How to use DbChange

1. (mandatory) Put @ExtendWith(DbChangeExtension.class) on test class.
2. (mandatory) Create public method in test class that returns instance of DefaultSqlExecutor.
3. (optional) Put @DbChangeOnce on test class
4. (optional) Put @SqlExecutorGetter on test class
5. (optional) Put @DbChange on test method

Points to notice:

• If there are no annotations @DbChangeOnce or @DbChange in test class then Dbchange library does nothing during test execution.
• If @SqlExecutorGetter is not present on test class then it is mandatory to set value sqlExecutorGetter in each @DbChangeOnce and @DbChange annotations.
• If you use @DbChangeOnce in test then you have to initialize instance of DataSource class at test class constructor or in static context (for example, using JUnit annotation @BeforeAll)

Here is simple example of DbChange usage:

Simple example of DbChange usage

DbChange workflow

Dbchange has following workflow:

1. Gather information from @DbChange or @DbChangeOnce annotation.
2. Generate sql queries with named JDBC parameters.
3. Send sql queries and its parameters to sql query executor.
4. Sql query executor send sql queries and its parameters to RDBMS via JDBC driver.

DbChange has multiply sources of changes in RDBMS. And this sources are called sql queries suppliers.

Sql queries suppliers

DbChange executes sql queries that you provide in annotations @DbChange or @DbChangeOnce , namely in sql query supplier values.

There are following sql queries suppliers:

• statements
• sql query files
• changeset
• sql query getter
• using JUnit @MethodSoure (only for JUnit parameterized test)

Point to notice:

all sql queries suppliers (except @MethodSource) are supported by @DbChange and @DbChangeOnce annotations.

Let’s see each sql query supplier in details.

Statements

This annotation value provides capability to set inline sql query statements.

Example of statements usage in DbChange

Pros:

• Easy to use
• Use sql query explicitly
• Declarative way to execute sql query

Cons:

• Difficult to reuse sql query in other tests
• Difficult to read Java code if there are a lot of sql statements
• Difficult to customize sql query with parameters
• Difficult to understand what values are exactly used in particular test
• Requires a lot of boring actions if needs to change all statements in all tests

Sql query files

This annotation value provides capability to set sql file name with sql statements. This sql query supplier is really useful to unite multiply sql statements into one file.

Example of sql query files usage in DbChange

Pros:

• Easy to use
• It is more easier way to read Java code in comparision to statements value

Cons:

• Difficult to reuse sql query in other tests
• Difficult to customize sql query with parametes
• Difficult to understand what values are exactly used in particular test
• Requires a lot of boring actions if needs to change all statements in all tests

Changeset

This annotation provides capability to set array of class that implement com.github.darrmirr.dbchange.sql.query.SqlQuery interface. Here is usage example:

Example of change set usage in DbChange

How does it works?

Annotation value expect array of classes that that implement com.github.darrmirr.dbchange.sql.query.SqlQuery interface. Here is interface definition:

SqlQuery interface definition

It is quite simple interface that returns sql query as Java String object.

DbChange provides some predefined implementation of SqlQuery interface:

• TemplateSqlQuery
• EmptyTemplateSqlQuery
• InsertSqlQuery
• SpecificTemplateSqlQuery

All this classes dedicated to provide sql query with JDBC query parameters. Such approach gives opportunity to reuse code and customize sql query.

Point to notice:

It is recommend to use InsertSqlQuery or SpecificTemplateSqlQuery.

It is not forbidden to use TemplateSqlQuery and EmptyTemplateSqlQuery but they are mostly used for internal needs.

InsertSqlQuery

This class extends TemplateSqlQuery one and provides capability to create SQL query template depends on parameter names and table name.

Class InsertSqlQuery is abstract one and you have to extend it in order to use:

Example of InsertSqlQuery usage

DbChange generates sql query according to InsertEmploee7 class definition. Here is example of generated sql query:

Example of generated sql query

Then DbChange follows to its workflow. It has described above.

SpecificTemplateSqlQuery

This class also extends TemplateSqlQuery as InsertSqlQuery one but it has different purpose. This class dedicated to reuse TemplateSqlQuery and override sql query parameters that only needed for particular test. Here is example:

Example of usage SpecificTemplateSqlQuery.

Method commonTemplateSqlQuery() contains instance of TemplateSqlQuery class. This object will be get as a base for sql query creation. It means that sql query template and parameters will be get from this object. But SpecificTemplateSqlQuery brings to us opportunity to override or add new query parameters. And specificParameters() method dedicated for this purpose. Let’s see on common template class:

Common class to insert Employee entity to database

There are 5 query parameters in InsertEmployeeCommon class. But class InsertEmployee5 overrides only 3 of them via specificParameters() method.

So, SpecificTemplateSqlQuery class brings great opportunity to reuse code and simplify adding new one and changing already existed classes.

Let’s sum up pros and cons of using changeset sql query supplier.

Pros:

• Capability to reuse code for generating sql queries
• It is more easier to support code compare with text files and Java strings.
• Easy to develop and navigate through the code using IDE
• Capability to set only needed parameters for test using by SpecificTemplateSqlQuery
• There is no need to hard code insert sql query string in code. InsertSqlQuery class generates sql query for you.

Cons:

• Requires to create separate class as file for each sql query
• Requires default constructor in query class

Sql query getter

Changeset sql query supplier has a lot of advantanges, but it also has some disadvantages. And sql query getter aimed to provide dependency injection capability for classes that implement SqlQuery interface and get rid of creation of separate classes for each sql query.

There is interface SqlQueryGetter in DbChange. Here is its definition:

SqlQueryGetter interface definition

This interface supply list of objects that implement SqlQuery interface. So, let’s take a look how to use it SqlQueryGetter :

Example of usage Sql query getter

TemplateSqlQuery and InsertSqlQuery classes implement pattern Builder. It gives us declaritive way to create instance of this classes without its creation expicitly as separate file. Also you can create static class or use anonymous class and then use any dependencies that you injected or created in test class. And finally, you can use plain Java strings for supplying sql query.

Pros:

• Includes all pros from changeset sql supplier
• There is no default sql constructor requirement
• There is no requirement to create separate class as file for sql query

Cons:

• Requires to create additional methods in test class

DbChange and parameterized Test

DbChange also provides capability to execute sql queries for parameterized test. It means you can define individual sql queries for each test during parameterized test execution.

DbChange supports only @MethodSource as source of changes to database. Let’s see code snippet:

Example of usage DbChange in parameterized tests

First, there is no @DbChange annotation in parameterized test. You can put it on method and then the same sql queries from this annotation would be executed for each test during parameterized test execution.

Second, you must put List<DbChangeMeta> dbChangeMetas in method arguments. It is mandatory due to JUnit internal logic.

What is DbChangeMeta?

DbChangeMeta is class in DbChange JUnit extension. Internally, DbChange covert all information from @DbChange and @DbChangeOnce into instances of DbChangeMeta at first step of its workflow. It developed in such way in order to simplify DbChange code base. Usually, extension user works only with @DbChange and @DbChangeOnce annotation. But there is one exception from this rule. It is parameterized test.

DbChange expects that list of DbChangeMeta will be provided at one of test method arguments. If such item is absent in method arguments then DbChange do nothing.

DbChangeMeta has the same structure as @DbChange and @DbChangeOnce annotations. And all rules of using sql query suppliers are applyed for DbChangeMeta too.

Chained sql queries

Let’s see on example:

Example of usage changeset sql query provider

It is not obvious from this code that InsertEmployee5.class depends on InsertOccupation3.class and InsertDepartment9.class . And if we change order, for example put InsertEmployee5.class at the top of changeSet list, then test execution finished with exception. It happens because there is no yet department and occupation in database that employee belongs to.

DbChange provides capability to chain such sql queries and execute it in proper order. There is interface dedicated for this:

Definition of ChainedSqlQuery interface

Point to notice:

Such capability available only for changeset and sqlQueryGetter sql query suppliers.

ChainedSqlQuery interface is quite simple. It has only one method next() . Let’s have a look how to use it:

Example of using chained sql queries

There are a lot of code lines at first glance. Let’s go through it.

InsertEmployee5Chained class is top level one and it extends SpecificTemplateSqlQuery class. This class reuses InsertDepartmentCommon and overrides one parameter at insert department sql query.

It maybe looks strange that top level class has name InsertEmployee5, but it contains information about department insertion. It is ok because employee could not be created without department according to example business domain. Therefore, we have to insert this values first because employee depends of it.

Also InsertEmployee5Chained class implements ChainedSqlQuery interface. And method next() point to next SqlQuery that must be executed after current one. So, next sql query is InsertOccupation3.

InsertOccupation3 class also implements ChainedSqlQuery interface. And next sql query is InsertEmployee5 .

So, sql query chain consist of three points:

insert department -> insert occupation -> insert employee.

Point to notice

You can chain queries as many as you need. Chain size is restricted only by thread stack size of your Java Virtual Machine (JVM)

And finally, let’s change code in @DbChange annotation:

Example of usage chained sql query

As you can see, there is only one class in changeSet list instead of three ones in previous version of this test.

DbChange Execution Phase

You maybe notice value executionPhase at @DbChange or @DbChangeOnce annotations in examples of this article.

Execution phase describes time when sql query should be executed during test execution. DbChange execution phases correspond to JUnit ones.

From my point of view, names of phase are self-explanatory. But please read JUnit official documentation if names are not clear for you.

SqlExecutorGetter

Usually, application has one DataSource. But sometimes application could have several DataSources. DbChange provides sqlExecutorGetter value for this case. This value contains method name in test class that returns instance of com.github.darrmirr.dbchange.sql.executor.SqlExecutor class.

Let’s see on example:

Example of usage sqlExecutorGetter

DbChange gets SqlExecutor from datasource2SqlExecutor method for InsertBankList.class and DeleteBankList.class . DbChange uses SqlExecutor from defaultSqlExecutor method if sqlExecutorGetter value is empty in @DbChange annotation. Such behavior is the same for @DbChangeOnce annotation.

Point to notice

If @SqlExecutorGetter is not present on test class then it is mandatory to set value sqlExecutorGetter in each @DbChangeOnce and @DbChange annotations.

Conclusion

DbChange is JUnit 5 extension that provides capability in declarative way to set sql queries and execute it in PreConditions and PostConditions stages.

DbChange repository is available on Github.com.
See usage examples in com.github.darrmirr.dbchange.component.DbChangeUsageTest class.