Grails datasource in resource.xml

So I have the need for a couple extra datasources in my grails application. This turned out to take a bit more research than I first thought it would, though likely just due to my lack of much heavy duty spring experience so far. Basically I just want access to another database resource that is NOT my main datasource used by the hibernate ORM. My domain classes for this application are separate from my need to access this other datasource which I would do just via SQL calls.

A quick review of the Nabble list gave me a couple of leads:
http://www.nabble.com/dataSource-on-ApplicationBootStrap-td11441836.html#a11441836
http://www.nabble.com/multiply-datasources--td10038844.html#a10038844

I created the following entry in resource.xml located in GRAILS_APP/web-app/WEB-INF/spring directory. Create this path and file if it doesn't already exist. The full XML document is like:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">

  <bean id="dataSourceJanus" class="org.springframework.jdbc.datasource.SingleConnectionDataSource">
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
        <property name="url" value="'jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521:janware"/>
        <property name="username" value="NAME"/>
        <property name="password" value="PASSWORD"/>
  </bean>
</beans>

though you may only need the bean entry if the file already exists of course. Thanks Alex (again) for finding my insanely stupid typo in this file. I stared for hours (sad to say) at it.

The only tricky part is that you can not directly reference the new datasource ( here called dataSourceJanus in my example) in say your controller class. Rather you will need to implement ApplicationContextAware. So you will have something like:

//  Needed for datasource 
import org.springframework.jdbc.core.JdbcTemplate
import groovy.sql.Sql


// Spring
import org.springframework.beans.BeansException
import org.springframework.context.ApplicationContext
import org.springframework.context.ApplicationContextAware
import org.codehaus.groovy.grails.commons.GrailsApplication

class FacetController implements ApplicationContextAware {
    
    GrailsApplication grailsApplication
    ApplicationContext appCtx

    def void setApplicationContext(ApplicationContext arg0) throws BeansException {
        appCtx = arg0;
    }

...

This will get you your application context object. Now it's rahter straight forward to use:

def showFacets = {
        def jt = new JdbcTemplate(appCtx.dataSourceJanus)
        def sqlString = "select latitude_degrees, longitude_degrees from hole where leg like '101' and site like '631' and hole like 'A'"
        response.contentType = "text/plain"
        jt.queryForList(sqlString).each {        
            response.outputStream << "${it.latitude_degrees} ${it.longitude_degrees}"
        }
}

That is just a dead simple method to call the datasource (bold) and writes directly to the output stream. Not the org.springframework.jdbc.core.JdbcTemplate import in the previous code sample along with the other imports used for the application context. Obviously you will likely return to a view or render a JSON serialization or some such in your method rather than write directly to the outputStream.

If you place def jt = new JdbcTemplate(appCtx.dataSourceJanus) outside the scope of the method you get a null reference at start up. So it seems this should be scoped inside the method that uses it. However, this logic might be better moved into a service class anyway depending on how much you will use it.

Some additional good reading on this includes:
Datasources: http://docs.codehaus.org/display/GRAILS/DataSources+New
Using JNDI resources instead: http://docs.codehaus.org/display/GRAILS/JNDI+Data+Sources
Spring Bean Builder: http://grails.org/Spring+Bean+Builder
Application Context: http://grails.org/Services

If you review and follow some of the thread in the Nabble site, you will see that seems one could in fact use dynamic methods with this new datasource if you do a few extra steps. I have not done this but it appears to involve making some entries in the hibernate.cfg.xml file. Reference http://www.nabble.com/How-to-get-multi-dataSource-in-grails--td12291409.html#a12291409 for some comments I found on trying to do this. There does seem to be some issues I am hearing about having/using dynamic methods on multiple datasource though, so I am not sure how this all plays out.

For my needs however, just having an SQL resource that I could use the very nice Groovy SQL capacity with.

enjoy
Doug