org.dbunit.dataset.NoSuchTableException: Did not find table 'xxx' in schema 'null'

Question

I know there have been discussions wrt to dbunit here. I have read most of them but I cant seem to find a solution to my problem.

I have set up hibernate and spring. I am doing TDD so I had to wire up a proper DAO testing framework before writing code. Dbunit came to mind and I got to setting it up. Here is ma testdataset.xml

    <?xml version='1.0' encoding='UTF-8'?>
    <dataset>
        <table name="status">
            <column>statusId</column>
            <column>status</column>
            <row>
                <value>0</value>
                <value>Available</value>
            </row>
        </table>
        <table name="user">
            <column>userId</column>
            <column>firstName</column>
            <column>lastName</column>
            <column>username</column>
            <column>password</column>
            <column>email</column>
            <row>
                <value>0</value>
                <value>system</value>
                <value>admin</value>
                <value>admin</value>
                <value>admin</value>
                <value>[email protected]</value>
            </row>
        </table>
        <table name="reservation">
            <column>reservationId</column>
            <column>userId</column>
            <column>reservationDate</column>
            <column>startDate</column>
            <column>endDate</column>
            <column>statusId</column>
            <row>
                <value>0</value>
                <value>0</value>
                <value>2011-02-20 12:46:00.0</value>
                <value>2011-03-01 12:00:00.0</value>
                <value>2011-04-01 12:00:00.0</value>
                <value>0</value>
            </row>
        </table>
    </dataset>

All seems well until i try to wire up some code using a base class that loads the dataset. Here is my code:

@Transactional
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath:test-applicationContext.xml" })
@TransactionConfiguration(transactionManager = "transactionManager", defaultRollback = true)
public class BaseContextSensitiveTest {

    @BeforeClass
public static void setUpDatabase() throws Exception {
    URL file = getInitalDatasetURL();
    testDataset = createDataset(file);
}

   @Before
public void init() throws Exception {
    log.info("Initializing Data Set");
    connection = createDBUnitConnection();

    DatabaseOperation.CLEAN_INSERT.execute(connection, testDataset);
}

private static URL getInitalDatasetURL() throws FileNotFoundException {
    URL file = ClassLoader.getSystemResource(TEST_DATASET_LOCATION);
    if (file == null) {
        throw new FileNotFoundException("Unable to find '"
                + TEST_DATASET_LOCATION + "' in the classpath");
    }
    return file;
}

private static IDataSet createDataset(URL file) throws IOException,
        DataSetException {

    return new XmlDataSet(file.openStream());
}

private IDatabaseConnection createDBUnitConnection()
        throws DatabaseUnitException, SQLException {

    Connection connection = getConnection();
    IDatabaseConnection dbUnitConn = new DatabaseConnection(connection);

    // use the hsql datatypefactory so that boolean properties work
    // correctly
    DatabaseConfig config = dbUnitConn.getConfig();
    config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY,
            new HsqldbDataTypeFactory());

    return dbUnitConn;
}

As soon as it hits the DatabaseOperation.CLEAN_INSERT.execute(connection, testDataset); it throws the following exception:

org.dbunit.dataset.NoSuchTableException: Did not find table 'USER' in schema 'null'
at org.dbunit.database.DatabaseTableMetaData.<init>(DatabaseTableMetaData.java:142)
at org.dbunit.database.DatabaseDataSet.getTableMetaData(DatabaseDataSet.java:290)
at org.dbunit.operation.DeleteAllOperation.execute(DeleteAllOperation.java:109)
at org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:79)
at com.cottage.test.BaseContextSensitiveTest.init(BaseContextSensitiveTest.java:64)

I have all the hibernate mapping files in place and the database is already set up with no data. Funny thing (or annoying thing depending on how you look at it) is that if I change the order of the tables in the dataset, the missingtableexception complains of another table... either user, reservation or status.

Any suggestions on what I might be doing wrong?

Gesundheit

What happen if you pre-pend the schema name to the table name status --> myschema.status ? – Bellbird

@Luciano Fiandesio, do you mean when creating IDatabaseConnection dbUnitConn = new DatabaseConnection(connection, "SCHEMA?NAME")? – Gesundheit

No, I mean in the xml dataset file <table name="status"> becomes <table name="myschema.status"> – Bellbird

How does your JDBC connection string look like? – Burnett

@Luciano Fiandesio: I tried that but get the same error. only with the schema name prepended to the "missing table". – Gesundheit

Answer 1

DBUnit will not create database tables for you because it has limited information from the dataset xml to create the intended database schema.

When used in tandem with hibernate, it will require proper hbm mapping files for every pojo (that you are trying to map to the in memory test database tables), which is ultimately used in the tests. Without the mapping files, you will get the org.dbunit.dataset.NoSuchTableException: Did not find table 'xxx' in schema 'yyy'.

Also, a valid hibernate.cfg.xml is required, correctly configured with all the hibernate mapping files.

You can delegate the database creation to hibernate by setting this property hibernate.hbm2ddl.auto=create-drop in your properties file.

The error message is a little misleading. Should probably contain more information to the effect of missing hibernate mapping files -- but this is a discussion on the DBunit wiki.

Gesundheit

Answer 2

I've also come across this same error and the accepted fix above did not fix my problems. However I was able to find the solution.

My setup consisted of DBUnit(2.4), EclipseLink(2.1) as my JPA provider, and Postgres as my backend database. Also, in my scenario I was not dropping and recreating the tables for each test run. My test data already existed. Bad practice I know, but it was more of a test/prototyping scenario. The code below illustrates the DBUnit configuration used to fix my problem.

54    // ctx represents a spring context
55    DataSource ds = (DataSource)ctx.getBean("myDatasourceBean");
56    Connection conn = DataSourceUtils.getConnection(ds);
57    IDatabaseConnection dbUnitConn = new DatabaseConnection(conn, "public");
58    DatabaseConfig dbCfg = dbUnitConn.getConfig();
59    dbCfg.setFeature(DatabaseConfig.FEATURE_CASE_SENSITIVE_TABLE_NAMES, Boolean.TRUE);
60    IDataSet dataSet = new FlatXmlDataSet(ClassLoader.getSYstemResourceAsStream("mydbunitdata.xml"));
61    DatabaseOperation.REFRESH.execute(dbUnitConn, dataSet);

Two things in the code above fixed my problem. First I needed to define the schema DBUnit should use. That is done on line 57 above. When the new DatabaseConnection is set, the schema("public") should be passed in if it is not null.

Secondly, I needed DBUnit to be case sensitive about the database table names. In my DBUnit xml file("mydbunitdata.xml") the table names are all lowercase like they are in the database. However, if you don't tell DBUnit to use case sensitive table names it looks for uppercase table names which Postgres didn't like. Therefore I needed to set the case sensitive feature in DBUnit which is done on line 59.

Bazar

I think the difference is JPA. I was using hibernate in tandem with generic-hibernate-DAO. And also, I did not use EclipseLink. – Gesundheit

with dbunit 2.4.5 and up, there is specific support for postgres, and you can get these options simply by using: DatabaseConfig dbUnitConConfig = dbUnitCon.getConfig(); dbUnitConConfig.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new PostgresqlDataTypeFactory()); – Amalea

For me passing schema name in DatabaseConnection constructor solved the problem even though I was specifying it in database URL itself. – Ge

Answer 3

remove the first line in your dataset.xml file

<!DOCTYPE dataset>

and replace it with this

<?xml version='1.0' encoding='UTF-8'?>

otherwise DBUnit tries to load the db tables schema from a DTD file and if no DTD is provided it will never match on any tables. Using the plain xml header will cause DBUnit to skip this DTD - table check that is throwing the error you see.

You must also do what Grzegorz did and have hibernate do a create-drop for your tables by including

<property name="hibernate.hbm2ddl.auto" value="create-drop"/>

or

configuration.setProperty(Environment.HBM2DDL_AUTO, "create-drop");

Appetizer

THIS is what I'm talking about, this has helped me enormously, because I had the create-drop property set and was setting up everything correctly. In previous versions of DBUnit, when you used <!DOCTYPE dataset> it still worked. Since I upgraded my DBUnit (in fact, I moved from Unitils to latest version of spring-test-dbunit - I can recommend it) this didn't work anymore, so I was looking at my problem but couldn't find the cause. So I can confirm that above fix works in version 1.1.0 of spring-test-dbunit! – Avowal

Answer 4

Have you created the database tables in your database?

dbunit won't do it for you, because it doesn't know the intended database structure. However you can ask Hibernat to do it. Use the hbm2ddl flag, e.g.:

 <property name="hibernate.hbm2ddl.auto" value="create-drop"/>

I couldn't find the main documentation for the flag, but if you searched for it you would get a lot of results, incl. http://docs.jboss.org/ejb3/docs/reference/build/reference/en/html/entityconfig.html

Burnett

Thanks @Grzegorz Oledzki for the quick reply. I created the tables in the database. I am using liquibase for db changelog management. – Gesundheit

Thanks @Grzegorz Oledzki: You were right that dbunit doesnot create the tables for me in my databse. Though that was not the problem, the problem was that I had forgotten to add my mapping files to the hibernate.cfg.xml file -- i sheepishly admit. However, I had asked hibernate to do it using this hibernate.hbm2ddl.auto=create-drop as a property in my properties file. I am not sure I should mark you answer as the correct one although it added to solving the problem. I really appreciate your help guys. Thanks a bunch -- its now working! – Gesundheit

Answer 5

You may be interested in this. I found this error also and by renaming my table in the hibernate.cfg.xml from 'message' to 'MESSAGE', It was able to find the schema.

Let me know if this works for you too.

Thyratron

Answer 6

I ran into the same error and had both my entity configured in hibernate.cfg.xml and I was using hbm2ddl to generate the tables, so I searched on and found that my entity mapping contained a field named "POSITION" which is a reserved term. When I changed it to "POS" the table was generated correctly.

You can check if your table is being generated by debugging the resultSet iteration in

org.dbunit.database.DatabaseDataSet.initialize().

Ancient

Answer 7

DbUnit classes take the default schema from the database, if the schema you want to use is not the default, you need to set the desired schema inside the .xml file and enable the recognition option of the other schemas, see the solution below. I created it.

Example.xml - Multiple Schemas

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
    <SCHEMA1.NAME_TABLE id="5" userName="1" />
    <SCHEMA2.NAME_TABLE id="2" roleName="1" />
<dataset>


<?xml version='1.0' encoding='UTF-8'?>
<dataset>
    <PEOPLE.USER id="5" userName="1" />
    <FUNCTIONS.ROLE id="2" roleName="1" />
<dataset>

TEST CLASS

    @Before
    public void initialize() throws Exception{
        //Insert data into database
        DatabaseOperation.CLEAN_INSERT.execute(getConnection(), getDataSet());
    }

    @After
    public void cleanup() throws Exception{
     //Clean up the database
      DatabaseOperation.DELETE_ALL.execute(getConnection(), getDataSet());
      System.out.println("DELETADO COM SUCESSO!");

    }

    private IDatabaseConnection getConnection() throws Exception{
            // Get the database connection
            Connection con = dataSource.getConnection();
            DatabaseMetaData  databaseMetaData = con.getMetaData();
            DatabaseConnection connection = new DatabaseConnection(con,databaseMetaData.getUserName().toUpperCase());
            //Allow multiple schemas to be used
            connection.getConfig().setFeature(DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES, true);
            return connection;    
    }

    private IDataSet getDataSet() throws Exception{
        //Get file to insert
        File file = new File("src/test/resources/Example.xml");
        return new FlatXmlDataSet(file);
}

Subscapular

Answer 8

For me this issue appeared when I renamed and removed few databases in schema and DBUnit anyways was trying to access it (??), but it was really absent and in datasets also.

for me helped this parameter:

@DataSet(..., useSequenceFiltering = false, ...)

or more generically:

@DBUnit(..., disableSequenceFiltering = true, ...)

From dbrider documentation:

• If true dbunit will look at constraints and dataset to try to determine the correct ordering for the SQL statements. (default true)

P.S.: probably, real issue is that table names were somehow cached, but I could not find where exactly.

Percolator