Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Javaプログラマーに贈る:Groovyで楽にSQLを実行してみよう (Club DB2)

Javaプログラマーに贈る:Groovyで楽にSQLを実行してみよう (Club DB2)

2013/2/22 Javaプログラマーに贈る:Groovyで楽にSQLを実行してみよう (Club DB2)
Club DB2 という、IBM DB2 の勉強会イベントで以前発表した資料です。かなり古いものなので情報が今とは異なる部分もあると思いますが、保存のためにアップロードしています。

Akira Shimosako

June 02, 2022
Tweet

More Decks by Akira Shimosako

Other Decks in Technology

Transcript

  1. 2 自己紹介 下佐粉 昭 ( しもさこ あきら ) 和歌山県生まれ 2001年

    IBMに中途入社 以来、DB2関連の仕事多し 現在は金融系のお客様向け技術支援 ▪書籍 「即戦力のDB2管理術」 – http://db2.jugem.cc/?eid=2341 (書籍紹介) 「XML-DB開発 実技コース」(共著) 「DB2 逆引きリファレンス」(共著) ▪オンライン Twitter - @simosako – http://twitter.com/simosako Unofficial DB2 Blog – http://db2.jugem.cc/ 全内容をWEBで公開しています http://db2watch.com/
  2. 4 内容 1. Java+JDBCの課題 – JDBCとは? – JDBCプログラミングの面倒なところ – JVM上の色々な言語とGroovy

    2. Groovy超入門 – 特徴 – インストール – Groovyの便利な機能 3. GroovyでRDBを操作する – Groovy SQL – SQLの実行 – Groovyで書くデメリット? この資料ではDB2 10.1とGroovy 2.1.1を使用しています
  3. 6 JDBCとは? Java言語から各種RDBを利用するための標準インターフェース –JDK 1.1の一部として提供される(1997年) –RDB実装に依存する部分は各RDB用のJDBCドライバで実装する –最新はJDBC 4.1 (JSR 221)

    標準化(共通化)している部分 –RDBへの接続と接続解除 –SQLの実行(DDL,DML,ストアドプロシージャ) –アンサーセットの取得、更新後に影響があった行数の取得 –トランザクション制御(COMMIT,ROLLBACK) –エラー制御(SQLExceptionによる例外処理) : 標準化していない部分 –実行されるSQL自体には一切関与しない(SQL方言の標準化はしない) –エラー内容についてもほぼ関与しない(※ JDBC 4.0から改善されました)
  4. 7 DB2のJDBC対応 JDBC 4.0に対応 – db2jcc4.jar - JDBC 4.0(以降)準拠 –

    db2jcc.jar - JDBC 3.0準拠 URLでType 2とType 4を切り替え可能 – Type 2: DB2クライアント経由で接続 • URL = jdbc:db2:DB名 • DB2クライアントの導入と設定(CATALOG)が必須 • ローカル接続時はIDとパスワードの指定を省略可能 • ローカル接続時はTCP/IPではなくシェアードメモリ経由で接続 – Type 4: 100% pure Java 実装 • URL = jdbc:db2://ホスト名:ポート番号/DB名 • DB2クライアントの導入が不要 JDBCドライバはDB2製品に同梱(無料のExpress-Cにも同梱) – 最新版のダウンロードはFix Packダウンロードページから • http://www.ibm.com/support/docview.wss?uid=swg27007053 どちらか1つを CLASSPATHに含める 参考)DB2 が提供している JDBC ドライバーの種類 http://www-01.ibm.com/support/docview.wss?uid=swg21601089 db2jcc.jarファイルを一 つコピーするだけで使え るのでお手軽です
  5. 8 Java+JDBC直接プログラミングはとても面倒... import java.sql.* ; public class TestJDBC { public

    static void main(String[] args) { try { Class.forName("com.ibm.db2.jcc.DB2Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); return; } Connection conn; Statement stmt; ResultSet result; try { conn = DriverManager.getConnection("jdbc:db2://localhost:5000/sample","db2admin","pass"); stmt = conn.createStatement(); result=stmt.executeQuery("SELECT * FROM STAFF WHERE ID < 100"); while (result.next()) { System.out.println(""+result.getInt("ID") + ": "+ result.getString("NAME")); } } catch (SQLException e) { e.printStackTrace(); } finally { if (result != null) { try {result.close();} catch (SQLException e) { e.printStackTrace();} } if (stmt != null) { try {stmt.close();} catch (SQLException e) { e.printStackTrace();} } if (conn != null) { try {conn.close();} catch (SQLException e) { e.printStackTrace();} } } } } JDBCドライバのロード等、準備 メインロジック 後処理
  6. 9 メインロジック以外の部分が多すぎる Java言語側 – 単にSQLを実行するだけのためにclassを作って public static void main() ...

    – DBの型定義に合わせて getInt(),getString()を実行するなど、型を意識した操作 • DB側の型が変わったらアプリケーションも要修正 JDBCライブラリ側 – Class.forNameによるJDBCドライバーのロード ※JDBC 4.0から不要に – Connectionオブジェクト→Statementオブジェクト→SQL実行 という三段階の手順 – キャッチ(検査)例外の処理が必須 – 後処理:順にリソースをクローズ処理する必要がある – エラーは全部SQLExceptionになるので詳細なエラー処理が大変 ※JDBC 4.0で改善 – (前ページのリストには無いが)ResultSetからJava Beanに詰め替えるのが面倒 (補足)上記のJDBCライブラリの不満を解消するための軽量ライブラリや、 O/Rマッピングソフトなどが多数存在します 参照→オープンソースのO/Rマッピングソフト一覧 http://db2.jugem.cc/?eid=2540
  7. 10 別の方法が必要だ! スクリプト言語のように楽に使えるJava VM(JVM)で稼働する言語 使いやすいRDB操作ライブラリ JVM上の言語であるメリット –多くの環境(OS、アプリケーションサーバ)で動作する –Javaの既存資産が使用できる = JDBCドライバ経由でRDBに接続できる

    新しいLisp系言語(関数型言語) 動的型 Clojure PythonのJVM上での実装 動的型 Jython JavaScriptのJVM上での実装 動的型 Rhino RubyのJVM上での実装 動的型 JRuby Write Once , Run Anywhereを実現するオブジェクト指向言語 静的型 Java 静的型でありながら簡潔な記述を実現しつつ、関数型言語の 特徴を取り入れた言語 静的型 Scala Javaとの高い親和性を持ちつつ、動的型、関数型プログラミン グといった特徴を持つ言語 動的型 Groovy 特徴 動的型・静的型 言語 (参考) http://matome.naver.jp/odai/2133273419683789401
  8. 11 Groovyだと、とても楽! メインロジックを書くことだけに集中できる! import groovy.sql.Sql sql=Sql.newInstance("jdbc:db2:sample","db2admin","pass","com.ibm.db2.jcc.DB2Driver") sql.eachRow("SELECT * FROM STAFF

    WHERE ID < 100", {println it.ID+": "+it.NAME}) sql.close() > groovy testsql1.groovy 10: Sanders 20: Pernal 30: Marenghi 40: O'Brien 50: Hanes 60: Quigley 70: Rothman 80: James 90: Koonitz importを含めても たったの4行
  9. 13 Groovy (グルービー) Java VM上で稼働する動的言語 –コンパイル不要。スクリプト言語としても使用できる –Javaとの高い親和性 • Javaのソースコードは、ほとんどそのままでGroovyのソースコード •

    既存Javaライブラリは全て(多分)使える • GroovyのコードをJavaから呼び出すのも簡単 –Javaの面倒な部分をうまく補完してくれる • 動的言語、動的型 • コレクション • クロージャ • Groovy Beans : http://groovy.codehaus.org/ Javaプログラマに優しい
  10. 14 (参考)Groovyの周辺技術 - エコシステム アプリケーションフレームワーク – Grails (フルスタックのWEBアプリケーションフレームワーク) • http://grails.org/

    – Gaelyk (Google App Engine用) • http://gaelyk.appspot.com/ – Griffon (GUIアプリケーション作成用) • http://griffon.codehaus.org/ 関連ツール – Gradle (ビルドツール) • http://www.gradle.org/ – IDE • Eclipse, Netbeans, IDEA 等多くのIDEが対応しています • Groovy/Grails Tool Suite (Eclipseベース) • http://grails.org/products/ggts
  11. 15 Groovyの導入 導入は簡単! 1. (前提)Java SDKの導入 2. Groovyホームページからバイナリーパッケージ(ZIP)をダウンロード – http://groovy.codehaus.org/

    – 今回はgroovy-binary-2.1.1.zipをダウンロード (約27MB) 3. 任意のディレクトリにZIPファイルを展開して、環境変数を設定する – JAVA_HOME (JDK導入ディレクトリ) – GROOVY_HOME(Groovy導入ディレクトリ) – GROOVY_HOME以下のbinにPATHを通す 4. コマンドラインから groovy -vコマンド等を実行して確認 (参照) http://groovy.codehaus.org/Installing+Groovy
  12. 16 コンパイル不要 groovyコマンドにファイルを渡せば即実行 ワンライナー – -e でワンライナー実行 – -n で入力されたデータを1行ずつ処理

    (perlやawkのような処理) > groovy -e "name='Groovy';println 'Hello,'+name" Hello, Groovy (参照)Groovy CLI http://groovy.codehaus.org/Groovy+CLI > cat /etc/services | groovy -n -e "if (line =~ /db2/) {println line}" db2c_DB2 50000/tcp 正規表現 name='Groovy' println 'Hello,'+name (hello.groovyファイル) >groovy hello.groovy Hello,Groovy
  13. 18 楽に書くための仕組み 書く量が少なくてすむ –クラス定義無しでも実行可能(スクリプトとして実行可能) –文末のセミコロン、メソッドの呼び出しの括弧、return が省略可能 –プリミティブ型は自動的にラッパー型に変換される(int → Integer) 便利なコレクション操作

    range //-> ['A','B','C'] range[0] //→ 'A' range = 'A'..'C' レンジ map['key1'] //→'abc' map.key2 //→100 map = ['key1':'abc','key2':100] マップ list[0] //→ 1が返る list = [1,2,'ABC'] リスト(配列) 値を取り出す リテラル コレクション アスタリスク(*)でコレクション全体のメソッドをまとめて呼び出す >groovy -e "println (['A','BC','DEF']*.length())" [1, 2, 3]
  14. 19 Java Beanを楽に - Groovy Bean Java Bean Groovy Bean

    class Staff { private int id; private String name; public void setId(int id) { this.id = id; } public int getId() { return this.id; } public void setName(String name) {this.name=name;} public void getName() {return this.name;} } class Staff { int id String name } staff = new Staff("id":100,"name":"KEN") println "${staff.id}:${staff.name}" •左のJava Bean定義と右のGroovy Bean定義は同じ •setter/getterは自動的に生成される •インスタンス.プロパティ名 でgetterとsetterにアクセスできる •x=staff.id は、実際には x=staff.getId() •staff.id=x は、実際には staff.setId(x) 文字列の中で${式}で 式を参照できる
  15. 20 クロージャ クロージャは、メソッド(関数)を変数に保存する仕組み(メソッドのポインタ) –クロージャの定義も使用も簡単 def hello = { println "こんにちは!"

    } hello() 挨拶をするクロージャを作成して、変数helloに入れる 変数に格納したクロージャを実行する 引数を持つクロージャを定義可能 (->の直前に仮引数を書く) def hello2 = {msg -> println "こんにちは, ${msg}さん!"} hello2('田中') msgが仮引数。複数ある場合はカンマで区切る 仮引数が1つの場合は省略可能 def hello3 = {println "こんにちは, ${it}さん!"} hello3('山田') 省略した場合、itが引数名
  16. 21 ループ処理とクロージャ コレクションは、保持する要素毎にeachメソッドに与えられたクロージャを実行 する機能を持つ >groovy -e "[1,2].each({v-> println v})" 1

    2 >groovy -e " ['a','b','c'].each {println it.toUpperCase()}" A B C コレクション クロージャ コレクションに含まれる値ごとに、クロージャが呼び出される
  17. 22 動的型付け (dynamic typing) 変数定義時に型を書かなくて良い (defキーワード) –書くことも出来る 実行時に型が決定する=動的型付け –Javaで実現する場合は複数のクラスで共通するメソッドを定義したインター フェースを作成し、それを各クラスで実装(implements)する必要がある

    class Cat { void bark() {println "ニャー!"}; } class Dog { void bark() {println "ワン!"}; } def animal if (new Random().nextInt(2) == 0) { animal = new Dog() } else { animal = new Cat() } animal.bark() class Person { def name int age } 1/2の確立でどちらか のインスタンスが作成 されるので実行する までanimalの型は分 からない
  18. 24 GroovyとJavaのシームレスな連係 Groovy言語 - Java言語の間でシームレスに連係できる Groovyから既存Javaクラスを呼び出す – Javaと同じようにimport するだけで、Groovyの中から使用可能 •

    例)import javax.swing.* • 例)def currentDate = new java.util.Date() JavaからGroovyで作成したプログラムを呼び出す – groovycでJavaバイトコードに変換される 1. groovycコマンドで*.groovyをコンパイルすると*.classが作成される 2. 上記classとembeddable/groovy-all-2.1.1.jarをCLASSPATHに追加 3. Java言語から、JavaのクラスとしてGroovyのクラスを呼び出す
  19. 26 GroovyからRDBに接続する (Groovy SQL) JDBCライブラリをそのまま使用する事もできますが... –JDBCをGroovy風に活用できるように、ラップしたgoorvy.sql パッケージ "Groovy SQL"がお勧め 接続(準備)方法(これだけ!)

    –実行時にJDBCドライバがCLASSPATHに含まれている必要があります groovy.sql.Sqlクラス –データベースそのもの(インスタンス)を表す • JDBCのConnectionとStatementを兼ねたようなクラス import groovy.sql.Sql sql = Sql.newInstance ("jdbc:db2://localhost:50000/sample","db2admin","pass","com.ibm.db2.jcc.DB2Driver") ※スクリプトで実行する場合 def を省略できます(バインド変数)
  20. 27 読み取り処理 Sql.rowsは、SELECT実行の結果セットをリストで返す staffList = sql.rows("SELECT * FROM STAFF WHERE

    ID < 100") staffList.each {row-> println row.ID+": "+row.NAME} リストのeachは引数にクロージャを取る リストの1要素毎にクロージャが呼び出される •クロージャは1つの引数を取る(引数はrowという名前にした) •リストの要素が1つずつrowに入ってクロージャが呼び出される sql.eachRow("SELECT * ...",{row -> println row.ID+": "+row.NAME}) Sql.eachRowは、引数にクロージャを取り、結果を1行づつクロージャに渡す –rowsでの結果取得 + リストのeach処理をまとめたもの –クロージャの引数が1つの場合は、引き数名を省略してitで代替できるので... sql.eachRow("SELECT * ...",{println it.ID+": "+it.NAME})
  21. 28 更新処理 INSERT,UPDATE,DELETE – Sql.executeUpdate メソッドで呼び出す – 戻り値は更新対象となった行数 INSERT時に自動生成された列の値を得たい場合はSql.executeInsertを使用 –

    戻り値はList型 id=1; str="UPDATED!" delete = sql.executeUpdate "DELETE FROM TEST2" insert = sql.executeUpdate "INSERT INTO TEST2 VALUES (1,'ABC'),(2,'DEF')" update = sql.executeUpdate "UPDATE TEST2 SET STRING=? WHERE ID =?",[str,id] println "DELETE COUNT=${delete}, INSERT COUNT=${insert}, UPDATE COUNT=${update}" > groovy testsql9.groovy DELETE COUNT=2, INSERT COUNT=2, UPDATE COUNT=1 プリペアード・ステートメントの使用(後述)
  22. 29 プリペアード・ステートメント (Prepared Statement) SQLを実行するメソッドの引数にプレースホルダ(?)を含むSQL文と、?に入れ る値を与えるとプリペアード・ステートメントとして実行される プリペアード・ステートメントを使うメリット –速度向上 (※次ページの補足参照) –SQLインジェクション対策

    select="SELECT * FROM STAFF WHERE JOB=? AND YEARS > ?" sql.eachRow (select,['Sales',7]) { println "${it.NAME} (JOB=${it.JOB},YEARS=${it.YEARS})" } insert="INSERT INTO TEST3(ID,NAME,JOB,YEARS) VALUES (?,?,?,?)" sql.execute(insert,[100,'KEN','Mgr',10]) sql.execute(insert,[101,'JOE','Sales',5])
  23. 30 (補足) Groovy SQLのプリペアード・ステートメント実装について Groovy SQLではSQL実行のメソッドがオーバーロードされており、引数にリス トがある場合にPREPARE(getPreparedStatement)するようになっている 例) query()メソッドの場合 (※Groovyソースコードからの一部抜粋です)

    public void query(String sql, Closure closure) throws SQLException { Statement statement = getStatement(connection, sql); try { results = statement.executeQuery(sql); closure.call(results); public void query(String sql, List<Object> params, Closure closure) throws SQLException { PreparedStatement statement = null; try { statement = getPreparedStatement(connection, sql, params); results = statement.executeQuery(); closure.call(results); 引数にリストが無 い場合は、SQLを 直接実行している 引数にリストを含 む場合は、 PREPAREしてか ら実行している Groovy 2.1.1のソースアーカイブから以下のファイルを参照、一部抜粋 groovy-2.1.1¥subprojects¥groovy-sql¥src¥main¥java¥groovy¥sql¥Sql.java つまり、?を含むSQL文を繰り返し実行した場合、毎回PREPAREされるため、 PREPAREの回数を減らす用途には使えない (...と思います) –ただし、RDBの多くは作成した実行計画をキャッシュするため、実行速度の 向上が見込める可能性は高い
  24. 31 トランザクション制御 Sql.executeやexecuteUpdate等を実行した場合、自動コミットされる Sql.withTransactionを使うとトランザクションが制御できる –渡されたクロージャを1つのトランザクション内で実行する –トランザクション途中で実行に失敗すると... • 自動的にROLLBACKされる • Exceptionがthrowされる

    Sql.commitやSql.rollbackを使い手動でトランザクション制御も可能 try { sql.withTransaction { sql.execute "INSERT INTO TEST2 VALUES (...)" sql.execute "INSERT INTO TEST2 VALUES (...)" } } catch (e) { println "$e" } トランザクションの実行に失敗した場合、 Exceptionが発生してcatch内に入るが、 その時点でROLLBACKされている
  25. 32 バッチ(一括)更新 Sql.withBatch を使用することでバッチ更新が可能 –内部的ではJDBCのexecuteBatchが実行される –戻り値は更新行数のリスト –同じSQLを繰り返す場合は以下のように書ける def counts =

    sql.withBatch { it.addBatch "DELETE FROM tab1 WHERE ..." it.addBatch "INSERT INTO tab1 VALUES (...)" it.addBatch "INSERT INTO tab1 VALUES (...)" } println "更新行数のリスト = ${counts}" SQLをまとめて実行 def counts = sql.withBatch('INSERT INTO T2(ID,NAME) VALUES(?, ?)'){ it.addBatch([1,"KEN"]) it.addBatch([2,"TARO"]) }
  26. 33 DDL、ストアドプロシージャ DDL – Sql.executeメソッドで呼び出す ストアドプロシージャ – Sql.callメソッドで呼び出す • 戻り値は更新行数、もしくは何も更新しない場合はゼロ

    ※APIドキュメントには上記のように記載されているのですが、DB2で以下の例だと-1が返ります ret=sql.call "CALL ADMIN_CMD('REORG TABLE EMPLOYEE')" println "ret=${ret}" sql.execute (''' CREATE TABLE STAFF ( ID SMALLINT NOT NULL , NAME VARCHAR(9) , JOB CHAR(5) , YEARS SMALLINT , SALARY DECIMAL(7,2) )''') クォーテーション3つでヒアドキュメント
  27. 34 Groovy(動的言語)でのRDBプログラミングのメリット RDB設計変更時の影響が少ない import groovy.sql.Sql class Staff { def ID

    def NAME def SALARY } def getHighPaidStaffs (salary) { sql = Sql.newInstance("jdbc:db2:sample","","","com.ibm.db2.jcc.DB2Driver") def staffs = [] sql.eachRow("SELECT ID,NAME,SALARY FROM STAFF WHERE SALARY > ${salary}"){ staffs << new Staff(it.toRowResult()) } return staffs } //スタッフを取得し、画面に表示 staffs = getHighPaidStaffs(90000) staffs.each { println "${it.ID}:${it.NAME}:${it.SALARY}" } //CLASS名を表示 println "staffs: "+staffs.class println "staff: "+staffs[0].class println "ID: "+staffs[0].ID.class println "NAME: "+staffs[0].NAME.class println "SALARY: "+staffs[0].SALARY.class •クラス(Groovy Beans)定義。この時点では型を指定していない •引数より高いSALARY のスタッフ一覧をDBか ら取得する関数 •関数内に型の情報が 無い 下記はgroovy user MLから情報を頂きました http://groovy.329449.n5.nabble.com/Mapping-GroovyResultSet-to-POGOs-td368903.html 結果セットからStaffオブジ ェクトを作成し、staffsコレ クションに詰める > groovy testsql5.groovy 10:Sanders:98357.50 140:Fraye:91150.00 210:Lu:90010.00 staffs: class java.util.ArrayList staff: class Staff ID: class java.lang.Integer NAME: class java.lang.String SALARY: class java.math.BigDecimal •取得したデー タは、RDB定 義と同じ型を持 っている •SALARYが 90,000以上は 3名
  28. 36 Groovyのデメリット? 静的型チェック(コンパイル)が無いのが不安... – その分便利に記述できるので、メリットであり、デメリットでもある – 静的型チェック機能 (@TypeCheckedアノテーション) で個別にチェックを強化可能 Javaより遅くなるのでは?

    – (一般的には)Javaと比べると遅くなります – 動的呼び出しの最適化:Java 7のinvokedynamicに対応 (設定が必要) 遅さが問題になる? – RDBプログラミングの場合、SQLを実行している時間がとても長いため、その他のオーバーヘ ッドは問題にならないケースが多い – 他と比べた速度が問題ではなく、用途に適した速度が出るかが問題 どうしても速度に満足できない場合 – Javaで書いてGroovyから呼び出す (クラス単位) – @CompileStaticアノテーションで静的コンパイル (メソッド単位) (参考) 2012年10月 G*ワークショップ「Vert.x+JavaOne+Groovy2.0なG*」での上原潤二さんの資料 「Groovy2.0 の新機能 」 ※@CompileStaticの有無による速度比較や、注意点、Java 7対応についての記載があります http://www.slideshare.net/uehaj/new-feature-of-groovy20-gworkshop
  29. 37 groovyコマンドの起動が遅い? スクリプト用途で使おうとすると、groovyコマンド(インタプリタ)自体の起動時間が長い – JVMの起動と、Groovy(jar)のロードとチェックに時間が掛かるため GroovyServで解決 – Groovy本体を事前に起動しておく仕組み(常駐させる) • http://kobo.github.com/groovyserv/

    • バイナリをダウンロードして展開し、binディレクトリにPATHを通すだけ ※図は以下より引用 http://www.infoq.com/jp/articles/groovyserv GroovyServを使って実行 – groovyコマンドの代わりにgroovyclientコマンドを 使用するだけ • groovyserverが自動起動される – groovyserverを停止する場合は-k > groovyserver -k ※Windows環境の場合はgroovyserver.batのコンソ ールウィンドウを閉じる
  30. 40 情報リソース① Groovy関連 Groovy言語ホームページ – ダウンロード、マニュアルなど、情報が充実しています – http://groovy.codehaus.org/ – 一部のページは日本語化されています

    • http://groovy.codehaus.org/Japanese+Home – Javaとの違い • http://groovy.codehaus.org/Japanese+Differences+from+Java 日本 Grails/Groovy ユーザーグループ (JGGUG) – Grails/Groovy技術全般に関するユーザーグループ。精力的にワークショップ等を開催されているようです • (実はこのCLUB DB2と同じ日・同じ時刻にワークショップが...) – http://www.jggug.org/ Grails Japan ! – GrailsやGroovyの最新情報多数 – http://grails.jp/ 「プログラミング Groovy」(書籍) <= お勧めです! – 丁寧な解説で分かりやすいGroovy解説書籍 – http://gihyo.jp/book/2011/978-4-7741-4727-7 – ISBN: 978-4-7741-4727-7
  31. 41 情報リソース② その他 Java 7 invokedynamicの概要 –http://www.slideshare.net/miyakawataku/java-7-invokedynamic-in-a- nutshell 無料で使える DB2

    Express-C –http://www.ibm.com/developerworks/jp/offers/db2express-c/ –Windows, Linux, Mac OS X, Solaris (x86-64) に対応