Archive

Posts Tagged ‘export’

Dbunit composite primary keys

April 20th, 2011

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<String, List<String>> tablePrimaryKeyMap = new HashMap<String, List<String>>();
   {
      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!

Share/Save/Bookmark

Java, testing , , , ,