Monday, March 29

Use Grails (GORM) with legacy Oracle 9i database

I am currently consulting on a project with a client who is using the legacy Oracle 9i database with JDK 1.4.2. I have recommended Groovy and Grails for their new enhancement project, however to add some grooviness to this kind of legacy platform introduce some pretty unique challenges. In this post I will attempt to compile a list of hurdles we went through to get Grails working perfectly with the legacy environment.

Grails version

First thing you need to know is that only Grails 1.0.x is compatible with JDK 1.4 so only limited grooviness is available but major improvement nevertheless from our client's existing environment.

Hibernate Dialect

Some of the meta API that Hibernate relies on to pick the right database dialect is not available with 9i so in your DataSource.groovy use the following configuration to specify a dialect manually.

dataSource {
driverClassName = "oracle.jdbc.OracleDriver"
dialect = "org.hibernate.dialect.Oracle9iDialect"

Table Level Mapping

A few thing needs to be configured on pretty much every one of your domain class to work with existing data schema. First map the table to an existing table (most of the legacy table naming convention will not be same as what hibernate expect unless you are extremely lucky ;-), and secondly turn off the optimistic locking so hibernate will not look for the version column on the table. This can be achieved by adding this static mapping field on our GORM domain class.

static mapping = {
version false

ID Generation Strategy

Most likely the legacy database might have some pretty custom ID generation strategy. To have the flexibility to map your domain class id to the legacy column you need to define the id field manually. Normally GORM/Hibernate automatically map the id field for you (usually using bigint or number of some sort) but manually defining it allows you to map it to pretty much any kind of data type.

String id

static mapping = {
id column: 'ENTITYID', generator: 'assigned', type: 'string', sqlType: 'varchar2'

We have found keeping the id field for every model class with the default name "id" makes the scaffolding and view generation working for most of the scenarios even with the legacy database, which is a big time saver for us.

For auto increment id with sequence you can rely on the hibernate default sequence, just make sure to create the sequence "hibernate_sequence" before you start the application. Or you can specify your generator using the following mapping:

Long id

static mapping = {
id column: 'ENTITYID', generator: 'sequence', params:[sequence:'custom_seq'], sqlType: 'integer'

Install plugins with Grails 1.0.x

The Grails plugin repository has grown tremendously in size, and the out-dated install-plugin script used by Grails 1.0.x does not seems to be able to handle the large list, besides it tries to update the entire plugin list each time which is very time consuming. What we found the best way to install plugin with Grails 1.0.x is by pin down the plugin yourself first, and then just install the plugin using the URL directly. Just make sure the right plugin version is used since not all the plugin works with Grails 1.0.x. All Grails plugins can be found here:

For example to install Testing plugin (part of Grails core since 1.1) using the following command:
grails install-plugin

After this you should be well on your way to unleash some grooviness with your perfectly aged environment. Time to impress the heck out of your boss and revitalize your work environment :D