ResultSet to array

[Jojomofoman]

Hey folk, I'm wondering if you guys can help me. I'm from a PHP background so I'm not too sure if I'm looking at this the wrong way - but I've grabbed some results from a database and need to put them into an array which I can send back to a JComboBox.

First of all, is it not possible to send the resultset back to the JComboBox in the necessary array? Or do I have to create a seperate array variable, loop through the results adding each result into newly created array?

The code I've got so far looks like this:

...
try {
 sql = "SELECT title FROM domains";
 rs = exec.getResults(sql);
			
 rs.last();
 totRows = rs.getRow();	

 String[] domainList = new String[totRows];

 for (int i = 0; i < domainList.length; i++) {
  domainList[i] = rs.getString(i); //This line doesn't actually work at the moment?
 }
			
 return domainList;
			
} catch (Exception e) {
 String[] domainError = { "Error returning Domains", };
 return domainError;
}
...

Is it not easier (or is it even possible in Java) to do the following?

..

String[] domainList = new String[totRows];

while(rs.next()){
 //append to domainList array
}

return domainList;

...

Many thanks in advance!

[travishein]

First of all, is it not possible to send the resultset back to the JComboBox in the necessary array? Or do I have to create a seperate array variable, loop through the results adding each result into newly created array?

It is usually better to not pass a result set around. it is kind of like a bookmark or a cursor, and has open handles or resources in the statement object, which in turn has open resources in the connection object.. So you should loop thru the results and populate an array. In situations where there are very very large number of results, I then do a row handler, such as implement some interface i design with an "handleRow" method (which might even pass in a result set object, as the understanding is the result set is only used for reading 1 row into a bean an doing something with it.)

To create a list of stuff from a result set in Java, maybe something like,

  /**
  * Gets the list of domain names from the domains table in the database.
  * @return the list of string domain names sorted alphabetically.
  */
  public List<String> getDomainList() throws SQLException {
    List<String> result = new ArrayList<String>();
    Connection con = null;
    PreparedStatement stmt = null;
    ResultSet rset = null;
    try {
      con = dataSource.getConnection();  // e.g. dataSource is resolved from JNDI connection pool resource, or commons-dbcp pool
      stmt = con.prepareStatement("select title from DOMAINS order by title"); // always used prepared statements!!!
      rset = stmt.executeQuery();
      
      while (rset.next() ) {
        result.add(rset.getString("title"));
      } // while
    }
    finally {
      // yes, we need all of this close stuff too.
      if (rset != null) {
        try {
          rset.close();
        }
        catch (SQLException ex) { /* empty */ }
      }
      if (stmt != null) {
        try {
          stmt.close();
        }
        catch (SQLException ex) { /* empty */ }
      }
      if (con != null) {
        try {
          con.close();
        }
        catch (SQLException ex) { /* empt */ }
      }
    }
    return result;
  }

where if you really wanted an array of string from that, you could wrap that function with

  String[] domainList = null;
  try {
    List<String> aList = getDomainList();
    domainList = (String[]) aList.toArray(new String[0]);
    return domainList;
  }
  catch (Exception ex) {
    String[] domainError = { "Error returning Domains:" + ex.getMessage() , };
   return domainError;
  }

where the real worker here is the toArray() method of the list object that converts the list contents into an array of the type of the parameter, and we cast it back to string array for our results.

[Jojomofoman]

Thanks so much for your help travishein, I decided to go with your first example as the number of results returned from the MySQL query could potentially be quite a lot.

It is usually better to not pass a result set around. it is kind of like a bookmark or a cursor, and has open handles or resources in the statement object, which in turn has open resources in the connection object.. So you should loop thru the results and populate an array.

Thanks for pointing the above out to me too, being new to Java it's this kind of stuff which will help me improve!