Idiot's guide to Tomcat 6 & Grails with JNDI DataSource

Ok, I found information on how to do this a little bit scattered and some gotchas, so this blog post is just a quick idiot's guide on how to setup Grails with a JNDI DataSource on Tomcat 6 with Mysql.

My setup is Ubuntu Jaunty server with Tomcat 6, Sun Java, Mysql 5 & Grails 1.1.1

1. Install Tomcat 6 on Ubuntu Server

apt-get install tomcat6

2. Install Mysql, I use the excellent http://ourdelta.org release

wget -q http://ourdelta.org/deb/ourdelta.gpg -O- | apt-key add -
wget http://ourdelta.org/deb/sources/jaunty-ourdelta.list -O /etc/apt/sources.list.d/ourdelta.list
apt-get update
apt-get install mysql-server libmysql-java

3. Install mysql connector into Tomcat

ln -s /usr/share/java/mysql.jar /usr/share/tomcat6/lib/mysql.jar

4. Create a file /var/lib/tomcat6/conf/Catalina/localhost/ROOT.xml and add the following (don't put anything in main server.xml like I had in previous versions of this blog). If you are not deploying to root, then change the name of file and docBase.

<Context docBase="ROOT" path="" reloadable="true">
    <Resource
        name="jdbc/mydatasource"
        auth="Container"
        type="javax.sql.DataSource"
        username="myusername"
        password="mypassword"
        driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost/mydatabase"
    />
</Context>

If you have a Context already - just add the above section to it. You can also put this in the context with your war - but I personally like having it in the context outside so that you easier modify.

5. Now over in your grails application, add something like this for your environment to your grails-app/conf/DataSource.groovy:

production {
    dataSource {
        pooled = false
        dbCreate = "update"
        jndiName = "jdbc/mydatasource"
    }
}

6. You'll also need to add the following to your scripts/_Event.groovy to ensure that the web.xml is crafted correctly for the environment you want to use JNDI on:

import groovy.xml.StreamingMarkupBuilder

if (Environment.current == Environment.PRODUCTION) {
    eventWebXmlEnd = {String tmpfile ->
        def root = new XmlSlurper().parse(webXmlFile)

        // add the data source
        root.appendNode {
            'resource-ref'{
                'description'('The JNDI Database resource')
                'res-ref-name'('jdbc/mydatasource')
                'res-type'('javax.sql.DataSource')
                'res-auth'('Application')
            }
        }
    
        webXmlFile.text = new StreamingMarkupBuilder().bind {
            mkp.declareNamespace("": "http://java.sun.com/xml/ns/j2ee")
            mkp.yield(root)
        }
    }
}

7. War up your grails application (this one is for production) using:

grails prod war

and deploy to your tomcat by simply copying your war file to /var/lib/tomcat6/webapps.

Your application will be available at http://yourservername.com:8080/applicationname-version. If you want to put it on the root of your server just rename it to ROOT.war

References:

http://docs.codehaus.org/display/GRAILS/JNDI+Data+Sources
http://blogs.bytecode.com.au/glen/2008/07/24/grails--jetty--glassfish-an...

Notes: Many of references recommend using autoReconnect=true on the database url. I read that this is depricated now so have not included it in my example. Here are some alternatives:

http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configurati...
http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-j2ee.html