问题描述
我有一个对象的数组列表,并试图将列表插入HANA。所以我的插入代码看起来像
I am having an arraylist of objects and trying to insert the list into HANA. So my insert code looks like
PreparedStatement stmt = conn
.prepareStatement("INSERT INTO SCHEMA.TABLE VALUES"
+ " (?, ?, ?, ?, ?, ?, ?, ARRAY("+"1,2,3"+")");
for (int i = 1; i <= ITERATION_MAX; i++) {
stmt.setInt(1, listofdata.get(i).get_id());
stmt.setInt(2, listofdata.get(i).get_name());
stmt.setInt(3, listofdata.get(i).get_place());
stmt.setInt(4, listofdata.get(i).get_year());
stmt.setInt(5, listofdata.get(i).get_day());
stmt.setInt(6, listofdata.get(i).get_rollno());
stmt.setInt(7, listofdata.get(i).get_main_subject());
stmt.setArray(8, listofdata.get(i).get_elective());
stmt.addBatch();
}
stmt.executeBatch();
$ b listofdata.get(i).get_elective()
返回一个整数数组。
但是这不工作。根据我的程序ARRAY函数每次被调用,但为什么不插入到HANA数据库。一段时间后,我理解,我必须转换JAVA数组到HANA数组。如何将java数组转换为HANA数组。
But this does not work.According to my program ARRAY function is called each time but why does not it inserting into HANA Database.So after a while I understood that I have to convert JAVA Array into HANA Array.How can I convert a java Array to HANA Array.Any help is appreciated.
推荐答案
@RKR确定,这里你有你的例子:
@RKR ok, here you have your example:
/*
* We're goin to insert several arrays into the HANA table,
* with different lengths
*
* let's assume we already got a table like this
* CREATE COLUMN TABLE T3 ( ID INT PRIMARY KEY, C1 INT ARRAY );
*/
Integer[][] myarr ={ {1}, {1,2}, {1,2,3,4,5}, {1,2}, {1,2,3} };
String testSection = "Insert Arrays one by one";
stopWatch.start(testSection);
myDBconn.setAutoCommit(false);
Statement stmt = myDBconn.createStatement();
stmt = myDBconn.createStatement();
stmt.execute("TRUNCATE TABLE DEVDUDE.T3");
// loop over our array of arrays and visit each once
for (int i = 0 ; i < (myarr.length); i++) {
int curr_length = myarr[i].length;
String arrayFunction = "ARRAY (";
for (int j = 0; j < (curr_length); j++){
arrayFunction = arrayFunction.concat(myarr[i][j].toString()) ;
// add comma if this is not the last element
if (j < (curr_length - 1)){
arrayFunction = arrayFunction.concat(", ") ;
}
}
arrayFunction = arrayFunction + ")" ;
// now the arrayFunction should loook like this
// ARRAY ( ..., .... ,... )
String insCMD = "INSERT INTO T3 (id, c1) "
+ " VALUES (" + i + ", "
+ arrayFunction
+ " ) ";
System.out.println(insCMD);
int affectedRows = stmt.executeUpdate(insCMD);
System.out.println("Loop round " + i
+ ", last affected row count " + affectedRows);
}
myDBconn.commit();
stmt.close();
stmt = null;
不,此代码不清除INSERT语句的输入但是这样做是为了避免SQL注入。
And no, this code does not sanitise the input to the INSERT statement but that has to be done in order to avoid SQL injection.
当我运行代码,这是什么打印:
When I run the code this is what gets printed:
INSERT INTO T3 (id, c1) VALUES (0, ARRAY (1) )
Loop round 0, last affected row count 1
INSERT INTO T3 (id, c1) VALUES (1, ARRAY (1, 2) )
Loop round 1, last affected row count 1
INSERT INTO T3 (id, c1) VALUES (2, ARRAY (1, 2, 3, 4, 5) )
Loop round 2, last affected row count 1
INSERT INTO T3 (id, c1) VALUES (3, ARRAY (1, 2) )
Loop round 3, last affected row count 1
INSERT INTO T3 (id, c1) VALUES (4, ARRAY (1, 2, 3) )
Loop round 4, last affected row count 1
表上的SELECT返回:
And a SELECT on the table returns:
ID C1
0 1
1 1, 2
2 1, 2, 3, 4, 5
3 1, 2
4 1, 2, 3
这篇关于使用JAVA在HANA中插入数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!