Relational Mapping between tables in different schema

Question

Currently application is working on multiple schema in database, so we have a common schema that stores master tables whereas other schemas where we store client specific data.

So, specific scenario is like

(Below tables are for example purpose)

Master table for AnimalType which is resided in common schema whereas Animal table which is available on all client schema such as schema1, schema2...schemaN.

We are using Grails which default uses Hibernate so, relation is like

class AnimalType {
   String type

   static mapping = {
     datasources(['index'])
   }
}

class Animal {
   String name
   AniamlType animalType

}

So, when I start the application it shows below exception:

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory': Invocation of init method failed; nested exception is org.hibernate.MappingException: An association from the table animal refers to an unmapped class: org.sample.AnimalType

What I understood from this is because, Animal is trying to refer to AnimalType in it's own schema, but AnimalType does not exists there.

So, basically I want to map Animal to AnimalType pointing to common schema.

Something like below with syntax in Grails

class Animal {
   String name

   @(POINTING TO COMMON SCHEMA)
   AnimalType animalType
}

Answer 1

In your mapping add something like this:

table name: "animal_type", schema: "common"

From the current docs

Answer 2

Revised Answer

Hibernate is an abstracted layer on top of the database. Based on your @(POINTING TO COMMON SCHEMA) notation you may be coming from a Java background. Using the basic method below our group connects to dozens of schemata and multiple databases. Leverage the Grails domain classes to use convention over configuration when connecting to various schemata. See the Grails documentation on mapping tables to domain classes. Let me try and explain by describing sample database schemata and domain class mappings.

Hopefully you are accessing the database through an application user. Let's call it ANIMAL_APP. ANIMAL_APP has access to the following tables:

• ANIMAL_TYPE (COMMON TABLE, all animals can access)
• TIGER (Only available to tigers)
• SNAKE (Only available to snakes)
• BEE (Only available to bees)

Each of these can have their own domain classes. They would also have their own controllers where individualized logic could be built in. BEEs fly and TIGERS do not which can be accounted for in separate controller actions.

AnimalType DOMAIN CLASS

class AnimalType{   
    //AnimalType properties  
    static mapping = {
        table 'ANIMAL_TYPE', schema: 'ANIMAL_APP'
        id column: 'id'
    }
}

TIGER DOMAIN CLASS //Could be any of the animals

class Tiger{
    Sting name
    String value
    static belongsTo = [animalType:AnimalType]

    static mapping = {
        table 'TIGER', schema: 'ANIMAL_APP'
        //Be aware that you could map to a different schema as follows
        //table TIGER, schema: 'TIGER_SCHEMA'
        id column: 'id'
    }
}

Once the classes are linked to each other, manage the table access where it should be managed, in the database. Create synonyms for the tables that the schema ANIMAL_APP can use for TIGER, SNAKE, and BEE. Grant the correct permissions to ANIMAL_APP for each of those tables.

Configure the data sources in Config.groovy and DataSource.groovy

Config.groovy Configure the JNDI data source for each environment. Remember you are mapping to the database with ANIMAL_APP.

grails.naming.entries = [
        "jdbc/devanimalapp": [
                type: "javax.sql.DataSource", //required
                auth: "Container", // optional
                description: "Data source for Production", //optional
                driverClassName: "oracle.jdbc.OracleDriver",
                url: "[replace with your databaseDriver]@[jndi location]",
                username: "ANIMAL_APP",
                password: "secret",
        ],
        "jdbc/prodanimalapp": [
                type: "javax.sql.DataSource", //required
                auth: "Container", // optional
                description: "Data source for Production", //optional
                driverClassName: "oracle.jdbc.OracleDriver",
                url: "[replace with your databaseDriver]@[jndi location]",
                username: "ANIMAL_APP",
                password: "secret",
        ]
]

DataSource.groovy is then configured as follows. Remember this is a guideline and documentation can be found in the Grails knowledge base.

environments {
    development {
        dataSource {
            dbCreate = "validate" // one of 'create', 'create-drop', 'update', 'validate', ''
            dialect = "[insert your database dialect]"
            jndiName = "java:comp/env/jdbc/devredapp"
        }
    }

    production {
        dataSource {
            dbCreate = "validate" // one of 'create', 'create-drop', 'update', 'validate', ''
            dialect = "[insert your database dialect]"
            jndiName = "java:comp/env/jdbc/prodanimalapp"
        }
    }
}

Mapping your domain classes this way will allow you to take advantage of Grails convention of configuration and be safe by only accessing the tables necessary through the ANIMAL_APP schema. Controlling permissions and synonyms in the ANIMAL_APP schema will allow you to connect to different schemata and even remote databases.

Original Answer below for transparency

I am not sure I understand the question correctly but if you are trying to have the Animal Class inherit (see Grails GORM Inheritence) the AnimalType class you must declare the relationship for Hibernate.

class AnimalType{
    //some properties
    static hasMany = [animals:Animal, ...]
}

class Animal{
    String name
    String value
    static belongsTo = [animalType:AnimalType]
}

or

class Animal extends AnimalType{
    String animalName
}

If I have severly misunderstood your question please let me know and I will try again.