GroovySql：如何用Arraylist变量更新表 [英] GroovySql: How to update a table with Arraylist variables

问题描述

我正在编写一个GroovySQL脚本，它有三个不同的Arraylist变量。 A1 [1,2,3]，A2 [4,5,6]，A3 [7,8,9]。

我想更新表格，表中三列的三行更新为

数据应该是（按行排列）


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]）
  

我管理知道如何更新一行。我会很感激任何人都可以提供任何提示或想法。 解决方案

你需要做的是创建一个2d数组和转置它通过循环执行更新查询。

以下是脚本：

  //定义你提到的数据
 def A1 = [1,2,3] 
 def A2 = [4,5,6] 
 def A3 = [ 7,8,9] 
 
 //在这里查找你想要更新的列名
 // column_0可以是你的where子句
 def columnNames = ['column_0 '，'column_1'，'column_2'] 
 
 //上述数据的二维数组
 def matrix = [A1，A2，A3] 
 
 //转置它改变行&列
 def transMatrix =（0 ..<（matrix * .size（）.max（）））。collect {
 matrix * .getAt（it）
} 
 printlnTranslated matrix is ==> $ transMatrix
 
 
 
 def sql = Sql.newInstance（jdbc：mysql：// localhost：3306 / words，测试，测试，com.mysql.jdbc.Driver）
 
 //循环转置矩阵，
 //构建查询
 //将它传递给executeUpdte 
 transMatrix.each {rowData  - > 
 def query =update word set $ {columnNames [1]} ='$ {rowData [1]}'，$ {columnNames [2]} ='$ {rowData [2]}'其中$ {columnNames生成的查询是：$ {query}
 sql.executeUpdate（query）
} 
 <$ {rowData [0]}'
 println / code> 

您可能会看到如何在下面构建查询：



点数 tim_yates for 转置矩阵

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].

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.

Here is the script:

//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:

Credits to tim_yates for transpose matrix

这篇关于GroovySql：如何用Arraylist变量更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！