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!