Monday, November 23, 2015

Running SQL queries - the groovy way

Recently I've been tasked with the creation of a utility that executes predefined queries against a database via a command-line interface. A maintenance-sort of utility. I thought this is a a perfect opportunity to freshen up my Groovy DSL skills and see what I can put together to make the code readable.

The idea

The generic idea was to create an object that would describe what the query is all about, with placeholders, then fill in those placeholders and finally execute the statement. It's a one-off operation so we won't be concerning ourselves with connection pooling and the like. Let's KISS.

The implementation

What I'm about to show you here is an over simplified version of a implementation that might be useful. Actually it is quite a bit of overhead for the simplicity Groovy already has in its toolset (the groovy.sql.Sql class) but it opens up a way of thinking of interoperability with the database.

So here we go. First we create a class to house the main property (the query to be executed) and allows for switching context to one where only execution-time properties play a role. In that switched context we execute the query and complete the execution.

Imagine now that instead of just using the Object[] array one would allow for a map to be passed on, like so:

It really doesn't get much more complex than this in regard to predefined queries :) If you'd extract the engine that fills in templates from placeholders and a map of keys (placeholders) and values you could apply the same to pretty much everything that takes a string and parameters and executes it, for example the execution of external commands :)

Happy coding

No comments: