Monday, June 22, 2009

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.

46 comments:

  1. Good stuff. You can omit the blank:false and unique:true constraints on the PK fields though - or are those required because of the nonstandard PKs?

    ReplyDelete
  2. Great post. I need to get "Grails in Action".

    ReplyDelete
  3. I am not 100% sure, but I think since the id generator is set to assigned, grails wouldn't do any constraint checking on the id. I will try and verify this for my next post.

    ReplyDelete
  4. Nice article,

    Can you post some sreendumps of the generated pages, I would like to know if the tables have both Id columns in them (like Id and countryId)

    cheers,
    Herman

    PS:
    not sure how you compiled this:
    void getRegionid() {
    return id
    }

    ReplyDelete
  5. I don't get it why do we have to define a regionId in the groovy code. Actually the default id field names work perfectly. This post gives sample code how it works.
    http://denistek.blogspot.com/2009/12/grails-and-legacy-database.html

    ReplyDelete
  6. Interesting post but you should really consider getting rid of that background - it's almost too hard to read the content...

    ReplyDelete
  7. Great precise info, I've been searching on this topic for a while. Bookmarked and recommended
    windows 7 home premium

    ReplyDelete
  8. Excellent! Just what I was looking for. My set up was simpler since my id field was actually named id in the db.

    Too easy!

    Now off to customize the view...

    ReplyDelete
  9. Nice post. Oracle is a relational database management system produced by oracle corporation. Nowadays most of the multinational companies used this oracle database for storing and managing their data's and programs. So learning Oracle Training in Chennai is one of the best idea to make a bright career.

    ReplyDelete
  10. Thanks for your post; marketing course is most trusted course to validate marketing. This course provides precise and complete information about a online marketing course.
    online marketing course in chennai

    ReplyDelete
  11. Thank you for this wonderful tutorial. It was really helpful.

    electrical maintenance in chennai

    ReplyDelete
  12. There are lots of information about latest technology and how to get trained in them, like Best Hadoop Training In Chennai in Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies Hadoop Training in Chennai By the way you are running a great blog. Thanks for sharing this blogs..

    ReplyDelete
  13. I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing..
    SalesForce Training in Chennai

    ReplyDelete
  14. Pretty article! I found some useful information in your blog, it was awesome to read,thanks for sharing this great content to my vision, keep sharing..
    Unix Training In Chennai

    ReplyDelete
  15. This information is impressive..I am inspired with your post writing style & how continuously you describe this topic. After reading your post,thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic..
    Android Training In Chennai In Chennai

    ReplyDelete
  16. SAP Training in Chennai
    This post is really nice and informative. The explanation given is really comprehensive and informative..

    ReplyDelete
  17. Oracle Training in chennai
    Thanks for sharing such a great information..Its really nice and informative..

    ReplyDelete
  18. Selenium Training in Chennai
    Wonderful blog.. Thanks for sharing informative blog.. its very useful to me..

    ReplyDelete
  19. Data warehousing Training in Chennai
    I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly..

    ReplyDelete
  20. Whatever we gathered information from the blogs, we should implement that in practically then only we can understand that exact thing clearly,
    but it’s no need to do it, because you have explained the concepts very well. It was crystal clear, keep sharing..
    Websphere Training in Chennai

    ReplyDelete
  21. Oracle DBA Training in Chennai
    Thanks for sharing this informative blog. I did Oracle DBA Certification in Greens Technology at Adyar. This is really useful for me to make a bright career..

    ReplyDelete
  22. This is really an awesome article. Thank you for sharing this.It is worth reading for everyone. Visit us:
    Oracle Training in Chennai

    ReplyDelete
  23. very nice blogs!!! i have to learning for lot of information for this sites...Sharing for wonderful information.Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.
    Oracle DBA Training in Chennai

    ReplyDelete
  24. great article!!!!!This is very importent information for us.I like all content and information.I have read it.You know more about this please visit again.
    Oracle RAC Training in Chennai

    ReplyDelete
  25. Wonderful tips, very helpful well explained. Your post is definitely incredible. I will refer this to my friend.
    SalesForce Training in Chennai

    ReplyDelete
  26. I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly.
    Java Training in Chennai

    ReplyDelete
  27. Really awesome blog. Your blog is really useful for me. Thanks for sharing this informative blog. Keep update your blog.
    PHP Training in Chennai

    ReplyDelete
  28. Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.Nice article i was really impressed by seeing this article, it was very interesting and it is very useful for me..
    Android Training in Chennai

    ReplyDelete
  29. Really awesome blog. Your blog is really useful for me. Thanks for sharing this informative blog. Keep update your blog.
    SAP Training in Chennai

    ReplyDelete
  30. Excellent information with unique content and it is very useful to know about the information based on blogs.
    Hadoop Training in Chennai

    ReplyDelete
  31. This information is impressive; I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic..
    Informatica Training in chennai | QTP Training in Chennai



    ReplyDelete
  32. Latest Govt Bank Jobs 2016


    Very interesting thanks. I believe there's even more that could be on there! Keep it up........................

    ReplyDelete
  33. Best SQL Query Tuning Training Center In Chennai This information is impressive; I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic..

    ReplyDelete
  34. I really like and appreciate your blog post, thanks again. Want some more stuff. New exclusive song by Music MG, this is a very good song.
    Curso java

    ReplyDelete
  35. Hibernate Training Institutes in ChennaiHibernate Training Institutes in Chennai Hibernate Online Training Hibernate Online Training Hibernate Training in Chennai Hibernate Training in Chennai Java Online Training Java Online Training

    ReplyDelete
  36. Struts2 Training Institutes in Chennai Struts2 Training Institutes in Chennai Spring Training Institutes in Chennai Spring Training Institutes in Chennai Java MicroServices Training Institutes In Chennai Java MicroServices Training Institutes In Chennai

    ReplyDelete
  37. Wonderful article with lots of useful information very interesting to read and hearty thanks to the article writer.
    Regards
    Selenium Training in Chennai | Java Training in Chennai

    ReplyDelete