Connect Grails with Oracle 11g Example Configuration

I developed a tiny web application in Grails that used HSQLDB by default and was wondering if it is possible to change the database to my Oracle 11g database. That would be neat and I can build more on Grails and at the same time use some of the powerful features offered by Oracle. It is very easy and straightforward to set up Grails to work with Oracle. However, you may encounter some exceptions during the configuration process and in this post I will explain how to setup Grails to work with your Oracle 11g database. Before we proceed any further, let me state the version of Grails and Oracle that I used for this tutorial.

Grails - 1.3.7
Oracle - 11.2.0.1.0
Java - 1.6

Follow the step by step guide provided below to setup any sample application developed on Grails to work with a Oracle Database;

Step-1: Inside your project folder, navigate to grails-app\conf and then edit the file DataSource.groovy in your favourite editor.This is what I initially changed for "datasource" and "development" sections.

dataSource {
    pooled = true
    driverClassName = "oracle.jdbc.driver.OracleDriver"
    username = "Enter User Name Here"
    password = "Enter Password Here"
}
development {
        dataSource {
            dbCreate = "update" // one of 'create', 'create-drop','update'
            url = "jdbc:oracle:thin:@ServerName:Port:SID"
        }
    }

Make sure you change the Server Name, Port and SID to suit to your needs.

Step-2: I tried "grails run-app" after doing this piece and I was greeted with the following exception.

Caused by: org.springframework.beans.factory.BeanCreationException: Error creati
ng bean with name 'transactionManager': Cannot resolve reference to bean 'sessio
nFactory' while setting bean property 'sessionFactory'; nested exception is org.
springframework.beans.factory.BeanCreationException: Error creating bean with na
me 'sessionFactory': Cannot resolve reference to bean 'hibernateProperties' whil
e setting bean property 'hibernateProperties'; nested exception is org.springfra
mework.beans.factory.BeanCreationException: Error creating bean with name 'hiber
nateProperties': Cannot resolve reference to bean 'dialectDetector' while settin
g bean property 'properties' with key [hibernate.dialect]; nested exception is o
rg.springframework.beans.factory.BeanCreationException: Error creating bean with
 name 'dialectDetector': Invocation of init method failed; nested exception is o
rg.springframework.jdbc.support.MetaDataAccessException: Error while extracting
DatabaseMetaData; nested exception is org.apache.commons.dbcp.SQLNestedException
: Cannot load JDBC driver class 'oracle.jdbc.driver.OracleDriver'
        ... 23 more
Caused by: org.springframework.beans.factory.BeanCreationException: Error creati
ng bean with name 'sessionFactory': Cannot resolve reference to bean 'hibernateP
roperties' while setting bean property 'hibernateProperties'; nested exception i
s org.springframework.beans.factory.BeanCreationException: Error creating bean w
ith name 'hibernateProperties': Cannot resolve reference to bean 'dialectDetecto
r' while setting bean property 'properties' with key [hibernate.dialect]; nested
 exception is org.springframework.beans.factory.BeanCreationException: Error cre
ating bean with name 'dialectDetector': Invocation of init method failed; nested
 exception is org.springframework.jdbc.support.MetaDataAccessException: Error wh
ile extracting DatabaseMetaData; nested exception is org.apache.commons.dbcp.SQL
NestedException: Cannot load JDBC driver class 'oracle.jdbc.driver.OracleDriver

To fix this problem ,I went to the following Oracle website link to pick the JAR file that will be required by the application. The file that I downloaded for my version of Java is ojdbc6.jar. After copying this file to the lib folder of the application, I tried starting the application again, and this time I got a different exception

Invocation of init method failed; nested exception is o
rg.codehaus.groovy.grails.orm.hibernate.exceptions.CouldNotDetermineHibernateDia
lectException: Could not determine Hibernate dialect for database name [Oracle]!

Step-3: To fix the Hibernate dialect exception, I added the following line under dataSource in my DataSource.groovy file.

dialect = "org.hibernate.dialect.Oracle10gDialect"

And that is all you have to do to connect Grails and Oracle. When you start the Grails application following this correction, it will be able to connect to Oracle automatically and start performing the basic CRUD operations (depending on your code). If you still get an exception after completing this part, post it to us with your Grails, Java and Oracle DB version. We will have a look.