Wednesday, December 30, 2009

Grails and Query Into Database View and Multiple Datasources

I want to bypass the Grails stack to query a database directly. It sounds silly. Anyway the very first reason why I chose Grails is to avoid that hassle. However I have a special case that it demands read-only queries into:
  1. multiple databases
  2. a database view
  3. Netezza, a database with no hibernate dialect
In my opinion, the difficulties of 2) and 3) keep any Object Relational Mapping (ORM) solutions out of the game. Otherwise, the Grails plugin, datasources, will be a good candidate to resolve issue 1).

The following solution is a Groovy-style DAO query:
  1. define the second datasource in grails-app/conf/spring/resources.groovy:

    import org.apache.commons.dbcp.BasicDataSource
    beans = {
    dataSourceNetezza(BasicDataSource) {
    driverClassName = "org.netezza.Driver"
    url = "jdbc:netezza://server:5480/database_name"
    username = "xyz"
    password = "abc"
    }
    }


  2. Use the dataSourceNetezza defined above in a controller where the Spring application context is available. Note that the dataSourceNetezza is auto-wired by Spring and the names have to match.

import groovy.sql.Sql

class TablesController {

def dataSourceNetezza

def list = {
if (servletContext.dbList == null)
servletContext.dbList = new Sql(dataSourceNetezza).rows("SELECT distinct DATABASE_NAME FROM REPOSITORY")
print servletContext.dbList
}
...
}

This approach looks extremely simple with less than 15 lines of code. Keep in mind that, there is no ORM.

No comments:

Post a Comment