SQL Zone is brought to you in partnership with:

Graduated in computer science at the University of Oldenburg, Germany. Develops software for over 20 years now. Likes to program in Java. Wrote and contributed Maven plugins and implemented initially Maven support for IntelliJ IDEA. Ralf 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

Referencing a Derby database column with a reserved name using DBUnit

  • submit to reddit

DBUnit is a pretty good tool to achieve reproducible results when unit testing your code. It puts your database in a known state before each test run.
In my case I use JPA with Hibernate as implementation to create the DB schema before each test run. Furthermore I use an embedded in-memory Derby database.

I ran into problems when inserting dbunit datasets into the created schema. A column was named intervalwhich is a reserved word in Derby.

No problem I thought, when escaping the column name (using ' or " ticks) the problem would be solved. Well it turns out to be not that simple :-(

The solution is to set a DBUnit related property/feature to tell DBUnit how to config and use the underlying database. Here is the relevant code snippet

IDatabaseConnection memDbConnection = new DatabaseDataSourceConnection(  
       (InitialContext) context, DEFAULT_DATASOURCE);  
   DatabaseConfig dbConfig = memDbConnection.getConfig();  
   dbConfig.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new DefaultDataTypeFactory());  
   dbConfig.setProperty(DatabaseConfig.PROPERTY_ESCAPE_PATTERN, "\"?\"");  

The line

dbConfig.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new DefaultDataTypeFactory());

tells DBUnit to use the default data type factory which works well with Derby. You don't have to set this property explicitly when using Derby.

The second line

dbConfig.setProperty(DatabaseConfig.PROPERTY_ESCAPE_PATTERN, "\"?\""); 

solved my issue. You have to explicitly declare an escape pattern. In my case this was "\"?"".
With default settings DBUnit uses no escape pattern.

Using this solution, a clean insert into the database with a column that uses a reserved name works as expected.

Published at DZone with permission of Ralf Quebbemann, author and DZone MVB. (source)

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