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 interval which 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.

Using the embedded database in Glassfish v2 is that simple

Using the embedded Derby database of Glassfish v2 is pretty simple.

I recently had the requirement to checkout a JEE project to a location where I don't have access to any standard database like DB2, Oracle, MySql, etc ...

Just the embedded database of Glassfish was a viable option. So I checked out the project with Maven and started a build. Before the deployment I had to configure the database to use.

I entered the Glassfish admin web interface and did the following steps:

Resources -> JDBC -> Connection Pools -> New connection pool

 Name = Name of your connection pool  
 Resource Type = javax.sql.DataSource  
 Database Vendor = Derby  

Click "Next"

DataSource Classname = org.apache.derby.jdbc.ClientDataSource (should be prefilled)

Under "Additional Properties"

 connectionAttributes => create=true (this is important)  
 User => APP (this is default)  
 DatabaseName => Name_of_your_database (this is important)  
 Password => APP (this is important)  

Click "Finish" and you are done with the connection pool. Next you will create the datasource.

Resources -> JDBC -> JDBC Resources -> New JDBC Resource

Choose the correct JNDI name which will be used by your JPA and assign the previously created pool to the resource.

Now you are finished.

The dababase will be created when you deploy your application the next time.