Grails is the sql store that will be used by services
I am writing a Grails application that will basically use the Springws web services plugin with service backed endpoints. Services will fetch data from multiple databases at the end (i.e. not via domain classes and GORM). I would like to store the sql that my services will use to get data for web services in external files. I am looking for suggestions on:
-
Where is the best place to store the files (i.e. I would like to put them somewhere in grails-app / sql form) and the best format (i.e. xml, configslurper, etc.).
-
Best way to abstract the sql text extraction so that my services that will execute sql don't have to know where and how they are being retrieved. Services will just provide sqlid and get sql.
source to share
I was recently working on a project where I needed to do something similar. I created the following directory to store sql files:
./Grails-application/CONF/SQL
For example, there is a file. / grails -app / conf / sql / hr / FIND_PERSON_BY_ID.sql, which has something like this:
select a.id
, a.first_name
, a.last_name
from person
where id = ?
I created a SqlCatalogService class that would load all the files in that directory (and subdirectories) and store the filenames (minus the extension) and the text of the file in the map. The service has a get (id) method that returns sql text that is cached on the Map. Since the files / directories stored in grails-app / conf are placed on the classpath, the SqlCatalogService uses the following code to read 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 using different data sources use SqlCatalogService to retrieve 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
}
}
}
We only have a few sql statements so far, so keeping all the text in the map is not a problem. If you need to store a lot of sql files, you might need to use something like Ehcache and define an eviction strategy (i.e. least used or least used) and only keep the most used in memory and leave the rest on disk until then. until needed.
Before doing this, I thought about using GORM and storing sql text in the database. But decided that with sql files it made development easier, as we could save the sql file directly from our sql tool (replacing the hardcode parameters with question marks) and we can let our version control system track the changes. I am not saying that the above service is the most efficient or correct way to handle this, but it has worked for our needs so far.
source to share
Have you considered using Grails GORM and an HSQLDB database to store the SQL you want to execute? You can then write an entry for each service that contains those SQL services and retrieve it using regular Grails GORM functions. You can create a set of default controllers and views that will allow you to edit SQL. If you want to store SQL in external files, you can create a subdirectory in your web application directory called sql and then save your SQL queries as text files. You can create a class that will take the name of the service, load the associated text file containing the SQL, and return the contents of that file. Without knowing how complex your SQL will be, I cannot tell which is the best format. If you were dealing with normal selection statements without the usual variable substitution text.If you're dealing with more complex SQL with lookups and multiple queries, you can use XML.
source to share