Another great way to incorporate Groovy into your enterprise Java projects is Groovy SQL. GSql makes JDBC access so easy and Groovy's seamless Java integration and dynamic nature provides for some interesting ways to take advantage of it.
In this example we'll build a utility class that will use convention to turn an SQL select statement into a List of data transfer objects.
Our goal here is to take an SQL statement and a Class, execute that statement and use the results to populate instances of the Class. We will assume the convention of camel case for object properties and underscores for database fields, so that a field called LAST_NAME would be mapped to a property called lastName. We will also assume that the select statement will only return fields that have a corresponding property in the Class.
So let's see what this might look like:
After defining a few variables, we create our groovy.sql.Sql object using the constructor that takes a Connection. This will allow us to use Connections provided by an application server such as WebLogic or WebSphere. We could easily change this method to take advantage of the other constructors.
Next we'll call the rows method of the Sql class, which will execute the SQL statement and return an ArrayList of GroovyRowResults. Now we'll iterate through this ArrayList and create a new instance of our DTO class for each row. GroovyRowResult implements the Map interface so we can loop through its keys with row.keySet().each. This will give us each of the fields in that row and we will turn them into property names with a call to the toCamelCase() method (which we'll look at shortly). Notice how we take advantage of GStrings to dynamically access properties of our DTO as well as keys of the row map. After we assign each of the fields in the row to properties of the DTO instance we add it to the result List. When the loop is done we just return the List. Obviously, more can be done to make this more robust and more flexible, but it's pretty amazing how easy it was to build a Groovy class that can replace tons of repetitive Java code.
Let's take a look at how we could use this class from a Java project:
That's all there is to it.
Now let's take a quick look at the toCamelCase() method we mentioned above. This doesn't directly have anything to do with the purpose of this post, but I am hoping that someone might see this and offer a groovier way to do the same thing. Basically the goal is to take a database field name such as LAST_NAME and turn it into a camelCase string like lastName. Here's how I did it:
I was sure there was a groovier way to do this and, sure enough, Andres Almiray came up with this awesome improvement:
Thanks Andres! And if that wasn't groovy enough, Ted Naleid came up with this cool mix of regular expressions and meta-programming. Good stuff Ted!