DBUnit and Spring Boot - Data may not be imported or existing when requesting in an integration test

Question

If I run dbunit with the following setup and request data via HTTP in an integration test, I didn't get any data because the database is empty. DBUnit writes data to the database, but it's empty when I request the data via HTTP.

This is my setup: Spring Boot 1.1.7 with spring-boot-starter-web (exclude tomcat), spring-boot-starter-jetty, spring-boot-starter-data-jpa, spring-boot-starter-test, liquibase-core, dbunit 2.5.0, spring-test-dbunit 1.1.0

Main Application Class:

@Configuration
@ComponentScan
@EnableAutoConfiguration
@RestController
@EnableTransactionManagement
@EnableJpaRepositories

Test configuration (application-test.yaml):

logging.level.org.springframework: DEBUG
logging.level.org.dbunit: DEBUG

spring.jpa.properties.hibernate.hbm2ddl.auto: update
spring.jpa.database: h2
spring.jpa.show-sql: true

// setting these properties to access the database via h2 console
spring.datasource.url: jdbc:h2:tcp://localhost/mem:my_db;DB_CLOSE_DELAY=-1;MVCC=true;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.username: sa
spring.datasource.password: sa
spring.datasource.driverClassName: org.h2.Driver
spring.jpa.database-platform: org.hibernate.dialect.H2Dialect

liquibase.change-log: classpath:/db/changelog/db-master.xml

Integration test:

@ActiveProfiles("test")
@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = HDImageService.class)
@TestExecutionListeners({
    DependencyInjectionTestExecutionListener.class,
    DbUnitTestExecutionListener.class })
@WebAppConfiguration
@IntegrationTest("server.port:0")
@DatabaseSetup("/database_seed.xml")
@DatabaseTearDown(value = "/database_tear_down.xml", type = DatabaseOperation.DELETE_ALL)

// test
@Test
public void get_works() throws Exception {
    // given
    String url = host + port + "/my-resource/1";

    // when
    ResponseEntity<String> response = template.getForEntity(url, String.class);

    // then
    assertThat(response.getStatusCode(), is(HttpStatus.OK));
}

I could post everything else here, like the entities, repository, controller, ... but these components are working, because I already write in the test via the injected repository to the database and get it via HTTP. So the problem is the import via dbunit, which is not working... I already used dbunit successfully in an older projected, but not together with spring boot. Perhaps the execution listeners are working not similar with spring boot?

I debug threw the classes of dbunit and read all the debug log output, but I don't get it. DBUnit is using the spring boot created dataSource (the above configured), so it's the same database.

When starting the integration tests, the following is happen:
- liquibase creates the database schema based on the liquibase configuration (perhaps jpa already pushed the schema before?)
- DBUnit insert into the database (says the log output and debugging)
- Get 404 not found (I return a 404 when no entry was found in the database with the given ID)

Update:

I'm searching for an alternative to dbunit, but can't find any good solution. So how do you prepare your database for your integration tests? Actually I'm only need to import individuell data before every test or test if the data is persisted as expected.

Update:

I use the following options to connect to the h2 database:

DB_CLOSE_DELAY=-1;MVCC=true;DB_CLOSE_ON_EXIT=FALSE

When I removed the complete spring.datasource.* configuration spring is creating the datasource with standard values and start an in-memory h2 database server. This will be done without the options I mentioned and I get a org.hibernate.PessimisticLockException because dbunit still locks the database table and the HTTP request which was send inside the test, has no access to the database table. This is because of the option MVCC=true; which added higher concurrency, what is basically the problem, why no data is present: "Connections only 'see' committed data, and own changes". When accessing the database via the HTTP request, the data of dbunit is not present, because the data of dbunit is not committed for the spring connection...

So, does anyone know why the h2 (and also derby) database table(s) are locked by dbunit?

score 6 · Accepted Answer · answered Oct 10 '14 at 11:44

I finally found a solution for this issue.

It was the right direction with the PessimisticLockException where I pointed to. DBUnit didn't release the database connection, that's why the connection from spring couldn't access the database table(s) which where locked.

I implemented my own database operation. I used the option to customize DBUnit database options.

First of all I implemented a class called AutoCommitTransactionOperation based on the TransactionOperation of DBUnit, with the difference that I removed the check jdbcConnection.getAutoCommit() == false and saved the auto commit value before setting auto commit to false. After the commit I set the value back to the saved value, to have the same state like before:

public class AutoCommitTransactionOperation extends DatabaseOperation {

    private final DatabaseOperation _operation;

    public AutoCommitTransactionOperation(DatabaseOperation operation) {
        _operation = operation;
    }

    public static final DatabaseOperation AUTO_COMMIT_TRANSACTION(DatabaseOperation operation) {
        return new AutoCommitTransactionOperation(operation);
    }

    public void execute(IDatabaseConnection connection, IDataSet dataSet) throws DatabaseUnitException, SQLException {
        logger.debug("execute(connection={}, dataSet={}) - start", connection, dataSet);

        IDatabaseConnection databaseConnection = connection;
        Connection jdbcConnection = databaseConnection.getConnection();

        boolean autoCommit = jdbcConnection.getAutoCommit();
        jdbcConnection.setAutoCommit(false);
        try {
            _operation.execute(databaseConnection, dataSet);
            jdbcConnection.commit();
        } catch (DatabaseUnitException e) {
            jdbcConnection.rollback();
            throw e;
        } catch (SQLException e) {
            jdbcConnection.rollback();
            throw e;
        } catch (RuntimeException e) {
            jdbcConnection.rollback();
            throw e;
        } finally {
            jdbcConnection.setAutoCommit(autoCommit);
        }
    }
}

Then I created the DatabaseLookup.

public class AutoCommitTransactionDatabaseLookup extends DefaultDatabaseOperationLookup {

    @Override
    public org.dbunit.operation.DatabaseOperation get(DatabaseOperation operation) {
        if (operation == operation.CLEAN_INSERT) {
            return AutoCommitTransactionOperation.AUTO_COMMIT_TRANSACTION(org.dbunit.operation.DatabaseOperation.CLEAN_INSERT);
        }
        return super.get(operation);
    }
}

and added it to my test class:

@DbUnitConfiguration(databaseOperationLookup = AutoCommitTransactionDatabaseLookup.class)

I'm not sure if this more hack... any hints to my hack?

I find it absolutely awesome that you shared your solution in such an amount of detail. I believe I ran into the same problem at work, although in my own dummy project at home it works both with and without your solution. I'll try it at work on Monday, that's for sure. — Serg Derbst, Jun 27 '15 at 18:27

DBUnit and Spring Boot - Data may not be imported or existing when requesting in an integration test

1 Answers1