3.3.6 Multiple Datasources - Reference Documentation
Authors: Graeme Rocher, Peter Ledbrook, Marc Palmer, Jeff Brown, Luke Daley, Burt Beckwith
Version: null
3.3.6 Multiple Datasources
By default all domain classes share a singleDataSource and a single database, but you have the option to partition your domain classes into two or more DataSources.Configuring Additional DataSources
The defaultDataSource configuration in grails-app/conf/DataSource.groovy looks something like this:dataSource {
pooled = true
driverClassName = "org.h2.Driver"
username = "sa"
password = ""
}
hibernate {
cache.use_second_level_cache = true
cache.use_query_cache = true
cache.provider_class = 'net.sf.ehcache.hibernate.EhCacheProvider'
}environments {
development {
dataSource {
dbCreate = "create-drop"
url = "jdbc:h2:mem:devDb"
}
}
test {
dataSource {
dbCreate = "update"
url = "jdbc:h2:mem:testDb"
}
}
production {
dataSource {
dbCreate = "update"
url = "jdbc:h2:prodDb"
}
}
}DataSource with the Spring bean named dataSource. To configure extra DataSources, add another dataSource block (at the top level, in an environment block, or both, just like the standard DataSource definition) with a custom name, separated by an underscore. For example, this configuration adds a second DataSource, using MySQL in the development environment and Oracle in production:environments {
development {
dataSource {
dbCreate = "create-drop"
url = "jdbc:h2:mem:devDb"
}
dataSource_lookup {
dialect = org.hibernate.dialect.MySQLInnoDBDialect
driverClassName = 'com.mysql.jdbc.Driver'
username = 'lookup'
password = 'secret'
url = 'jdbc:mysql://localhost/lookup'
dbCreate = 'update'
}
}
test {
dataSource {
dbCreate = "update"
url = "jdbc:h2:mem:testDb"
}
}
production {
dataSource {
dbCreate = "update"
url = "jdbc:h2:prodDb"
}
dataSource_lookup {
dialect = org.hibernate.dialect.Oracle10gDialect
driverClassName = 'oracle.jdbc.driver.OracleDriver'
username = 'lookup'
password = 'secret'
url = 'jdbc:oracle:thin:@localhost:1521:lookup'
dbCreate = 'update'
}
}
}Configuring Domain Classes
If a domain class has noDataSource configuration, it defaults to the standard 'dataSource'. Set the datasource property in the mapping block to configure a non-default DataSource. For example, if you want to use the ZipCode domain to use the 'lookup' DataSource, configure it like this;class ZipCode { String code static mapping = {
datasource 'lookup'
}
}DataSources. Use the datasources property with a list of names to configure more than one, for example:class ZipCode { String code static mapping = {
datasources(['lookup', 'auditing'])
}
}DataSource and one or more others, use the special name 'DEFAULT' to indicate the default DataSource:class ZipCode { String code static mapping = {
datasources(['lookup', 'DEFAULT'])
}
}DataSources use the special value 'ALL':class ZipCode { String code static mapping = {
datasource 'ALL'
}
}Namespaces and GORM Methods
If a domain class uses more than oneDataSource then you can use the namespace implied by each DataSource name to make GORM calls for a particular DataSource. For example, consider this class which uses two DataSources:class ZipCode { String code static mapping = {
datasources(['lookup', 'auditing'])
}
}DataSource specified is the default when not using an explicit namespace, so in this case we default to 'lookup'. But you can call GORM methods on the 'auditing' DataSource with the DataSource name, for example:def zipCode = ZipCode.auditing.get(42) … zipCode.auditing.save()
DataSource to the method call in both the static case and the instance case.Services
Like Domain classes, by default Services use the defaultDataSource and PlatformTransactionManager. To configure a Service to use a different DataSource, use the static datasource property, for example:class DataService { static datasource = 'lookup' void someMethod(...) {
…
}
}DataSource, so be sure to only make changes for domain classes whose DataSource is the same as the Service.Note that the datasource specified in a service has no bearing on which datasources are used for domain classes; that's determined by their declared datasources in the domain classes themselves. It's used to declare which transaction manager to use.What you'll see is that if you have a Foo domain class in dataSource1 and a Bar domain class in dataSource2, and WahooService uses dataSource1, a service method that saves a new Foo and a new Bar will only be transactional for Foo since they share the datasource. The transaction won't affect the Bar instance. If you want both to be transactional you'd need to use two services and XA datasources for two-phase commit, e.g. with the Atomikos plugin.XA and Two-phase Commit
Grails has no native support for XADataSources or two-phase commit, but the Atomikos plugin makes it easy. See the plugin documentation for the simple changes needed in your DataSource definitions to reconfigure them as XA DataSources.

