Inserting Array list into HANA database

To insert array lists into a HANA database, you can use JDBC statements with HANA's ARRAY function. This approach allows you to store multiple values as array columns in your database table.

Example

The following code demonstrates how to insert array data into a HANA database table ?

Integer[][] myarray = { {1}, {1,2}, {1,2,3,4,5} };
String test = "Insert Arrays";
stopWatch.start(test);
myDBconn.setAutoCommit(false);
Statement stmt = myDBconn.createStatement();
stmt.execute("TRUNCATE TABLE Schema.Table1");

// Running a loop over our array of arrays
for (int i = 0 ; i < (myarray.length); i++) {
    int curr_length = myarray[i].length;
    String arrayFunction = "ARRAY (";
    
    for (int j = 0; j < (curr_length); j++){
        arrayFunction = arrayFunction.concat(myarray[i][j].toString());
        // add comma if this is not the last element
        if (j < (curr_length - 1)){
            arrayFunction = arrayFunction.concat(", ");
        }
    }
    arrayFunction = arrayFunction + ")";
    
    // arrayFunction will look like: ARRAY(1, 2, 3, 4, 5)
    String insCMD = "INSERT INTO Table1 (id, Value) "
        + " 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;

This code creates a 2D array with different sub-arrays, then iterates through each sub-array to build an SQL INSERT statement using HANA's ARRAY function. Each array is inserted as a single row with an ID and the array values.

Output

When you run a SELECT statement on the table after insertion, the output will be ?

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

Conclusion

Using HANA's ARRAY function with JDBC allows you to efficiently insert array data into database tables, making it easy to store and retrieve structured collections of values.