GORM map table without primary key

Used versions: GORM 6.1.9 

Hibernate 5.1.5

Grails 3.3.10

This week I had to map an object on an existing database table with an existing primary key not named ‘id’ but ‘aniId’. By convention GORM is expecting such an ‘id’ property and if it is absent GORM inserts it for us which is very handy with new tables. But with an existing table this is not what you want GORM to do, because eventually the database will throw a SQL error for this unknown column.

GORM is the acronym for Groovy Object Relational Mapping. It is build upon Hibernate. GORM provides us a nice interface for defining domain objects such as shown below. Hibernate is performing the actual mapping of the domain object on a database table by generating sql-queries.

The issue

Given the following domain class (Groovy) which maps to a table ‘ANIMAL’ with aniId as primary key:

import groovy.transform.ToString
import groovy.util.logging.Slf4j 

@ToString(includeNames = true) 
@Slf4j 
class Animal { 
  Integer aniId 
  String fullName 
  static mapping = { 
    datasource 'DATABASE' 
    table "ANIMAL" 
    cache usage:'read-only' 
    version false 
    aniId column: "ANI_ID" 
    fullName column: "ANIMAL_NAME", sqlType: "varchar", length: 30
  }
}

When we try to find an Animal

   Animal animal = Animal.get(1)

GORM is inserting the ‘id’ property automatically which causes Hibernate to add ‘id’ to the sql-query:

Hibernate: 
  select * from
  ( select
    this_.id as id1_1_0_,
    this_.ANI_ID as ANI_ID5_1_0_,
    this_.ANIMAL_NAME as ANIMAL_NAME9_1_0_,
    from ANIMAL this_
    where
    this_.ANIMAL_NUMBER=?
  )
  where rownum <= ?

Which obviously raises an error:

ERROR — [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : ORA-00904: “THIS_”.”ID”: invalid identifier

The solution

Add an ‘id’ property for GORM to use it and prevent Hibernate from using it in the query by marking it ‘transient’. Point the ‘id’ property to the real identifier ‘aniId’

class Animal {
  transient Integer id
  Integer aniId
  String fullName

Add a list with transients

  static transients = ['id']

And, most important, in static mapping add a mapping for id onto the real primary key

  static mapping = {
    datasource 'DATABASE'
    table "ANIMAL"
    cache usage:'read-only'
    version false

    id name: ‘aniId’

    aniId column: "ANI_ID"
    fullName column: "ANIMAL_NAME", sqlType: "varchar", length: 30
  }
}

Now the query is generated correctly and executed successfully:

Hibernate:
  select * from
  ( select
    this_.ANI_ID as ANI_ID1_1_0_,
    this_.FORE_NAME as FORE_NAME2_1_0_,
    this_.PAR_ID_DATA_PROVIDER as PAR_ID_DATA_PROVID3_1_0_,
    this_.ANI_ID_SIRE as ANI_ID_SIRE4_1_0_,
    this_.COLOUR_CODE as COLOUR_CODE5_1_0_,
    this_.BIRTH_DATE as BIRTH_DATE6_1_0_,
    this_.ANIMAL_NUMBER as ANIMAL_NUMBER7_1_0_,
    this_.ANIMAL_NAME as ANIMAL_NAME8_1_0_,
    this_.ANI_ID_DAM as ANI_ID_DAM9_1_0_
    from
      ANIMAL this_ 
    where this_.ANIMAL_NUMBER=? 
  )
  where rownum <= ?