Calling PostgreSQL Stored Procedures from Grails - Optimized Service class

Here is a more-fully optimized Grails Service that creates and re-uses the thread-safe SqlFunction, StoredProcedure and JdbcTemplate classes. Note - I'm using a @PostConstruct annotation on an init() method. Yes, you can do that in Grails - it's just a Spring application. This makes it possible to pick up the automatically injected dataSource and wire it into the various objects that require it.

Enjoy.

package org.playball.service

import org.springframework.jdbc.object.SqlFunction
import org.springframework.jdbc.core.JdbcTemplate
import org.playball.db.StoredProcSubclass
import javax.annotation.PostConstruct
import java.sql.Types


class StoredProcedureRunnerService {

  boolean transactional = true
    
  def dataSource
  def template
  def spcaller
  def sqlFunction


  @PostConstruct 
  def init() {
      int [] params = new int[1];
      params[0]=java.sql.Types.INTEGER;

      template = new JdbcTemplate(dataSource)
      spcaller = new StoredProcSubclass(dataSource)
      sqlFunction = new SqlFunction(dataSource,
                      "select salesByBookId(?)", params)
      sqlFunction.compile()

  }

  // automatically set up by name (Spring injected) on startup
  def sumBookSalesByBookIdWithJdbcTemplate(def bookId) {
      template.queryForInt("select salesByBookId(?)", bookId)
  }

  // use Spring JDBC's StoredProcedure class (see bottom of this class for definition)
  def sumBookSalesByBookIdWithSPSubclass(int bookId) {
      def map = spcaller.execute(bookId, bookId)
      map['#result-set-1']['result'][0]
  }

  def sumBookSalesByBookIdSqlFunction(int bookId) {

      def result
      try {
        result = sqlFunction.run(bookId)
      } catch (org.springframework.dao.TypeMismatchDataAccessException e) {
        log.error "Result type cannot be inferred - no result so returning 0"
        0
      }
      result
  }

  def sumBookSalesByBookIdGSQL(int bookId) {
      def gsql = new groovy.sql.Sql(dataSource)
      def results = gsql.rows("select salesByBookId(${bookId})")
      results["salesbybookid"][0]
  }
}

Calling PostgreSQL Stored Procedures from Grails - Part 4 - Groovy SQL

Every time I say "Groovy SQL" I feel there should be an implied comma. Like, "Groovy, SQL!"

Groovy SQL is a simple API for calling SQL Statements in the vein of JdbcTemplate, but it's totally, um... Groovy. Here is the very tiny snippet of code to call our Stored Procedure with Groovy SQL:

  def sumBookSalesByBookIdGSQL(int bookId) {
    def gsql = new groovy.sql.Sql(dataSource)
    def results = gsql.rows("select salesByBookId(${bookId})")
    results["salesbybookid"][0]
  }

There. Isn't that, just, Groovy, SQL? The results returned seem to be a map of results, with each one having a list of values. I think for simple Stored Functions such as the one in Part 1, I say it's Groovy SQL FTW...

Calling PostgreSQL Stored Procedures from Grails - Part 3, SqlFunction

SqlFunction is another class that takes a stored function (which is our sample procedure in Part 1), and runs it. I've done a rather inefficient sample below. In reality, you should be using the constructor passing the input types, and using the version of run which allows you to pass parameters.

The SqlFunction section of our service

def sqlFunction = new SqlFunction(dataSource, "select salesByBookId(${bookId})")
    sqlFunction.compile()
    def result
    try {
      result = sqlFunction.run()
    } catch (org.springframework.dao.DataAccessException e) {
      log.error "Result type cannot be inferred - " +
                 " no result so returning 0"
      0
    }
    result

I should explain a few things about the code snippet:

• The dataSource I auto-inject using def dataSource in the Grails Service
• I return 0 if the key is wrong
• The SqlFunction throws subclasses of DataAccessException when the call fails (if the row doesn't come back, for example. You could use TypeMismatchDataAccesException, which is a subclass of DataAccessException but I chose the wider exception in the hierarchy.
• I'm using the nifty Grails log variable, which is wired into every Grails object automatically. You can control logging via the Config.groovy file in your grails-app directory.
• Ideally, you should set up the SqlFunction instance and re-use it over and over again, as it is compiled. Again, this is more showing you the features, not best practices, as I'm just blogging this to put a little more information out there. I'll refactor this a bit later, if I get the chance...

Calling PostgreSQL Stored Procedures from Grails - Part 2 - Spring's StoredProcedure class

In this part of my "Calling PostgreSQL procedures" series, I'm going to use the Spring StoredProcedure class to initiate the call.

What is the StoredProcedure class?

The Spring JDBC API has a class, StoredProcedure, that wraps calls to a stored procedure, providing access to both input and output parameters. The general strategy for using this class is:

1. Define your StoredProcedure subclass, listing parameters, and compiling the procedure (binding it to a Callable Statement)
2. Call the stored procedure using the execute() method, passing a Map of parameters

My sample code follows:

The Stored Procedure class

package org.playball.db

import org.springframework.jdbc.core.SqlParameter
import javax.sql.DataSource
import org.springframework.jdbc.object.StoredProcedure

class StoredProcSubclass extends StoredProcedure {
    public StoredProcSubclass(DataSource ds) {
      super(ds, "salesByBookId")
      setFunction true
      declareParameter(
         new SqlParameter("book_id", 
                  java.sql.Types.INTEGER))
      compile()
    }

    public Map execute(Integer bookId) {
      Map inputs = new HashMap()
      inputs.put("book_id", bookId)
      return super.execute(inputs)
    }
}

I've placed this class in src/groovy/org/playball/db. Now, to call it from our service from the last post, here is a snippet in the StoredProcedureRunnerService class:

  def sumBookSalesByBookIdWithSPSubclass(int bookId) {
    def spcaller = new StoredProcSubclass(dataSource)
    def map = spcaller.execute(bookId)
    // result is in entry '#result-set-1, 
    // with field 'result' which is a list, taking entry 0
    map['#result-set-1']['result'][0]
  }

Wasn't that fun? The strangest thing is the result output. Apparently, the return of the StoredProcedure call is a map of result sets, (#result-set-1), with each result being a map of fields w/values. Figure that out in a unit test! I actually hacked this with the Groovy Console, and it worked great for figuring that stuff out.

Please note - you should set up your StoredProcedure subclass in a Service or Spring Bean, not at the instance level, or you have to compile it each time. Rather inefficient... Will clean up later as this was just a demo of technology, not efficiency!

PostgreSQL East Conference - link to Grails Presentation materials

Brent Baxter and I presented a 90 minute Grails session, with a focus on GORM and PostGreSQL.  I put the PDF of the slides, along with one of the sample projects (just a starter but with targeted example), on a GitHub site - there you can download a zipfile or .gz file of the whole archive.

I will post a few blog entries about calling Stored Procedures from Grails.  I hope they prove useful.

URL for the GitHub site