Using DbUnit with tables which do not have primary keys

Question

I'm attempting to set up my unit testing environment to use DbUnit.

I'm having a few problems as the tables which I am attempting to control do not have primary keys. I have been getting a org.dbunit.dataset.NoPrimaryKeyException.

I have followed the steps here http://dbunit.wikidot.com/noprimarykeytable but how do 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 do I rewrite connection.getConfig().setProperty("http://www.dbunit.org/properties/primaryKeyFilter", new MyPrimaryKeyFilter("A1")); in this instance?

Many thanks for any advice.

Icebound

Answer 1

You need to make sure that your MyPrimaryKeyFilter handles all the tables in your schema. In the example, there is only one table, so the simple filter class provided works fine. In your case, I would probably change that class to take a Map containing table -> pk column name mappings:

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

    }

and then set up the map with {NO_PK1 -> A1}, {NO_PK2 -> B1}, and {NO_PK3 -> C1} entries.

Caracal

Thanks slushi. That makes sense. What about if one of the tables has a key made up of more then one field? – Icebound

i think there you could change the Map to a Map<String, Set<String>> and do a contains check. – Caracal

So the return statement becomes return pseudoKey.get(tableName).contains(column.getColumnName()); ? – Icebound

i would think so, you may have to change it slightly to handle case insensitivity. I would run a test to see what case column.getColumnName() returns. Or you could uppercase everything in your Set's as well as the value from column.getColumnName(). – Caracal

Answer 2

I fall into same issue and found solution in 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's authors start from http://dbunit.wikidot.com/noprimarykeytable

This code shown different strategies for checking id:

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

Achaemenid