Convert Java resultset to String array

Question

Hey everyone,

I am writing a program that will query an MS access database, return the query as a result set, and then I want to ultimately convert that result set into a String array, so that I can pass it into the constructor of a Swing JComboBox - so the ComboBox will list the items returned by the query.

I have been able to store the rows of the result set into an ArrayList, and then convert that ArrayList into an object array, and the combobox will list the correct items, but as objects. I simply cannot ever cast that ArrayList to a String array. Does anyone know if this is possible? Here is some of my code...

// Convert the Resultset into an array list

public ArrayList<ArrayList<Object>> Results2Array(ResultSet rs)
  throws SQLException {
 ResultSetMetaData metaData = rs.getMetaData();
 int columns = metaData.getColumnCount();

 ArrayList<ArrayList<Object>> al = new ArrayList<ArrayList<Object>>();

 while (rs.next()) {
  ArrayList<Object> record = new ArrayList<Object>();

  for (int i = 1; i <= columns; i++) {
   Object value = rs.getObject(i);
   record.add(value);
  }
  al.add(record);
 }
 return al;

}

// Convert ArrayList to Object Array, and pass into GUI

    ArrayList<String> Locations = new ArrayList<String>();
 ArrayList<String> Months = new ArrayList<String>();
 ArrayList<String> Years = new ArrayList<String>();

 try {
  DB.loadDriver();
  DB.makeConnection();
  DB.buildStatement();

  Locations = DB.getLocations();
  Months = DB.getMonths();
  Years = DB.getYears();

  Object[] arrLocations = Locations.toArray();
  Object[] arrMonths = Months.toArray();
  Object[] arrYears = Years.toArray();

  dbGUI ui = new dbGUI(arrLocations, arrMonths, arrYears);
  ui.setVisible(true);

Can anyone offer any suggestions? Thanks!

---

UPDATE:

Here is the stack trace that I am receiving:

java.lang.ArrayStoreException at java.lang.System.arraycopy(Native Method) at java.util.Arrays.copyOf(Unknown Source) at java.util.ArrayList.toArray(Unknown Source) at kidsfirstdb.Main.main(Main.java:23)

Answer 1

String[] arrLocations = locations.toArray(new String[0]);

The code above will convert List of Strings to String array. Now in your case you are creating List of Objects so you can't directly convert it to the String array. Generally you have 2 choices:

1. Initially define your List as List of Strings and when populating the List convert your values to Strings (ether by doing Object#toString or extracting/generating meningful String value) and store that into your List, e.g. in your code do this String value = rs.getObject(i).toString();
2. If you don't do it in step 1 then you will have to allocate a String array, loop through your List, convert current value into String and stick it into array. I would go with option 1.

Just as a side note - methods in Java should start with lowercase letter

Answer 2

   String[] arrLocations = locations.toArray(new String[0]);

Is the correct answer. The reason for your exception is that all the objects are in fact not strings.

You need to change this:

   Object value = rs.getObject(i);

to this:

   String value = rs.getString(i);

or this:

   String value = rs.getObject(i).toString();

That last one will need a null check if you can be returning null columns.

Note that the toString() representation may not be exactly what you are looking for in all cases, but it will get you started.

Edit: If you are filling a combo box, you are going to need one column per row, no? If not, you need to represent the whole row as a string in some fashion. Then you put that in the value and put the value directly in the final array list, so your loop needs to look like this:

    ArrayList<String> al = new ArrayList<String>();
    while (rs.next()) {
            ArrayList<String> record = new ArrayList<String>();
            for (int i = 1; i <= columns; i++) {
                    String value = rs.String(i);
                    record.add(value);
            }
            String value = methodWhichConvertsArrayListToStringTheWayYouNeedItFormatted(record);
            al.add(value);
    }        
    return al;

Answer 3

    String[] res = new String[array.length];
    for (int i = 0; i < array.length; i++) {
        res[i] = array[i] == null ? null : array[i].toString();
    }

you can you method you need instead of toString() here

Answer 4

Why not use rs.getString(). I will not recommend to do that though. But it would solve your problem. I mean just deal with String from the start. Example,

// Not a good idea to pass a active resultset as a parameter. 
// Use disconnected implementation instead, for example rowset.
public ArrayList<ArrayList<Object>> results2Array(ResultSet rs)
            throws SQLException {
    ResultSetMetaData metaData = rs.getMetaData();
    int columns = metaData.getColumnCount();

    ArrayList<String[]> list = new ArrayList<String[]>();

    while (rs.next()) {
            String[] record = new String[columns];

            for (int i = 1; i <= columns; i++) {
                    // Not a good idea to get everything as a string. This way you will
                    // get a default string representation of objects. Suppose, you 
                    // want to format dates and doubles as per some requirement.
                    record[i-1] = rs.getString(i);
            }
            list.add(record);
    }
    return list;
}

Now you need to get it like below.

String[][] arrLocations = locations.toArray(new String[locations.size()][0]);

Better still check the type and get the values appropriately, using meta-data you are having. This will help you format data, namely, dates and doubles.

Answer 5

You're asking about implementation details of the way you choose to solve the problem. However, it's better to ask about the way to use instead.

So, Swing follows MVC pattern (Model-UI actually), so, you can separate business-data from its representation instead of using just representation at GUI controls. I.e. you can define the combo box model that performs necessary conversion. Here is an example with DefaultComboBoxModel that just renders data via calling toString(), i.e. makes exactly what you want:

package com;

import java.awt.*;
import static java.util.Arrays.asList;
import java.util.Date;
import java.util.List;
import javax.swing.*;

public class AAA {

    public static void main(String[] args) throws Exception {
     final JFrame frame = new JFrame();
     frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
     frame.setSize(640, 480);
     frame.setLocation(300, 300);

     List<Object> data = asList(1, "asd", new Object(), new Date());
     final JComboBox comboBox = new JComboBox(new DefaultComboBoxModel(data.toArray()));

     SwingUtilities.invokeLater(new Runnable() {
      @Override
      public void run() {
       frame.setLayout(new FlowLayout());
       frame.add(comboBox);
       frame.setVisible(true);
      }
     });
    }
}