Home > Java, testing > Dbunit composite primary keys

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 , , , ,

  1. Tim
    May 25th, 2011 at 14:45 | #1

    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

  2. May 26th, 2011 at 01:07 | #2

    @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!

  3. Anne
    August 8th, 2011 at 20:33 | #3

    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()

  4. Anne
    August 8th, 2011 at 20:34 | #4
  5. August 8th, 2011 at 20:48 | #5

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

  6. John Mikich
    January 13th, 2012 at 15:01 | #6

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

  7. patrice
    March 2nd, 2012 at 19:05 | #7

    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);
    }
    }

  1. No trackbacks yet.