Grails -- Using Embedded Derby Database

Apache Derby is a relational database implemented in Java. It is light-weighted and can be easily embed it in any Java-based solution. Here is a summary of using Derby Embedded JDBC driver within Grails framework. I used grails-0.3.1 for the time being.

Section 1: Install Software:

1.Install Apache Derby (v10.1.3.1)

Follow the tutorial from http://db.apache.org/derby/papers/DerbyTut/index.html to download and install software. (Note: ij tool is great to run SQL query from command line).

2.Install Grails (v 3.0.1)

The installation instruction can be found here. Set up environmental variables as described in the tutorial.

Section 2: Configure Grails for Using Embedded Derby Database

Inside $GRAILS_HOME, create sample application.

1. Create new Grails application by run command grails create-app, set up corresponding application name as myTest. Run command “grails create-domain-class”, set up domain name as “Book”.

2. Copy derby.jar and derbytools.jar from $DERBY_INSTALL to myTest/lib.

3. Configure data sources. In myTest/grails-app/conf, there are three data source files:

• DevelopmentDataSource.groovy
• ProductionDataSource.groovy
• TestDataSource.groovy

We need change the settings to let application talk to Derby instead of default Hypersonic database. A sample setting in ProductionDataSource.groovy would look like:

class ProductionDataSource {
boolean pooling = true
String dbCreate = "update" // one of 'create', 'create-drop','update'

##note: this setting point to a embedded db in /opt/db-derby-10.2.1.6-bin.
##The file could be anywhere that the application can reach
String url = "jdbc:derby:/opt/db-derby-10.2.1.6-bin/derbyDB"

String driverClassName = "org.apache.derby.jdbc.EmbeddedDriver”

String username = ""
String password = ""
}

4. We need another file in myTest/grails-app/hibernate/ called hibernate-dialects.properties which looks like:

DerbyDialect=Apache Derby

This file is required especially for Derby DB but not for Postgresql as I know.

Section 3: Run Application:

1. Then we are ready to run the application, execute the command:

grails run-app.

If everything goes right, we should be able to launch the application from http://localhost:8080/myTest.

2. To test the application by create some new records.

3. To verify whether the records are in Embedded Derby database, user Derby ij tool to run SQL query against our database prodDB. Please note, Derby won't allowed access from multiple applications for embedded model, so we have to shut down our application in order to run ij tool.

References

1. Apache Derby Tutorial (http://db.apache.org/derby/papers/DerbyTut/index.html)
2. Apache Derby Downloads (http://db.apache.org/derby/derby_downloads.html)
3. Grails Installation Instructions http://grails.codehaus.org/Installation