CODEWITHSUNDEEP
javamysqljdbc
Ryan
Ryan

Reputation: 262

Java, MySQL method for returning ambiguous ResultSet as an ArrayList

I'm working on an assignment but am confused by the wording of this task:

"Add a method named getData that accepts an SQLstring and the number of fields. getData should perform the query that was passed in, then convert the ResultSet into a simple 2-d ArrayList."

Here is what I have so far...

public ArrayList<ArrayList<String>> getData(String sqlString, int numFields){
    Statement stmnt= this.conn.createStatement();

    ResultSet rs = stmnt.executeQuery(sqlString);
    ArrayList<ArrayList<String>> ary = new ArrayList();
    int row = 0;
    while (rs.next()){ // Get next row
        for (int i = 1; i <= numFields; i++) { // each column
            ary[row][i-1] = rs.getString(i);
        }
        row++;
    }
    return ary;
}

I'm getting an error error: array required, but ArrayList<ArrayList<String>> found

ary[row][i-1] = rs.getString(i);
   ^

Thank you in advance for your help and for reading.

EDIT: I believe this is the final/correct/working version, thanks to the answers provided by other awesome users :) now jGrasp is just yelling at me about catching exceptions. I appreciate the help!!!

public ArrayList<ArrayList<String>> getData(String sqlString, int numFields){
    Statement stmnt= this.conn.createStatement();

    ResultSet rs = stmnt.executeQuery(sqlString);
    ArrayList<ArrayList<String>> ary = new ArrayList<>();

    while (rs.next()){   // Get next row
        ArrayList<String> columnsList = new ArrayList<>();
        for (int i = 1; i <= numFields; i++) { // each column
            columnsList.add(rs.getString(i));
            // Add the list of columns to our list of rows
            ary.add(columnsList);
        }
    }
    return ary;
}

Upvotes: 0

Views: 41

Answers (3)

hd1
hd1

Reputation: 34677

You can do this trivially using Apache DbUtils:

public ArrayList<ArrayList<String>> getData(String sqlString, int numFields){ // numFields unnecessary
    QueryRunner run = new QueryRunner();
    List ret = new ArrayList();
    ret.addAll(run.query(conn, sqlString));
    DbUtils.close(conn);
    return ret;
}

Upvotes: 0

Vikas
Vikas

Reputation: 7175

You need to use the get() method to get the element at a particular index from an ArrayList. You can't use [] to get the element at a particular index, in an arraylist. Its possible only for arrays and your resultSet is not an array, but an ArrayList. You can add to arraylist instead of array as below,

ArrayList<ArrayList<String>> ary = new ArrayList();
    int row = 0;
    while (rs.next()){ // Get next row
    ArrayList<String> columns = new ArrayList<>();
        for (int i = 1; i <= numFields; i++) { // each column
            columns.add(rs.getString(i));
        }
        ary.add(columns)
  }

And thet use get() method on list to retrieve.

Upvotes: 1

Zephyr
Zephyr

Reputation: 10253

You can think of a 2D ArrayList as being a "list of lists." So as Kartik mentioned in the comments, you would declare it like this:

ArrayList<ArrayList<String>> rows = new ArrayList<>();

Now that you have your base List, you can create and add new ones to that base list:

while (rs.next()){   // Get next row
    ArrayList<String> columnsList = new ArrayList<>();
    for (int i = 1; i <= numFields; i++) { // each column
        columnsList.add(rs.getString(i));
        // Add the list of columns to our list of rows
        rows.add(columnsList);
    }
}
return rows;

Upvotes: 1

Related Questions