CODEWITHSUNDEEP
sqlgrailsdatasource
Peter
Peter

Reputation: 29837

How to specify alternate datasource when doing raw SQL queries in Grails 2.3.x?

We have a reporting read only database clone set up as an alternate datasource in our Grails application named 'reporting'. This works great when using dynamic finders or criteria as per the grails MyDomain.reporting.findByXXXX(..etc..)

However there are some nasty queries that have to be done in raw SQL. Our current way of doing this (in a service) is 

def sessionFactory; 

public static List getSomeBigNastyData(...)
{
   sessionFactory.currentSession.createSQLQuery(
   """
   Big Ugly Query
   """ 
   ).list();
}

But this does not go to the reporting database and there doesn't seem to be a way of specifying 'reporting' - is there a way to specify the datasource to execute raw SQL against?

Upvotes: 0

Views: 130

Answers (1)

Joshua Moore
Joshua Moore

Reputation: 24776

It's possible to use the dataSource as an injected bean and groovy.sql.Sql to run your queries. Below is a simple example of a service that will use your data source and allow you to run a query against it.

package com.example

import groovy.sql.GroovyRowResult
import groovy.sql.Sql

class ExampleSqlService {
  def dataSource_reporting // your named data source

  List<GroovyRowResult> query(String sql) {
    def db = new Sql(dataSource_reporting)
    return db.rows(sql)
  }
}

Using a service (like the above example) allows you to access it from basically anywhere (Controller, Service, TagLib, Domain, etc.)

Upvotes: 1

Related Questions