Grails - store sql that will be used by services

Question

I am writing a Grails application that will mostly be using the springws web services plugin with endpoints backed by services. The services will retrieve data from a variety of back end databases (i.e., not via domain classes and GORM). I would like to store the sql that my services will be using to fetch the data for the web services in external files. I'm looking for suggestions on:

1. Where is the best place to keep the files (i.e., I'd like to put them somewhere obvious like grails-app/sql) and best format (i.e., xml, configslurper, etc.)

2. Best way to abstract the retrieving of the sql text so my services that will execute the sql will not need to know where or how they are fetched. Services will just provide a sqlid and get the sql.

Answer 1

Have you considered using Grails GORM and a HSQLDB database to store the SQL you want executed? You could then put in a record for each service containing that services SQL and retrieve it using normal Grails GORM functions. You could generate a default set of controllers and views that would allow you to edit the SQL. If you want to store the SQL in external files you can create a sub directory in the web-app directory called sql, then store your SQL statements as text files. You could create a class that would take a service name, load the associated text file containing the SQL and return the contents of that file. With out knowing how complex your SQL will be I cant' say what the best format would be. If your dealing with normal select statements with no parameter substitution plain text would be best. If your dealing with more complex SQL with substitutions and multiple queries you may want to use XML.

Answer 2

I was working on a project recently where I needed to do something similar. I created the following directory to store the sql files:

./grails-app/conf/sql

For example there is a file ./grails-app/conf/sql/hr/FIND_PERSON_BY_ID.sql that has something like the following:

select a.id
, a.first_name
, a.last_name 
from person 
where id = ?

I created a SqlCatalogService class that would load all files in that directory (and subdirectories) and store the filenames (minus extension) and file text in a Map. The service has a get(id) method that returns the sql text that is cached in the Map. Since files/directories stored in grails-app/conf are placed in the classpath, the SqlCatalogService uses the following code to read in the files:

....
....
Map<String,String> sqlCache = [:]
....
....
void loadSqlCache() {
    try {
     loadSqlCacheFromDirectory(new File(this.class.getResource("/sql/").getFile()))
    } catch (Exception ex) {
        log.error(ex)
    }       
}

void loadSqlCacheFromDirectory(File directory) {
    log.info "Loading SQL cache from disk using base directory ${directory.name}"
    synchronized(sqlCache) {
     if(sqlCache.size() == 0) {
      try {  
                directory.eachFileRecurse { sqlFile ->
                 if(sqlFile.isFile() && sqlFile.name.toUpperCase().endsWith(".SQL")) {
                  def sqlKey = sqlFile.name.toUpperCase()[0..-5]
                     sqlCache[sqlKey] = sqlFile.text
                     log.debug "added SQL [${sqlKey}] to cache"
                 }
                }                     
      } catch (Exception ex) {
       log.error(ex)
      }     
     } else {
      log.warn "request to load sql cache and cache not empty: size [${sqlCache.size()}]"
     }
    }
}

String get(String sqlId) {
    def sqlKey = sqlId?.toUpperCase()
    log.debug "SQL Id requested: ${sqlKey}"
 if(!sqlCache[sqlKey]) {
  log.debug "SQL [${sqlKey}] not found in cache, loading cache from disk"
     loadSqlCache()
 }
    return sqlCache[sqlKey]
}

Services that use various datasources use the SqlCatalogService to retrieve the sql by calling the get(id) method:

class PersonService {

    def hrDataSource
    def sqlCatalogService

    private static final String SQL_FIND_PERSON_BY_ID = "FIND_PERSON_BY_ID"

    Person findPersonById(String personId) {
     try {
      def sql = new groovy.sql.Sql(hrDataSource)
      def row = sql.firstRow(sqlCatalogService.get(SQL_FIND_PERSON_BY_ID), [personId])
      row ? new Person(row) : null
     } catch (Exception ex) {
      log.error ex.message, ex
      throw ex
     }
    }
}

For now we only have a few sql statements so storing all the text in a Map is not an issue. If you lots of sql files to store you may need to think about using something like Ehcache and defining an eviction strategy (i.e., least recently used or least frequently used) and only storing the most used in memory and leaving the rest on disk until needed.

Before doing this I thought about using GORM and storing the sql text in the database. But decided that having the sql in files made it easier to develop with since we could pretty much save the sql to file directly from our sql tool (replacing hard-code params with question marks) and are able to let our revision control system track the changes. I'm not saying the above service is the most efficient or correct way to handle this, but it's worked so far for our needs.