grails: How to map one to many relationship with composite ForeignKey

Question

I have a pre-existing database with two tables FileContainer and FileObjects. FileContainer has a composite primary key of clientId and fileContainerId. FileContainerId is unique per client. FileObjects also has a composite primary key of clientId(FileContainerClientId) and fileObjectId. FileContainer and FileObjects has a one to many relationship. Foreign Key Relationship is having multiple columns(clientId and fileContainerId). So my Domain classes look like the following.

class FileContainer implements Serializable {

  BigDecimal clientId
  BigDecimal fileContainerId
  ...
  ...
  static hasMany = [fileObjects: FileObject]

  static mapping = {
    table 't_container'
    id composite : ["clientId", "fileContainerId"]
  }
}

class FileObject implements Serializable {

  BigDecimal FileContainerClientId
  BigDecimal fileObjectId
  FileContainer fileContainerId
  ...
  ...
  static belongsTo = [FileContainer]

  static mapping = {
    table 't_file_object'
    id composite : ["FileContainerClientId", "fileContainerId"]
    column fileContainerId: 'custom_name_container_id'
    column FileContainerClientId: 'client_id'
  }
}

But unfortunately above Domain classes fails validation and give the following exception with text org.hibernate.MappingException: 'Repeated column in mapping for entity: FileObject column: file_container_client_id (should be mapped with insert="false" update="false")'.

I tried using a custom UserType consisting of columns (client_id and file_container_id). But got all sorts of different errors.

How should domain classes be created in this case ?

Also, If somebody can give an example of using org.hibernate.usertype.UserType that would be helpful.

=============================================================== Also tried the following in the FileObject class.

class FileObject implements Serializable {

  BigDecimal clientId
  BigDecimal fileObjectId
  BigDecimal fileContainerId
  ...
  ...
  static belongsTo = [fileContainer: FileContainer]

  static mapping = {
    table 'file_object'
    id composite : ["FileContainerClientId", "fileContainerId"]
    column fileContainerId: 'custom_name_container_id'
    column clientId: 'client_id'
    fileContainer column: 'client_id'
    fileContainer column: 'file_container_id'
  }
}

But got the missing file_container_client_id column exception.

========================================================================= Since the FileContainerClientId in Object class is a foreign key to clientId in Container class, it should never be inserted or updated directly, it should always come from FileContainer class. Going by the above logic, I tried adding the following mapping

static mapping = { 
  FileContainerClientId insertable: false 
  FileContainerClientId updateable: false 
}

but Compile failed since the property name starts with capital letter. If I change the property name to fileContainerClientId then it compiles but cannot map the column to foreignKey and throws a missing file_container_client_id column exception during validation

Also tried to add the following in the Container, static mappedBy = [Objects: ["clientId", "containerId"]]. But got the compilation exception that Found an ArrayList where java.lang.String expected. — Arvind Sachdeva
The names of the classes etc has been modified just prior to publishing the question. — Arvind Sachdeva

Arvind Sachdeva Arvind Sachdeva · Accepted Answer · 2017-02-22T11:26:30

The following code can be used to map such cases.

class FileContainer implements Serializable {

  BigDecimal clientId
  BigDecimal fileContainerId
  ...
  ...
  static hasMany = [fileObjects: FileObject]

  static mapping = {
    table 't_container'
    id composite : ["clientId", "fileContainerId"]
  }
}

class FileObject implements Serializable {

  BigDecimal clientId
  BigDecimal fileObjectId
  BigDecimal fileContainerId
  FileContainer fileContainer
  ...
  ...
  static belongsTo = [FileContainer]

  static mapping = {
    table 't_file_object'
    id composite : ["clientId", "fileObjectId"]
    columns {
        fileContainer {
            column name:'client_id'
            column name:'file_container_id'
        }
    }
    fileContainer insertable:false, updateable:false

  }
}

grails: How to map one to many relationship with composite ForeignKey

1 Answers