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:
static Collection getData(String query, Class clazz, Connection con){
def field = ''
def result = []
def sql = new Sql(con)
sql.rows(query).each {row ->
def dto = clazz.newInstance()
row.keySet().each{
field = toCamelCase(it)
dto."${field}" = row."${it}"
}
result << dto
}
result
}
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:
...
Connection con = getConnectionFromPool();
String query = "SELECT FIRST_NAME, LAST_NAME, EMAIL FROM MEMBER";
Collection members = GroovyData.getData(query, MemberDTO.class, con);
...
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:
static String toCamelCase(String val){
def sb = new StringBuffer()
Boolean newWord = false
val.toLowerCase().each{
if (it == '_')
newWord = true
else{
if (newWord){
newWord = false
sb << it.toUpperCase()
}
else
sb << it
}
}
sb.toString()
}
I was sure there was a groovier way to do this and, sure enough,
Andres Almiray came up with this awesome improvement:
static String toCamelCase(String val){
def result = val.split("_").collect { word ->
word[0].toUpperCase() + word[1..-1].toLowerCase()
}.join("")
result[0].toLowerCase() + result[1..-1]
}
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!
def camelCaseConverter(orig) {
orig.toLowerCase().replaceAll(/_(\w)?/) { wholeMatch, firstLetter ->
firstLetter?.toUpperCase() ?: ""
}
}
String.metaClass.toCamelCase = {->
camelCaseConverter(delegate)
}
