Spring boot and Database testing with connection pool

Question

I am trying to create tests for my app which connects to a database. The DataSource is a conection pool (Hikari).

Here is my test configuration:

@Configuration
public class SqlTestConfig {

    @Bean
    DataSource dataSource() {
        HikariConfig config = new HikariConfig();
        config.setMaximumPoolSize(2);
        config.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        config.setJdbcUrl("jdbc:sqlserver://serversql:1433;database=myDatabase");
        config.setUsername("user");
        config.setPassword("password");
        return new HikariDataSource(config);
    }
}

Here is my test class:

@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = SqlTestConfig.class)
@Slf4j
@Sql(
        scripts = "/clearTables.sql",
        config = @SqlConfig(separator = "GO")
)
public class SqlTest {

    @Autowired
    DataSource dataSource;

    @Test
    public void test1() throws SQLException {
        log.info("catalog:" + dataSource.getConnection().getCatalog());
    }

    @Test
    public void test2() throws SQLException {
        log.info("catalog:" + dataSource.getConnection().getCatalog());
    }

    @Test
    public void test3() throws SQLException {
        log.info("catalog:" + dataSource.getConnection().getCatalog());
    }

    @Test
    public void test4() throws SQLException {
        log.info("catalog:" + dataSource.getConnection().getCatalog());
    }
}

Notice that the MaximumPoolSize is set to 2. When I run the test class the first two tests are successfully completed and the remaining tests fail because the pool gets depleted of connections (connection timeout).

I believe the problem is because of the @Sql annotation which causes DataSourceInitializer -s to be created to execute the cleanup script but the connections are never returned to the pool.

When I set MaximumPoolSize to 4 all tests are successfully completed. I cannot tell if I have made a configuration error or if this is a bug in Spring.

Answer 1

The getConnection acquires connection from underlying pool. Change your tests to properly close the acquired connection like so:

@Test
public void test1() throws SQLException {
    try (Connection connection = dataSource.getConnection()) {
        log.info("catalog:" + connection.getCatalog());
    }
}