Datasources Plugin

This plugin doesn't work with Grails 2.0 or higher since multiple datasources support has been added to Grails core. See the docs in the reference guide for the new syntax.

The Datasources plugin allows you to define multiple databases/datasources and decide which datasource each of your Domain classes attaches to. Grails doesn't directly support this - there's only one DataSource and one SessionFactory, and all domain classes use them.

There are some implications of the approach I took. This doesn't provide XA transactions, 2PC, etc. It's just a partitioning of classes between two or more datasources. The way it works is to run after HibernateGrailsPlugin and DomainClassPlugin have done their work. Then it uses a configuration defined in grails-app/conf/Datasources.groovy and creates one or more extra DataSource, SessionFactory, TransactionManager, etc. and re-runs the HibernateGrailsPlugin's doWithDynamicMethods closure for the appropriate subset of domain classes. This way when you call a magic GORM method (e.g. list(), get(), findByNameAndDate(), etc.) it will use the correct underlying datasource. Any domain class not defined as using a secondary datasource will use the 'core' datasource defined in DataSource.groovy.

Another issue is that all domain classes stay defined in the core datasource/SessionFactory - the existing behavior isn't changed, other than redefining the metaclass methods to use another datasource. The only effect of this is that if you use dsCreate = 'create-drop' or 'create' or 'update' for the core datasource, all tables will be created in the core database even though some won't be used. There is a work around if you want to limit the tables that are manipulated.

Datasources DSL

The DSL used in Datasources.groovy is very similar to the format of DataSource.groovy. One difference is that the 'hibernate' section is inside the 'datasource' section, and there are a few extra attributes.

See the sample app (link below) for a usage example.

OpenSessionInView

An OpenSessionInViewInterceptor is defined for each datasource, so the features that it provides are available to all domain classes. For example you can load a domain instance and set a property, and it will be detected as dirty and pushed to the database. Also, lazy loaded collections will load since there's an active session available.

Further, if you save, create, load, etc. domain instances from multiple datasources in one controller method, all will work fine.

Transactional Services

By default, any service defined as transactional will use the core datasource. If you want a service to use a specific datasource, add its name to the 'services' attribute for a datasource definition. If there's no one datasource for a particular service, you can still define programmatic transactions using withTransaction on any domain class for the appropriate datasource for each method or code block.

HibernateTemplate

I can't think of non-contrived reasons to do so, but it's possible to use a domain class in two or more datasources. The problem here is that the metaclass methods will end up mapped to the last declared datasource, so there's no way to use GORM for the other datasource(s). However you can use Spring's HibernateTemplate yourself - it has a lot of the functionality of GORM (GORM uses it under the hood). You can use the convenience method DatasourcesUtils.newHibernateTemplate(String dsName) to create a HibernateTemplate configured with the SessionFactory for the named datasource.

Direct access to the datasources

Typically you'll just use domain class GORM methods to access the different databases, but if you want to run a SQL query or update, you can access the extra datasources two ways. One is with dependency injection. For example if your Datasources.groovy defines a secondary datasource named billing then you can inject that into a service, controller, etc. with the name dataSource_billing, so declaring def dataSource_billing as a class-scope field will inject that datasource, and you could use it with a groovy.sql.Sql instance, e.g. Sql sql = new Sql(dataSource_billing).

The other way is to use DatasourcesUtils.getDataSource(String dsName), e.g. Sql sql = new Sql(DatasourcesUtils.getDataSource('billing')).

Java domain classes mapped with hbm.xml

If you have legacy Java classes mapped with hbm.xml files you can use them by creating a hibernate.cfg.xml in grails-app/conf/hibernate/ for each data source. Note that you can mix hbm.xml-mapped classes with regular GORM classes in a data source. To tell the plugin which data source to use it in, prefix the name of the file with the data source name, e.g. grails-app/conf/hibernate/ds2_hibernate.cfg.xml for a data source named "ds2".

Refer to the Hibernate documentation on the format for hbm.xml and hibernate.cfg.xml files, but as an example if you have the POJO com.foo.bar.SomeHbmMappedPojo.java and its corresponding com.foo.bar.SomeHbmMappedPojo.xml in "ds2", then your ds2_hibernate.cfg.xml would look like this:

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
   '-//Hibernate/Hibernate Configuration DTD 3.0//EN'
   'http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd'>
<hibernate-configuration>
   <session-factory>
      <mapping resource='com/foo/bar/SomeHbmMappedPojo.hbm.xml'/>
   </session-factory>
</hibernate-configuration>

Usage

To install the plugin in your application just run the 'install-plugin' Grails script, e.g.

grails install-plugin datasources

You might want to use this plugin even if you have only one database. Since you can define a datasource as being read-only and point read-only domain classes at it, your prevent yourself from accidentally creating, deleting, or updating instances.

I've created a basic (and rather contrived) test application. It has three datasources and five domain classes:

• Country, State
• Library, Book
• Visit

Download the test app here (this is from the original blog post describing v0.1 of the plugin)

To test the app:

• Create the databases using the scripts (ddl_core.sql, ddl2.sql, and ddl3.sql)
• run 'grails run-app'
• Create a new Library at http://localhost:8080/ds_test/library/create
• Create a new Book at http://localhost:8080/ds_test/book/create
• check the database to ensure they ended up in the correct database
• Create a new Visit at http://localhost:8080/ds_test/visit/create
• check the database to ensure it ended up in the correct database
• edit a State at http://localhost:8080/ds_test/state/edit/1, save, and note that change didn't take effect due to read-only datasource
• create a State at http://localhost:8080/ds_test/state/create, save you should see the error page due to read-only datasource
• test transaction failure at http://localhost:8080/ds_test/transactionTest/fail - you should see the error page and if you check the database nothing should have been inserted
• test transaction success at http://localhost:8080/ds_test/transactionTest/succeed - you should see a simple success message and if you check the database the inserts should have succeeded

Author

Burt Beckwith burt@burtbeckwith.com

Please report any issues to the Grails User mailing list and/or write up an issue in JIRA at http://jira.codehaus.org/browse/GRAILSPLUGINS under the Grails-Datasources component.

History

• December 28, 2009
• released version 0.4
• May 1, 2009
• released version 0.3
• December 28, 2008
• released version 0.2
• September 4, 2008
• released initial version 0.1

A: The datasources that are created by the plugin are regular Spring beans, so they're overrideable in the regular way in resources.groovy:

import com.mchange.v2.c3p0.ComboPooledDataSource
beans = {
   dataSource_ds2(ComboPooledDataSource) { bean ->
      bean.destroyMethod = 'close'
      user = …
      password = …
      driverClass = …
      jdbcUrl = …
      maxPoolSize = …
      maxStatements = …
      maxConnectionAge = …
   }
   dataSource_ds3(ComboPooledDataSource) { bean ->
      bean.destroyMethod = 'close'
      user = …
      password = …
      driverClass = …
      jdbcUrl = …
      maxPoolSize = …
      maxStatements = …
      maxConnectionAge = …
   }
}