Saturday, March 15, 2008

Grails' ORM DSL Rules!

Probably my favorite feature in Grails is GORM, the Grails Object Relational Mapping. GORM gives you a host of time saving and powerful tools. Early on I was wowed by the GORM methods that were dynamically added to my domain classes: save(), delete(), list(), get(), findByProp1AndProp2, and the list goes on. I still appreciate these features, but a more recent addition to GORM has moved to the head of the class with me. The ORM DSL introduced in Grails 1.0 makes it trivially easy to map your domain classes onto legacy database schemas. It was already possible to do this in Grails and Jason Rudolph has given us some helpful tutorials, but the ORM DSL makes it so easy even I could do it. And that's important because it enabled me to get Grails into a production project with my current client. They were impressed with the productivity promise of Grails and were quite ready to look into something beyond the JSF/EJB stack they had been fighting with, but having anything or anyone besides the DBA creating the database schemas was out of the question.

Let's take a look at just how easy it is to use the Grails ORM DSL. For our example we will work with the tried and true Book and Publisher classes, but with legacy tables in an Oracle 10g database.

Here's our domain classes in Grails:

class Publisher {
String name
String city
static hasMany = [books : Book]
}

class Book {
String title
String author
Integer pages
Publisher publisher
static belongsTo = Publisher
}

And here's the legacy tables we have to work with:

TABLE PUB01
Column Name Data Type
ID_PUB_PK NUMBER
PUB_NM VARCHAR(100)
PUB_CTY VARCHAR(100)
SEQUENCE ID_PUB_PK_SEQ

TABLE BK01
Column Name Date Type
ID_BK_PK NUMBER
BK_TITLE VARCHAR2(100)
BK_AUTHOR VARCHAR2(100)
BK_PGS NUMBER
ID_PUB_FK NUMBER
SEQUENCE ID_BK_PK_SEQ

Now let's map our Publisher class to the PUB01 table:

class Publisher {
String name
String city
static hasMany = [books : Book]
static mapping = {
table 'PUB01'
columns{
id column: 'ID_PUB_PK'
name column: 'PUB_NM'
city column: 'PUB_CTY'
}
id generator:'sequence', params:[sequence:'ID_PUB_PK_SEQ']
}
}

Let's look at what we did here. We created a static block called 'mapping'. Inside the mapping block, we first define the table for this class (note that the table name and all column names are in quotes), and then we create a nested block called columns. In the columns block we list each property, a space, and then column: 'column name'. Using the word 'column' on each line might seem redundant, but there are other attributes of a property that can be set in the columns block, so when you are setting the column you have to be specific. After the columns block we have the id generation scheme, which we'll discuss in a bit.

Now we'll modify our Book class and discuss one interesting thing there:

class Book {
String title
String author
Integer pages
Publisher publisher
static belongsTo = Publisher
static mapping = {
table 'BK01'
columns{
id column: 'ID_BK_PK'
title column: 'BK_TITLE'
author column: 'BK_AUTHOR'
pages column: 'BK_PGS'
publisher column: 'ID_PUB_FK'
}
id generator: 'sequence', params:[sequence:'ID_BK_PK_SEQ']
}
}

You'll notice that this was pretty much the same thing we did with Publisher. The one difference is the way that we mapped the relationship. The publisher property, which is of type Publisher, is mapped using the foreign key for the Publisher table, 'ID_PUB_FK'

Now let's talk about id generators. In Grails you can use any id generation scheme supported by Hibernate. To do this with the ORM DSL, you basically call an id method and pass it a Map. The first key in the Map is the word generator, and the first value is the name of the generator class, such as 'hilo', 'seqhilo', 'identity' or 'sequence'. The Hibernate docs list the possible classes and their params. The next key is the word params and the value is another Map. The keys for this Map are name attributes of each of the param tags taken by this generator class. The values are the bodies of the param tags. In the case of the sequence generator we only have one param tag, with a name attribute of sequence, and the body would be the name of the sequence in the database, in our example 'ID_PUB_PK_SEQ' or 'ID_BK_PK_SEQ'.

One more note: We were able to talk our DBA into adding a number field called version to each of our legacy tables, so we left versioning on. If you cannot add a version field to your tables (and don't already have one), then you can turn off versioning by adding the line version false after the line where you declare your table. If you do this you will lose the optimistic concurrency that Grails / Hibernate give you and will have to provide some other method of handling concurrency.

There is a great deal more that you can do with the Grails ORM DSL, but I only wanted to discuss features that I've actually worked with. As I dig into this more I may add more details, but in the meantime you can read all about it in the Grails online docs.

8 comments:

Jason Rudolph said...

Good stuff, Dave! The ORM DSL is certainly a nice improvement over the old Hibernate XML mapping approach.

One suggestion: Capitalizing the names of your instance variables seems a bit odd, and is not idiomatic.


String Title


should be


String title


Congrats on freeing yourself from EJB+JSF land! ;-)

Dave Klein said...

Thanks Jason. Sometimes I switch between idiomatic and idiotic. :)

Daniel Spiewak said...

GORM certainly qualifies as a far better way to use Hibernate. To be honest though, I'm still a little skeptical as to the merits of the Data Mapper pattern (which is what GORM implements through Hibernate). Unfortunately, the Active Record pattern really hasn't taken off in Java-land.

Now if Scala had an Active Record ORM separate from Lift, that would be a force to be reckoned with!

Anonymous said...

On the subject of mapping, is it possible to map a controller not only to a table, but also to a particular database? I can set up several databases in the spring configuration file, and even switch between them using this call in my custom tag library: def sql = Sql.newInstance(databaseService.getDataSource(databaseService.VIMSCUBES))

I would like to be able to use the grails scaffolding, but pull tables from several different databases.

Thanks

DreamerForever said...

Yellow text on white background ? Are you serious ??? :o Please put at least white text on black background, so I don't have to use nigh vision gogles to read your code.

Thanks!

Dave Klein said...

I apologize for the code and background colors. That happened when I changed to a different blogspot theme. I'll try to get it fixed soon.

Matt said...

thanks much for the format of
id generator: 'sequence', params:[sequence:'ID_BK_PK_SEQ']

My problem is connecting to an Oracle DB with a ID column, a Sequence, Grails doesn't seem to find the existing sequence. I get the error
2010-08-13 15:45:32,260 [main] ERROR hbm2ddl.SchemaUpdate - Unsuccessful: create sequence WEBSYS.LOCS_SEQ
2010-08-13 15:45:32,260 [main] ERROR hbm2ddl.SchemaUpdate - ORA-01031: insufficient privileges

I've tried both [sequence:'SEQ_NAME'] and [sequence:'SCHEMA.SEQ_NAME']. But Grails seems to want to create a new one rather than use the one that is there. Thoughts?

Matt said...

re: previous error, in Oracle I had to run
GRANT SELECT ON "SCHEMA"."SEQ_NAME" to "ROLE";