원문정보#

http://www-106.ibm.com/developerworks/library/j-pg01115.html?ca=drs-j0405

Practically Groovy: JDBC programming with Groovy #

Build your next reporting application using GroovySql

Andrew Glover (aglover@vanwardtechnologies.com)
CTO, Vanward Technologies
11 Jan 2005

GroovySql은 당신으로 부터 Groovy프레임워크에게 리소스 관리 부담을 제거함으로써 JDBC프로그래밍을 쉽게 하기 위해서 클로저(closures)와 이터레이터(iterator)로 이루어져 있다.

이전의 Groovy설치에서 당신은 Groovy의 몇몇 매력적인 기능들을 발견했다. 처음 글에서 당신은 단순하고 일반적인 자바코드의 좀더 빠른 유닛테스트를 위해서 Groovy를 적용하는 방법을 배웠다. 두번째 설치에서 당신은 Groovy가 Ant빌드를 가져오는 구문을 보았다. 이 시점에서 당신은 Groovy의 또다른 실험적인 사용법을 알게된다. 그것은 SQL기반의 리포팅 애플리케이션을 빨리 빌드하기 위해서 당신이 그것을 사용하는 방법이다.

스크립팅 언어는 리포팅 애플리케이션을 빨리 만들기 위해서 일반적으로 뛰어난 툴이다. 하지만 그런 애플리케이션을 만드는 것은 Groovy와 함께 분명하게 상퇘한 것이다. Groovy의 가벼운 문법은 자바의 JDBC의 복잡함을 덜어줄수 있다. 하지만 이것의 진짜힘은 클로저(closures)로 부터 나온다. 클라이언트로 부터의 리소스 관리의 책임은 다루기 쉬운 플레임워크 자신에게로 이전된다.

이번달 글에서 나는 GroovySql기능의 빠른 개요와 함께 시작할것이다. 그리고 당신에게 간단한 데이터리포팅 애플리케이션을 만듬으로써 그것들을 사용하는 방법들을 보여줄것이다. 다음의 내용을 쉽게 받아들이기 위해서 당신은 자바플래폼에서의 JDBC프로그래밍에 매우 친숙해야만 한다. 당신은 Groovy내의 클로저(closure)에 대한 소개를 리뷰하길 원할수도 있다. 왜냐하면 그것들은 여기서 매우 중요한 역활을 담당하기 때문이다. 가장 중요한 개념은 어쨌든 반복(iteration)이다. 왜냐하면 이너레이터(iterator)는 JDBC의 Groovy환경에서 매우 중요한 역활동 작동한다. 그래서 나는 Groovy내의 iterator의 개념으로 부터 시작할것이다.

iterator로 들어가기(Enter the iterator)#

반복(Iteration)은 모든 종류의 프로그래밍 환경에서 대단히 공통적이고 유용한 방법중에 하나이다. iterator는 어떤 집합과 컨테이너내의 데이터에 빨리 접근하도록 도와주는 어떤 종류의 코드이다. Groovy는 그것들을 내포하고 좀더 사용하기 간단하도록 만들어서 자바내 iterator의 개념을 발전시켰다. Listing 1에서 당신은 자바언어를 사용해서 문자열 집합에서 각각의 요소를 프린트 하는데 드는 노력을 볼수 있다.

Listing 1. Iterators in normal Java code

import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
public class JavaIteratorExample {
  public static void main(String[] args) {
     Collection coll = new ArrayList();
     coll.add("JMS");
     coll.add("EJB");
     coll.add("JMX");
     for(Iterator iter = coll.iterator(); iter.hasNext();){
        System.out.println(iter.next());
     }
  }
}

Listing 2에서 당신은 Groovy로 나의 노력을 간단하게 만드는 노력을 볼수 있다. 나는 iterator인터페이스를 우회해서 iterator와 비슷한 집합(collections)자신의 메소드를 사용한다. 더해서 Groovy의 iterator메소드는 각각의 반복주기를 만들어내는 closure를 받는다. Listing 2는 Groovy에 의해 변형된 자바언어기반의 예제를 처리하는 과정을 보여준다.

Listing 2. Iterators in Groovy

class IteratorExample1{
   static void main(args) {
     coll = ["JMS""EJB""JMX"]
     coll.eachitem | println item }
   }
}

당신이 보는것처럼 전형적인 자바코드와는 다르다. Groovy는 내가 필요할때 그것을 보내는 것을 허락하는 동안 나의 반복적인 코드를 조정한다. 이 컨트롤과 함께 Groovy는 교모하게 나로부터 Groovy자신에게 리소스 관리의 책임을 이동시킨다. 리소스 관리시에 Groovy는 굉장히 강력하다. 이것은 더 쉽고 다 일관적이게 그리고 더 빠른 프로그래밍을 야기한다.

이 시리즈에대해서
The key to incorporating any tool into your development practice is knowing when to use it and when to leave it in the box. Scripting languages can be an extremely powerful addition to your toolkit, but only when applied properly to appropriate scenarios. To that end, Practically Groovy is a series of articles that explores the practical uses of Groovy, helping you learn when and how to apply them successfully.

Introducing GroovySql
Groovy's SQL magic is found in an elegant API called GroovySql. Using closures and iterators, GroovySql neatly shifts JDBC resource management from you, the developer, to the Groovy framework. In so doing, it removes the drudgery from JDBC programming so that you can focus on queries and their results.

Just in case you've forgotten what a hassle normal Java JDBC programming can be, I'm all too happy to remind you! In Listing 3, you can see a simple JDBC programming example in the Java language. Listing 3. JDBC programming in normal Java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCExample1 {
  public static void main(String[] args) {
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
    try{
      Class.forName("org.gjt.mm.mysql.Driver");
      con = DriverManager.getConnection("jdbc:mysql://localhost:3306/words",
           "words""words");
      stmt = con.createStatement();
      rs = stmt.executeQuery("select * from word");
      while (rs.next()) {
        System.out.println("word id: " + rs.getLong(1+
            " spelling: " + rs.getString(2+
            " part of speech: " + rs.getString(3));
      }
    }catch(SQLException e){
      e.printStackTrace();
    }catch(ClassNotFoundException e){
      e.printStackTrace();
    }finally{
      try{rs.close();}catch(Exception e){}
      try{stmt.close();}catch(Exception e){}
      try{con.close();}catch(Exception e){}
   }
  }
}

Wow. Listing 3 contains roughly 40 lines of code just to view the contents of a table! How many do you think it takes using GroovySql? If you guess more than 10 lines, you would be wrong. Watch how Groovy elegantly lets me focus on the task at hand -- performing a simple query-- and handles the underlying resource handling for me in Listing 4. Listing 4. Welcome to GroovySql!
import groovy.sql.Sql
class GroovySqlExample1{
  static void main(args) {
    sql = Sql.newInstance("jdbc:mysql://localhost:3306/words""words",
           "words""org.gjt.mm.mysql.Driver")
    sql.eachRow("select * from word"){ row |
       println row.word_id + " " + row.spelling + " " + row.part_of_speech
    }
  }
}

Not bad. Using just a few lines, I've just coded the same behavior from Listing 3, without relying on Connection closing, ResultSet closing, or any of the other familiar heavy lifters found in JDBC programming. Pretty exciting stuff, if you ask me -- and so easy, too. Now let me show you exactly how I did it.

Performing a simple query
In the first line of Listing 4, I created an instance of Groovy's Sql class, which is used to connect to a desired database. In this case, I created an Sql instance pointing to a MySQL database running on my machine. So far, pretty basic, right? The real knockout is the next part, where the one-two punches of iterators and closures shows their power.

Think of the eachRow method as an iterator on the result of the passed-in query. Underneath, you can visualize a JDBC ResultSet object being returned and its contents being passed into a for loop. Consequently, the closure I passed in is executed for each iteration. If the word table found in the database only had three rows, the closure is executed three times -- printing out the word_id, spelling, and part_of_speech values.

The code is simplified even further by dropping my named variable, row, from the equation and using one of Groovy's implicit variables: it, which happens to be the instance of the iterator. If I did this, the preceding code would be written as shown in Listing 5. Listing 5. Groovy's it variable in GroovySql

import groovy.sql.Sql
class GroovySqlExample1{
  static void main(args) {
    sql = Sql.newInstance("jdbc:mysql://localhost:3306/words""words",
           "words""org.gjt.mm.mysql.Driver")
    sql.eachRow("select * from word"){ println it.spelling +  " ${it.part_of_speech}"}
  }
}

In this code I was able to drop the row variable and use it instead. Additionally, I can reference the it variable in String statements, as I did with ${it.part_of_speech}.

Doing more complex queries
The previous examples are fairly simple, but GroovySql is just as solid when it comes to more complex data manipulation queries such as insert, update, and delete queries. For these, you wouldn't necessarily want to use iterators, so Groovy's Sql object provides the execute and executeUpdate methods instead. These methods are reminiscent of the normal JDBC statement class, which has an execute and an executeUpdate method as well.

In Listing 6, you see a simple insert that uses variable substitution again with the ${} syntax. This code simply inserts a new row into the word table. Listing 6. Inserts with GroovySql

 wid = 999
 spelling = "Nefarious"
 pospeech = "Adjective"
 sql.execute("insert into word (word_id, spelling, part_of_speech) 
   values (${wid}, ${spelling}, ${pospeech})")

Groovy also provides an overridden version of the execute method, which takes a list of values that correspond to any ? elements found in the query. In Listing 7, I've simply queried for a particular row in the word table. Underneath the hood, GroovySql creates an instance of the normal Java language java.sql.PreparedStatement. Listing 7. PreparedStatements with GroovySql
val = sql.execute("select * from word where word_id = ?"[5])

Updates are much the same in that they utilize the executeUpdate method. Notice, too, that in Listing 8 the executeUpdate method takes a list of values that will be matched to the corresponding ? elements in the query. Listing 8. Updates with GroovySql
 nid = 5
 spelling = "Nefarious"
 sql.executeUpdate("update word set word_id = ? where spelling = ?"[nid, spelling])

Deletes are essentially the same as inserts, except, of course, that the query's syntax is different, as shown in Listing 9. Listing 9. Deletes with GroovySql
 sql.execute("delete from word where word_id = ?" [5])

Simplifying data manipulation Any API or utility that intends to simplify JDBC programming had better have some rock-solid data manipulation features and in this section, I'll show you three more.

DataSets
Building on its foundation of simplicity, GroovySql supports the notion of DataSet types, which are basically object representations of database tables. With a DataSet, you can iterate over rows and add new rows. Indeed, using datasets is a convenient way of representing a collection of data common to a table.

The one current failing of GroovySql DataSet types, however, is that they do not represent relationships; they are simply a one-to-one mapping with a database table. In Listing 10, I've created a DataSet from the word table. Listing 10. Datasets with GroovySql

import groovy.sql.Sql
class GroovyDatasetsExample1{
  static void main(args) {
    sql = Sql.newInstance("jdbc:mysql://localhost:3306/words""words",
          "words""org.gjt.mm.mysql.Driver")
    words = sql.dataSet("word")
    words.eachword |
     println word.word_id + " " + word.spelling
    }
    words.add(word_id:"9999", spelling:"clerisy", part_of_speech:"Noun")
  }
}

As you can see, GroovySql's DataSet type makes it easy to iterate over the contents of a table with the each method and add a new rows with the add method, which takes a map representing the desired data.

Using stored procedures and negative indexing
Stored procedure calling and negative indexing can be essential aspects of data manipulation. GroovySql makes stored procedure calling as simple as using the call method on the Sql class. For negative indexing, GroovySql provides its enhanced ResultSet type, which works much like collections in Groovy. For example, if you wanted to grab the last item in a result set, you could do as shown in Listing 11. Listing 11. Negative indexing with GroovySql

 sql.eachRow("select * from word"){ grs |
   println "-1  = " + grs.getAt(-1//prints spelling
   println "2  = " + grs.getAt(2//prints spelling
 }

As you can see in Listing 11, grabbing the last element in a result set is as easy as indexing with a -1. If wanted to, I could also access the same element using the 2 index.

Again, these examples are pretty basic, but they should give you a good sense of GroovySql's powers. I'll close this month's lesson with a real-world example demonstrating all the features discussed so far.

Writing a simple reporting application
Reporting applications usually pull information from a database. In a typical business environment, you might be asked to write a reporting application to inform the sales team about current Web sales or to let the development team do daily checkups on the performance of some aspect of the system, such as its database.

For the sake of this simple example, let's assume you've just deployed an enterprisewide Web application. It's running flawlessly, of course, because you wrote a wealth of unit tests (in Groovy) as you went along; but you still need to generate a report about the state of the database for tuning purposes. You want to know how the application is being used by your customers so you can anticipate performance issues and address them.

Usually time constraints limit the number of bells and whistles you can apply to such an application. But your newly acquired knowledge of GroovySql will let you knock out this application in a snap, leaving you with time to add in extra features if you so desire.

The details
Your target database is in this case MySQL, which just so happens to support the notion of discovering status information with a query. The status information you're interested in is as follows:

    * Uptime
    * Total number of overall queries processed
    * Proportions of specific queries, such as insert, update, and select

Getting this information out of a MySQL database is almost too easy using GroovySql. Since you're building it for the development team, you'll probably just start out with a simple command-line report, but you could easily Web-enable the report in a later iteration. The use case for the reporting example might look something like this: 1. Connect to our application's live database 2. Issue show status queries and capture: a. uptime b. total queries c. total inserts d. total updates e. total selects 3. With those data points, calculate: a. queries per minute b. percentage of total insert queries c. percentage of total update queries d. percentage of total select queries

In Listing 12, you can see the final result: an application that reports the desired database statistics. The initial lines of code obtain a connection to the production database, followed by a series of show status queries that let you calculate queries per minute and then break them down by type. Notice how variables like uptime pop into existence as they're defined. Listing 12. Database status reporting with GroovySql

import groovy.sql.Sql
class DBStatusReport{
  static void main(args) {
     sql = Sql.newInstance("jdbc:mysql://yourserver.anywhere/tiger""scott",
        "tiger""org.gjt.mm.mysql.Driver")
     sql.eachRow("show status"){ status |
        if(status.variable_name == "Uptime"){
           uptime =  status[1]
        }else if (status.variable_name == "Questions"){
           questions =  status[1]
        }
     }
     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_%'"){ status |
        if(status.variable_name == "Com_insert"){
           insertnum =  Integer.valueOf(status[1])
        }else if (status.variable_name == "Com_select"){
           selectnum =  Integer.valueOf(status[1])
        }else if (status.variable_name == "Com_update"){
           updatenum =  Integer.valueOf(status[1])
       }
    }
    println "% Queries Inserts = " 100 (insertnum / Integer.valueOf(uptime))
    println "% Queries Selects = " 100 (selectnum / Integer.valueOf(uptime))
    println "% Queries Updates = " 100 (updatenum / Integer.valueOf(uptime))
    }
}

Reviewing today's lesson
In this month's installment of Practically Groovy, you've seen how GroovySql can simplify JDBC programming. This nifty API combines closures and iterators with Groovy's relaxed syntax to facilitate rapid database application development on the Java platform. Most powerfully, GroovySql shifts resource management tasks from the developer to the underlying Groovy framework, letting you focus on the more important stuff of queries and results. But don't just take my word for it. Next time you're asked to delve into the drudgery of JDBC, try a little GroovySql magic on it instead. Then send me an e-mail and tell me about your experience.

In next month's installment of Practically Groovy, I'll cover the ins and outs of Groovy's template framework. As you'll discover, it's a snap to create the view component of an application with this clever framework.

Resources

About the author Andrew Glover is the CTO of Vanward Technologies, a Washington, D.C., metro area company specializing in the construction of automated testing frameworks, which lower software bug counts, reduce integration and testing times, and improve overall code stability.

Add new attachment

Only authorized users are allowed to upload new attachments.
« This page (revision-4) was last changed on 21-Dec-2008 13:57 by DongGukLee