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
Google Professional Machine Learning Engineer Exam 2021
Exam Description A Professional Machine Learning Engineer designs, builds, and productionizes ML models to solve business challenges using Google Cloud technologies and knowledge of proven ML models and techniques. The ML Engineer is proficient in all aspects […]
Designing Kubernetes Controllers
There has been some excellent online discussion lately around Kubernetes controllers, highlighted by an excellent Speakerdeck presentation assembled by Tim Hockin. What I’d like to do in this post is explore some of the implications […]
React Server Components
The React Team recently announced new work they are doing on React Server Components, a new way of rendering React components. The goal is to create smaller bundle sizes, speed up render time, and prevent […]
Jolt custom java transform
Jolt is a JSON to JSON transformation library where the transform is defined in JSON. It’s really good at reorganizing the json data and massaging it into the output JSON you need. Sometimes, you just […]