Groovyでデータベース操作（GroovySQL）
GinAのデータベースの章*1に書かれていることをまとめておこうと思う。
ここに書かれている内容はSQLをある程度理解している方を対象としています。また、JavaでDB操作を行ったことがあると理解が早まります。
DBに接続
まずは、JDBCドライバを使ってDBに接続する。ひとまず簡単な方法はファクトリメソッドを使う方法。groovy.sql.Sql#newInstance() を利用する。戻り値はgroovy.sql.Sqlインスタンス。
def db = Sql.newInstance( 'jdbc:〜〜', // DB接続文字列 'ユーザ名', // ユーザ名 'パスワード', // パスワード 'jdbcドライバクラス' // JDBCドライバ )
データの挿入・更新・削除
データの挿入・更新・削除を実行する方法としては、groovy.sql.Sql#execute(statment)メソッドを利用する。*2
データの挿入(INSERT)
その１：SQLにすべて直に書く。
db.execute ''' INSERT INTO テーブル名 ( 'column1', 'column2', … ) VALUES ( 'value1', 'value2', … ) '''
その２：Prepared Statememtを利用する。?は ' で囲んではいけないらしい。Groovyがうまいことやってくれる。
db.execute ''' INSERT INTO テーブル名 ( 'column1', 'column2', … ) VALUES ( ?, ?, … ) ''', ['value1', 'value2']
その３：GStringを利用する。
db.execute """ INSERT INTO テーブル名 ( 'column1', 'column2', … ) VALUES ( ${value1}, ${value2}, … ) """
データの更新(UPDATE)
その１：SQLにすべて直に書く。(INSERTと同様のため省略）
その２：Prepared Statememtを利用する。(INSERTと同様のため省略）
その３：GStringを利用する。
db.execute ''' UPDATE テーブル名 SET column1 = ${value1} '''
db#xecuteの代わりに、db#executeUpdateを利用することもできる。db#xecuteは戻り値がResultSetを返したかどうかのBoolean値であるのに対して、db#executeUpdateはUPDATE文によって更新された件数を返すみたい。
データの削除(DELETE)
その１：SQLにすべて直に書く。(INSERTと同様のため省略）
その２：Prepared Statememtを利用する。(INSERTと同様のため省略）
その３：GStringを利用する。
db.execute ''' DELETE FROM テーブル名 WHERE column1 = ${value1} '''
データの取得
取得するメソッドはバリエーションが豊富。
queryによるResultSetの取得(query)
db.query('SELECT column1, column2, … FROM テーブル名 WHERE 検索条件) { resultSet -> if (resultSet.next()) { print '[' + resultSet.getString(1) + '] ' println '[' + resultSet.getString('column2') + '] …' }
出力
[value1] [value2] …
：
：
ちょっと不便。
一行ずつ取得(eachRow)
db.eachRow('SELECT column1, column2, … FROM テーブル名 WHERE 検索条件) { row -> println "[${row.column1}] [${row.column2}] …" }
出力
[value1] [value2] …
：
：
すべての行の一括取得(rows)
List rows = db.rows('SELECT column1, column2, … FROM テーブル名 WHERE 検索条件) { row -> println "result size is ${rows.size()}" rows.each{ println "[${it[0]}] [${it.column2}] …" }
出力
result size is 10"
[value1] [value2] …
：
：
groovy.sql.Sqlを利用したデータの取得系メソッド
| 戻り型 | メソッド名 | パラメータ |
|---|---|---|
| void | eachRow | String statement { row -> code } |
| void | eachRow | String prepStmt, List values { row -> code } |
| void | eachRow | GString prepStmt { row -> code } |
| void | query | String statment { resultSet -> code } |
| void | query | String prepStmt, List values { resultSet -> code } |
| void | query | GString prepStmt { resultSet -> code } |
| List | rows | String statment |
| List | rows | String prepStmt, List values |
| Object | firstRow | String statment |
| Object | firstRow | String prepStmt, List vlaues |
データの取得(DataSet編)
groovy.sql.DataSetを利用すると、データの取得や挿入をちょっと便利に操作できる。groovy.sql.DataSetはgroovy.sql.Sqlのサブクラスであり、デコレータでもある。DataSetのインスタンスを簡単に取得するにはgroovy.sql.Sql#dataSetを利用する。
def dataSet = db.dataSet('テーブル名')
簡単にデータ挿入(add)
def dataSet = db.dataSet('table') table.add( column1: 'value1', column2: 'value2', : : )
こんな感じで、Mapにして渡すだけで、データの挿入(INSERT)を行うことが可能。省略された列の値はnullが自動的に挿入される。
簡単にデータ検索
DataSetのインスタンスはPrepared Statememtを内包している。#getSql()を呼び出してみることでその内容を確認することができる。
例えば、DataSetのインスタンスを取得してすぐ、これを試してみると
select * from テーブル名
というSQLが取得できる。
WHERE句, ORDER BY句の構築(findAll, sort)
DataSetのfindAllメソッドを呼び出すと、Prepared Statementに対してクロージャで指定された内容のWHERE句を構築したDataSetを返却してくれる。
例)
def dataSet = db.dataSet('table') assert dataSet.sql == 'select * from table' def dataSet1 = dataSet.findAll{ it.column1 == 'value1' } assert dataSet1.sql == 'select * from table where column1 = ?' def dataSet2 = dataSet.sort{ it.column2 } assert dataSet2.sql == 'select * from table where column1 = ? order by column2'
findAllやsortを呼び出しても実際にSQLが実行されるわけではないことに注意する。
これらのクロージャに書いた式は以下のように解釈され、変換される。
DataSet実装はクロージャコードのGroovy内部表現を取得します。この内部表現は「Abstract Syntax Tree」(AST)と呼ばれ、Grovyパーサーによって生成されます(Visitorパターンを使って)。ASTを辿ることで、DataSet実装は各ASTノードと等価なSQL文を生成します。
とGinA P294にも書いてあります。
