search
LoginSignup
14
Help us understand the problem. What are the problem?

More than 5 years have passed since last update.

posted at

updated at

GroovyでのDB処理

DB処理のまとめ。

@GrabConfig(systemClassLoader=true)
@Grapes(
//    @Grab("postgresql:postgresql:9.1-901.jdbc4")
    @Grab("mysql:mysql-connector-java:6.0.5")
)
import groovy.sql.Sql

def dbServer = "localhost"
def dbName = "test"
def dbPort = "3309"
//def url = "jdbc:postgresql://${dbServer}:${dbPort}/${dbName}"
def url = "jdbc:mysql://${dbServer}:${dbPort}/${dbName}"
def user = "yamap"
def password = "yamap"
//def driver = "org.postgresql.Driver"
//def driver = "com.mysql.jdbc.Driver"
def driver = "com.mysql.cj.jdbc.Driver"
def sql = Sql.newInstance(url, user, password, driver)

println "テーブル作成"
sql.execute "create table data (id varchar(50),name varchar(50))"

println "一括で取得".center(30,"-")
// ArrayList<GroovyRowResult>で返ってきます。
def rows = sql.rows("select * from data")
println rows
// 中身取得する時はこんな感じ
println rows[0].name

println "条件指定".center(30,"-")
println sql.rows("select * from data where name = ?", ["yamada"])

println "条件複数指定".center(30,"-")
println sql.rows("select * from data where name = ? and id = ?", ["yamada",1])

println "複数のMapで条件設定".center(30,"-")
println sql.rows("select * from data where name = ?1.piyo and id = ?2.huga", [piyo:"yamada"],[huga:1])

println "こんな置き換えもできる_1".center(30,"-")
println sql.rows("select * from data where name=:piyo", [piyo:"yamada"])

println "こんな置き換えもできる_2".center(30,"-")
class MyClass {
  def hoge = 1
}
println sql.rows("select * from data where name=?2.piyo and id=?1.hoge", new MyClass(), [piyo:"yamada"])

println "一行づつ取得".center(30,"-")
// rowsではできた、Mapとかクラスで指定する事はできないようです。
sql.eachRow("select * from data where name = ?", ["yamada"]) { 
  println it
}

println "先頭行を取得".center(30,"-")
println sql.firstRow("select * from data order by id")

println "挿入1".center(30,"-")
sql.executeUpdate("insert into data (id, name) values (?, ?)", ["99","don takosu"])
println sql.rows("select * from data where id = ?", ["99"])

println "挿入2_Mapで設定".center(30,"-")
def dataSet2 = sql.dataSet('data')
dataSet2.add (
  id:'666',
  name:'takahashi'
)
println sql.rows("select * from data where id = ?", ["666"])

// 削除は挿入と同じ
println "削除1".center(30,"-")
sql.execute("delete from data where id='99' and name='don takosu'")
println sql.rows("select * from data where id = ?", ["99"])

println "削除2".center(30,"-")
sql.executeUpdate("delete from data where id=:piyo and name=:huga",[piyo:"666", huga:"takahashi"])
println sql.rows("select * from data where id = ?", ["666"])

// 参考URL
// http://groovy.codehaus.org/api/groovy/sql/Sql.html
// http://d.hatena.ne.jp/fumokmm/20090505/1241504105

こういうコード書く時「println」だとわかりにくいですよね?
どう書けばわかりやすいでしょう?

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
14
Help us understand the problem. What are the problem?