Last active
February 22, 2016 00:41
-
-
Save sizer/0e4d09e8a7c2a07e0abc to your computer and use it in GitHub Desktop.
creating update queries.
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
@Grapes([ | |
@Grab(group='postgresql', module='postgresql', version='9.0-801.jdbc4'), | |
@Grab(group='com.ibm.icu', module='icu4j', version='56.1'), | |
@GrabConfig(systemClassLoader=true, initContextClassLoader=true) | |
]) | |
import com.ibm.icu.text.Transliterator | |
//------------- script -----------------// | |
println "-----start-----" + new Date() | |
def lineSeparator = System.properties['line.separator'] | |
def resultFile = new File("C:\\dev\\groovy\\updateLoginIdResult.sql") | |
def db = groovy.sql.Sql.newInstance( | |
"jdbc:postgresql://localhost:5432/databaseName", | |
"postgres", | |
"postgres1", | |
"org.postgresql.Driver" | |
) | |
def query = | |
""" | |
select p.person_id as id, text.item_text as kana | |
from person p | |
inner join text | |
on text.person_id = p.person_id and text.item_id = 103 | |
order by p.person_id; | |
""" | |
resultFile.text = "" | |
db.eachRow(query){ | |
row -> | |
latin = getLatin(row.kana) | |
resultFile.append(getQuery(row.id, latin) + lineSeparator) | |
} | |
println "-----finished-----" + new Date() | |
//--------------- functions -----------------// | |
def String getQuery(userId, login){ | |
def query = "update user set login_id = \'%s\', update_timestamp = current_timestamp, update_function = 'FunctionName' where user_id = %s;" | |
def f = new Formatter() | |
f.format(query, login, userId) | |
} | |
def String getLatin(katakana){ | |
tmp = Transliterator.getInstance("Katakana-Latin").transliterate(katakana) | |
//TODO キタノ シンノスケ->kitano shin'nosukeになるため'を抜いているが、icu4jの設定等で解決できるならそっちでやる | |
tmp.replace(' ', '.').replace('\'', '') | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment