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 <= ?