Jan 24, 2014

Simpler Stored Procedures with Groovy

Using Groovy almost makes calling Stored Procedures an enjoyable process. More like a less painful adventure. But since many large enterprises have thousands of stored procedures lying around, at least we can make calling them and using them a bit simpler than the Java counterpart of registering inputs and outputs.

The Groovy Sql class has many features and we’ll focus on two methods of interest for stored procedures: call() to handle output parameters, and rows() to handle ResultSet rows.

Calling an example GetACount stored proc on schema ABC with a lastName input and handling the output parameters is as simple as:

Calling a similar FindByFirst that copies the ResultSet rows into a List of GroovyRowResult objects is also straight forward:

Unfortunately the Sql class does not have a method to handle both output parameters AND a ResultSet in the same closure. GROOVY-3048 has been an open feature request since 2008. Until that feature is complete, I’ve created a simple SqlHelper class that adds a callWithRows() method.

First, showing how to use callWithRows() to get the rows and output parameters in a closure. Notice that callWithRows() returns the result of the closure to the original caller for you.

Now here is the source for SqlHelper.groovy. As an extension of Sql.groovy it can reuse many protected helper methods from the super class.

Hopefully this was helpful in showing multiple Groovy ways of dealing with the burden of calling Stored Procedures.

About the Author

Jeff Sheets profile.

Jeff Sheets

VP - Technology

Jeff has developed Java, Groovy, Grails, and Javascript web apps for industries as varied as Defense, Energy, Weather, Insurance, and Telecom. He is a co-organizer of the Omaha Java Users Group. Jeff has worked on Grails projects since the Grails 1.3.x days, and has experience with production Groovy code as well as Spock tests and Gradle builds. His latest focus has been on AngularJS and Spring Boot applications using JHipster. Jeff also enjoys volunteering at local CoderDojo events to teach programming to our next generation.

One thought on “Simpler Stored Procedures with Groovy

  1. Perhaps you might want to contribute to GROOVY-3048 ?

  2. Jeff Sheets says:

    Absolutely! I’ll post my solution to the ticket, and we can work out any changes from there.

    Thanks!

  3. Jeff Sheets says:

    As a follow-up for clarity. The callWithRows() feature was merged into the groovy-core Sql class, and is scheduled to be released with Groovy 2.3.0

Leave a Reply

Your email address will not be published.

Related Blog Posts
A security model for developers
Software security is more important than ever, but developing secure applications is more confusing than ever. TLS, mTLS, RBAC, SAML, OAUTH, OWASP, GDPR, SASL, RSA, JWT, cookie, attack vector, DDoS, firewall, VPN, security groups, exploit, […]
Building Better Data Visualization Experiences: Part 1 of 2
Through direct experience with data scientists, business analysts, lab technicians, as well as other UX professionals, I have found that we need a better understanding of the people who will be using our data visualization products in order to build them. Creating a product utilizing data with the goal of providing insight is fundamentally different from a typical user-centric web experience, although traditional UX process methods can help.
Kafka Schema Evolution With Java Spring Boot and Protobuf
In this blog I will be demonstrating Kafka schema evolution with Java, Spring Boot and Protobuf.  This app is for tutorial purposes, so there will be instances where a refactor could happen. I tried to […]
Redis Bitmaps: Storing state in small places
Redis is a popular open source in-memory data store that supports all kinds of abstract data structures. In this post and in an accompanying example Java project, I am going to explore two great use […]