Resultset into 2d array

Question

I just did a mysql query, which was put into a resultset called result.

Now i want it to go into a 2d object, which i can use to show the query result later on.

I am not quite sure how to do it, if i should make a while loop and then put into an array. I have tested that there is something in the result by out.write it to a txt file.

ResultSet result = stmt.executeQuery(sql);

Here is what i got, i tried:

int i =0;
        sqlresult = new String[result.getMetaData().getColumnCount()];
        while(result.next()){
            sqlresult[i] = result.getArray(sql);
            i++;
        }

But this i keep getting an error, and its only 1d array.

Solution 1

This should give you a 2-dimensional data structure, as a List, containing as many elements as there are rows in the result set, with String values for each column value in the row.

int nCol = result.getMetaData().getColumnCount();
List<String[]> table = new ArrayList<>();
while( result.next()) {
    String[] row = new String[nCol];
    for( int iCol = 1; iCol <= nCol; iCol++ ){
            Object obj = result.getObject( iCol );
            row[iCol-1] = (obj == null) ?null:obj.toString();
    }
    table.add( row );
}

// print result
for( String[] row: table ){
    for( String s: row ){
        System.out.print( " " + s );
    }
    System.out.println();
}

Solution 2

I am not sure what you want but this code will get the column value from the current resultset and store it in an array.

         while(result.next()){
             java.sql.Array columnVar = rs.getArray("ColumnName");
             String[] zips = (String[])columnVar.getArray();
        }

Solution 3

This converts the whole resultset into a 2d array of Objects.

        Object[][] resultSet = new Object[rows][columns];
        int row = 0;
        while (result.next()) {
            for (int i = 0; i < columns; i++) {
                resultSet[row][i] = result.getObject(i+1);
            }
            row++;
        }

Comments

[Klaus Joe Christiansen]

I dont know why, but when i try this i get the references into the array table. so when i sysout it, i get something like [Ljava.lang.String;@6487d5bd, .. Any things i do wrong?

[laune]

That's to be expected. You'll have to write a wee loop to print the rows one by one.

[Klaus Joe Christiansen]

Thanks.. yeah i forgot i had to loop when its 2darray :)

[daOnlyBG]

This assumes you know the row count, doesn't it?

[rizerphe]

While this code may solve the question, including an explanation of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please edit your answer to add explanations and give an indication of what limitations and assumptions apply.