How do I test with DBUnit with plain JDBC and HSQLDB without facing a NoSuchTableException?

Question

I am trying to use DBUnit with plain JDBC and HSQLDB, and can't quite get it to work -- even though I've used DBUnit with Hibernate earlier with great success. Here's the code:

import java.sql.PreparedStatement;
import org.dbunit.IDatabaseTester;
import org.dbunit.JdbcDatabaseTester;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.XmlDataSet;
import org.junit.Test;

public class DummyTest {

    @Test
    public void testDBUnit() throws Exception {
        IDatabaseTester databaseTester = new JdbcDatabaseTester("org.hsqldb.jdbcDriver", "jdbc:hsqldb:mem", "sa", "");
        IDataSet dataSet = new XmlDataSet(getClass().getResourceAsStream("dataset.xml"));
        databaseTester.setDataSet(dataSet);
        databaseTester.onSetup();
        PreparedStatement pst = databaseTester.getConnection().getConnection().prepareStatement("select * from mytable");
    }
}

And this is the dataset.xml in question:

<dataset>
    <table name="mytable">
        <column>itemnumber</column>
        <column>something</column>
        <column>other</column>
        <row>
            <value>1234abcd</value>
            <value>something1</value>
            <value>else1</value>
        </row>
    </table>
</dataset>

This test gives me a NoSuchTableException:

org.dbunit.dataset.NoSuchTableException: mytable
    at org.dbunit.database.DatabaseDataSet.getTableMetaData(DatabaseDataSet.java:282)
    at org.dbunit.operation.DeleteAllOperation.execute(DeleteAllOperation.java:109)
    at org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:79)
    at org.dbunit.AbstractDatabaseTester.executeOperation(AbstractDatabaseTester.java:190)
    at org.dbunit.AbstractDatabaseTester.onSetup(AbstractDatabaseTester.java:103)
    at DummyTest.testDBUnit(DummyTest.java:18)

If I remove the databaseTester.onSetup() line, I get an SQLException instead:

java.sql.SQLException: Table not found in statement [select * from mytable]
    at org.hsqldb.jdbc.Util.throwError(Unknown Source)
    at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
    at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
    at DummyTest.testDBUnit(DummyTest.java:19)

The dataset in itself is working, since I can access it like it should:

ITable table = dataSet.getTable("mytable");
String firstCol = table.getTableMetaData().getColumns()[0];
String tName = table.getTableMetaData().getTableName();

What am I missing here?

EDIT: As @mlk points out, DBUnit doesn't create tables. If I insert the following before adding the dataset, everything goes smoothly:

PreparedStatement pp = databaseTester.getConnection().getConnection().prepareStatement(
     "create table mytable ( itemnumber varchar(255) NOT NULL primary key, "
   + " something varchar(255), other varchar(255) )");
pp.executeUpdate();

I posted a followup question as Is there any way for DBUnit to automatically create tables from a dataset or dtd?

Answer 1

dbUnit does not create tables. Nor could it with the limited information given in the XML file. Hibernate I believe can create the tables.

This is one of the reasons I stopped using in-memory databases and instead got the DBA to give each developer their own database. Every developer then keeps the database up to date using the same scripts which are later ran on live. This adds a small overhead (all developers need to keep their databases up to date) but means you don't need to mess about building the database for each run and you can be sure that the queries ran in test work in live.

The second reason was speed. I found creating the in memory-database took a lot longer than simply connecting to an existing database.

The third reason was the tear down is none-destructive (start up wipes the database). This means I can run the SQL under test on the database to help work out why a test is failing.

---

Update: 20171115

I've since switched to using JUnit rules that start up a real instance of the database server and something like FlywayDB to build the database (and using the same scripts in live as in test, with the application responsible for building the database). It is significantly slower than using a prebuilt database. However using well defined microservices (and so reducing the functionality that needs testing) and being very tight on which tests gets a database you can migrate such issues and get the benefits of local database that always matches live.

It does alas mean the test tear down is always destructive, but a well-placed break point solves that.