Convert SQL Query result into array of strings

Question

I am making queries to a SQLite3 database in Java using the SQLiteJDBC (Java JDBC driver for SQLite).

If I make the SQL query SELECT name, pass FROM loginTable WHERE name='%s'; is there a function to Return OR Convert the name & pass strings returned from the query into a String array or ArrayList?

The following code attempts to put the queries return into an array list but it fails, I know that the SQL database is fine & the query I make because when I do this in command line it works.

ArrayList <String> result = new ArrayList<String>();
ResultSet rs = stat.executeQuery( "SELECT ..." );

for (int i=0; rs.next(); i++)
{
   result.add( rs.getString(i) );
}

It fails because the table loginTable has the columns: Index, name, pass. So looking at above, when i=0, & I go rs.getString(0), it tries to get the string in the Index column.

The error output is

java.sql.SQLException: column 0 out of bounds [1,1]

Does anyone know how I can put all results from an SQL query(ResultSet object) in an ArrayList or array?

Answer 1

In JDBC, the columns are indexed starting from 1, not 0.

Answer 2

To store the first row's columns only, try

int columnCount = rs.getMetaData().getColumnCount();
rs.next();
for (int i = 0; i <columnCount ; i++)
{
   result.add( rs.getString(i + 1) );
}

If you want to store a ArrayList<String[]> which contains all rows and all columns:

ArrayList <String[]> result = new ArrayList<String[]>();
ResultSet rs = stat.executeQuery( "SELECT ..." );
int columnCount = rs.getMetaData().getColumnCount();
while(rs.next())
{
    String[] row = new String[columnCount];
    for (int i=0; i <columnCount ; i++)
    {
       row[i] = rs.getString(i + 1);
    }
    result.add(row);
}

Updated per Shaun's answer