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.

Answer 2

In case you do create your tables upfront like suggested here and still get a NoSuchTableException, then there is something wrong with the schema. Before you now turn crazy, fiddling with it in all sorts of weird and wonderful ways, try setting the schema parameter to PUBLIC when you create the IDatabaseConnection, like so:

IDatabaseConnection databaseConnection = new HsqldbConnection(sqlConnection, "PUBLIC");

It took me some stepping through the DbUnit code with the debugger but this seems to do the trick.