How to add new auto-generated primary key field into legacy table with Grails?

Question

I have a legacy table I'm trying to hook up with Grails. I'm going to simplify it's model to make things easier. There are 2 fields in the table: "NAME" which is of type string and "CPAR" which is of type Integer and acts like a table's ID (primary key).

What I want to do is keep "CPAR" for backwards compatibility and insert a new field "ID" into the table that will be auto generated and actually be table's primary key.

Here is my domain class:

Class Partners {

    Long id
    String name
    Integer cpar

    static mapping = {
        table "PARTNERS"
        version false

        columns {
            id column: "ID", generator: "sequence"
            name column: "NAME"
            cpar column: "CPAR"
        }
    }
}

When I run this application it does create ID field in the table but all rows are populated with zeroes (The "ID" field is zero, not the whole row). When trying to access application via it's scaffolded web interface it gives me error: Cannot get property 'id' on null object. What I would like is to have those "ID" fields populated with proper values instead of zeroes. How can I accomplish this? Database I'm using is Firebird.

Answer 1

I'm not familiar with Firebird, but I'd assume it uses sequences similar to Oracle and MySql. The first problem that I see is the declaration of Long id. Hibernate provides the id for you so you don't need this line of code. Next you can specify the sequence that you want to use when you define the column.

static mapping = {
    columns {
        id column: "ID", generator:'sequence', params:[name:'PARTNERS_ID_SEQ']
    }
}

I've never used the columns{} closure I always have just done all of my column mapping inside of the mapping{} closure, so I'm not 100% sure the above code will work. If it doesn't you can move the generator and name outside of the columns{} and it should work:

static mapping = {
    columns {
        id column: "ID"
    }
    id generator:'sequence', params:[name:'PARTNERS_ID_SEQ']
}

This article talks specifically about mapping legacy tables: http://dave-klein.blogspot.com/2008/03/grails-orm-dsl-rules.html

This is the Grails doc on mapping IDs which doesn't really talk about sequence name: http://grails.org/doc/latest/ref/Database%20Mapping/id.html