GroovySql:如何使用 Arraylist 变量更新表
我正在尝试编写一个具有三个不同 Arraylist 变量的 GroovySQL 脚本.A1[1,2,3],A2[4,5,6],A3[7,8,9].
I am trying to write an GroovySQL script that will have three different Arraylist variable. A1[1,2,3],A2[4,5,6],A3[7,8,9].
我想更新表格,使表格三列的三行更新为
数据应该是(按行)
R1:1,4,7
R2:2,5,8
R3:3,6,9
I want to update the table such that three rows of the three columns of the table are updates as
Data should be (in row wise)
R1: 1,4,7
R2: 2,5,8
R3: 3,6,9
def sql = Sql.newInstance("jdbc:mysql://localhost:3306/words", "Test",
"test", "com.mysql.jdbc.Driver")
def nid = 1
def newupdate = "hello world"
sql.executeUpdate("update word set spelling = ? where word_id = ?", [ newupdate, nid])
我知道如何更新一行.如果有人可以提供任何提示或想法,我将不胜感激.
I managed know how to update one row. I will be thankful if anyone can give any hints or ideas.
推荐答案
你需要做的就是创建一个二维数组并转置它,然后通过它循环执行更新查询.
All you need to do is create a 2d array and transpose it, then execute the update query by loop thru it.
这是脚本:
//Defined the data that you mentioned
def A1 = [1,2,3]
def A2 = [4,5,6]
def A3 = [7,8,9]
//Chage here your column names that you want to update
//column_0 can be your in your where clause
def columnNames = ['column_0', 'column_1', 'column_2']
//2d array of above data
def matrix = [A1, A2, A3]
//Transpose it to change rows & columns
def transMatrix = (0..<(matrix*.size().max())).collect {
matrix*.getAt(it)
}
println "Transposed matrix is ==> $transMatrix"
def sql = Sql.newInstance("jdbc:mysql://localhost:3306/words", "Test", "test", "com.mysql.jdbc.Driver")
//Loop thru transposed matrix,
//Build the query
//Pass it to executeUpdte
transMatrix.each { rowData ->
def query = "update word set ${columnNames[1]} = '${rowData[1]}', ${columnNames[2]} = '${rowData[2]}' where ${columnNames[0]} = '${rowData[0]}'"
println "Generated query is : ${query}"
sql.executeUpdate(query)
}
您可以在下面看到查询是如何构建的:
You may see how the query is build in below:
感谢 tim_yates 的 转置矩阵
相关文章