DBUnit unable to import data for CollectionTable witout Primary Key

I ran into an issue running some DBUnit tests trying to seed a database where tables do not have primary keys such as CollectionTables in JPA.

This blog illustrates how to the http://www.dbunit.org/properties/primaryKeyFilter property to refresh a table that do not have primary keys. You can basically choose the columns to pseudo ignore during processing.

I have the following JPA Entity:

@Entity
@Table
@NamedQuery(name = Constants.FINDALLFINDERNAME,
query = Constants.FINDALLQUERY)
public class Customer extends AuditableEntity {
 
...
 
@ElementCollection(fetch = FetchType.EAGER)
@CollectionTable(name = Constants.PHONES, joinColumns = @JoinColumn(name = Constants.CUSTOMER_ID))
@Column(name = Constants.CUSTOMER_PHONES, nullable = true)
private List<Phone> phones;

I want to import the following data into my schema during a test:

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
<CUSTOMER id='101' USERNAME="user1" FIRSTNAME="Mick" LASTNAME="Knutson"/>
 
<PHONES AREACODE="415" P_NUMBER="5551212" TYPE="WORK" CUST_ID="101" />
</dataset>

But I get this error:

[EL Info]: 2011-01-31 10:57:56.945--ClientSession(30624226)--Communication failure detected when attempting to create transaction on database.  Attempting to retry begin transaction. Error was: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.0.v20110129-r8902): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.h2.jdbc.JdbcSQLException: The object is already closed [90007-148]
Error Code: 90007.
Dataset written
 
org.dbunit.dataset.NoPrimaryKeyException: PHONES

Here is my unit test:

public class CustomerTest {
 
//-----------------------------------------------------------------------//
// Attributes
//-----------------------------------------------------------------------//
private static EntityManagerFactory emf;
private static EntityManager em;
private static EntityTransaction tx;
 
//-----------------------------------------------------------------------//
// Lifecycle Methods
//-----------------------------------------------------------------------//
@BeforeClass
public static void initEntityManager() throws Exception {
emf = Persistence.createEntityManagerFactory(Constants.PERSISTENCEUNIT);
em = emf.createEntityManager();
}
 
@AfterClass
public static void closeEntityManager() throws SQLException {
if (em != null) em.close();
if (emf != null) emf.close();
}
 
@Before
public void initTransaction() throws Exception {
tx = em.getTransaction();
seedData();
}
 
@After
public void afterTests() throws Exception {
dumpData();
}
 
//-----------------------------------------------------------------------//
// Unit Tests
//-----------------------------------------------------------------------//
 
@Test
public void test__Create__and__Read_SingleCustomer() throws Exception {
// Creates an instance of Customer
Customer customer = CustomerFixture.createSingleCustomer();
 
// Persists the Customer to the database
tx.begin();
em.persist(customer);
tx.commit();
 
tx.begin();
assertNotNull("ID should not be null", customer.getId());
// Retrieves a single Customer from the database
TypedQuery<Customer> q = em.createNamedQuery(
Constants.FINDALLFINDERNAME, Customer.class);
List<Customer> customers = q.getResultList();
assertThat(customers.size(), is(4));
tx.commit();
}
 
@Test
public void test__DeleteCustomer() throws Exception {
tx.begin();
 
// Uses Sting Based Criteria
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Customer> c = cb.createQuery(Customer.class);
Root<Customer> cust = c.from(Customer.class);
c.select(cust)
.where(cb.equal(cust.get("username"), "user1"));
Customer result = em.createQuery(c).getSingleResult();
 
em.remove(result);
 
// Retrieves all the Customers from the database
TypedQuery<Customer> q = em.createNamedQuery(
Constants.FINDALLFINDERNAME, Customer.class);
List<Customer> customers = q.getResultList();
 
tx.commit();
 
assertThat(customers.size(), is(3));
}
 
//-----------------------------------------------------------------------//
// DBUnit Helper Methods
//-----------------------------------------------------------------------//
 
protected void seedData() throws Exception {
tx.begin();
Connection connection = em.unwrap(java.sql.Connection.class);
 
try {
IDatabaseConnection dbUnitCon = new DatabaseConnection(connection);
 
dbUnitCon.getConfig().setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY,
new H2DataTypeFactory());
 
IDataSet dataSet = getDataSet();
 
DatabaseOperation.REFRESH.execute(dbUnitCon, dataSet);
} finally {
tx.commit();
connection.close();
}
}
 
protected IDataSet getDataSet() throws Exception {
return new FlatXmlDataSetBuilder().build(new FileInputStream("./src/test/resources/dataset.xml"));
}
 
protected void dumpData() throws Exception {
tx.begin();
Connection connection = em.unwrap(java.sql.Connection.class);
 
try {
IDatabaseConnection dbUnitCon = new DatabaseConnection(connection);
dbUnitCon.getConfig().setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY,
new H2DataTypeFactory());
 
IDataSet dataSet = dbUnitCon.createDataSet();
 
FlatXmlDataSet.write(dataSet, new FileOutputStream("./target/test-dataset_dump.xml"));
System.out.println("Dataset written");
} finally {
tx.commit();
connection.close();
}
}
}

I can across http://dbunit.wikidot.com/noprimarykeytable and tried the solution, but modified it a bit:

The property:

// Set the property by passing the new IColumnFilter
dbUnitCon.getConfig().setProperty(
DatabaseConfig.PROPERTY_PRIMARY_KEY_FILTER,
new NullPrimaryKeyFilter("ID", "ADDRESS_KEY", "P_NUMBER", "HOBBY_NAME"));

then the Filter Class:

class NullPrimaryKeyFilter implements IColumnFilter {
private String[] keys = null;
 
NullPrimaryKeyFilter(String... keys) {
this.keys = keys;
}
 
public boolean accept(String tableName, Column column) {
for(String key: keys){
if(column.getColumnName().equalsIgnoreCase(key)){
return true;
}
}
return false;
}
}

Now I can use the filter for all my TABLES in my test.

[1]: http://dbunit.wikidot.com/noprimarykeytable

From http://www.baselogic.com/blog/development/test-driven-development/dbunit-unable-import-data-collectiontable-witout-primary-key