Tuesday, 19 May 2009

DbUnit and nullable columns

We use DbUnit for our database integration tests and this works very well allowing us to populate the database with test data specific for the test at hand.

I have had a problem for the last few hours that has driven me to distraction. I was populating a table with a row and the tests using this data were all OK.

I then added an extra row that also wanted to populate an extra nullable column. The test failed because the test thought this column was null. I stopped the test in the debugger and yes the code was correct, the column was null. What could be wrong ?

My DbUnit XML looked like this:
<dataset>
<MY_TABLE
ID="1"
MANDATORY_COL="This column must have data"
/>

<MY_TABLE
ID="2"
MANDATORY_COL="This column must have data"
NULLABLE_COL="This column is nullable"
/>
</dataset>


The problem appears to be that DbUnit builds its list of columns from the first occurrance of each table. Any optional columns listed in subsequent entries are simply ignored.

There are two solutions. Either reverse the order of the two items in the XML file, or set the column to null in the rows where the nullable column is not required.

7 comments:

Anonymous said...

So this has kept me busy for the whole of last Friday and today until now....
Thanks for posting it.

I wasn't aware before of this issue, and consequently did not know how to search for it on the web :-)

hamster2k said...

Thanks for the post!

There is one problem: Suppose a table does not have a primary key, so that the only way to (not necessarily uniqely) identify one of its rows is to specify the value of all columns. DbUnit can handle this case, if you specify that all columns are part of the primary key (see http://dbunit.wikidot.com/noprimarykeytable - essentially, provide an IColumnFilter that returns true for all columns of the table). However, if you specify a column to be part of the primary key as described in the link above, then dbunit assumes that it is not nullable. If you do set one of the values to null, then you may get

java.lang.NullPointerException
at org.dbunit.dataset.datatype.IntegerDataType.setSqlValue(IntegerDataType.java:106)
at org.dbunit.operation.RefreshOperation$RowExistOperation.execute(RefreshOperation.java:348)
at org.dbunit.operation.RefreshOperation.execute(RefreshOperation.java:110)
at org.dbunit.AbstractDatabaseTester.executeOperation(AbstractDatabaseTester.java:190)
at org.dbunit.AbstractDatabaseTester.onSetup(AbstractDatabaseTester.java:103)
at org.dbunit.DatabaseTestCase.setUp(DatabaseTestCase.java:156)
at no.finntech.base.SQLUnitProcedureTest.setUp(SQLUnitProcedureTest.java:59)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at org.springframework.test.context.junit4.SpringMethodRoadie.runBefores(SpringMethodRoadie.java:273)
at org.springframework.test.context.junit4.SpringMethodRoadie$RunBeforesThenTestThenAfters.run(SpringMethodRoadie.java:332)
at org.springframework.test.context.junit4.SpringMethodRoadie.runWithRepetitions(SpringMethodRoadie.java:217)
at org.springframework.test.context.junit4.SpringMethodRoadie.runTest(SpringMethodRoadie.java:197)
at org.springframework.test.context.junit4.SpringMethodRoadie.run(SpringMethodRoadie.java:143)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.invokeTestMethod(SpringJUnit4ClassRunner.java:142)
at org.junit.internal.runners.JUnit4ClassRunner.runMethods(JUnit4ClassRunner.java:51)
at org.junit.internal.runners.JUnit4ClassRunner$1.run(JUnit4ClassRunner.java:44)
at org.junit.internal.runners.ClassRoadie.runUnprotected(ClassRoadie.java:27)
at org.junit.internal.runners.ClassRoadie.runProtected(ClassRoadie.java:37)
at org.junit.internal.runners.JUnit4ClassRunner.run(JUnit4ClassRunner.java:42)
at com.intellij.rt.junit4.Junit4ClassSuite.run(Junit4ClassSuite.java:99)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:40)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90)

the only way I've found to solve this problem is to set up your test data in such a way that the columns you do list as part of the primary key are never null.

Bill Comer said...

Hamster,

I think it is a fair enough restriction to state that any column that is part of the primary key must NOT be null.

Anonymous said...

Thanks for your solution, just spent half of the day searching for a problem in my code ;)

Anonymous said...

DOH! (Homer Simpson grunt). Spent a half day on this, before Googling and ending up here. This behavior is so bizarre - didn't suspect an issue in DbUnit until having spent too many hours looking for faults in all the wrong places...
Thanks for the post!

Anonymous said...

Thanks for the post.
But how do you set a null value?
cause column="null" will cause the column attribute to be a String with the "null" value

Bill Comer said...

google is your friend. Not done it myself but look here - http://dbunit.sourceforge.net/components.html#FlatXmlDataSet