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

Object Partners profile.

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
Natively Compiled Java on Google App Engine
Google App Engine is a platform-as-a-service product that is marketed as a way to get your applications into the cloud without necessarily knowing all of the infrastructure bits and pieces to do so. Google App […]
Building Better Data Visualization Experiences: Part 2 of 2
If you don't have a Ph.D. in data science, the raw data might be difficult to comprehend. This is where data visualization comes in.
Unleashing Feature Flags onto Kafka Consumers
Feature flags are a tool to strategically enable or disable functionality at runtime. They are often used to drive different user experiences but can also be useful in real-time data systems. In this post, we’ll […]
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, […]