Testing Spring Boot with TestNG-Chapter 4: Testing Relational Databases

Testing Spring Boot with TestNG

I prefer to use TestNG for unit and integration testing, so the examples in this paper are based on TestNG.
And Spring & Spring Bot's official examples of Testing are mostly based on JUnit, so this article can also provide some useful help to TestNG enthusiasts.

Github address

Chapter list

Chapter 4: Testing Relational Databases

Spring Test Framework Provided with JDBC support It makes it easy for us to do integration testing for relational databases.

Spring Boot also provides and Flyway Of Integrate Support, can easily manage the SQL files generated in the development process, with Spring has provided tools to more easily initialize the database before testing and after testing empty the database.

This chapter uses H2 as the test database for convenience.

Note: In a real development environment, the integrated test database should be consistent with the final production database. This is because different databases are not completely compatible with each other for SQL. If you do not pay attention to this point, it is likely that the integrated test will pass, but it will be wrong in the production environment.

Because it's integration testing, we use maven-failsafe-plugin to run. The difference between maven-failsafe-plugin and maven-surefire-plugin is that maven-failsafe-plugin only searches for * IT.java to run tests, while maven-surefire-plugin only searches for * Test.java to run tests.

If you want to skip integration testing while packaging maven, you only need MVN clean install-DskipITs.

Tested class

First, introduce the class under test.

Foo.java:

public class Foo {

  private String name;

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }
}

FooRepositoryImpl.java:

@Repository
public class FooRepositoryImpl implements FooRepository {

  private JdbcTemplate jdbcTemplate;

  @Override
  public void save(Foo foo) {
    jdbcTemplate.update("INSERT INTO FOO(name) VALUES (?)", foo.getName());
  }

  @Override
  public void delete(String name) {
    jdbcTemplate.update("DELETE FROM FOO WHERE NAME = ?", name);
  }

  @Autowired
  public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
    this.jdbcTemplate = jdbcTemplate;
  }

}

Example 1: Do not use the tools provided by Spring Testing

Spring_1_IT_Configuration.java:

@Configuration
@ComponentScan(basePackageClasses = FooRepository.class)
public class Spring_1_IT_Configuration {

  @Bean(destroyMethod = "shutdown")
  public DataSource dataSource() {

    return new EmbeddedDatabaseBuilder()
        .generateUniqueName(true)
        .setType(EmbeddedDatabaseType.H2)
        .setScriptEncoding("UTF-8")
        .ignoreFailedDrops(true)
        .addScript("classpath:me/chanjar/domain/foo-ddl.sql")
        .build();
  }

  @Bean
  public JdbcTemplate jdbcTemplate() {

    return new JdbcTemplate(dataSource());

  }
}

In Spring_1_IT_Configuration, we define a Data Source Bean of H2 and construct a JdbcTemplate Bean.

Notice the code addScript("classpath:me/chanjar/domain/foo-ddl.sql"), and let Embedded Database execute it. foo-ddl.sql The script builds the table:

CREATE TABLE FOO (
  name VARCHAR2(100)
);

Spring_1_IT.java:

@ContextConfiguration(classes = Spring_1_IT_Configuration.class)
public class Spring_1_IT extends AbstractTestNGSpringContextTests {

  @Autowired
  private FooRepository fooRepository;

  @Autowired
  private JdbcTemplate jdbcTemplate;

  @Test
  public void testSave() {

    Foo foo = new Foo();
    foo.setName("Bob");
    fooRepository.save(foo);

    assertEquals(
        jdbcTemplate.queryForObject("SELECT count(*) FROM FOO", Integer.class),
        Integer.valueOf(1)
    );

  }

  @Test(dependsOnMethods = "testSave")
  public void testDelete() {

    assertEquals(
        jdbcTemplate.queryForObject("SELECT count(*) FROM FOO", Integer.class),
        Integer.valueOf(1)
    );

    Foo foo = new Foo();
    foo.setName("Bob");
    fooRepository.save(foo);

    fooRepository.delete(foo.getName());
    assertEquals(
        jdbcTemplate.queryForObject("SELECT count(*) FROM FOO", Integer.class),
        Integer.valueOf(0)
    );
  }

}

As you can see in this test code, we tested the save and delete methods of FooRepository, and used JdbcTemplate to validate the results in the database.

Example 2: Using the tools provided by Spring Testing

In this case, we will use JdbcTestUtils To assist in testing.

Spring_2_IT_Configuration.java:

@Configuration
@ComponentScan(basePackageClasses = FooRepository.class)
public class Spring_2_IT_Configuration {

  @Bean
  public DataSource dataSource() {

    EmbeddedDatabase db = new EmbeddedDatabaseBuilder()
        .generateUniqueName(true)
        .setType(EmbeddedDatabaseType.H2)
        .setScriptEncoding("UTF-8")
        .ignoreFailedDrops(true)
        .addScript("classpath:me/chanjar/domain/foo-ddl.sql")
        .build();
    return db;
  }

  @Bean
  public JdbcTemplate jdbcTemplate() {

    return new JdbcTemplate(dataSource());

  }

  @Bean
  public PlatformTransactionManager transactionManager() {
    return new DataSourceTransactionManager(dataSource());
  }

}

The difference between this and Example 1 is that we provide a Platform Transaction Manager Bean because AbstractTransactional TestNGSpringContextTests in the following test code needs it.

Spring_2_IT.java:

@ContextConfiguration(classes = Spring_2_IT_Configuration.class)
public class Spring_2_IT extends AbstractTransactionalTestNGSpringContextTests {

  @Autowired
  private FooRepository fooRepository;

  @Test
  public void testSave() {

    Foo foo = new Foo();
    foo.setName("Bob");
    fooRepository.save(foo);

    assertEquals(countRowsInTable("FOO"), 1);
    countRowsInTableWhere("FOO", "name = 'Bob'");
  }

  @Test(dependsOnMethods = "testSave")
  public void testDelete() {

    assertEquals(countRowsInTable("FOO"), 0);

    Foo foo = new Foo();
    foo.setName("Bob");
    fooRepository.save(foo);

    fooRepository.delete(foo.getName());
    assertEquals(countRowsInTable("FOO"), 0);

  }

}

Here we use countRowsInTable("FOO") to validate the database results, which is the agent of AbstractTransactional TestNGSpringContextTests for JdbcTestUtils.

Also note that each test method rolls back automatically after execution, so in the first line of test Delete, we assert Equals (countRowsInTable ("FOO"), 0), which is different from that in Example 1.

For more information about Spring Testing Framework and Transaction, see Spring's official documentation Transaction management.

Example 3: Using Spring Boot

Boot_1_IT.java:

@SpringBootTest
@SpringBootApplication(scanBasePackageClasses = FooRepository.class)
public class Boot_1_IT extends AbstractTransactionalTestNGSpringContextTests {

  @Autowired
  private FooRepository fooRepository;

  @Test
  public void testSave() {

    Foo foo = new Foo();
    foo.setName("Bob");
    fooRepository.save(foo);

    assertEquals(countRowsInTable("FOO"), 1);
    countRowsInTableWhere("FOO", "name = 'Bob'");
  }

  @Test(dependsOnMethods = "testSave")
  public void testDelete() {

    assertEquals(countRowsInTable("FOO"), 0);

    Foo foo = new Foo();
    foo.setName("Bob");
    fooRepository.save(foo);

    fooRepository.delete(foo.getName());
    assertEquals(countRowsInTable("FOO"), 0);

  }
  
  @AfterTest
  public void cleanDb() {
    flyway.clean();
  }
  
}

Because Spring Boot is used for integration testing, thanks to its AutoConfiguration mechanism, it is not necessary to build Bean s of DataSource, JdbcTemplate and Platform Transaction Manager by itself.

And because we have added flyway-core to our maven dependencies, Spring Boot will use flyway to help us initialize the database. All we need to do is to put sql files in the db/migration directory of classpath:

V1.0.0__foo-ddl.sql:


CREATE TABLE FOO (
  name VARCHAR2(100)
);

And at the end of the test, we emptied the database with flyway:

@AfterTest
public void cleanDb() {
  flyway.clean();
}

flyway has many advantages:

The version number is specified for each sql file name
flyway is executed in the order of version number
During development, you just need to put sql files in the db/migration directory instead of writing code like Embedded Database Builder. addScript ().
Based on the above three points, the database initialization SQL statements can also be incorporated into the integration test to ensure the correctness of the code matching SQL statements.
It can help you empty the database, which is very useful when you use non-memory database, because you need a clean database before and after the test.

Reference document

This chapter covers Spring Testing Framework JDBC, SQL-related tools:

flyway related:

Posted by matt_wonders on Tue, 25 Dec 2018 15:45:07 -0800

Programmer Group