Cannot Configure Grails 3 Multiple Data Sources

Question

I have recently attempted to create an application that allows me to compare 3 separate databases for their values. The databases are 3 Oracle instances which house (essentially) the same database, but in a DEV/TEST/PROD setting.

What I want to do is create one Domain class in GRAILS 3. I then want to be able to fetch the records which that domain class maps to, but do it for all 3 environments.

From reading the Grails 3 docs, it looks like this should be possible by defining 3 datasources in application.yml (in my example here I define 4):

dataSources:
    dataSource:
        pooled: true
        jmxExport: true
        logSql: true
        driverClassName: oracle.jdbc.OracleDriver
        username: MYUSER
        password: Password1
        dbCreate: validate
        url: jdbc:oracle:thin:@someserver:1521:DEV1
    dataSource1:
        pooled: true
        jmxExport: true
        logSql: true
        driverClassName: oracle.jdbc.OracleDriver
        username: MYUSER
        password: Password1
        dbCreate: validate
        url: jdbc:oracle:thin:@someserver:1521:DEV1
    dataSource2:
        pooled: true
        jmxExport: true
        logSql: true
        driverClassName: oracle.jdbc.OracleDriver
        username: MYUSER
        password: Password1
        dbCreate: validate
        url: jdbc:oracle:thin:@someserver:1521:TEST1
    dataSource3:
        pooled: true
        jmxExport: true
        logSql: true
        driverClassName: oracle.jdbc.OracleDriver
        username: MYUSER
        password: Password1
        dbCreate: validate
        url: jdbc:oracle:thin:@someserver:1521:PROD1

and then within the domain class' mapping specifying datasources in the mapping:

package plsutils

class DmjTypes {
    String code
    String description
    Date insertDate
    String insertUser
    Date modifyDate
    String modifyUser

    String dbEnv

    static mapping = {
        datasources(['dataSource1', 'dataSource2', 'dataSource3'])

        version false
        table name: "CDE_DMJ_TYPES", schema: "MYSCHEMA"

        id generator: 'sequence' ,params:[sequence: 'DMJTY_SEQ']

        columns {
            id column: "DMJTY_ID"
            code column: "DMJTY_CDE"
            description column: "DMJTY_DESCR"
            insertDate column: "INSERT_DTT"
            insertUser column: "INSERT_USER"
            modifyDate column: "MODIFY_DTT"
            modifyUser column: "MODIFY_USER"
            dbEnv formula:'( select inst.instance_name || \'-\' ||     inst.host_name from v$instance inst ) '
        }

    }
}

and then, within my controller, I should be able to do something like this:

params.max = Math.min(max ?: 10, 100)
dmjTypesListDev = DmjTypes.dataSource1.list(params)
dmjTypesListTest = DmjTypes.dataSource2.list(params)
dmjTypesListProd = DmjTypes.dataSource3.list(params)

I am getting an error upon the first call:

URI /dmjTypes/index
Class groovy.lang.MissingPropertyException
Message null
Caused by No such property: dataSource1 for class: plsutils.DmjTypes

I'm using ojdbc7.jar, connection to Oracle 11g and I'm using Grails 3.0.9.

I can't help but think I'm doing something subtly stupid somewhere. Can anyone please help me with this?

Cheers, Allen

Answer 1

Once I corrected the version of grails referred to in the gradle.properties and performed a complete clean and rebuild, I was able to get it working. I believe all my issues were because of a version mismatch of the grails I was using to build it.

So, this is the appropriate datasource entry:

dataSources:
    dataSource:
        pooled: true
        jmxExport: true
        driverClassName: oracle.jdbc.OracleDriver
        username: MYUSER
        password: Password1
        dbCreate: validate
        url: jdbc:oracle:thin:@somelocalserver:1521:LOCAL
    one:
        pooled: true
        jmxExport: true
        driverClassName: oracle.jdbc.OracleDriver
        username: MYUSER
        password: Password1
        dbCreate: validate
        url: jdbc:oracle:thin:@somedevserver:1521:DEV1
    two:
        pooled: true
        jmxExport: true
        driverClassName: oracle.jdbc.OracleDriver
        username: MYUSER
        password: Password1
        dbCreate: validate
        url: jdbc:oracle:thin:@sometestserver:1521:TEST1
    three:
        pooled: true
        jmxExport: true
        driverClassName: oracle.jdbc.OracleDriver
        username: MYUSER
        password: Password1
        dbCreate: validate
        url: jdbc:oracle:thin:@someproductionserver:1523:PROD1

This is the working domain class:

class DmjTypes {
    String code
    String description
    Date insertDate
    String insertUser
    Date modifyDate
    String modifyUser

    String dbEnv

    static mapping = {
        datasources(['one', 'two', 'three'])

        version false
        table name: "CDE_DMJ_TYPES", schema: "MYSCHEMA"

        id generator: 'sequence' ,params:[sequence: 'DMJTY_SEQ']

        columns {
            id column: "DMJTY_ID"
            code column: "DMJTY_CDE"
            description column: "DMJTY_DESCR"
            insertDate column: "INSERT_DTT"
            insertUser column: "INSERT_USER"
            modifyDate column: "MODIFY_DTT"
            modifyUser column: "MODIFY_USER"
            dbEnv formula:'( select inst.instance_name || \'-\' ||     inst.host_name from v$instance inst ) '
        }

    }
}