grails 多数据源相关配置 博客分类： Grails&Groovy
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 对于多数据源的增删改查操作