Using DbUnit with tables that do not have primary keys

Question

I am trying to set up a unit testing environment to use DbUnit.

I have a few problems, since the tables I'm trying to control do not have primary keys. I get org.dbunit.dataset.NoPrimaryKeyException .

I followed the following steps http://dbunit.wikidot.com/noprimarykeytable , but how can I use:

 connection.getConfig().setProperty("http://www.dbunit.org/properties/primaryKeyFilter", new MyPrimaryKeyFilter("A1")); 

for each of my tables?

For example, I have the following database:

 CREATE TABLE `NO_PK1` ( `A1` int(11) NOT NULL, `A2` varchar(50) default NULL ); <?xml version="1.0" encoding="UTF-8"?> <dataset> <NO_PK1 A1="1" A2="Test1" /> <NO_PK1 A1="2" A2="Test2" /> <NO_PK1 A1="3" /> </dataset> CREATE TABLE `NO_PK2` ( `B1` int(11) NOT NULL, `B2` varchar(50) default NULL ); <?xml version="1.0" encoding="UTF-8"?> <dataset> <NO_PK2 B1="1" B2="Test1" /> <NO_PK2 B1="2" B2="Test2" /> <NO_PK2 B1="3" /> </dataset> CREATE TABLE `NO_PK3` ( `C1` int(11) NOT NULL, `C2` varchar(50) default NULL ); <?xml version="1.0" encoding="UTF-8"?> <dataset> <NO_PK3 C1="1" C2="Test1" /> <NO_PK3 C1="2" C2="Test2" /> <NO_PK3 C1="3" /> </dataset> 

How can I rewrite connection.getConfig().setProperty("http://www.dbunit.org/properties/primaryKeyFilter", new MyPrimaryKeyFilter("A1")); in this case?

Thanks so much for any advice.

Answer 1

You need to make sure your MyPrimaryKeyFilter processes all the tables in your schema. In this example, there is only one table, so a simple filter class works fine. In your case, I would probably change this class to display a map containing tables -> pk, column names:

 class MyPrimaryKeyFilter implements IColumnFilter { private Map<String, String> pseudoKey = null; MyPrimaryKeyFilter(Map<String, String> pseudoKey) { this.pseudoKey = pseudoKey; } public boolean accept(String tableName, Column column) { return column.getColumnName().equalsIgnoreCase(pseudoKey.get(tableName)); } } 

then configure the map using the entries {NO_PK1 → A1}, {NO_PK2 → B1} and {NO_PK3 → C1}.

Answer 2

I hit the same problem and found a solution on these blogs:

• http://www.expertaya.com/2011/04/20/dbunit-composite-primary-keys/
• http://blog.eflow.org/archives/65
• http://www.baselogic.com/blog/development/test-driven-development/dbunit-unable-import-data-collectiontable-witout-primary-key/

All blog authors start at http://dbunit.wikidot.com/noprimarykeytable

This code shows various id verification strategies:

 public static IDatabaseConnection getConnection(DataSource ds) throws Exception { Connection con = ds.getConnection(); final DatabaseMetaData dbMetaData = con.getMetaData(); DatabaseConnection dbUnitCon = new DatabaseConnection(con, dbMetaData.getUserName().toUpperCase()); DatabaseConfig dbUnitConfig = dbUnitCon.getConfig(); dbUnitConfig.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new Oracle10DataTypeFactory()); dbUnitConfig.setProperty(DatabaseConfig.FEATURE_SKIP_ORACLE_RECYCLEBIN_TABLES, Boolean.TRUE); dbUnitConfig.setProperty(DatabaseConfig.PROPERTY_PRIMARY_KEY_FILTER, new IColumnFilter() { Map<String, List<String>> tablePrimaryKeyMap = new HashMap<>(); { tablePrimaryKeyMap.put("CLIENT", Arrays.asList(new String[]{"FIRST_NAME", "MIDDLE_NAME", "LAST_NAME"})); // ... } @Override public boolean accept(String tableName, Column column) { if ((tableName.startsWith("DATA_") || tableName.startsWith("PAYMENT_")) && ("COMPANY".equalsIgnoreCase(tableName) || "FILIAL".equalsIgnoreCase(tableName) || "BRANCH".equalsIgnoreCase(tableName) || "CASTOMER".equalsIgnoreCase(tableName) || "XDATE".equalsIgnoreCase(tableName))) return true; if (tablePrimaryKeyMap.containsKey(tableName)) return tablePrimaryKeyMap.get(tableName).contains(column.getColumnName()); else if ("id".equalsIgnoreCase(column.getColumnName())) { return true; } try { ResultSet rs = dbMetaData.getPrimaryKeys(null, null, tableName); while (rs.next()) { rs.getString("COLUMN_NAME"); if (rs.getString("COLUMN_NAME").equalsIgnoreCase(column.getColumnName())) { return true; } } } catch (SQLException ex) { Logger.getLogger(DistributionControllerDbTest.class.getName()).log(Level.SEVERE, null, ex); } return false; } }); return dbUnitCon; }