Dbunit composite primary keys

For some task I was doing I had to make a snapshot of mysql database having only jdbc connection available. I wasn’t able to use mysqldump (remote access) or other backup tools (I wanted to do this as part of my java program – programmatically).

So, I came across http://dbunit.sourceforge.net/ which allowed me to backup my database to xml file.

The routine is rather simple (please note the escape parameter setting which is required if (like in my case) author of legacy code you have to use put ‘table’ or some other odd name as a column name 🙂 :

public void saveDb(OutputStream os) 
   throws DatabaseUnitException, SQLException, IOException {

   IDatabaseConnection connection = new DatabaseConnection(getConnection());
   DatabaseConfig config = connection.getConfig();
   config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, 
      new MySqlDataTypeFactory());
   config.setProperty(DatabaseConfig.PROPERTY_METADATA_HANDLER, 
      new MySqlMetadataHandler());
   config.setProperty(DatabaseConfig.PROPERTY_ESCAPE_PATTERN, "`?`");

   IDataSet fullDataSet = connection.createDataSet();
   DefaultTableFilter tableFilter = new DefaultTableFilter();
   tableFilter.excludeTable("ignoredtable*");

   IDataSet filteredDataSet = new FilteredDataSet(tableFilter, fullDataSet);
   FlatXmlDataSet.write(filteredDataSet, os);

}

Then, when I wanted to restore it with:

public void restoreDb() throws Exception {

   IDatabaseConnection connection = new DatabaseConnection(db.getConnection());
   DatabaseConfig config = connection.getConfig();
   config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, 
      new MySqlDataTypeFactory());
   config.setProperty(DatabaseConfig.PROPERTY_METADATA_HANDLER, 
      new MySqlMetadataHandler());
   config.setProperty(DatabaseConfig.PROPERTY_ESCAPE_PATTERN, "`?`");

   IDataSet dataSet = new FlatXmlDataSetBuilder().build(new File(fileName));

   DatabaseOperation.DELETE_ALL.execute(connection, dataSet);
   DatabaseOperation.REFRESH.execute(connection, dataSet);
}

This method wont’t work if you have a table without primary key or oddly defined composite keys. It will throw: org.dbunit.dataset.NoPrimaryKeyException

The solution is to add primary key filter configuration option like this:

config.setProperty(DatabaseConfig.PROPERTY_PRIMARY_KEY_FILTER, new IColumnFilter() {

 Map&lt;string, list&lt;string=""&gt;&gt; tablePrimaryKeyMap = new HashMap&lt;string, list&lt;string=""&gt;&gt;();
   {
      tablePrimaryKeyMap.put("table_with_composite1",
         Arrays.asList(new String[] { "composite_part1", "composite_part2" }));
      tablePrimaryKeyMap.put("table_with_composite2",
       Arrays.asList(new String[] { "other_part1", "other_part2", "other_part3" }));
      // ...
   }

   @Override
   public boolean accept(String tableName, Column column) {
      if (tablePrimaryKeyMap.containsKey(tableName)) {
         return tablePrimaryKeyMap.get(tableName).contains(column.getColumnName());
      } else {
         // you can add default handling if for example all
         // other table ids are named as 'id'
         return column.getColumnName().equalsIgnoreCase("id");
      }
   }
});

And that’s it. Yee!

Posted

April 20, 2011

Java, testing

gagi

Tags:

database, dbunit, export, Java, jdbc

Comments

7 responses to “Dbunit composite primary keys”

Tim

May 25, 2011

Hi,

This is a very clear explanation, thanks a lot for that! As I’m having the exact same issue, this article should have done the trick for me. Sadly it doesn’t. I noticed that the accept() method is never triggered. Do you have any idea what might cause this? I’m extending the DBUseCase from DBUnit and I use your adapted code right before super.setUp();. Any help is appreciated!

Thanks,
Tim

Reply
gagi

May 26, 2011

@Tim
The primary key filter described above should be set before calling build method but I guess that you have already done it like that.
I cannot think of any other thing now unless you give me more details about your problem (e.g. code sample).
Maybe posting a stackoverflow.com question containing your code will get you to the solution faster than this. If you do that, please put a link to the question here so I could take a look into it as well and possibly earn some reputation on SO.

Good luck!

Reply

Anne

August 8, 2011

I had the same problem as Tim. I fixed it by overriding the setUpDatabaseConfig as follows:

protected void setUpDatabaseConfig(DatabaseConfig config) {
  config.setProperty(DatabaseConfig.PROPERTY_PRIMARY_KEY_FILTER, 
    new IColumnFilter() {
	        	 
    Map tablePrimaryKeyMap = new HashMap<String, List>();
    {
      tablePrimaryKeyMap.put("VOLATILITY_SURFACE_POINT",
      Arrays.asList(new String[] { "id_1", "id_2", "id_3", "id_4"}));

    }
	        	 
    @Override 
    public boolean accept(String tableName, Column column) {
      if (tablePrimaryKeyMap.containsKey(tableName)) {
        return tablePrimaryKeyMap
          .get(tableName).contains(column.getColumnName());
      } else {
        return column.getColumnName().equalsIgnoreCase("id");
      }
    }

  });
}

This will ensure that accept() is called before build()

Anne

August 8, 2011

@Anne
Source:
http://www.dbunit.org/howto.html

Reply
gagi

August 8, 2011

@Anne
Thanks!
(fyi, I’ve edited your code so it could appear better on this template)

Reply
John Mikich

January 13, 2012

This is problematic, since you have to define the key of every single table in the accept method.

Reply
patrice

March 2, 2012

To solve the problematic, I added this change on DBUnitCode to use db primary keys first then the filter if not found :

File DatabaseTableMetaData.java, method getPrimaryKeys() :

lastKeyFilter = primaryKeysFilter;
// first use defined primary key then use filter if no primary key found
String[] pkNames = getPrimaryKeyNames();
_primaryKeys = Columns.getColumns(pkNames, getColumns());
if (_primaryKeys.length == 0) {
if (primaryKeysFilter != null) {
_primaryKeys = Columns.getColumns(getTableName(), getColumns(),
primaryKeysFilter);
}
}

Reply

Dbunit composite primary keys

Comments

7 responses to “Dbunit composite primary keys”

Leave a Reply Cancel reply