Referencing a Derby database column with a reserved name using DBUnit
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, "\"?\"");
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
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.