Sunday, September 11, 2011

Grails Foreign ID Generator

I haven't found a complete example on the web of using a foreign id generator in grails, so here's one: Say you have two domains, Primary and Secondary, with a one-to-one relationship (Primary hasOne Secondary and Secondary belongsTo Primary). Primary and Secondary basically represent the same entity, but Secondary has a bunch of data about the entity you rarely use. You map Primary to the DB table named primary, and Secondary to the DB table secondary; and since you've got a one-to-one relationship between Primary and Secondary, you just want the same column in the secondary table to be used as both its primary key and its foreign key to the primary table.

So you define Primary and Secondary like this:

class Primary { int oftUsedInfo int moreOftUsedInfo static hasOne = [ secondary: Secondary ] static mapping = { secondary cascade: 'all-delete-orphan' } } class Secondary { String littleUsedInfo String moreLittleUsedInfo static belongsTo = [ primary: Primary ] static mapping = { id column: 'primary_id', generator: 'foreign', params: [ property: 'primary' ] primary insertable: false, updateable: false } }

With that mapping, hibernate will create tables for you like the following (when using the MySQL InnoDB dialect):

CREATE TABLE `primary` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `version` BIGINT(20) NOT NULL, `oft_used_info` INT(11) NOT NULL, `more_oft_used_info` INT(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; CREATE TABLE `secondary` ( `primary_id` BIGINT(20) NOT NULL, `version` BIGINT(20) NOT NULL, `little_used_info` VARCHAR(255) NOT NULL, `more_little_used_info` VARCHAR(255) NOT NULL, PRIMARY KEY (`primary_id`), KEY `FK12344567ABCDEF` (`primary_id`), CONSTRAINT `FK12344567ABCDEF` FOREIGN KEY (`primary_id`) REFERENCES `primary` (`id`) ) ENGINE=InnoDB;

Instead of the secondary table having its own separate AUTO_INCREMENT id column, it just re-uses the primary_id column (referencing the primary table) as its primary key.

When you create a new Primary and Secondary instances programmatically, you'd do it like this:

new Primary( oftUsedInfo: 1, moreOftUsedInfo: 2, secondary: new Secondary( littleUsedInfo: 'foo', moreLittleUsedInfo: 'bar', ), ).save()

Or, if you want to do it property by property:

def primary = new Primary() primary.oftUsedInfo = 1 primary.moreOftUsedInfo = 2 primary.secondary = new Secondary() primary.secondary.littleUsedInfo = 'foo' primary.secondary.moreLittleUsedInfo = 'bar' primary.save()

And when you delete, you only have to delete the Primary domain (because of the all-delete-orphan cascade setting):

Primary.findAllByOftUsedInfo(1).each { it.delete() }

One more thing to note: using the assigned generator like this seems to generate the same database schema:

class Secondary { String littleUsedInfo String moreLittleUsedInfo static belongsTo = [ primary: Primary ] static mapping = { id column: 'primary_id', generator: 'assigned' primary insertable: false, updateable: false } }

Not sure if the behavior is exactly the same, however.

7 comments:

  1. Thank you, thank you, thank you. I have been struggling for 3 days to get my domain classes defined correctly to reflect the one-to-one relationship in my data model. I finally found the "foreign" generator type, and searching for examples of that led me to this blog post. It was perfect. My precise situation was ever so slightly different, so I still struggled just a bit to translate your example to my situation. But, I eventually did, and it works, and I never would have gotten to that point without this excellent blog.

    Thank you!!

    ReplyDelete
  2. Hi Justin,

    at first, I kind of frowned at "id column: 'primary_id'" (where does the id column go?). But then, it just works as intended!

    Plays nicely with SQL server: The primary key is used both as as a "clustered index" (i.e. rows are stored within the primary index) and at the same time supporting the foreign key constraint, avoiding any artificial overhead introduced by separate foreign key columns and/or indexes.

    Great work! Thank you,
    Reiner

    ReplyDelete
  3. Just experienced there is a price to pay for one-to-one using Hibernate: Good Bye lazy loading. From JBoss Community Some explanations on lazy loading (one-to-one)

    ReplyDelete
  4. Super! Thanks a lot for your great post! Searched a lot and after a long time, eventually, I found this post. :-)

    ReplyDelete
  5. Thank you for your post from 2016:)

    ReplyDelete