JDBC programming with Groovy

Build your next reporting application with GroovySQL.

In the previous installments of the Groovy series, you've learned about some of the most beautiful features of Groovy. In Episode 1, I learned how to use Groovy to make unit tests of normal j**a *simpler and faster. In Episode 2, we saw what Groovy can bring to Ant builds. This time you'll find another practical application of Groovy, how to quickly build SQL-based reporting applications.

Scripting languages are typically excellent tools for building reporting applications quickly, but building such applications is especially easy for Groovy. Groovy's lightweight syntax removes some of the redundancy of JDBC in the J**a language, but its real power comes from closures, which gracefully shift the responsibility for resource management from the client to the framework itself, making it easier to lift the weight.

In this month's article, I'll start with an overview of GroovySQL and then show you how to put them to work by building a simple data reporting application. To get the most out of the discussion, you should be familiar with JDBC programming on the J**a platform. You may also want to review last month's introduction to closures in groovy, as they play an important role here. However, the key concept of this month is iteration, as iterators play an important role in Groovy's enhancements to JDBC. So, I'll start with an overview of iterators in groovy. Iteration is the most common and useful technique in a variety of programming environments. An iterator is an assistant of sorts that allows you to quickly access data in any collection or container, one data at a time. Groovy improves the concept of iterators in the J**a language by making iterators implicit and easier to use. In Listing 1, you can see that it is printed in the j**a languagestringThe work that needs to be done for each element of the collection.

Listing 1Iterator in normal ja.

import j**a.util.arraylist;import j**a.util.collection;import j**a.util.iterator;public class j**aiteratorexample }}

Listing 2Iterators in groovy.

class iteratorexample1}}

About this series:

The key to integrating any tool into your development practice is knowing when to use it and when to leave it in the box. A scripting language can be an extremely powerful addition to your toolbox, but only if applied properly to the right scenario.

The magic of Groovy's SQL lies in a sophisticated API called GroovySQL. Using closures and iterators, GroovySQL cleanly shifts JDBC's resource management responsibilities from the developer to the Groovy framework. Doing so removes the tedious JDBC programming and allows you to focus on queries and query results.

If you've forgotten how much trouble plain j**a jdbc programming is, I'd be happy to remind you! In Listing 3, you can see a simple JDBC programming example in the J**a language.

Listing 3JDBC programming for ordinary j**a.

import j**a.sql.connection;import j**a.sql.drivermanager;import j**a.sql.resultset;import j**a.sql.sqlexception;import j**a.sql.statement;public class jdbcexample1 }catch(sqlexception e)catch(classnotfoundexception e)finallycatch(exception e){}trycatch(exception e){}trycatch(exception e){}

Listing 4Welcome to Groovysql!

import groovy.sql.sqlclass groovysqlexample1}}

In Listing 4Welcome to Groovysql! In the first line, I created groovy'ssqlAn instance of the class that is used to connect to the specified database. In this example, I createdsqlinstance, pointing to the MySQL database running on my machine. It's pretty basic up to now, right? What really matters is the following sections, where iterators and closures show their power in a click or two. Please puteachrowA method is used as an iterator on the result generated by an incoming query. At the bottom level, you can see that JDBC is returnedresultsetobject, its contents are passed intoforCirculate. So, every iteration has to execute the closure that I pass in. If found in the databasewordIf the table has only three rows, then the closure will be executed three times - printedword_idspellingwithpart_of_speechvalue.

If you would the equation in the variables I specifiedrowremove and use one of Groovy's implicit variablesit(which happens to be an instance of an iterator), *could be further simplified. If I do this, then the previous ** can be written like this in Listing 5:

Listing 5groovysqlitVariable.

import groovy.sql.sqlclass groovysqlexample1"}}}

The previous example is fairly simple, but GroovySQL is handling more complex data manipulation queries (eginsertupdatewithdeletequery) is also very reliable. For these queries, you don't need to use iterators, so groovy'ssqlObjects are provided additionallyexecutewithexecuteupdateMethod. These methods are reminiscent of plain JDBCstatementclass, it also hasexecutewithexecuteupdateMethod.

In Listing 6, you see a simpleinsert, and again it to:The syntax uses variable substitution. This ** is just towordA new row is inserted into the table.

Listing 6Insert with groovysql.

wid = 999spelling = "nefarious"pospeech = "adjective"sql.execute("insert into word (word_id, spelling, part_of_speech)values ($")

Listing 7Create an instance of PreparedStatement with GroovySQL.

val = sql.execute("select * from word where word_id = ?", [5])

Listing 8Update with groovysql.

nid = 5spelling = "nefarious"sql.executeupdate("update word set word_id = ? where spelling = ?", [nid, spelling])

Listing 9Delete with groovysql.

sql.execute("delete from word where word_id = ?" , 5])

Built on top of the simplicity of GroovySQL and supported by GroovySQLdatasetThe concept of type, which is basically an object representation of a database table. usedataset, you can traverse through the rows, or you can add new rows. In fact, using a dataset is a convenient way to represent a public collection of data.

However, currently groovysqldatasetThe disadvantage of types is that they are not represented; They are simply one-to-one mappings to database tables. In Listing 10, I created a file fromwordTabledataset

Listing 10Create a dataset with GroovySQL.

import groovy.sql.sqlclass groovydatasetsexample1 words.add(word_id:"9999", spelling:"clerisy", part_of_speech:"noun")}}

Stored procedure calls and negative indexing can be important aspects of data manipulation. GroovySQL makes stored procedure calls as simple as they aresqlclasscallThe method is the same. For negative indexes, GroovySQL offers its own enhancementsresultsettype, which works very much like collections in groovy. For example, if you want to get the last item in the result set, you can do it as shown in Listing 11:

Listing 11Negative indexing with groovysql.

sql.eachrow("select * from word")

These examples are very simple, but they give you a good sense of the power of GroovySQL. I'm going to end this month's lesson with a practical example that demonstrates all of the features discussed so far.

Reporting applications typically drag information out of a database. In a typical business environment, you might be asked to write a reporting application that informs the sales team of current web sales, or has the development team perform routine monitoring of the performance of certain aspects of the system, such as the system's database.

To continue with this simple example, let's say you've just deployed an enterprise-wide web application. Of course, because you also wrote ample unit tests (in groovy) when you wrote **, it ran without problems; However, you still need to generate a report on the status of the database so that you can tune it. You want to know how your customers are using your application so you can identify performance issues and fix them.

In general, time constraints limit the number of prompts you can use in these types of applications. But your newfound knowledge of GroovySQL will make it easy for you to complete the application, giving you time to add more features you want.

In this example, your target database is MySQL, which happens to support the concept of discovering state information with queries. Here is the status information you are interested in:

Running time. The total number of queries processed. The proportion of a particular query, for exampleinsertupdatewithselect。It's too easy to get this information from a MySQL database with GroovySQL. Since you're building status information for your development team, you might just start with a simple command-line report, but you can put the report on the web in later iterations. The use case for this report example might look something like this:

1.Connect to our app's active database. 2.Releaseshow statusQuery and capture: aRunning time bThe number of all queries cAllinsertNumber dAllupdateNumber eAllselectNumber 3Using these data points, calculate: aQueries per minute bAllinsertQuery Percentage CAllupdatePercentage of queries dAllselectPercentage of queries.

In Listing 12, you can see the end result: an application that will report the required database statistics. The first few lines get a connection to the production database, followed by a series of successionsshow statusQueries, which lets you count the number of queries per minute and separate them by type. Please note likeuptimeHow such variables are created at the time of definition.

Listing 12Database status reporting with Groovysql.

import groovy.sql.sqlclass dbstatusreportelse if (status.variable_name == "questions") }println "uptime for database: " + uptime println "number of queries: " + questions println "queries per minute = " + integer.valueof(questions)/integer.valueof(uptime) sql.eachrow("show status like 'com_%'")else if (status.variable_name == "com_select")else if (status.variable_name == "com_update") }println "% queries inserts = " + 100 * insertnum / integer.valueof(uptime)) println "% queries selects = " + 100 * selectnum / integer.valueof(uptime)) println "% queries updates = " + 100 * updatenum / integer.valueof(uptime))

In next month's article on Groovy in action, I'm going to cover the details of the Groovy template framework. You'll find that creating an application's view component with this smarter framework is a piece of cake.