Overview

Dataset formats store field values as text — always for Flat XML, XML, CSV, JSON, and YAML; for Excel, except where an .xls cell is natively a number, date, or boolean. dbUnit turns each value into a typed column value with the column’s DataType: DataType.typeCast() parses a string using the SQL type dbUnit resolved for the column (from database metadata, a DTD, or an explicit column declaration), and passes a value that is already the right type straight through. Because the conversion is driven by the column’s type rather than the file format, the rules on this page apply the same way whichever format the value came from.

A handful of those rules are triggered by an extended syntax: when a string value starts with [, the binary and date/time types treat the bracketed prefix as an instruction rather than literal content (DataType.isExtendedSyntax() — value starts with [). This is how [TEXT|BASE64|FILE|URL] loads binary data and how [now…​] produces a relative date. String columns never do this — a leading [ is stored verbatim.

The ReplacementDataSet decorator is a separate mechanism: it swaps placeholder objects and substrings before typeCast() sees them, and is the basis for the widely used [NULL] convention below.

Null

Each format has its own way to say "this cell is null":

Each format page states its own convention in its own words; the table above is a cross-reference, not a replacement.

ReplacementDataSet dataSet = new ReplacementDataSet(new FlatXmlDataSet(...));
dataSet.addReplacementObject("[NULL]", null);

Empty string vs. missing

A missing value is null (previous section). An explicitly empty string — COL="" in Flat XML — is different: by default dbUnit rejects it on INSERT/UPDATE rather than treating it as null or as an empty string.

table.column=TEST_TABLE.COL0 value is empty but must contain a value (to disable this feature check, set DatabaseConfig.FEATURE_ALLOW_EMPTY_FIELDS to true)

The check (AbstractBatchOperation) fires only for a genuinely empty string (""), not for null or a missing attribute. Set the FEATURE_ALLOW_EMPTY_FIELDS feature to true to let empty strings through to the database:

config.setFeature(DatabaseConfig.FEATURE_ALLOW_EMPTY_FIELDS, true);

Binary, BLOB, and CLOB

dbUnit datasets are text, so binary columns (BINARY, VARBINARY, LONGVARBINARY, BLOB) use an extended syntax to say how the text should become bytes:

... COL0="[TEXT|BASE64|FILE|URL <optional argument>] optional text"

The command word is matched case-insensitively. Everything after the closing ] is the payload.

<!DOCTYPE dataset SYSTEM "my-dataset.dtd">
<dataset>
    <TABLE_WITHBLOB COL0="[TEXT]This is my text, saved in UTF-8 (default) encoding.  Java: bon café!"
                    COL1="row 0 col 1"
                    COL2="row 0 col 2"/>
</dataset>

<!DOCTYPE dataset SYSTEM "my-dataset.dtd">
<dataset>
    <TABLE_WITHBLOB COL0="[TEXT ISO-8859-1]This is my text, saved in ISO-8859-1 encoding.  Java: bon café!"
                    COL1="row 0 col 1"
                    COL2="row 0 col 2"/>
</dataset>

<!DOCTYPE dataset SYSTEM "my-dataset.dtd">
<dataset>
    <TABLE_WITHBLOB COL0="[BASE64]VGhpcyBpcyBteSB0ZXh0Lg=="
                    COL1="row 0 col 1"
                    COL2="row 0 col 2"/>
</dataset>

<!DOCTYPE dataset SYSTEM "my-dataset.dtd">
<dataset>
    <TABLE_WITHBLOB COL0="[FILE]/path/to file to insert contents"
                    COL1="row 0 col 1"
                    COL2="row 0 col 2"/>
</dataset>

<!DOCTYPE dataset SYSTEM "my-dataset.dtd">
<dataset>
    <TABLE_WITHBLOB COL0="[URL]http://url%20here"
                    COL1="row 0 col 1"
                    COL2="row 0 col 2"/>
</dataset>

Dates and times

... field_name="[now{diff...}{time}]"

UUIDs in binary columns

A BINARY/VARBINARY/LONGVARBINARY column (mapped by UuidAwareBytesDataType) recognizes a UUID literal, so UUIDs stored as raw bytes need not be Base64- or hex-encoded by hand. Write the field as uuid’xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' (hex, big-endian); it is stored as the 16-byte encoding. The literal is matched anywhere in the value, so anything around it is discarded — keep the field to just the literal.

<company id="uuid'791ae85a-d8d0-11e2-8c43-50e549c9b654'" name="ACME"/>

This applies to BINARY-family columns only. A BLOB column (BlobDataType) does not decode the literal — it would store the 42 characters of uuid'…​' as text bytes.

Booleans and numbers from strings

Boolean columns (BOOLEAN, BIT): true and false are recognized case-insensitively. Any other value is parsed as an integer, then 0 is false and any non-zero is true. A value that is neither (yes, y, …​) is a TypeCastException.

Integer columns (TINYINT, SMALLINT, INTEGER): also accept true / false (case-insensitive) as 1 / 0, otherwise parse the value as a number.

Decimal columns (NUMERIC, DECIMAL): a Boolean value becomes 1 or 0, but the strings "true" / "false" are not special here — they are a TypeCastException. Everything else goes through new BigDecimal(value).

In CSV, a numeric field with a trailing space before the comma is read as a string, not a number.

Token and placeholder replacement

dbUnit has no built-in templating in field values beyond what the sections above describe. Arbitrary substitution is done by the ReplacementDataSet / ReplacementTable decorators, which replace whole placeholder objects and substrings that you register:

ReplacementDataSet dataSet = new ReplacementDataSet(new FlatXmlDataSet(...));
dataSet.addReplacementObject("[NULL]", null);
dataSet.addReplacementSubstring("$USER", "scott");

Injecting the current system time is a worked example — register a java.util.Date object under a placeholder and it is substituted before comparison or insert. Data File Loader implementations wrap every dataset they load in a ReplacementDataSet automatically.

Literal leading bracket

A value that starts with [ is only special for binary and date/time columns:

There is no escape character for a leading [ on a binary or temporal column. If a date/time or binary column must hold a value that would collide with the extended syntax, substitute it in with a ReplacementDataSet instead of writing it in the file.

Column sensing (Flat XML)

Flat XML deduces a table’s columns from its first row. A later row with an attribute the first row lacked produces:

Extra columns on line x. Those columns will be ignored. Please add the extra
columns to line 1, or use a DTD to make sure the value of those columns are populated.

Three ways to fix it:

• Declare every column on the first row (use [NULL] or omit-for-null as needed).
• Reference a DTD — dbUnit takes the table’s columns from it.
• Enable column sensing, which buffers the whole document and adds columns as they first appear:

FlatXmlDataSetBuilder builder = new FlatXmlDataSetBuilder();
builder.setColumnSensing(true);
IDataSet dataSet = builder.build(new File("dataset.xml"));

YAML and JSON do not have this problem — they take a table’s columns from the union of all its rows. CSV declares columns in a header row, so it is unaffected too.