Skip to content

Instantly share code, notes, and snippets.

@seriwb
Created March 28, 2015 15:25
Show Gist options
  • Save seriwb/aa6626e54d3aa2bf4842 to your computer and use it in GitHub Desktop.
Save seriwb/aa6626e54d3aa2bf4842 to your computer and use it in GitHub Desktop.
update table by newer data of csv format.
package sql
import groovy.sql.Sql
/**
* テーブルデータ入れ替えツール
*
* 既存のテーブルデータに対して、PKを使って新しいテーブルデータを上書きする。
* 既存テーブルのみに存在するレコードには何も行わない。
* 新規テーブルにのみ存在するレコードは別ファイルにinsert文を作成する。
* それ以外のケース(2件ヒットなど)は、ファイルに該当PKのselect文を作成する。
*
* ※10000レコードの実施に約10分くらいかかる。
*
* @author seri
*/
class UpdateTable {
/**
* 別にmainじゃなくてもいいけど、今回はmain使ってみた。
* 読み込むデータは、以下の形式のCSVファイル
* ・文字列はダブルクォート囲み:"some string"
* ・数値はそのまま:1.0000
* ・カンマ区切り:"hoge","foo","bar",3,0.444
*
* @param args 0:新規テーブルデータ ファイル名は"テーブル名.csv"にすること
*/
static main(args) {
// 更新元のテーブルデータ
def file = new File(args[0])
def table = file.name.replaceAll(/\.csv\z/, "") // 更新するテーブル名
println "対象テーブルのデータをUPDATEします:$table" // ${table}と書いても同じ
// // update
// def updatefile = new FileWriter("src/sql/update.sql")
// insert
def insertfile = new FileWriter("src/sql/insert.sql")
// error
def errorfile = new FileWriter("src/sql/error.sql")
// JDBCの設定
def db = Sql.newInstance(
'jdbc:db2://localhost:50000/LOCAL', // DB接続文字列
'username', // ユーザ名
'password', // パスワード
'com.ibm.db2.jcc.DB2Driver' // JDBCドライバ(これはDB2の例)
)
// 対象のテーブル名のカラム名一覧を保持しているファイル(DDLの順)
def columnlistFile = new File("src/sql/${table}.txt")
def column = []
// カラムごとに取り込み
columnlistFile.eachLine { line ->
line.splitEachLine(",") {
it.each { tmp ->
column << tmp.substring(1, tmp.size()-1)
}
}
}
// where句を作成
def whereStr = createWhere(column)
def whereKey = column[0..4] // 先頭5カラムがユニークなPK(とする)
println whereKey
// DBにレコードがあればupdate、なければinsert文を作成。2件以上はerror
// 実行数カウンター一覧
def updatecounter = 0
def insertcounter = 0
def errorcounter = 0
def totalcounter = 0
// 1行ずつ実施
file.eachLine { line ->
line.splitEachLine(",") {
// 文字列のみになったカラム値("を除去)
def searchValue = []
// SQLで参照できる「文字列」になったカラム値(Stringはシングルクォートで囲む)
def searchValueSingle = []
it.each { tmp ->
if (tmp != null && tmp[0] == "\"") {
searchValue << tmp.substring(1, tmp.size()-1)
searchValueSingle << "'" + tmp.substring(1, tmp.size()-1) + "'"
} else {
searchValue << tmp
searchValueSingle << tmp
}
}
// println searchValue
// println searchValueSingle
def count = db.firstRow("select count(*) as \"count\" from ${table}" +
whereStr, searchValue[0..4])
// 1件ならupdate、0件ならinsert文作成、それ以外はselect文を作成
if (count.get("count") == 1) {
def updatestr =
"update ${table} set " +
connectUpdateStr(column, searchValueSingle) +
connectWhereStr(column, searchValueSingle[0..4])
// println updatestr
db.execute(updatestr)
// updatefile.append(updatestr + ";\n");
updatecounter++
}
else if (count.get("count") == 0) {
def insertstr =
"insert into ${table} (" + column.join(', ') +
") values (" + searchValueSingle.join(', ') + ");"
insertfile.append(insertstr + "\n")
insertcounter++
}
else {
def errorstr =
"select * from ${table}" +
connectWhereStr(column, searchValueSingle[0..4]) + ";"
errorfile.append(errorstr + "\n")
errorcounter++
}
if (totalcounter++ % 10000 == 0) {
println totalcounter
}
}
}
println "update:$updatecounter"
println "insert:$insertcounter"
println "error:$errorcounter"
// updatefile.close()
insertfile.close()
errorfile.close()
db.close()
}
/**
* バインド変数つきのwhere句を作成して返す
*
* @param key カラム値
* @return
*/
static createWhere(key) {
def sb = new StringBuilder()
sb.append(" where ")
for (i in 0..3) {
sb.append(key[i]).append(" = ?").append(" and ")
}
sb.append(key[4]).append(" = ?")
sb.toString()
}
/**
* バインド変数なしのwhere句を作成して返す
*
* @param key カラム名
* @param value 検索値
* @return where句
*/
static connectWhereStr(key, value) {
def sb = new StringBuilder()
sb.append(" where ")
for (i in 0..3) {
sb.append(key[i]).append(" = ").append(value[i]).append(" and ")
}
sb.append(key[4]).append(" = ").append(value[4])
sb.toString()
}
/**
* バインド変数なしのupdate文のset句を作成して返す
*
* @param key カラム名
* @param value 更新値
* @return update文のset句
*/
static connectUpdateStr(key, value) {
def str = new StringBuilder()
for (i in 5..key.size()-2) {
str.append(key[i]).append(" = ").append(value[i]).append(", ")
}
str.append(key[key.size()-1]).append(" = ").append(value[key.size()-1])
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment