Using ResultSet with tables with unknown # of rows and columns

[amberdextrous]

Hello all,

I'm developing an app that connects to a database (in my case, an Access database) and can run SQL queries against it (select, insert, delete, etc.). I'm trying to read data from the database using a ResultSet so I can output it to a JTable. There are several tables in the database, and each one has a different # of columns. My problem is when I'm iterating through the Result Set, I don't know how many rows or columns there will be.

I know I can use:

int columnCount = resultSet.getMetaData().getColumnCount();

to get the column number, but I can't count the rows because I can't use .first() to go back to the beginning. So, I have to do everything as it iterates.

I figure I need to use an ArrayList and something like this:

while(resultSet.next()) {
     for (int i = 1; i <= columnCount; i++) {
     }
}

But that's where I'm stuck. Anyone know what I should do? I'm really pulling my hair out over this.

 

[Diancecht]

Something like

ArrayList allValues = new ArrayList();
String[] values = new String[columnCount];
while(resultSet.next()) {
     for (int i = 1; i <= columnCount; i++) {
        values[i]=rs.getString(i);
     }
allValues.add(values);
String[][] stringValues = (String[][])allValues.toArray(new String[][]);
}

Cheers,
Dian

 

[amberdextrous]

Thanks, but I couldn't get it to work. Here's the code I have now:

public void processSQLSelect(String sqlCommand) throws SQLException {
			view.getJScrollPane().setViewportView(view.getJtasqlResult());
			statement = connection.createStatement();

try {
				ResultSet resultSet = statement.executeQuery(sqlCommand);
				int columnCount = resultSet.getMetaData().getColumnCount();

				ArrayList<Object> allValues = new ArrayList<Object>();
				String[] values = new String[columnCount];
				while(resultSet.next()) {
					for (int i = 1; i <= columnCount; i++) {
						values[i]=resultSet.getString(i);
					}
					allValues.add(values);
					String[][] stringValues = (String[][])allValues.toArray(new String[][]);
				}
				
				for (int i = 1; i <= allValues.size(); i++) {
					view.setJtasqlResult(allValues.get(i).toString());	
				}
				
				//Retrieve column names, store them in an array, then set the column
				//names in the table model to the names in the array
				for (int i = 1; i <= columnCount; i++) {
					tableHeader[i] = new String(resultSet.getMetaData()
							.getColumnName(i));
				}
				view.getTableModel().setColumnIdentifiers(tableHeader);
				
			} catch (java.lang.Exception ex) {
				view.getJtasqlResult().setText("Error. Query cannot be executed.");
			}
}

The line:

String[][] stringValues = (String[][])allValues.toArray(new String[][]);

gives me an error: "Variable must provide either dimension expressions or an array initializer"

I also added <Object> in the ArrayList definition because the compiler warned me that it should be parameterized. I'm not very familiar with ArrayLists though, so correct me if my addition is not right. Anyway, I commented out the line with the error, and when I ran the program it went to the catch statement (error message).
BTW this is in MVC so that's why you see view.getjtasqlResult, it's a text area used to show output, so the code I can't get to work is in the controller, which updates the text area in the view.

 

[amberdextrous]

Ahh, I did some tinkering and got it to read the data in to the ArrayList and output to a text area.

public void processSQLSelect(String sqlCommand) throws SQLException {
			view.getJScrollPane().setViewportView(view.getJtasqlResult());
			//view.getJScrollPane().setViewportView(view.getOutputTable());
			statement = connection.createStatement();

try {
				ResultSet resultSet = statement.executeQuery(sqlCommand);
				int columnCount = resultSet.getMetaData().getColumnCount();
				
				ArrayList<Object> al = new ArrayList<Object>();
				while (resultSet.next() )
				{
					ArrayList<Object> record = new ArrayList<Object>();

					for (int i = 1; i <= columnCount; i++)
					{
						Object value = resultSet.getObject(i);
						record.add(value);
					}
					al.add(record);
				}

				for (int i = 0; i < al.size(); i++) {
					view.setJtasqlResult(al.get(i).toString() + "\n");
				}

//Retrieve column names, store them in an array, then set the column
				//names in the table model to the names in the array
				tableHeader = new String[columnCount + 1];
				
				for (int j = 1; j < columnCount+1; j++) {
					tableHeader[j] = new String(resultSet.getMetaData()
							.getColumnName(j));
				}
				view.getTableModel().setColumnIdentifiers(tableHeader);

} catch (java.lang.Exception ex) {
				//view.getJtasqlResult().setText("Error. Query cannot be executed.");
				ex.printStackTrace();
			}
}

But, I need to change the text area to a JTable, I was only using a text area for testing purposes. Now that the data is being read, I just have to figure out how to pass the data to the JTable in my view class.