Groovy XML and SQL

I saw a request on the JavaRanch forums regarding a way to grab data out of an XML document and stuff it into a database. Since it sounded like a pretty common thing to do and since I know Groovy has some cool XML handling features and since I have nothing better to do besides complete my projects in a timely manner, I figured I would attempt to solve the problem using some Groovy magic. I’m going to list pieces here as I figure them out so let’s just see how this works out. Through this post I’ll assume the following XML document needs to be stuff into a table called people with columns name, age, and preference of type varchar, integer, and varchar respectively.

   <person name="Cliff" age="30">
      <preference>Hip-hop and R&amp;B Music</preference>
      <preference>Car stereos</preference>
   <person name="Bill Clinton" age="59">
      <preference>White house Interns</preference>
   <person name="Dennis Rodman" age="45">
      <preference>Women's clothing</preference>

We start with a limited understanding of Groovy XML features and dive into the API docs to see what’s tucked away. (The “we” in “we start” is French for “I” since I’m the one starting and I’m the one with limited knowledge and since French is my second language it all kinda’ works out.) Hmm, there is an XMLSlurper object under the groovy.util package. That looks like a good place to start. XMLSlurper.parse() returns a GPathResult. I like the sound of this! Partly because GPath sounds like XPath but mostly because I know what GPath can do for you. Assuming a variable called xml set to the String value of the above XML I have this code to play with:

def people = new XmlSlurper().parseText(xml)
people.person.each { person ->
   println "Person " << person["@name"] << " is " << person["@age"] << " years old and likes " << person.preference

Pretty cool eh? I can easily iterate the nodes in the XML and print whatever is in them to the screen. Now it feels like half of my job is done which is a really good thing because I don’t wanna spend all day on the problem. It’s also a good thing because I’m picturing a typical Java developer struggling with XML Parsers, the JAX API, the event driven SAX API, the compiler, the swear words, you know the typical struggles. Here I’ve written a simple four-liner that could probably be condensed a line or two and I’m almost finished.

“Wait a minute! You only printed some stoopid stuff to the screen! Your not half way there! There’s the JDBC commands, the connection management, the database, the horror! Oh, the horror!”

Oh but if you knew what I know of Groovy’s good SQL support then maybe you wouldn’t be so fast to judge me. The only trouble I’m having here is my being too lazy to set up a test DB to verify everything works. I add the following to my groovy snippet including comments and I just imagine it working:

def people = new XmlSlurper().parseText(xml)
def sql = Sql.newInstance("jdbc:mysql://localhost:3306/mydb", "user",
                      "pswd", "com.mysql.jdbc.Driver")
people.person.each { person ->
   println "Person " << person.@name << " is " << person.@age << " years old and likes " << person.preference
   /*sql.executeUpdate( "insert into people(name, age, preference) "
   << "values('${person.@name}', ${person.@age}, '${person.preference}')" )*/

Does it work? I believe it would and I can’t see any reason why it wouldn’t but I’m too lazy to try it on a real DB. Maybe on another day. Right now I have to get back to work. So I leave you the remainder as an exercise for the reader, that’s you. (Now exercise!) I return to my daily work and leave you hanging because I can. It’s my blog and I’ll leave off any steps that I darn well please! An astute reader would point out that I could drop HSQL in and use GSQL to generate a DB on which to test. I say to the astute reader, “My dear friend, I just don’t feel like being bothered. Thank you for your suggestion, and I’ll holla…”

(Holla’ing is my sincerest form of salutation. Take no offense, those of you with limited English grammar as I deeply respect all of my audience, the three people that visit regularly. Until such time as our paths cross again, leave a comment and I’ll be in touch.)

One thought on “Groovy XML and SQL

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s