Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

DBUnit unable to import data for CollectionTable witout Primary Key

DZone's Guide to

DBUnit unable to import data for CollectionTable witout Primary Key

· Java Zone ·
Free Resource

The CMS developers love. Open Source, API-first and Enterprise-grade. Try BloomReach CMS for free.

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

BloomReach CMS: the API-first CMS of the future. Open-source & enterprise-grade. - As a Java developer, you will feel at home using Maven builds and your favorite IDE (e.g. Eclipse or IntelliJ) and continuous integration server (e.g. Jenkins). Manage your Java objects using Spring Framework, write your templates in JSP or Freemarker. Try for free.

Topics:

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}