Multiple Data Sources

You need to use one more layer called dataSources ←– with s in your application.yml file.

Assume using MS SQL Server 2012. You need to replace * with your own data. In this example, we have the default dataSource, and an additional data source otherSource. We will talk about how to select the which data source in the domain in the next section. We only focus on setting up the development environment here, but we have to somehow provide some fake config in the test, and production environments, or the project cannot be build/run.

dataSources:
    dataSource:
        pooled: true
        jmxExport: true
        username: ********
        password: ********
        driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
    otherSource:
        dialect: org.hibernate.dialect.SQLServer2012Dialect
        driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
        pooled: true
        jmxExport: true
        username: ********
        password: ********
        url: jdbc:sqlserver://********:1433;databaseName=********;charset=utf8;useUnicode=yes&characterEncoding=UTF-8
        dbCreate: none

environments:
    development:
        dataSources:
            dataSource:
                logSql: true
                dbCreate: none
                dialect: org.hibernate.dialect.SQLServer2012Dialect
                url: jdbc:sqlserver://********:1433;databaseName=********;charset=utf8;useUnicode=yes&characterEncoding=UTF-8
                properties:
                    jmxEnabled: true
                    initialSize: 5
                    maxActive: 50
                    minIdle: 5
                    maxIdle: 25
                    maxWait: 10000
                    maxAge: 600000
                    timeBetweenEvictionRunsMillis: 5000
                    minEvictableIdleTimeMillis: 60000
                    validationQuery: SELECT 1
                    validationQueryTimeout: 3
                    validationInterval: 15000
                    testOnBorrow: true
                    testWhileIdle: true
                    testOnReturn: false
                    jdbcInterceptors: ConnectionState
                    defaultTransactionIsolation: 2 # TRANSACTION_READ_COMMITTED

            otherSource:
                properties:
                    jmxEnabled: true
                    initialSize: 5
                    maxActive: 50
                    minIdle: 5
                    maxIdle: 25
                    maxWait: 10000
                    maxAge: 600000
                    timeBetweenEvictionRunsMillis: 5000
                    minEvictableIdleTimeMillis: 60000
                    validationQuery: SELECT 1
                    validationQueryTimeout: 3
                    validationInterval: 15000
                    testOnBorrow: true
                    testWhileIdle: true
                    testOnReturn: false
                    jdbcInterceptors: ConnectionState
                    defaultTransactionIsolation: 2 # TRANSACTION_READ_COMMITTED

    test:
        dataSources:
            dataSource:
                dbCreate: update
                url: jdbc:h2:mem:testDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
            otherSource:
                dbCreate: update
                url: jdbc:h2:mem:testDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE

    production:
        dataSources:
            dataSource:
                jndiName: "java:/********"
                logSql: false
                dbCreate: none
            otherSource:
                dbCreate: update
                url: jdbc:h2:mem:testDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE 

By now, you might noticed that the config in the environments section, and the dataSources section is interchangeable, meaning that you can put the environment config in the dataSources config or the other way around.

Just put the following code in your domain object, and it will automatically map to our new otherSource. If we do not put anything datasource for the mapping, the domain will use the default one by default.

    static mapping = {
        datasource 'otherSource'
    }