CODEWITHSUNDEEP
javasqlderby
Eric
Eric

Reputation: 373

Getting Embedded Derby Database entire table into a string array

I'm a beginner with SQL, though I know enough to get by. The statements aren't what is confusing me, it's the combination with Java that gets me.

Essentially I'm trying to display a JTable that shows the data in the Database. My train of thought is to simply get all the data into a String array and use a for loop to add to my JTable's model.

The database table only has 2 columns, a varchar(30) and a date. I'm completely lost though using Derby and the online documentation is latin to me. I've been able to set up the database without issue using Connection, Statement objects to exicute the SQL Queries.

I think my answer lies somewhere within ResultSet, as I can query the entire table with SELECT * FROM table_name, just don't know how to get that object into a String Array.

I apologize for the disorganized question, dead tired from work.

TL;DR: Get entire table into a String Array from a Derby Database.

Upvotes: 0

Views: 893

Answers (2)

user330315
user330315

Reputation:

You probably want a List rather than an array. As you essentially have a two-dimensional result you need a List of arrays (or lists).

The "outer" list represents the rows in the result, the "inner" list contains the columns of each row. 

String query = "select varchar_column, date_colum from the_table";
// this could a List<List<String>> just as well
List<String[]> rows = new ArrayList<String[]>();


Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(query);

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

while (rs.next()) 
{
   // create a list holding the values for a single row
   String[] columnValues = new String[2];
   columnValues[0] = s.getString(1); 
   java.sql.Date date = rs.getDate(2);
   columnValues[1] = sdf.format(date);
   rows.add(columnValues);
}
rs.close();
stmt.close();

Of course you will need to add error handling to the above. You could also use an Object[] instead of String[] as the elements of the "row list". An Object[] has the advantage that you can leave the formatting of the Date instances to the renderer of your JTable, which gives you a bit more flexibility.

Using the "nested List" you can then create a TableModel that can be used for the JTable. The most important part is the getValueAt() method. A very basic implementation could look like this:

public class MyModel 
  extends AbstractTableModel
{

  private List<String[]> data = null;
  public MyModel(List<String[]> data)
  {
     this.data = data;
  }

  public Object getValueAt(int row, int column)
  {
    List<String> columnValues = data.get(row);
    return columnValues[column];
  }

  ... implement other methods from the TableModel
}

Again this is without error checking or handling, just a basic example.

Upvotes: 1

Niel de Wet
Niel de Wet

Reputation: 8398

You don't really need to get the String array. You can just loop through the ResultSet using next().

From the docs:

A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.

Something like this:

ResultSet rs = ...
while (rs.next()) {
    String yourVarCharColumn = rs.getNString("varchar column name");
    Date yourDateColumn = rs.getDate("date column name");
    // Add to your JTable
}

Upvotes: 1

Related Questions