Created
October 10, 2010 07:24
-
-
Save nobusue/619054 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// g100pon #44 DBとテーブル名を引数指定して標準入力から読み取ったCSV/TSVデータをinsert | |
import groovy.sql.Sql | |
import groovy.grape.Grape | |
if(args.size()<2){ | |
println "Usage: groovy Text2DB.groovy <DB> <TABLE> < <CSV|TSV>" | |
System.exit(1) | |
} | |
def dbName = args[0] | |
def tableName = args[1] | |
println "[DB]$dbName, [TABLE]$tableName" | |
// JDBCドライバの取得とGroovySQLクラスのセットアップ | |
Grape.grab(group:'org.apache.derby', module:'derby', version:'[10.5.3,)', | |
classLoader:this.class.classLoader.rootLoader) | |
def sql = Sql.newInstance("jdbc:derby:memory:${dbName};create=true", | |
'user', 'password', 'org.apache.derby.jdbc.EmbeddedDriver') | |
// テーブル作成(簡単のため。不要であれば削除。) | |
sql.execute("""create table ${tableName} ( | |
id integer not null primary key, | |
firstname varchar(20), | |
lastname varchar(20), | |
location_id integer, | |
location_name varchar(30) | |
)""".toString()) | |
// INSERT文の定義 | |
def insertStmt = """ | |
insert into ${tableName} | |
values (?, ?, ?, ?, ?) | |
""" | |
// 標準入力からテキストを読み込んでリストに変換 | |
def data = parseText(System.in.text) | |
// INSERT実行 | |
data.each{ | |
sql.execute insertStmt, it | |
} | |
// 実行結果確認 | |
sql.eachRow("select * from ${tableName}".toString()){ println it} | |
// 簡易CSV/TSVパーサー | |
def parseText(String text){ | |
def separator = ',' | |
if(text.contains('\t')) separator = '\t' | |
def data = [] | |
text.eachLine{ | |
data << it.tokenize(separator) | |
} | |
return data | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment