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?

Glottochronology

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.

Triumvirate

I have now switched to a local Oracle XE instance running in a VM. The reason for this is that we can continue developing when not connected to the internal network. – Triumvirate

With an in-memory database, I can run unit tests anywhere, without having to switch up any configuration and without having to start up a DB server. Primarily they run on various dev boxes and on the CI server. That's a huge advantage in my book. – Glance

Yes it is. I personally found it to be significantly slower however this may have changed now. The time taken to start a local VM'ed Oracle XE instance is a short once-per-day thing. – Triumvirate

Answer 2

...several years later now we have better options

Spring Boot/ Spring JDBC can initialize a database with plain JDBC.

Spring JDBC has a DataSource initializer feature. Spring Boot enables it by default and loads SQL from the standard locations schema.sql and data.sql (in the root of the classpath). In addition Spring Boot will load the schema-${platform}.sql and data-${platform}.sql files (if present), where platform is the value of spring.datasource.platform, e.g. you might choose to set it to the vendor name of the database (hsqldb, h2, oracle, mysql, postgresql etc.).

https://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html

Gillan

Answer 3

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.

Woermer