|
|
-
Which relational database systems are supported?
-
See the publicly maintained
list of compatible RDBMS
at the
DbUnit Wiki.
Don't hesitate to contribute to this list, particularly for database not listed yet.
-
How to build DbUnit?
-
BuildingDbUnit
detailed instruction.
-
How to see SQL statements issued by DbUnit using P6Spy?
-
P6Spy
is a transparent JDBC proxy-driver
specialized to log the statements performed against the actual driver you normally would use.
Using p6spy would allow to log the SQL statements issued by DbUnit.
To install P6Spy, complete the following steps:
-
Put the p6spy.jar file in your classpath.
-
Move spy.properties into a directory listed in your classpath. Unlike
JAR files, you do not directly reference your property file in the
classpath (e.g.: if you have the file c:\r\proj\confspy.properties make sure you
put c:\r\proj\conf in the classpath).
-
Modify your application to use the P6Spy database driver i.e.
com.p6spy.engine.spy.P6SpyDriver.
-
Modify the realdriver line in the spy.properties file to reflect the
wrapped database driver. An example of a modified realdriver line follows:
realdriver = oracle.jdbc.driver.OracleDriver
Installation is complete. When you run your application, a spy.log file is
generated in the same directory from where you run the application.
The log file contains a list of all of the database statements
executed. You can change both the destination of spy.log and what it logs by
editing the spy.properties file.
-
How to learn more about known and fixed issues?
-
Look at
ChangesSinceLastRelease
and at
Issue Tracking
.
-
How to extract a flat XML dataset from my database?
-
The following sample demonstrates how you can export one or many tables
from a database to an flat XML dataset file.
public class DatabaseExportSample
{
public static void main(String[] args) throws Exception
{
// database connection
Class driverClass = Class.forName("org.hsqldb.jdbcDriver");
Connection jdbcConnection = DriverManager.getConnection(
"jdbc:hsqldb:sample", "sa", "");
IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);
// partial database export
QueryDataSet partialDataSet = new QueryDataSet(connection);
partialDataSet.addTable("FOO", "SELECT * FROM TABLE WHERE COL='VALUE'");
partialDataSet.addTable("BAR");
FlatXmlDataSet.write(partialDataSet, new FileOutputStream("partial.xml"));
// full database export
IDataSet fullDataSet = connection.createDataSet();
FlatXmlDataSet.write(fullDataSet, new FileOutputStream("full.xml"));
}
}
-
How to generate a DTD representing my database schema?
-
The following sample demonstrates how you can generate a flat xml dataset
DTD from a database.
public class DatabaseExportSample
{
public static void main(String[] args) throws Exception
{
// database connection
Class driverClass = Class.forName("org.hsqldb.jdbcDriver");
Connection jdbcConnection = DriverManager.getConnection(
"jdbc:hsqldb:sample", "sa", "");
IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);
// write DTD file
FlatDtdDataSet.write(connection.createDataSet(),
new FileOutputStream("test.dtd"));
}
}
-
Can I work with multiple database schemas having tables with identical name?
-
Yes, see
Why I get a "AmbiguousTableNameException"?
.
-
Can I use DbUnit with IDENTITY or auto-increment
columns?
-
Many RDBMS allow IDENTITY and auto-increment columns to be overwritten with client values implicitly. DbUnit can be used with these RDBMS natively.
Some databases, like MS SQL Server and Sybase, need to explicitly activate client values writing. The way to activate this feature is vendor specific.
DbUni
t provides this functionality for MS SQL Server with the
IdentityInsertOperation
class.
-
How to specify NULL values with flat XML dataset?
-
See
FlatXmlDataSet documentation
-
Can I use DbUnit with database views?
-
Yes. By default DbUnit is configured to only recognize normal tables. Modify the
table type property
to work with other table types.
For example, use
{"TABLE", "VIEW"}
for views.
Be aware that REFRESH, DELETE and UPDATE operations are not compatible with tables without
primary keys. They are not usable with views without
overriding primary keys detection
. CLEAN_INSERT, INSERT and DELETE_ALL
operations are compatible with views.
-
How to enable batched statement?
-
See
batched statement
feature.
-
What are the dates formats supported by DbUnit?
-
DbUnit use the JDBC escape formats for string representation.
|
Type
|
Format
|
|
DATE
|
yyyy-mm-dd
|
|
TIME
|
hh:mm:ss
|
|
TIMESTAMP
|
yyyy-mm-dd hh:mm:ss.fffffffff
|
-
How to replace the default data type factory?
-
You can replace the default DbUnit
data type factory
to get support for custom data type.
DbUnit provides extended factories for some vendors, which are located in
org.dbunit.ext
subpackages.
Here is how to setup the Oracle factory:
IDatabaseConnection connection = new DatabaseConnection(
jdbcConnection, schema);
DatabaseConfig config = connection.getConfig();
config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY,
new OracleDataTypeFactory());
Don't hesitate to submit your own implementation if you encounter types not currently supported by DbUnit.
-
How to perform streamed import and export?
-
Historically, DbUnit has memory consumption issues when working with very large dataset files. DbUnit 2.0 includes many improvements, like using SAX2 instead of the Electric XML parser and and streamed XML dataset writing, to overcome the memory consumption problems.
For compatibility reason, streamed export and import are not enabled by default. When working with large dataset, using this feature can make a huge difference.
Database Export:
Configure your DbUnit connection to use ForwardOnlyResultSetTable when exporting very
large dataset. ForwardOnlyResultSetTable is a very efficient database table implemtation useful
when random data access is not required. By default, DbUnit uses CachedResultSetTable which consume
more memory but provides random data access.
Following sample shows how to configure your DbUnit connection to use
ForwardOnlyResultSetTable:
IDatabaseConnection connection = new DatabaseConnection(
jdbcConnection, schema);
DatabaseConfig config = connection.getConfig();
config.setProperty(DatabaseConfig.PROPERTY_RESULTSET_TABLE_FACTORY,
new ForwardOnlyResultSetTableFactory());
Database Import:
Use the very efficient
StreamingDataSet
to load your XML dataset when
working with forward only database operations like UPDATE, INSERT, REFRESH.
-
How to enable flat XML dataset validation?
-
Flat XML validation is disabled by default even if you are using a DTD. Following sample demonstrate how to load a flat XML dataset with DTD validation enabled:
FlatXmlProducer producer = new FlatXmlProducer(
new InputSource("dataset.xml"));
producer.setValidating(true);
IDataSet dataSet = new CachedDataSet(producer);
-
How to improve the performance of my DbUnit tests?
-
It is normal that testing with a real database is slower than testing with
MockObjects
. Here are few tricks that will help to speedup your DbUnit tests.
1. Reuse the same connection thorough your test suite
Creating a new DbUnit connection every time has a cost. The overhead is much more than just creating a new JDBC connection. DbUnit need to fetches tables' metadata to determine columns data types. This information is cached in the DbUnit connection. So this is highly recommended to reuse the same DbUnit connection thorough your test suite; more you have tables greater are the benefits.
2. Specify the database schema name
If your database server supports multiple schemas, like Oracle, you should always specify the schema name you want to use when
creating the DbUnit connection. DbUnit can potentially fetch the metadata of all tables it have access to.
This include tables from other schemas if you are using a
god JDBC connection
. So in this situation,
specifying a schema name can dramatically improve DbUnit performance.
3. Test with little data
Unit testing require relatively little data. So try to
keep your setup datasets as small as possible
. There is no necessity to reset the entire database content at the beginning of every test. Try to use only the data you need for a particular test case.
4. Setup stale data once for the entire test suite
If most of your tests are using the same read-only data, you should consider initializing this data once for an entire test class or test suite.
5. Enable the batched statement feature
The
batched statements
feature is disabled by default because there are many JDBC drivers incompatible with it. This is recommended to enable this feature if your driver supports it. The performance gain may not be very significant when testing with
small datasets
.
-
How to automatically orders tables according their foreign keys?
-
DbUnit operations insert and update tables' rows in the same order they are found in your
dataset and delete are done in reverse order. You must order your tables and rows
appropriately in your datasets to prevent foreign keys constraint violation.
Since version 2.0, the
DatabaseSequenceFilter
can now be used to automatically determine the tables order using foreign/exported keys information.
The following sample demonstrate how to use this class to export a flat XML dataset:
IDatabaseConnection conn = new DatabaseConnection(jdbcConn);
ITableFilter filter = new DatabaseTableFilter(conn);
IDataSet dataset = new FilteredDataSet(filter,
conn.createDataSet());
FlatXmlDataSet.write(dataset, new File(fileName));
-
How to add the DOCTYPE declaration when writing a flat XML dataset?
-
Use the setDocType() method of the FlatXmlWriter class like this:
FlatXmlWriter datasetWriter = new FlatXmlWriter(
new FileOutputStream("dataset.xml"));
datasetWriter.setDocType("dataset.dtd");
datasetWriter.write(connection.createDataSet());
This can also be done with the
DbUnit Ant task
.
-
How to exclude some table columns at runtime?
-
The FilteredTableMetaData class, introduced in DbUnit 2.1, can be used in combination with the IColumnFilter interface to decide the inclusion or exclusion of table columns at runtime.
FilteredTableMetaData metaData = new FilteredTableMetaData(
originalTable.getTableMetaData(), new MyColumnFilter());
ITable filteredTable = new CompositeTable(metaData, originalTable);
You can use your own IColumnFilter implementation or use the DefaultColumnFilter class provided by DbUnit. DefaultColumnFilter supports wildcards. This class also offers some convenience methods, includedColumnsTable() and excludedColumnsTable(), to ease creation of column filtered table.
The following sample demonstrates the usage of DefaultColumnFilter to exclude all columns prefixed with "PK" or suffixed by "TIME".
DefaultColumnFilter columnFilter = new DefaultColumnFilter();
columnFilter.excludeColumn("PK*");
columnFilter.excludeColumn("*TIME");
FilteredTableMetaData metaData = new FilteredTableMetaData(
originalTable.getTableMetaData(), columnFilter);
Same than above but using the excludedColumnsTable() convenience method.
ITable filteredTable = DefaultColumnFilter.excludedColumnsTable(
originalTable, new String[]{"PK*", "*TIME"});
See also
Ignoring some columns in comparison
.
-
How to use InsertIdentityOperation with user defined types?
-
The IColumnFilter interface is now used by InsertIdentityOperation to detect identity columns. The default implementation assumes that type name of identity columns end with "identity". If you are using user defined types that does not follow this assumption you can now provide your own implementation via the
MS SQL identity column filter property
.
IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);
connection.getConfig().setProperty(
"http://www.dbunit.org/properties/mssql/identityColumnFilter",
new MyIndentityFilter());
-
How to customize primary keys detection?
-
The IColumnFilter interface can also be used to determine which columns are primary keys instead of using DatabaseMetaData.getPrimaryKeys(). This can be useful if your primary keys are not explicitly defined in your database model.
IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);
connection.getConfig().setProperty(
"http://www.dbunit.org/properties/primaryKeyFilter",
new MyPrimaryKeyFilter());
|
Wiki
Project Info
Project Reports