September 30, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Groovy Development

  • September 15, 2005
  • By Dick Wall
  • Send Email »
  • More Articles »

SQL Handling

Fortunately, SQL handling is just about as strong as XML handling in Groovy. Because space is limited in this article, I am not going to go into the setup of JDBC drivers, and making the connection to the database. Assuming you already have a JDBC connection, getting a Groovy object to make life easy is as simple as saying:

sql = new groovy.sql.Sql(jdbcConn)

For the remainder of the article, I am going to assume that there are some suitable tables in the database, let's say:

Table: SECTIONS
Fields: SECTION_ID, SECTION_NAME

and

Table: BOOKS
Fields: BOOK_ID, SECTION_ID, TITLE, AUTHOR

Now, this SQL variable can be used to do some very powerful operations against the database:

Getting Data Out from the DB

Given the SQL variable defined as above, executing a query and getting the results is super easy:

sql.eachRow("SELECT SECTION_NAME, SECTION_ID FROM SECTIONS")
   { section ->

This line will execute the SELECT statement, returning a results set of sections. It then will iterate over these and put the current row in the variable section ready for you to use. You can refer to the fields in the row by name, for example:

section.SECTION_NAME

will give you the section name for the current row.

Now, suppose that you want to get a list of books in a particular section. In the above enclosure, you could write:

sql.eachRow("SELECT TITLE, AUTHOR FROM BOOKS WHERE SECTION_ID=
            '${section.SECTION_ID}'") {
               book ->

Note the use of the expression language in the query, and also the single quotes around the expression (these are present to keep the SQL parser of the database happy), so that the resulting query will be:

SELECT TITLE, AUTHOR FROM BOOKS WHERE SECTION_ID='101'

with the quotes around the number.

So, upi see now that nesting SQL queries that depend on foreign key relationships like the SECTION_ID field is also easy.

Putting Data Into the DB

The same SQL variable can be used to put data back into the database. To do this, you use the execute method:

sql.execute("INSERT INTO BOOKS(BOOK_ID, SECTION_ID, TITLE,
             AUTHOR) VALUES (?,?,?,?)",
           [book_id, section_id, title, author])

Where book_id, section_id, title and author are just variables in scope within Groovy, and a list is created for them on the fly for insertion. You also could use the expression language notation if you wanted to, but separating out the variables using this notational form and using the list means that the implementation of the SQL handler can try and optimize queries by pre-compiling them and binding in the list of variables (this will be a familiar concept to anyone who has done much in the way of JDBC programming—pre-compiled statements with variables bound in at runtime are much more efficient).

Note: When you are putting data back in to the database based on an XML file input, you may have to keep a track of ID fields using counters (for example, book_id and section_id) they they are an invention of the relational model and do not exist in the XML file (where the relationships are done by nesting). For this purpose, you could simply use a variable counter, but it is probably safer to use some kind of database provided sequence.

Time To Put It All Together

OK, now that you can read and write both XML and SQL data pretty easily, it's time to do an actual translation where you read the DB data and output it to XML. Again, this app assumes you have done the work to have a JDBC connection ready to use (which means setting up a schema, adding the right driver to the classpath, and opening it in Groovy—that part you can use the equivalent Java code for—there should be no work required to get Groovy to accept it).

import groovy.xml.MarkupBuilder;
myXMLDoc = new MarkupBuilder()
sql = new groovy.sql.Sql(jdbcConn)
myXMLDoc.library {
   // find the sections using the database query
   sql.eachRow("SELECT SECTION_NAME, SECTION_ID FROM SECTIONS")
      { sectionRow ->
   // output the section name to the XML file
   myXMLDoc.section(id:"${sectionRow.SECTION_NAME}") {
   // find the books in that section
   sql.eachRow("SELECT * FROM BOOKS
                WHERE SECTION_ID='${sectionRow.SECTION_ID}'") {
                   bookRow ->
         myXMLDoc.book(title:"${bookRow.TITLE}",
                       author:"${bookRow.AUTHOR}")
      }
   }
}
println myXMLDoc

And, that's it! A complete translator from a database schema to an XML document. Yes this is simple, but it illustrates the potential (the translators I wrote were much more complex and did manipulations on the ordering and grouping of the resulting XML files to meet the customer's requirements, but that would have gone far beyond the scope of this article and also broken client confidentiality).

So Why Not XSLT, Oracle's XML System, Castor, and So Forth....

The above example is extremely simplistic, and the same results can be achieved in numerous other ways with similar compactness. For example, XSLT could be employed to create the SQL statements to import data, Oracle has its own XML pipeline system that you might have used, and other options abound for this kind of work, so why did I use Groovy?

The reasons I believe Groovy is a good choice for this kind of task are (in no particular order)

  • The XML and SQL notation are about as compact as I have seen anywhere
  • The familiarity of syntax and semantics that are very close to Java, thereby removing a learning curve that may exist with other solutions
  • Groovy is a full programming language, backed up by a full platform library (in other words, it has access to everything Java does). This means that if you need to go beyond simply translating data, you can do anything that Java can (which is basically anything you like). In practice, this meant that the full business rules logic necessary for successful data transformation was possible, and even easier with Groovy than Java because of some of the excellent language features
  • Transparency of use, Groovy (using groovyc) compiles to .class files and can be wrapped as a jar. Furthermore, it has an ant task for compilation, and can inherit from and be inherited by other Java classes. The integration is so tight that, from the outside, another developer will not even know that you have used Groovy (unless you tell them).

But, there are things to be aware of when using Groovy:

  • The lack of a debugger means that problems will need to be sorted out with diagnostics and patience. For this reason, keep the scripts as simple and compact as possible. In fact, I wrote the support framework for the translators in Java. The framework handled everything like setting up the DB connections and opening the XML files, and so on. That way, when I got to write the translator, I only had to concentrate on taking data from one source and converting it to the other. This is good practice anyway, but even better when you can move any potentially problematic code into Java (where it can be easily debugged).
  • The JSR releases are pretty reliable and I have not hit any big problems, but remember that it is not final yet. In particular, the documentation (or lack thereof) can be the biggest challenge. In practice, this is another reason to keep the scripts small and focused.
  • It's a quirky thing, but the name can be a hard sell with management. I found referring to it as JSR 241 got me a lot further than Groovy (in truth, it is a poor choice of name for easy acceptance into a corporate environment).

Conclusion

While not the answer to everything (at least not yet), used with care Groovy can be an extremely useful tool in a developer's toolbox. It is not an overstatement to say that it saved me many hours of development time on the translator project (I estimate that the translators took probably a third of the time they would have taken to write in Java, and that is learning the Groovy language and environment along the way, although, in truth, they were very similar to Java).

About the Author

Dick Wall is a Lead Systems Engineer for NewEnergy Associates, A Siemens Company based in Atlanta, GA that provides energy IT and consulting solutions for decision support and energy operations. He can be reached for comment on this and other matters at dick.wall@newenergyassoc.com. He also co-hosts the JavaCast, a podcast devoted to Java news and the Java community, which can be found at http://javacast.thepostmodern.net.





Page 3 of 3



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel