General
Using DbUnit
Errors
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.
[top] |
Building DbUnit detailed instruction.
[top] |
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:
[top] |
Look at Changes Report and at Issue Tracking.
[top] |
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")); // dependent tables database export: export table X and all tables that // have a PK which is a FK on X, in the right order for insertion String[] depTableNames = TablesDependencyHelper.getAllDependentTables( connection, "X" ); IDataSet depDataset = connection.createDataSet( depTableNames ); FlatXmlDataSet.write(depDataSet, new FileOutputStream("dependents.xml")); } }
[top] |
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")); } }
[top] |
[top] |
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.
DbUnit provides this functionality for MS SQL Server with the IdentityInsertOperation class.
[top] |
See FlatXmlDataSet documentation
[top] |
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.
[top] |
[top] |
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 |
[top] |
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.
[top] |
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.
Configure your DbUnit connection to use ForwardOnlyResultSetTable when exporting very large dataset. ForwardOnlyResultSetTable is a very efficient database table implememtation, 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());
Use the very efficient StreamingDataSet to load your XML dataset when working with forward only database operations like UPDATE, INSERT, REFRESH.
[top] |
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);
[top] |
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.
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.
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.
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.
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.
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.
[top] |
IDatabaseConnection conn = new DatabaseConnection(jdbcConn); ITableFilter filter = new DatabaseSequenceFilter(conn); IDataSet dataset = new FilteredDataSet(filter, conn.createDataSet()); FlatXmlDataSet.write(dataset, new File(fileName));
[top] |
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());
[top] |
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.
[top] |
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());
[top] |
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());
[top] |
This error occurs when no schema is specified and that DbUnit detect that it is getting columns information from multiple tables having the same name and located in different schemas.
You can solve this problem in three different ways:
[top] |
[top] |
By default, DbUnit only support standard JDBC data types. You will get this warning message if you are using vendor specific data types.
Read how to replace the default data type factory and how to disable this warning message.
[top] |
If you are using the Microsoft driver (i.e. com.microsoft.jdbc.sqlserver.SQLServerDriver), you'll need to use the SelectMethod=cursor parameter in the JDBC connection string (as outlined by this JDC thread). Your database Url would look something like the following:
jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=mydb;SelectMethod=cursor
[top] |
DbUnit uses JDBC 2.0 features (batch updates). By default, DB2 installs the JDBC 1.0 driver. You have to install the JDBC 2.0 driver in order for DbUnit to work or you will get an UnsatisfiedLinkError from the DB2 JDBC 1.0 driver.
The steps for installing the DB2 JDBC 2.0 driver are covered in the DB2 documentation.
[top] |