I am attempting to populate a jTable from a resultSet using SQLServer with the MS Driver 3.0.
The table will generate as long as I do not close the resultset, statement or connection. If I close them, then my table replies back with the message:
com.microsoft.sqlserver.jdbc.SQLServerException: The result set is closed.
I attempted to create my tablemodel using the "How to use Tables" tutorial.
Here is my Table Model:
TableModel model = new AbstractTableModel() {
private String[] columnName = {"Employee ID", "Job Start", "Job ID", "Oper. ID", "Indir. ID"};
public int getColumnCount() {
return columns;
}
public int getRowCount() {
return rows;
}
public Object getValueAt(int row, int col) {
try {
rs.absolute(row+1);
return rs.getObject(col+1);
} catch (SQLException ex) {
System.err.println(ex);
}
return null;
}
public String getColumnName(int col) {
return columnName[col];
}
};
My connections were originally done in a try with resources block, but in trying to find the issue I put it into a try-catch-finally block with the rs.close(), stmt.close(), and connection.close(). As I mentioned above, it will work fine if I do not have the close statements, but if I have them, then it states that the resultset is closed by the time it gets to the getValueAt method in my Table Model.
My whole code is here:
package dashboard;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.event.TableModelListener;
import javax.swing.table.AbstractTableModel;
import javax.swing.table.TableModel;
import org.omg.CORBA.DATA_CONVERSION;
public class Dashboard extends JFrame {
ResultSet rs;
Connection connection;
Statement stmt;
ResultSetMetaData md;
JScrollPane scrollpane;
int columns, rows;
JFrame frame;
String connectionUrl;
@SuppressWarnings("empty-statement")
public Dashboard() throws SQLException {
try {
connection = DriverManager.getConnection(connectionUrl);
stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery(query);
ResultSetMetaData md = rs.getMetaData();
//Count number of rows
rs.last();
rows = rs.getRow();
rs.first();
//Get column Count
columns = md.getColumnCount();
TableModel model = new AbstractTableModel() {
private String[] columnName = {"Employee ID", "Job Start", "Job ID", "Oper. ID", "Indir. ID"};
public int getColumnCount() {
return columns;
}
public int getRowCount() {
return rows;
}
public Object getValueAt(int row, int col) {
try {
rs.absolute(row+1);
return rs.getObject(col+1);
} catch (SQLException ex) {
System.err.println(ex);
}
return null;
}
public String getColumnName(int col) {
return columnName[col];
}
};
JTable table = new JTable(model);
table.setAutoCreateRowSorter(true);
scrollpane = new JScrollPane(table);
getContentPane().add(scrollpane);
frame = new JFrame();
frame.setDefaultCloseOperation(EXIT_ON_CLOSE);
frame.pack();
frame.setTitle("Employee Dashboard");
frame.setBounds(300, 300, 1300, 750);
frame.setResizable(false);
frame.add(scrollpane);
frame.setVisible(true);
} catch (SQLException ex) {
System.err.println(ex);
} finally {
rs.close();
stmt.close();
connection.close();
}
}
public static void main(String[] args) throws InterruptedException, SQLException {
Dashboard me = new Dashboard();
}
}
Update 6-4-13
I ended up tossing everything out and starting from scratch based on what @Reimeus said. I am not positive, but I think this is working. When I out print model.getRowCount() it tells me that it has the same rows as my query. The code is here:
DefaultTableModel model = new DefaultTableModel();
ResultSetMetaData meta = rs.getMetaData();
int numberOfColumns = meta.getColumnCount();
while (rs.next()) {
Object[] rowData = new Object[numberOfColumns];
for (int i = 0; i < rowData.length; ++i) {
rowData[i] = rs.getObject(i + 1);
}
model.addRow(rowData);
System.out.println(model.getColumnName(1));
}
The issue I am having now is that I cannot generate a table with the table model. Here is what I thought would have worked:
JTable table = new JTable(model);
table.setAutoCreateRowSorter(true);
scrollpane = new JScrollPane(table);
getContentPane().add(scrollpane);
frame = new JFrame();
frame.setDefaultCloseOperation(EXIT_ON_CLOSE);
frame.pack();
frame.add(scrollpane);
frame.setVisible(true);
In my mind I am thinking:
DB to RS RS to Table Model TM to Table Table to ScrollPane ScrollPane to Jtable
I am sure it is something dumb that I am doing but I am still not knowledgeable enough to recognize it yet.
