Grails Version 3.0 and above configure multiple data sources

Keywords: JDBC Oracle SQL MySQL

grails has been upgraded to version 3.0.9 due to work needs, and needs to configure multiple data sources.
grails 2.1's multi-source configuration is relatively simple, but more than 3.0 version of the database configuration changes to application.yml for configuration, how to configure multi-source, Baidu and bing search no answer (forgive me I am not too good at turning over the wall with Google).
This problem has been bothering me for two or three days and has finally been solved. Although the explanations in the official documents are more comprehensive, there are still some errors.
First of all, the basic configuration of Grails version 3.0 or above is as follows:

dataSource: pooled: true jmxExport: true driverClassName: oracle.jdbc.driver.OracleDriver username: test password: test properties: maxActive = -1 minEvictableIdleTimeMillis=1800000 timeBetweenEvictionRunsMillis=1800000 numTestsPerEvictionRun=3 testOnBorrow=true testWhileIdle=true testOnReturn=true validationQuery="SELECT 1 FROM DUAL" environments: development: dataSource: dbCreate: update url: jdbc:oracle:thin:@ip:port:sid production: dataSource: dbCreate: update url: jdbc:oracle:thin:@ip:port:sid

It should be noted that there are strict requirements for alignment of data in application.yml, and errors may occur if the data is not aligned properly; furthermore, if there is something behind the colon, it must be blank, such as:
dbCreate: update
The configuration of multiple data sources is as follows:

dataSources: dataSource: pooled: true jmxExport: true driverClassName: oracle.jdbc.driver.OracleDriver username: test password: test properties: maxActive = -1 minEvictableIdleTimeMillis=1800000 timeBetweenEvictionRunsMillis=1800000 numTestsPerEvictionRun=3 testOnBorrow=true testWhileIdle=true testOnReturn=true validationQuery="SELECT 1 FROM DUAL" gibmsSource: pooled: true jmxExport: true driverClassName: oracle.jdbc.driver.OracleDriver username: test1 password: test1 properties: maxActive = -1 minEvictableIdleTimeMillis=1800000 timeBetweenEvictionRunsMillis=1800000 numTestsPerEvictionRun=3 testOnBorrow=true testWhileIdle=true testOnReturn=true validationQuery="SELECT 1 FROM DUAL" environments: development: dataSources: dataSource: dbCreate: update url: jdbc:oracle:thin:@ip:port:sid gibmsSource: dbCreate: update url: jdbc:oracle:thin:@ip1:port1:sid1 production: dataSources: dataSource: dbCreate: update url: jdbc:oracle:thin:@ip:port:sid gibmsSource: dbCreate: update url: jdbc:oracle:thin:@ip1:port1:sid1

Multiple data sources can also be configured with domian, which is configured in mapping as follows:

class TestGibms { String code static mapping = { datasource 'gibmsSource' } static constraints = { code nullable: true, maxSize: 80 } }

class TestGibms { String code static mapping = { datasources(['gibmsSource','ds1']) } static constraints = { code nullable: true, maxSize: 80 } }

I haven't studied how to configure the domains of other data sources to use the. save() method yet. It's impossible to save the domains according to the configuration in the document, which needs to be studied here.
If Gsql is used in service, multiple data sources can be used. The rules for defining data sources are as follows: def dataSource_gibmsSource. It is not the static data source ='gibmsSource'in the official document, which defines the data source as a string variable directly; nor is it the static data Source ='gibmsSource', which uses the default data source as def data Source effect; nor is it def gibmsSource, which results in null. The service example is as follows:
class TestService {
    def dataSource_gibmsSource
    def insertTest(){
        Sql sql = new Sql(dataSource_gibmsSource)
        def gibmsSql = "insert into test_gibms(code) values ('000')"
        def result = sql.executeInsert(gibmsSql)
        sql.close()
    }
}
After I solved this problem, some colleagues told me that it would be possible to configure multiple data sources in controllers in the following way, which is not validated for the time being, but it would be more troublesome to configure them regularly and conveniently through application.yml. Examples are as follows:

Sql sql = Sql.newInstance("jdbc:mysql://localhost:1081/wyl","root","root","com.mysql.jdbc.Driver");

Impressions:
1. This question is rarely asked in the online Chinese search, and the answer is not available, so record it for others to use.
2. For technical problems, if Chinese search is not available, English search can be considered.
3. Too lazy to find other people's experience directly. In fact, they still turn back to grails'documents. In the future, they still need to check more documents when they encounter problems.
4. There are also some mistakes in the official documents. The debugging can't pass anyway. Only after consulting another English document can we find a solution.

Reference:
        http://stackoverflow.com/questions/31105556/multiple-datasources-in-grails-3-service
        http://fossies.org/linux/www/grails-docs-3.1.3.zip/guide/pages/multipleDatasources.html

Posted by kingbeastie on Sat, 25 May 2019 15:21:02 -0700