Converting a SQL query result to an array of strings

Question

I am querying a SQLite3 database in Java using SQLiteJDBC (Java JDBC driver for SQLite).

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

The following code tries to return the queries to the list of arrays, but it fails, I know that the SQL database is beautiful and the query I make, because when I do this on the 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 loginTable table has columns: Index, name, pass. Therefore, looking above, when I = 0 and I go rs.getString (0), it tries to get the row in the Index column.

Error output

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

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

Answer 1

To save only the columns of the first row, 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 save an ArrayList<String[]> that 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 for Shaun's answer

Answer 2

In JDBC, columns are indexed starting at 1, not 0.

Answer 3

This worked for me:

 public static String[] getRooms() throws SQLException, JSONException, ClassNotFoundException{ ArrayList<String> a = new ArrayList<String>(); Class.forName(sqlDriver); Connection con = DriverManager.getConnection(dtbSet, dtbUsername, dtbPassword); PreparedStatement ps = con.prepareStatement("SELECT room_name FROM "+ dtbTbl2); ResultSet rs = ps.executeQuery(); while(rs.next()) { a.add(rs.getString(1)); } return (String[]) a.toArray(new String[a.size()]); } 

Answer 4

First, do not use for loop because you only get one column from each row ... every time you press rs.next() , it goes to the next row before you finish getting all the column values ​​from one strings. Instead, use while (rs.next()) to check for the existence of a string.

Secondly, as @shaun mentioned, JDBC columns start at 1, not zero.

Thirdly, to put data in a list, you will need a POJO or bean to store all the information from each row.

So, at the end, the code looks something like this: -

 Connection con = ...; PreparedStatement ps = con.prepareStatement("SELECT name, pass FROM loginTable WHERE name = ?"); ps.setString(1, "mike"); ResultSet rs = ps.executeQuery(); List<User> users = new ArrayList<User>(); while(rs.next()) { String name = rs.getString(1); String pass = rs.getString(2); users.add(new User(name, pass)); } ... 

Answer 5

You might want to take a look at this structure. http://commons.apache.org/dbutils/

In particular, you can query the database and return the query arraylist. Here is an example in pseudo code:

 ResultSetHandler h = new ArrayListHandler(); QueryRunner qr = new QueryRunner(); List<Object[]> results = (List<Object[]>)qr.query(sql, h); 

Also here is another site that uses this: http://www.stupidjavatricks.com/?p=45