With nearly two decades of experience working in the IT industry in various roles as Enterprise technology consultant, Java Architect, project leader, Engineer, Designer and Developer, Mr. Knutson has gained a wide variety of experience in disciplines including JavaEE, Web Services, Mobile Computing and Enterprise Integration Solutions. Over the course of his career, Mr. Knutson has enjoyed long lasting partnerships with many of the most recognizable names in the Health Care, Financial, Banking, Insurance, Manufacturing, Telecommunications, Utilities, Product Distribution, Industrial and Electronics industries employing industry standard full software life cycle methodologies including the Rational Unified Process (RUP), Agile, SCRUM, and Extreme Programming (XP). Mr. Knutson has also undertaken speaking engagements, training seminars, white paper and book publishing engagements world-wide. As an active Blogger and tweeter, Mr. Knutson has also been inducted in the prestigious DZone.com Most Valuable Blogger (MVB) group and can be followed at http://www.dzone.com/page/mvbs and twitter at http://twitter.com/mickknutson. Able to quickly and effectively adapt to any phase of an existing project from Business Modeling to Production Support, Mr. Knutson’s wide array of technical expertise in OOA / OOD / OOP, Java, J2EE, Internet, Oracle, Enterprise Integration and Message Oriented Middleware allow him to seamlessly integrate into your Information Systems project at any phase and provide immediate productivity. Mr. Knutson is exceptional at team building and motivating either at a peer-to-peer level, or in a leadership role with excellent communications skills and the ability to adapt to all environments and cultures with ease. Mick is a DZone MVB and is not an employee of DZone and has posted 18 posts at DZone. You can read more from them at their website. View Full User Profile

DBUnit unable to import data for CollectionTable witout Primary Key

07.10.2011
| 5473 views |
  • submit to reddit

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

Published at DZone with permission of Mick Knutson, author and DZone MVB.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Andy Jefferson replied on Mon, 2011/07/11 - 8:42am

Not all JPA join tables need be without a primary-key. An indexed List ought to use owner-id+index as the PK. A Set (OneToMany) ought to use owner-id+element-id as the PK. A Set (non-persistable) could use owner-id+element (depending on element column type). And the JPA implementation could easily allow an "adapter column" for those cases that aren't mentioned above (assuming the JPA implementation was complete enough)

Manuel Jordan replied on Mon, 2011/07/11 - 9:00am

Thanks for the article

Exists an error in the title, must be without and not witout

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.