Sunday, March 29, 2015

AmbiguousTableNameException and NoPrimaryKeyException from DBUnit

Recently I wrote some JUnit tests with DBUnit. The first problem encountered was the exception:

org.dbunit.database.AmbiguousTableNameException: INFOCODE.

 Looking into the DBUnit source code, I figured out that actually INFOCODE is a table name in my database and DBUnit thought that there were more than one tables with that same name. (The database is large and I am not familiar with all tables. So my first though was that INFOCODE is a code used by DBUnit to tell something. It is worth of pointing out that my unit tests and the code to be tested never access the INFOCODE table.) If the exception were named DuplicatedTableNameException, it would help more to figure out the problem sooner. Then it came up to me that my database has multiple schemata and some of them have a table with identical name. Searching DBUnit documentation yielded a solution: passing the desired schema name as an argument to the DatabaseConnection constructor.

 IDatabaseConnection connection = new DatabaseConnection(sqlConnection,  schemaName); 

 where sqlConnection is a javas.sql.Connection, and schemaName is the name of the desired schema. (IDatabaseConnection is DBUnit's database connection interface)

The second problem was the exception:

org.dbunit.dataset.NoPrimaryKeyException: ACCOUNT

ACCOUNT is the name of a table that my code accesses. My database is an old one and no primary key is defined for that table. The DBUnit document does address this problem. The solution, however, is partly incorrect and lacks necessary details. Looking into the DBUnit source code helped to find the right solution:

DefaultColumnFilter columnFilter = new DefaultColumnFilter();
columnFilter.includeColumn("accountId"); // telling DBUnit to treat the named column as primary key
connection.getConfig().setProperty(DatabaseConfig.PROPERTY_PRIMARY_KEY_FILTER,  columnFilter);
where connection is a DBUnit IDatabaseConnection.

By the way, the JUnit tests do not have to extend any DBUnit class or to implement any DBUnit interface. Data can be inserted into, deleted from, and read from database using statements like:
DatabaseOperation.INSERT.execute(connection, dataSet); 
DatabaseOperation.DELETE.execute(connection, dataSet);
ITable accountTable = connection.createQueryTable("account table", "SELECT * FROM account WHERE accountId = 'myAccountId'");
where dataSet is DBUnit IDataSet.

The appendix below gives sample code to construct IDataSet from data in xml files.

Appendix – Load Data from xml Files on Classpath
public static IDataSet importData(String path) {
    InputStream iStream = instance.getClass().getClassLoader().getResourceAsStream(path);
    if (iStream == null) {
        throw new RuntimeException("In valid file path: " + path + ". The path must be on classpath.");
    InputSource inputSource = new InputSource(iStream);
    FlatXmlProducer flatXmlProducer = new FlatXmlProducer(inputSource);
    IDataSet dataSet = null;
    try {
        dataSet = new FlatXmlDataSet(flatXmlProducer);
    } catch(DataSetException e) {
        throw new RuntimeException("In valid data in file: " + path + ".");
 return dataSet;

No comments: