使用JAVA在HANA中插入数组

Question

我有对象的arraylist，并尝试将列表插入HANA。所以我的插入代码看起来像

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();

这里
listofdata.get(i).get_elective()

返回一个整数数组。

但这无法正常工作。根据我的程序每次都会调用ARRAY函数，但是为什么不将其插入HANA数据库中呢，所以过了一会儿我才知道我必须将JAVA
Array转换为HANA Array。如何转换Java Array到HANA阵列。任何帮助表示赞赏。

Answer 1

@RKR好的，这里有您的示例：

     /*
     * 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注入。

当我运行代码时，将输出以下内容：

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返回：

ID  C1           
0   1            
1   1, 2         
2   1, 2, 3, 4, 5
3   1, 2         
4   1, 2, 3