Friday, May 16, 2008

Another way to use Groovy in the Enterprise

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)
}

5 comments:

Andres Almiray said...

Here are 2 versions, the first one is Java-ish (sorry if formatting messes up the code)

def camelCaseConverter = { str ->
def result = ""
str.split( "_" ). eachWithIndex { word, index ->
word = word.toLowerCase()
switch( index ){
case 0:
result += word
break
default:
result += word[0].toUpperCase() + word[1..-1]
break
}
}
result
}

The second one is groovier

def camelCaseConverter = { str ->
def result = str.split("_").collect { word ->
word[0].toUpperCase() + word[1..-1].toLowerCase()
}.join("")
// make sure first char is lowerCase
result[0].toLowerCase() + result[1..-1]
}

Of course you can test them with

def columnName = "EMPLOYEE_ID"
assert "employeeId" == camelCaseConverter( columnName )

String.metaClass.toCamelCase = { ->
camelCaseConverter(delegate)
}
assert "employeeId" == columnName.toCamelCase()

=)

Dave Klein said...

Thanks Andres! I updated the post with your groovier version.

Ted Naleid said...

You can also use regular expressions to do the same thing in a pretty groovy way. String has a replaceAll that you can pass a closure to. In that closure, you have access to the groups that were matched in your regular expression:


def camelCaseConverter(orig) {
orig.toLowerCase().replaceAll(/_(\w)?/) { wholeMatch, firstLetter ->
firstLetter?.toUpperCase() ?: ""
}
}

String.metaClass.toCamelCase = {->
camelCaseConverter(delegate)
}

assert "fooBarBaz" == "FOO_bar_bAZ_".toCamelCase()
assert "camelCaseWord" == "camel_case_word".toCamelCase()

Shawn Hartsock said...

Groovy is awesome for this kind of work. I have been doing a less groovy technique using custom DAO that map result set columns onto POGOs. I did all my mapping in the custom queries that would alias column names onto the name used by the POGO. For example:

SELECT FIRST_NAME as firstName, LAST_NAME as lastName, ... and so on...

I'll have to give this technique a try instead.

What's great about this is that even if you are forced (for various reasons) not to use GORM for certain data you can still define constraints on these POGOs and show them to Controllers that can then do the normal Grails validation magic before sending the POGO back to the DAO.

Or you can use this as a data import or ETL.

Either way, neat stuff!

Joshua Jacobs said...

This may go without saying, but if you want to camel case words that are space delimited, you can just do this:


def camelCaseConverter(orig) {
orig.toLowerCase().replaceAll(/[_ ](\w)?/) {wholeMatch, firstLetter ->
firstLetter?.toUpperCase() ?: ""
}
}