Wednesday, March 26, 2008

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

7 comments:

Okke said...

Hi Doug,

actually it is even easier than that. You can add the datasource in
the conf/spring/resources.groovy file like this:

dataSourceJanus(BasicDataSource) {
driverClassName = "oracle.jdbc.driver.OracleDriver"
url = "jdbc:oracle:thin:@somehost:1521:DBNAME"
username = "someuser"
password = "somepassword"
}

In the controller you only have to create a variable like this:

def dataSourceJanus

You don't need to initialize it, Grails auto-wires it to the bean if the
bean has the same name. After that you can use the Groovy SQL DSL with the
datasource:

import groovy.sql.Sql

def sql = Sql.newInstance(dataSourceJanus)

That's it :)

I have an example in my blog: http://tijhuis.wordpress.com/2008/03/21/really-enjoying-grails/

Okke said...

Btw, in your case the resources.groovy probably needs to contain:

import org.springframework.jdbc.datasource.SingleConnectionDataSource

beans = {
dataSourceJanus(SingleConnectionDataSource) {
driverClassName = "oracle.jdbc.driver.OracleDriver"
url = "jdbc:oracle:thin:@somehost:1521:DBNAME"
username = "someuser"
password = "somepassword"
}
}

You might want to try without the import. Could be that you don't even
need it.

Got to love Grails, most of the time you won't even need XML at all.

Regards,

Okke

fils said...

Okke,
Thanks! Very cool comments. I had seen evidence that the groovy approach you mention here was a possibility. I'll definitely give this a try.

So in my case and yours is database pooling automatic?

I agree.. gotta love Grails.
Doug

Okke said...

Doug,

I think it uses the same defaults as the datasource.groovy file, so then it should use pooling by default. Otherwise you could try adding pooled=true. I didn't need pooling myself in this case so I haven't tried that yet.

Regards,

Okke

Sadhna said...

Hi Doug,
Very useful article.
I want to use multiple datasource and also want to use domain GORM feature on both DB domains . Is it possible to use ?
I m able to use multiple DB but Gorm is not working on second DB.
how to map two DB and use domains saparately??

Waiting for the response

Thanks

Andrew Son said...

There are lots of information about latest technology and how to get trained in them, like Oracle Training in Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies(Oracle Course in Chennai). By the way you are running a great blog. Thanks for sharing this.

jhansi joe said...

Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing.
Regards,
cognos Training in Chennai