Composite Key and mapping in grails 2.2.5 with legacy database

Question

I have 4 tables. osiguranje_paket, atribut, tip_unosa, razna_polja. osiguranje_paket, atribut, tip_unosa are Parents of razna_polja table. razna_polja table has composite key that is consisted from two primary keys (osgp_id = osiguranje_paket table + atr_id = atribut table). The relationships between them are one-to-many bidirectional and I'm using Legacy PostgreSQL Database with dynamic scaffolding, I can not make any changes to database or tables or anything. How can I map my classes to use composite key, what do I need to add or change in my domains? Any help would be appreciated.

CREATE TABLE revoco.osiguranje_paket
  (
  osgp_id serial NOT NULL,
  osg_id integer NOT NULL,
  osgp_napomena character varying(500),
  tpo_id integer NOT NULL,
  osgp_link character varying(155),
  osgp_oznaka character varying(10),
  CONSTRAINT osgp_pk PRIMARY KEY (osgp_id),
  CONSTRAINT osg_osgp_fk FOREIGN KEY (osg_id)
      REFERENCES revoco.osiguranje (osg_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tpo_osgp_fk FOREIGN KEY (tpo_id)
      REFERENCES revoco.tip_osiguranja (tpo_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
) 

CREATE TABLE revoco.atribut
(
  atr_id serial NOT NULL,
  atr_naziv character varying(155) NOT NULL,
  lab_id integer,
  atr_rbr integer,
  CONSTRAINT atr_pk PRIMARY KEY (atr_id),
  CONSTRAINT atr_lab_labela_fk FOREIGN KEY (lab_id)
      REFERENCES common.labela (lab_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE TABLE common.tip_unosa
(
  tpu_id serial NOT NULL,
  tpu_val character varying(32) NOT NULL,
  CONSTRAINT tpu_pk PRIMARY KEY (tpu_id),
  CONSTRAINT tpu_vrijednost_unique UNIQUE (tpu_val)
)

CREATE TABLE common.razna_polja
(
  osgp_id integer NOT NULL,
  atr_id integer NOT NULL,
  tpu_id integer NOT NULL,
  rap_odjel integer NOT NULL DEFAULT 0,
  rap_vidljiv boolean NOT NULL DEFAULT true,
  CONSTRAINT rap_pk PRIMARY KEY (osgp_id, atr_id),
  CONSTRAINT rap_atr_atribut_fk FOREIGN KEY (atr_id)
      REFERENCES revoco.atribut (atr_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT rap_osgp_paket_fk FOREIGN KEY (osgp_id)
      REFERENCES revoco.osiguranje_paket (osgp_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT rap_tpu_tip_unosa_fk FOREIGN KEY (tpu_id)
      REFERENCES common.tip_unosa (tpu_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT rap_ispravan_odjel_ck CHECK (rap_odjel >= 0 AND rap_odjel <= 1)
)

This are my Domain classes

OsiguranjePaket.groovy

import common.RaznaPolja

   class OsiguranjePaket {

    Integer id
    String osgp_napomena
    String osgp_link
    String osgp_oznaka


    static belongsTo = [osg: Osiguranje, tpo: TipOsiguranja]
    static hasMany = [raznaPolja: RaznaPolja]

        String toString(){
            "${osgp_oznaka}"
        }

    static fetchMode = [raznapolja: 'eager']


    static constraints = {

        id(unique: true)
        osgp_link (nullable: true, blank: false, size: 0..155)
        osgp_napomena (nullable: true, blank: false, size: 0..500)
        osgp_oznaka (nullable: true, blank: false, size: 0..10)     
    }

    static mapping = {
        table name: 'osiguranje_paket', schema: 'revoco'
        version false       
        id generator :'identity', column :'osgp_id', type:'integer'
    }   
}

Atribut.groovy

import common.RaznaPolja
import common.Labela

class Atribut {

Integer id
String atr_naziv
Integer atr_rbr



static hasMany = [raznaPolja: RaznaPolja]
static belongsTo = [lab: Labela]

static fetchMode = [raznaPolja: 'eager']


String toString(){
    "${atr_naziv}"
}

static mapping = {
    table name: "atribut", schema: "revoco"
    version false
    id generator :'native', column :'atr_id'
}

static constraints = {

    id(blank: false, unique: true)
    atr_naziv (blank: false, size: 0..155)
    atr_rbr (nullable: true)
    }
}

TipUnosa.groovy

class TipUnosa {

    Integer id
    String tpu_val


    static hasMany = [raznaPolja: RaznaPolja]

    static fetchMode = [raznaPolja: 'eager']

        String toString(){
            "${tpu_val}"
        }

    static constraints = {
        id (blank:false, size: 0..10)
        tpu_val (blank:false, unique:true, size:0..32)
    }

    static mapping = {
        table name: "tip_unosa", schema: "common"
        version false
        id generator :'identity', column :'tpu_id', type:'integer'
}

}

RaznaPolja.groovy

import java.io.Serializable;

import revoco.Atribut
import revoco.OsiguranjePaket

class RaznaPolja implements Serializable  {


    Integer rap_odjel
    Boolean rap_vidljiv     

//without this getting common.RaznaPolja(unsaved) 
    String toString(){
        "${id}" //Getting null
    }   

    static belongsTo = [atr: Atribut, osgp: OsiguranjePaket, tpu: TipUnosa]

    static mapping = {
        table name: 'razna_polja', schema: 'common'

        id composite: ['osgp', 'atr']
//      cache usage:'read-only'
        version false
        rap_odjel column: 'rap_odjel', type: 'integer'
        rap_vidljiv column:'rap_vidljiv', type: 'boolean'
}
}

score 0 · Answer 1 · answered Aug 17 '15 at 02:31

Composite key

To set a composite key you need to use the mapping property of your domain class and your domain class must implement the Serializable interface. Here's an example from the Grails documentation.

import org.apache.commons.lang.builder.HashCodeBuilder
class Person implements Serializable {

    String firstName
    String lastName

    boolean equals(other) {
        if (!(other instanceof Person)) {
            return false
        }

        other.firstName == firstName && other.lastName == lastName
    }

    int hashCode() {
        def builder = new HashCodeBuilder()
        builder.append firstName
        builder.append lastName
        builder.toHashCode()
    }

    static mapping = {
        id composite: ['firstName', 'lastName']
    }
}

Database mapping

The domain class mapping property is also used to change the database table and columns your domain class maps to as you can see here.

Associations

As for the relationships between the tables, the documentation can give you some clues. You may have to add some mapping domain classes here and there to create what you need, but the Grails associations should be able to handle your needs.

Composite Key and mapping in grails 2.2.5 with legacy database

1 Answers1

Composite key

Database mapping

Associations