No Programming, No Life

プログラミング関連の話題や雑記

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)

その1:SQLにすべて直に書く。

db.execute '''
    INSERT INTO テーブル名 ( 'column1', 'column2', … )
                    VALUES ( 'value1', 'value2', … )
'''

その2:Prepared Statememtを利用する。?は ' で囲んではいけないらしい。Groovyがうまいことやってくれる。

db.execute '''
    INSERT INTO テーブル名 ( 'column1', 'column2', … )
                    VALUES ( ?, ?, … )
''', ['value1', 'value2']

その3:GStringを利用する。

db.execute """
    INSERT INTO テーブル名 ( 'column1', 'column2', … )
                    VALUES ( ${value1}, ${value2}, … )
"""
データの更新(UPDATE)

その1:SQLにすべて直に書く。(INSERTと同様のため省略)

その2:Prepared Statememtを利用する。(INSERTと同様のため省略)

その3:GStringを利用する。

db.execute '''
    UPDATE テーブル名 SET column1 = ${value1}
'''

db#xecuteの代わりに、db#executeUpdateを利用することもできる。db#xecuteは戻り値がResultSetを返したかどうかのBoolean値であるのに対して、db#executeUpdateはUPDATE文によって更新された件数を返すみたい。

データの削除(DELETE)

その1:SQLにすべて直に書く。(INSERTと同様のため省略)

その2:Prepared Statememtを利用する。(INSERTと同様のため省略)

その3:GStringを利用する。

db.execute '''
    DELETE FROM テーブル名 WHERE column1 = ${value1}
'''
groovy.sql.Sqlを利用したデータベースからのデータ更新系メソッド
戻り型 メソッド名 パラメータ
boolean execute String statment
boolean execute String statment, List values
boolean execute GString prepStmt
int executeUpdate String statment
int executeUpdate String statment, List values
int executeUpdate GString prepStmt

データの取得

取得するメソッドはバリエーションが豊富。

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にも書いてあります。

Groovy ASTノードとSQLの対応
ASTノード 対応するSQL
&& and
¦¦ or
== =
その他の演算子 それ自身(リテラル)
it.propertyname プロパティ名
定数式 ? (式はパラメータリストに追加される)


実際に試してみると分かるのだが、クロージャの中にかける式は制限が結構あるみたい。たとえばLIKE文などはうまく作る方法がないし、GStringを使った構築もうまくできなかった。ソースを確認してないため、調査不足ではあるが…本当はできるのかもしれない。*3

結果取得(each, rows)

そして、eachやrowsを呼び出すと実際に取得できる。

// eachはgroovy.sql.SqlのeachRowと同じ動き
dataSet.each{
    println "[${row.column1}] [${row.column2}] …"
}

// rowsはgroovy.sql.Sqlのrowsと同じ動き
List rows = dataSet.rows()
println "result size is ${rows.size()}"
rows.each{ println "[${it[0]}] [${it.column2}] …" }

*1:第10章 P276〜

*2:INSERTに関しては別の便利な方法もある、もうちょっと後で書く。

*3:groovy.sql.SqlWhereVisitor, groovy.sql.SqlOrderByVisitorというクラスがあり、DataSet#getSqlWhereVisitor(), DataSet#getSqlOrderByVisitor() で取得できるようである。挙動を変えたりできるのかもしれない。