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.


  1. I have read your blog its very attractive and impressive. I like it your blog.

    Java Training in Chennai Core Java Training in Chennai Core Java Training in Chennai

    Java Online Training Java Online Training Core Java 8 Training in Chennai Core java 8 online training JavaEE Training in Chennai Java EE Training in Chennai

  2. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in NETEZZA TRAINING, kindly contact us
    MaxMunus Offer World Class Virtual Instructor led training on TECHNOLOGY. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Sangita Mohanty
    Skype id: training_maxmunus
    Ph:(0) 9738075708 / 080 - 41103383