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. Required fields are marked *

Related Blog Posts
An Exploration in Rust: Musings From a Java/C++ Developer
Why Rust? It’s fast (runtime performance) It’s small (binary size) It’s safe (no memory leaks) It’s modern (build system, language features, etc) When Is It Worth It? Embedded systems (where it is implied that interpreted […]
Getting Started with CSS Container Queries
For as long as I’ve been working full-time on the front-end, I’ve heard about the promise of container queries and their potential to solve the majority of our responsive web design needs. And, for as […]
Simple improvements to making decisions in teams
Software development teams need to make a lot of decisions. Functional requirements, non-functional requirements, user experience, API contracts, tech stack, architecture, database schemas, cloud providers, deployment strategy, test strategy, security, and the list goes on. […]
JavaScript Bundle Optimization – Polyfills
If you are lucky enough to only support a small subset of browsers (for example, you are targeting a controlled set of users), feel free to move along. However, if your website is open to […]