Grails 3.x multiple datasources

Question

Back in Grails 2.x world I could create multiple dataSources in an environment:

development {
    dataSource {
        ...
    }
    dataSource_new {
        ...
    }
}

and reference them in the controller:

def db = new SQL(dataSource_new)

and everything worked awesome. In Grails 3.x, everything is not awesome:

development:
    dataSource:
        ...
    dataSource_new:
        ...

and calling

def db = new SQL(dataSource_new)

throws:

Ambiguous method overloading for method groovy.sql.Sql#

Anyone have success with this (or can point out what's changed that I've missed)?

Tried mapping in domain with no luck:

class abc {
    String ...

    static mapping = {
        datasource: ['DEFAULT', 'dataSource_new']
}

throws:

Ambiguous method overloading for method groovy.sql.Sql#<init>. Cannot resolve which method to invoke for [null] due to overlapping prototypes between: [interface java.sql.Connection] [interface javax.sql.DataSource]

Answer 1

I work with multiply datasources in Grails 3.x like following:

assuming you have configuration:

development {
  dataSources {
    dataSource {
      url = 'your_url'
      password = 'psw'
      ...
    }
    second {
      url = 'you_url_2'
      password = 'psw2'
      ...
    }
}

So in a service you will have mapped data source like this: dataSource_second

And to create Sql instance you will need to do the following:

def sql = new Sql(dataSource_second)

It works in Grails 3.0.11. Hope it will help.

Answer 2

In official docs you can find an example for injecting datasource into service:

https://grails.github.io/grails-doc/latest/guide/single.html#multipleDatasources

class DataService {
    static datasource = 'lookup'

    void someMethod(...) {
        //…
    }
}

or into command:

http://grails.github.io/grails-doc/latest/guide/upgrading.html

import grails.dev.commands.*
import javax.sql.*
import groovy.sql.*
import org.springframework.beans.factory.annotation.*

class RunQueryCommand implements ApplicationCommand {

    @Autowired
    DataSource dataSource

    boolean handle(ExecutionContext ctx) {
        def sql = new Sql(dataSource)
        println sql.executeQuery("select * from foo")
        return true
    }
}

Moreover look at this thread and quoted examples: https://github.com/grails/grails-core/issues/701