grails 多数据源相关配置

Grails 多数据源相关配置

 

1.Mysql、SQLServer、PG、Oracle 单数据源配置

---

hibernate:

    cache:

        queries: true

        use_second_level_cache: true

        use_query_cache: true

        region.factory_class: org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory

    # jdbc:

        # use_get_generated_keys: true

dataSource:

    pooled: true

    jmxExport: true

    driverClassName: com.mysql.jdbc.Driver

    # driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver

    # driverClassName: org.postgresql.Driver

    # driverClassName: oracle.jdbc.OracleDriver

    username: username

    password: password

 

environments:

    development:

        dataSource:

            dbCreate: update

            url: jdbc:mysql://localhost:3306/databaseName?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=false

            # url: jdbc:sqlserver://10.0.8.241:1433;DatabaseName=databaseName

            # url: jdbc:postgresql://127.0.0.1:5432/databaseName

            # url: jdbc:oracle:thin:@10.201.1.41:1521:serverName

    test:

        dataSource:

            dbCreate: create-drop

            url: jdbc:mysql://localhost:3306/loan-160?useUnicode=true&characterEncoding=UTF-8&useSSL=false

    production:

        dataSource:

            dbCreate: update

            # product

            url: jdbc:mysql://localhost:3306/databaseName?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=false

            # url: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=databaseName

            # url: jdbc:postgresql://127.0.0.1:5432/databaseName

            # url: jdbc:oracle:thin:@127.0.0.1:1521:serverName

            properties:

                jmxEnabled: true

                initialSize: 50

                maxActive: 500

                minIdle: 20

                maxIdle: 300

                maxWait: 120000

                maxAge:  600000

                timeBetweenEvictionRunsMillis: 60000

                minEvictableIdleTimeMillis: 300000

                validationQuery: SELECT 1

                # validationQuery: SELECT 1 from DUAL

                validationQueryTimeout: 60

                validationInterval: 30000

                testOnBorrow: true

                testWhileIdle: true

                testOnReturn: false

                jdbcInterceptors: ConnectionState

                defaultTransactionIsolation: 2 # TRANSACTION_READ_COMMITTED

 

注意：

1.mysql驱动：runtime 'mysql:mysql-connector-java:5.1.41'

2.SQLserver驱动：在libs下添加sqljdbc42.jar

3.Postgres驱动：在libs下添加postgresql-42.1.4.jar

4.Oracle驱动：runtime "com.oracle:ojdbc6:11.2.0.3"，另外额外需要配置参数

  （1）jdbc:

                use_get_generated_keys: true

  （2）validationQuery: SELECT 1 from DUAL

 

 

2.多数据源配置

 

---

hibernate:

    cache:

        queries: true

        use_second_level_cache: true

        use_query_cache: true

        region.factory_class: org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory

    jdbc:

        use_get_generated_keys: true

dataSources:

    dataSource:

        pooled: true

        jmxExport: true

        driverClassName: oracle.jdbc.OracleDriver

        username: username

        password: password

    extraSource:

        dialect: org.hibernate.dialect.PostgreSQLDialect

        driverClassName: org.postgresql.Driver

        username: username

        password: password

 

environments:

    development:

      dataSources:

        dataSource:

            dbCreate: update

            url: jdbc:oracle:thin:@127.0.0.1:1521:serverName

        extraSource:

            dbCreate: update

            url: jdbc:postgresql://127.0.0.1:5432/databaseName

    test:

        dataSources:

        dataSource:

            dbCreate: update

            url: jdbc:oracle:thin:@127.0.0.1:1521:serverName

    production:

        dataSources:

        extraSource:

            dbCreate: update

            url: jdbc:postgresql://127.0.0.1:5432/databaseName

            properties:

                jmxEnabled: true

                initialSize: 50

                maxActive: 500

                minIdle: 20

                maxIdle: 300

                maxWait: 120000

                maxAge:  600000

                timeBetweenEvictionRunsMillis: 60000

                minEvictableIdleTimeMillis: 300000

                validationQuery: SELECT 1 from dual

                validationQueryTimeout: 60

                validationInterval: 30000

                testOnBorrow: true

                testWhileIdle: true

                testOnReturn: false

                jdbcInterceptors: ConnectionState

                defaultTransactionIsolation: 2 # TRANSACTION_READ_COMMITTED

 

注意：配置两个数据源Oracle和Postgres，默认数据源为：dataSource

 

创建数据库表的时候，如果未指定，会自动创建到默认数据源dataSource中，如果想创建到extraSource指定的数据库中需要在domain的mapping中指定数据源

 

static mapping = {

    datasource 'extraSource'

}

 

static mapping = {

    datasources(['dataSource','extraSource'])

}

 

static mapping = {

  datasource 'ALL'

}

 

还有要注意的地方是：dialect: org.hibernate.dialect.PostgreSQLDialect，一定要声明第二个数据源，各数据库配置可参考官方文档：http://docs.grails.org/latest/guide/conf.html#multipleDatasources

 

在controller和service中获取数据源方式：def dataSource 和 def dataSource_extraSource 

 

配置多数据源时候，必须有一个数据源名字为dataSource，Grails会认为它是默认数据源

 

 

至于在controller和service中对于数据源的操作，请参考：Grails 对于多数据源的增删改查操作