Use DBUnit in Spring without defined entities via "nativequery"

Question

I am trying to use DBunit while testing a Spring controller. The tests fail to initialize because my dataset contains tables/columns which are not defined by an entity.

The queries are defined with the nativeQuery = true flag so I wouldn't expect things to go awry:

@Query(value = "select * from TEST_TABLE where TEST_COLUMN = '2'";, nativeQuery = true)
TestClass getTestClass();

The Dataset.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
    <TEST_TABLE TEST_COLUMN="2"/>
</dataset>

The Test Class:

@RunWith(SpringJUnit4ClassRunner.class)
@ActiveProfiles("test")
@ContextConfiguration(classes = {Application.class, RepoTestApplication.class})
@SpringBootTest(classes = MockServletContext.class)
@TestPropertySource(properties = {"server.port=8444","hibernate.hbm2ddl.auto=create-drop"})
@WebAppConfiguration
@DirtiesContext(classMode = DirtiesContext.ClassMode.BEFORE_CLASS)
@TestExecutionListeners({DbUnitTestExecutionListener.class, DependencyInjectionTestExecutionListener.class,
        DirtiesContextBeforeModesTestExecutionListener.class})
@DatabaseSetup("classpath:datasets/Dataset.xml")
public class TestClassTest { ... }

The referenced "RepoTestApplication.class":

@Configuration
@EnableJpaRepositories
public class RepoTestApplication {
    
    @Bean
    public DataSource dataSource() {
        SimpleDriverDataSource dataSource = new SimpleDriverDataSource();
        dataSource.setDriverClass(org.h2.Driver.class);
        dataSource.setUrl("jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE");
        dataSource.setUsername("sa");
        return dataSource;
    }

}

The Error:

org.dbunit.dataset.NoSuchTableException: APP_VER
    at org.dbunit.database.DatabaseDataSet.getTableMetaData(DatabaseDataSet.java:305)
    ...

It seems like the table doesn't create itself, even though I added the property "hibernate.hbm2ddl.auto=create-drop". Interestingly, I don't get this issue if the dataset references a table which is defined by an entity like:

@Entity
@Table(name = "users")
public class UserRecord { ... }

Answer 1

I overcame this by specifying a DataSourceInitializer Bean which runs a sql file for setting up tables:

@Configuration
public class RepoNativeQueryDataSourceInitializer {

    @Bean
    public DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
        ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator();
        resourceDatabasePopulator.addScript(new ClassPathResource("/schemas/schema.sql"));
        DataSourceInitializer dataSourceInitializer = new DataSourceInitializer();
        dataSourceInitializer.setDataSource(dataSource);
        dataSourceInitializer.setDatabasePopulator(resourceDatabasePopulator);
        return dataSourceInitializer;
    }

}

In the referenced SQL File (src/test/resources/schemas/schema.sql) I also attempt to delete the tables first in-case they were already created by JPA/Hibernate. This also let me get rid of any constraints which I didn't want to deal with:

drop table if exists EXAMPLE_TABLE;
create table EXAMPLE_TABLE
(
    EXAMPLE_TABLE_ID                 NUMBER(20),
)

...

My final test class configuration ended up looking like this:

@RunWith(SpringJUnit4ClassRunner.class)
@ActiveProfiles("test")
@SpringBootTest
@ContextConfiguration(classes = {Application.class, RepoTestApplication.class, RepoNativeQueryDataSourceInitializer.class})
@TestExecutionListeners({DbUnitTestExecutionListener.class, DependencyInjectionTestExecutionListener.class})
@TestPropertySource(properties = {"spring.jpa.properties.hibernate.show_sql=true", "server.port=8445"})
@DatabaseSetup("classpath:datasets/AllMyDataForTheTests.xml")
public class StuffRepositoryTest {

    @Autowired
    StuffRepository stuffRepository;

    ...
}