Calling PostgreSQL Stored Procedures from Grails - Part 1, the setup and JdbcTemplate

So, you have a want to write a Grails-based application, but your database has stored procedures? You can still use Grails for the MVC, and all of the rich plugins. This series of posts outlines a mechanism to get you there.

The Setup

Here's a table definition and a stored procedure to go with it. The table defines sales for a book in an online store (ala the old Sybase pubs database). The stored procedure (function) just goes and counts the total revenue from all sales on a given book id.

The table schema

CREATE TABLE sale
(
book_id BIGINT NOT NULL,
sale_id BIGINT NOT NULL,
quantity INT NOT NULL,
sale_price DECIMAL(6,2) NOT NULL
);

(other tables and keys omitted for brevity)

Stored Procedure

CREATE OR REPLACE FUNCTION salesByBookId(
 book_id int)
 RETURNS decimal
 AS $$
DECLARE
amount DECIMAL;
BEGIN
 select sum(s.quantity * s.sale_price) INTO amount
 from sale s, book b
 where s.book_id = b.book_id
  and b.book_id = book_id;

 RETURN amount;
END;
$$ LANGUAGE plpgsql;

Ok, so now we've got a procedure to call. You can invoke it in PostgreSQL like this:

select salesByBook(80);

So, what options do I have on the Grails side? Let's look at the simplest option, using Spring's JdbcTemplate:

A Grails Service to call stored procedures

Grails provides the service object for writing re-usable business logic (ala an EJB or Spring Bean). In fact, it actually IS a Spring Bean, and happens to be transactionally managed. Just use grails create-service to build one. Here is an example one using the Spring JdbcTemplate (see the fully optimized version in this post):

class StoredProcedureRunnerService {
 def dataSource
 boolean transactional = true

 // automatically set up by name (Spring injected) on startup
 def sumBookSalesByBookIdWithJdbcTemplate(def bookId) {
     def template = new JdbcTemplate(dataSource)
        template.queryForInt("select salesByBookId(${bookId})")
     }
  }

Some items of note:

• Grails is a Spring application development platform. So you can easily import any standard Spring APIs such as JdbcTemplate
• note the def dataSource in the code above. Spring will look for a bean it has created with that name and inject it automatically. Our Grails datasource has the Spring Bean name of 'dataSource'
• The last line of thesumBookSalesByBookIdWithJdbcTemplate method just calls template.queryForInt - that happens to be the last evaluated statement in the method, which Groovy uses as the return value of the method. In other words, the value returned is the value of the queryForInt method.

So, there you have it. To use this service, just do a defStoredProcedureRunnerService at the top of a Controller, and call it - that class is a Spring Bean, and is autowired at startup time directly into the Controller. Here is my test controller (a fragment):

class TestController {
  def storedProcedureRunnerService
  def index = {
   render(view: "index", 
     model: [bookId: params.bookId != null ? 
          params.bookId : 0])
 }

  def template = {
     def safeBookId = parseInt(params.bookId)

     def result = storedProcedureRunnerService.
       sumBookSalesByBookIdWithJdbcTemplate(safeBookId);

     def model = [result: result,
                  bookId: safeBookId]
        render(view: "index", model: model)
  }
}