Access a Legacy Database using GORM DSL

• Introduction

Many tutorials and posts discuss creating green-field grails application. In this post, I am going to cover creating a simple grails CRUD application using an existing legacy database schema using GORM DSL. This article assumes that you understand Oracle database, grails and GORM basics. Follow on posts may discuss topics such as testing the GORM DSL mappings, adding access control and creating simple reports.
NOTE: I got a lot of the information for this post from the "Advanced GORM kung fu" chapter of the Grails in Action book from Manning. I highly recommend this book.

• Environment

In creating this post, I am using the netbeans 6.7 RC3 IDE, Sun JDK 1.6.0_14, groovy 1.6.3, grails 1.1.1 and Oracle XE on an Ubuntu workstation. However, the code I am showing should work just fine on any OS/IDE/Oracle combination. I have chosen the Oracle XE database because it comes with several example database schemas in a freely available database and Oracle is a database that I often work with professionally.

After you have installed the Oracle XE database: 1) enable the hr user and 2) change it's password.

1) ALTER USER hr ACCOUNT UNLOCK;
2) ALTER USER hr IDENTIFIED BY your_hr_password;

Verify that you can successfully connect to the hr database with you new password via sqlplus, SQL Developer or some other tool.

Here is a diagram of the hr database:

• Create the Application

I will start by creating domain classes for just 3 of the tables, the Region, Country and Location tables. These tables are more straight forward and we can cover the other tables in another post.

1. Create the grails application:
   grails create-app hrReports


2. Copy the Oracle JDBC driver ojdbc14.jar to hrReports/lib. The Oracle JDBC Driver can be copied from $ORACLE_HOME/jdbc/lib or downloaded from the Oracle site.


3. Create the grails domain classes Region, Country and Location:
   grails create-domain-class Region
   grails create-domain-class Country
   


4. Create the grails controller Region, Country and Location:
   grails create-controller Region
   grails create-controller Country
   

• Configure our DataSource

Modify our DataSource.groovy and add something similar to the following as your development environment:

development {
    dataSource {
        driverClassName = "oracle.jdbc.OracleDriver"
        url = "jdbc:oracle:thin@localhost:1521:xe
        username = "hr"
        password = "your_hr_password"
    }
}

• Configure the Scaffolding

In the RegionController and CountryController classes add:

def scaffolding = true

Our front end development is complete.

• Create the Region Domain Object

The Regions table has only two fields: REGION_ID NUMBER and REGION_NAME VARCHAR(25). This is represented in our Region Domain class:

Integer regionId
String regionName

The constraints are:

static constraints = {
    regionId(blank:false, unique:true)
    regionName(nullable:true, maxSize(25)
}

Since the Region Domain Class is mapped to the REGIONS database table, we need to add the following mapping:

static mapping = {
    table 'REGIONS'
}

REGION_ID is the primary key and it is assigned by the user and not by a sequence or some other method. Additionally, hibernate expects the primary key to be named id. Hibernate also uses a field named version (which the DBA will not allow us to add to our legacy database) for optimistic locking. We address both of these issues by adding the following to our mapping closure:

version false
id generator:'assigned', column:'REGION_ID', type:'integer'

Now comes the funky part; dealing with a natural key as a primary key. We want to refer to the primary key as regionId in our groovy code and we also want the field to appear in the grails generated scafolding. We can accomplish this by changing the variable name from regionId to id (so hibernate will work) and create a transient variable named regionId. We create a getter and setter for regionId to tie it to the id field:

Integer id
//regionId is the primary key
static transients = ['regionId']
void setRegionId(Integer regionid) {
id = regionId
}
void getRegionid() {
return id
}

This change allows hibernate to work (it has an id), the code to reference regionID and the mapping from the GORM DSL to Database columns to work as expected. If we are inserting a new record with this approach, we need to user the save(insert:true) since a non-null id indicates a persisten instance.

Pull it all together and our Region.groovy Domain Class looks like this:

class Region {
 Integer id
 // regionId is the primary key
 static transients = ['regionId']
 void setRegionId(Integer regionId) {
     id = regionId
 }
 Integer getRegionId() {
     return id
 }
 String regionName

 static constraints = {
     id(blank:false, unique:true)
     regionName(nullable:true, maxSize:25)
 }

 static mapping = {
     table 'REGIONS'
     version false
     id generator:'assigned', column:'REGION_ID', type:'integer'
 }

 String toString() {
     "$regionName"
 }
}

• Create the Country Domain Object

The Country Domain Object if very similar to the Region Domain Object. There are two primary differences:

1. The primary key is a String and not an Integer
2. There is a one-to-many relationship that is represented by the region field.
   

Here is the finished Country.groovy Domain Class:

class Country {
 String id
 // countryId is the primary key
 static transients = ['countryId']
 void setCountryId(String countryId) {
     id = countryId
 }
 String getCountryId() {
     return id
 }
 String countryName
 Region region

 static constraints = {
     id(blank:false, unique:true, maxSize:2)
     countryName(nullable:true, maxSize:40)
     region(nullable:true)
 }

 static mapping = {
     table 'COUNTRIES'
     version false
     id column:'COUNTRY_ID', generator:'assigned', type:'string'
 }

 String toString() {
     "$countryName"
 }
}

• Conclusions

1. If you made it this far, you really do care about GORM legacy DB mappings.
2. It takes longer to write this blog post than to figure out and write the CRUD application using the GORM DSL mappings.
3. It does seem to be possible to quickly map legacy databases with GORM DSL.
4. This was completed in a couple of hours and in 224 LOC (not counting Unit Tests). I think the next times will be much quicker.
   

• Next Steps

I can make available the Domain Classes for the rest of the HR schema. Also, I would like to write more thorough Unit Tests to verify that the GORM DSL mappings are doing what we expect. I think normally this shouldn't be necessary and we can expect our ORM tools to do what they are supposed to, but in this case, it will help to reassure myself that the GORM DSL mappings are doing what I expect them to be doing.