Grails - store sql that will be used by services

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.

-------------Problems Reply------------

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.

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.

Category:sql Views:0 Time:2009-08-29
Tags: sql grails

Related post

  • Store sql query output in a excel file 2009-09-15

    Store sql query output in a excel file --------------Solutions------------- In SQlTip article some one has suggested following approach.You can find original article here. Exporting data from SQL Server to Excel can be achieved in a variety of ways.

  • How can I store sql statements in an oracle table? 2010-02-16

    We need to store a select statement in a table select * from table where col = 'col' But the single quotes messes the insert statement up. Is it possible to do this somehow? --------------Solutions------------- From Oracle 10G on there is an alternat

  • How can I install SQL Server Express 2008 with Advanced Services? 2009-02-02

    Has anyone successfully installed SQL Server Express 2008 with Advanced Services? I've tried several times and half way through the installation I always get the error: The downloaded file appears to be corrupt. I found this thread on MSDN but it see

  • Web Service and Report Manager don't load (Sql Server Express 2008 with Reporting Services) 2009-02-09

    I've just installed Microsoft SQL Server Express 2008 (with Reporting Services). My installation is side by side with SQL Server 2005. So far creating reports and testing them in the development environment has worked fine, however I can't get the we

  • SQL CLR Stored Procedure and Web Service 2009-04-15

    I am current working on a task in which I am needing to call a method in a web service from a CLR stored procedure. A bit of background: Basically, I have a task that requires ALOT of crunching. If done strictly in SQL, it takes somewhere around 30-4

  • Error Accessing SQL Server from an ASMX Web Service 2009-10-21

    I am accessing my server through remote desktop connection and have configured a webservice in IIS. I am able to see the methods but when I click on the button to "Invoke" I get the following error: System.Data.SqlClient.SqlException: Login failed fo

  • How do you deploy SQL Server 2008 Express with Advanced Services AND SP1 2010-02-08

    I am trying to write an installation program. I have completely automated the SQL Server 2008 Express installation (silently), using the Command Line install process as described here (http://msdn.microsoft.com/en-us/library/bb264562(SQL.90).aspx). I

  • How can i install sql server in my windows xp service pack 3? 2013-05-08

    Original title: Hii,,, How can i install sql server in my windows xp service pack 3 with pentium(R) dual-core processor.. --------------Solutions------------- No "full" version of SQL server will install on any consumer edition of Windows. There are

  • Grails: Store XML values in database 2011-02-20

    Is there any way to store some properties of domain object in database instead of storing them as strings? i.e. class Document { String name Node value } instead of: class Document { String name String value } --------------Solutions------------- Her

  • How to store SQL query results for fast access? 2009-11-04

    SQL beginner here. I have a query which takes around 10 seconds to run, so we have a slow down in the application. Would it be possible in MySQL or more generally in SQL for the server to periodically (every 1 to 5 minutes) run the query and store it

  • Grails: Using SQL Server 2005 with Grails 2010-10-01

    We have a requirement where a Grails application is required to connect to an external SQL Server 2005 (i.e. client's database server via a linked server). Our grails application will have its own SQL Server database instance for user authentication

  • Store sql Base-64 vs. binary disk space 2011-06-27

    Which way is the most efficient way to save sql data compare base-64 or binary? I am using a SQL Server database. I believe with binary we have to store it as binary or text data but with base-64 we can store it as text. I think binary is base on 8 b

  • How to store sql server express in a different project from the UI? 2011-06-30

    I have two projects in my solution: MyProject.UI (ASP.NET MVC 3) MyProject.Domain (class library) I have added a sql server express database to my class library project as well as an entity framework edmx file. The model designer in entity framework

  • Is it bad practice to store SQL queries in resource file? 2011-07-19

    I have a web application that communicates with SQL server. Rather than hard-coding all of the query strings, I have opted to store them in a global resource file. Is that considered bad practice? On a side note, when I do this, Visual Studio yells a

  • Understand the process used by .Net to store SQL Server session state 2011-10-26

    Background We have a large asp.net application and uses a lot of sessions like datasets, datatables etc. We want to support web farms for this application, so we want to save the session state in sql server. I am successfully storing all the required

  • Store SQL in MySQL Database 2011-11-10

    My technical blog didn't get off to a good start as Wordpress wouldn't save my post that contained PHP, HTML and SQL code. I'll have to modify Wordpress sanitation methods or roll my own basic blogging platform but what is the correct and safe way to

  • ruby on rails store sql result on a variable 2012-01-13

    Newbie here. I am trying to store the result of my search onto a variable. @answer = q.answers.select(:name) which runs "SELECT name FROM "answers" WHERE "answers"."question_id" = 1;" and returns "t" for true. It runs fine on the command line and sho

  • Grails: store a class literal in the database 2012-03-06

    I want to store whether something should be a String or an Integer in the database. My fantasy would be something like this: Class FantasyClass{ String someInfo Class whichClass } And then I could call it like this: def thisThing = new FantasyClass(s

  • Best way to store Sql Scripts in a database table 2012-04-05

    I need to store stored procedure execution scripts in a database table. As an example: exec proc_name 'somedata' These are for execution at a later time after the data that will be changed has gone through a moderation process. What is the best way t

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.099 (s). 11 q(s)