Over a million developers have joined DZone.

Referencing a Derby database column with a reserved name using DBUnit

DZone's Guide to

Referencing a Derby database column with a reserved name using DBUnit

· Java Zone
Free Resource

Download Microservices for Java Developers: A hands-on introduction to frameworks and containers. Brought to you in partnership with Red Hat.

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.

Download Building Reactive Microservices in Java: Asynchronous and Event-Based Application Design. Brought to you in partnership with Red Hat


Published at DZone with permission of Ralf Quebbemann, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}