GroovySqlDerby.groovy

// g100pon #98 DerbyでGroovySQL

import groovy.sql.Sql
import groovy.grape.Grape

Grape.grab(group:'org.apache.derby', module:'derby', version:'[10.5.3,)',
    classLoader:this.class.classLoader.rootLoader)

def sql = Sql.newInstance('jdbc:derby:memory:testdb;create=true',
    'user', 'password', 'org.apache.derby.jdbc.EmbeddedDriver')

// 既にPERSONテーブルがあれば削除
try {
   sql.execute("drop table PERSON")
} catch(Exception e){}

// PERSONテーブル作成
sql.execute('''create table PERSON (
    id integer not null primary key,
    firstname varchar(20),
    lastname varchar(20),
    location_id integer,
    location_name varchar(30)
)''')

// executeでSQLを直接実行
sql.execute('''
    insert into PERSON (id,firstname,lastname,location_id,location_name)
    values (1,'Guillaume','Laforge',10,'Paris')
''')
sql.execute('''
    insert into PERSON (id,firstname,lastname,location_id,location_name)
    values (2,'Dierk','Konig',20,'Zurich')
''')
sql.execute('''
    insert into PERSON (id,firstname,lastname,location_id,location_name)
    values (3,'Paul','King',30,'Brisbane')
''')

// eachRowで結果のリストを取得
sql.eachRow('select * from PERSON'){ println it}
sql.execute('delete from PERSON')

// placeholderを利用するパターン
// 内部的にはPrepared Statementに変換されている
def personInsert = '''
    insert into PERSON (id,firstname,lastname,location_id,location_name)
    values (?, ?, ?, ?, ?)
'''
sql.execute personInsert, [1, 'Guillaume', 'Laforge', 10, 'Paris']
sql.execute personInsert, [2, 'Dierk',     'Konig',   20, 'Zurich']
sql.execute personInsert, [3, 'Paul',      'King',    30, 'Brisbane']

sql.eachRow('select * from PERSON'){ println it}
sql.execute('delete from PERSON')

// GStringを利用するパターン
// 内部的にはPrepared Statementに変換されている
def persons = [
    [id:1, first:'Guillaume', last:'Laforge', locid:10, loc:'Paris'],
    [id:2, first:'Dierk',     last:'Konig',   locid:20, loc:'Zurich'],
    [id:3, first:'Paul',      last:'King',    locid:30, loc:'Brisbane']
]
persons.each { p ->
    sql.execute """
    insert into PERSON (id,firstname,lastname,location_id,location_name)
    values (${p.id}, ${p.first}, ${p.last}, ${p.locid}, ${p.loc})
    """
}

// queryでResultSetを直接取得
println ' Person Info '.center(25,'-')
sql.query('select id,firstname,lastname,location_id,location_name from PERSON'){ rs ->
    while(rs.next()){
        println "${rs.getInt(1)}: ${rs.getString(2)} ${rs.getString(3)}"
        println "Location: ${rs.getString(5)}(${rs.getInt(4)})"
        println '-'*25
    }
}