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


Posted

in

,

by

Comments

7 responses to “Dbunit composite primary keys”

  1. Tim Avatar
    Tim

    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. gagi Avatar
    gagi

    @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 Avatar
    Anne

    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. gagi Avatar
    gagi

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

  5. John Mikich Avatar
    John Mikich

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

  6. patrice Avatar
    patrice

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

Leave a Reply

Your email address will not be published. Required fields are marked *