Most simple code to populate JTable from ResultSet
Solution 1
I think the simplest way to build a model from an instance of ResultSet
, could be as follows.
public static void main(String[] args) throws Exception {
// The Connection is obtained
ResultSet rs = stmt.executeQuery("select * from product_info");
// It creates and displays the table
JTable table = new JTable(buildTableModel(rs));
// Closes the Connection
JOptionPane.showMessageDialog(null, new JScrollPane(table));
}
The method buildTableModel
:
public static DefaultTableModel buildTableModel(ResultSet rs)
throws SQLException {
ResultSetMetaData metaData = rs.getMetaData();
// names of columns
Vector<String> columnNames = new Vector<String>();
int columnCount = metaData.getColumnCount();
for (int column = 1; column <= columnCount; column++) {
columnNames.add(metaData.getColumnName(column));
}
// data of the table
Vector<Vector<Object>> data = new Vector<Vector<Object>>();
while (rs.next()) {
Vector<Object> vector = new Vector<Object>();
for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
vector.add(rs.getObject(columnIndex));
}
data.add(vector);
}
return new DefaultTableModel(data, columnNames);
}
UPDATE
Do you like to use javax.swing.SwingWorker
? Do you like to use the try-with-resources
statement?
public class GUI extends JFrame {
public static void main(String[] args) {
EventQueue.invokeLater(new Runnable() {
@Override
public void run() {
new GUI().setVisible(true);
}
});
}
private final JButton button;
private final JTable table;
private final DefaultTableModel tableModel = new DefaultTableModel();
public GUI() throws HeadlessException {
setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE);
table = new JTable(tableModel);
add(new JScrollPane(table), BorderLayout.CENTER);
button = new JButton("Load Data");
button.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
new SwingWorker<Void, Void>() {
@Override
protected Void doInBackground() throws Exception {
loadData();
return null;
}
}.execute();
}
});
add(button, BorderLayout.PAGE_START);
setSize(640, 480);
}
private void loadData() {
LOG.info("START loadData method");
button.setEnabled(false);
try (Connection conn = DriverManager.getConnection(url, usr, pwd);
Statement stmt = conn.createStatement()) {
ResultSet rs = stmt.executeQuery("select * from customer");
ResultSetMetaData metaData = rs.getMetaData();
// Names of columns
Vector<String> columnNames = new Vector<String>();
int columnCount = metaData.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
columnNames.add(metaData.getColumnName(i));
}
// Data of the table
Vector<Vector<Object>> data = new Vector<Vector<Object>>();
while (rs.next()) {
Vector<Object> vector = new Vector<Object>();
for (int i = 1; i <= columnCount; i++) {
vector.add(rs.getObject(i));
}
data.add(vector);
}
tableModel.setDataVector(data, columnNames);
} catch (Exception e) {
LOG.log(Level.SEVERE, "Exception in Load Data", e);
}
button.setEnabled(true);
LOG.info("END loadData method");
}
}
Solution 2
I think this is the Easiest way to populate a table with ResultSet with a method like
FillTable(MyTable, "select * Customers;");
And a very simple method can be made as
public void FillTable(JTable table, String Query)
{
try
{
CreateConnection();
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery(Query);
//To remove previously added rows
while(table.getRowCount() > 0)
{
((DefaultTableModel) table.getModel()).removeRow(0);
}
int columns = rs.getMetaData().getColumnCount();
while(rs.next())
{
Object[] row = new Object[columns];
for (int i = 1; i <= columns; i++)
{
row[i - 1] = rs.getObject(i);
}
((DefaultTableModel) table.getModel()).insertRow(rs.getRow()-1,row);
}
rs.close();
stat.close();
conn.close();
}
catch(InstantiationException | IllegalAccessException | SQLException e)
{
}
}
Solution 3
Well I'm sure that this is the simplest way to populate JTable from ResultSet, without any external library. I have included comments in this method.
public void resultSetToTableModel(ResultSet rs, JTable table) throws SQLException{
//Create new table model
DefaultTableModel tableModel = new DefaultTableModel();
//Retrieve meta data from ResultSet
ResultSetMetaData metaData = rs.getMetaData();
//Get number of columns from meta data
int columnCount = metaData.getColumnCount();
//Get all column names from meta data and add columns to table model
for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++){
tableModel.addColumn(metaData.getColumnLabel(columnIndex));
}
//Create array of Objects with size of column count from meta data
Object[] row = new Object[columnCount];
//Scroll through result set
while (rs.next()){
//Get object from column with specific index of result set to array of objects
for (int i = 0; i < columnCount; i++){
row[i] = rs.getObject(i+1);
}
//Now add row to table model with that array of objects as an argument
tableModel.addRow(row);
}
//Now add that table model to your table and you are done :D
table.setModel(tableModel);
}
Solution 4
I think this is the Easiest way to populate/model a table with ResultSet.. Download and include rs2xml.jar Get rs2xml.jar in your libraries..
import net.proteanit.sql.DbUtils;
try
{
CreateConnection();
PreparedStatement st =conn.prepareStatement("Select * from ABC;");
ResultSet rs = st.executeQuery();
tblToBeFilled.setModel(DbUtils.resultSetToTableModel(rs));
conn.close();
}
catch(Exception ex)
{
JOptionPane.showMessageDialog(null, ex.toString());
}
Solution 5
Class Row
will handle one row from your database.
Complete implementation of UpdateTask responsible for filling up UI.
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JTable;
import javax.swing.SwingWorker;
public class JTableUpdateTask extends SwingWorker<JTable, Row> {
JTable table = null;
ResultSet resultSet = null;
public JTableUpdateTask(JTable table, ResultSet rs) {
this.table = table;
this.resultSet = rs;
}
@Override
protected JTable doInBackground() throws Exception {
List<Row> rows = new ArrayList<Row>();
Object[] values = new Object[6];
while (resultSet.next()) {
values = new Object[6];
values[0] = resultSet.getString("id");
values[1] = resultSet.getString("student_name");
values[2] = resultSet.getString("street");
values[3] = resultSet.getString("city");
values[4] = resultSet.getString("state");
values[5] = resultSet.getString("zipcode");
Row row = new Row(values);
rows.add(row);
}
process(rows);
return this.table;
}
protected void process(List<Row> chunks) {
ResultSetTableModel tableModel = (this.table.getModel() instanceof ResultSetTableModel ? (ResultSetTableModel) this.table.getModel() : null);
if (tableModel == null) {
try {
tableModel = new ResultSetTableModel(this.resultSet.getMetaData(), chunks);
} catch (SQLException e) {
e.printStackTrace();
}
this.table.setModel(tableModel);
} else {
tableModel.getRows().addAll(chunks);
}
tableModel.fireTableDataChanged();
}
}
Table Model:
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.table.AbstractTableModel;
/**
* Simple wrapper around Object[] representing a row from the ResultSet.
*/
class Row {
private final Object[] values;
public Row(Object[] values) {
this.values = values;
}
public int getSize() {
return values.length;
}
public Object getValue(int i) {
return values[i];
}
}
// TableModel implementation that will be populated by SwingWorker.
public class ResultSetTableModel extends AbstractTableModel {
private final ResultSetMetaData rsmd;
private List<Row> rows;
public ResultSetTableModel(ResultSetMetaData rsmd, List<Row> rows) {
this.rsmd = rsmd;
if (rows != null) {
this.rows = rows;
} else {
this.rows = new ArrayList<Row>();
}
}
public int getRowCount() {
return rows.size();
}
public int getColumnCount() {
try {
return rsmd.getColumnCount();
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
public Object getValue(int row, int column) {
return rows.get(row).getValue(column);
}
public String getColumnName(int col) {
try {
return rsmd.getColumnName(col + 1);
} catch (SQLException e) {
e.printStackTrace();
}
return "";
}
public Class<?> getColumnClass(int col) {
String className = "";
try {
className = rsmd.getColumnClassName(col);
} catch (SQLException e) {
e.printStackTrace();
}
return className.getClass();
}
@Override
public Object getValueAt(int rowIndex, int columnIndex) {
if(rowIndex > rows.size()){
return null;
}
return rows.get(rowIndex).getValue(columnIndex);
}
public List<Row> getRows() {
return this.rows;
}
public void setRows(List<Row> rows) {
this.rows = rows;
}
}
Main Application which builds UI and does the database connection
import java.awt.BorderLayout;
import java.awt.Dimension;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JTable;
public class MainApp {
static Connection conn = null;
static void init(final ResultSet rs) {
JFrame frame = new JFrame();
frame.setLayout(new BorderLayout());
final JTable table = new JTable();
table.setPreferredSize(new Dimension(300,300));
table.setMinimumSize(new Dimension(300,300));
table.setMaximumSize(new Dimension(300,300));
frame.add(table, BorderLayout.CENTER);
JButton button = new JButton("Start Loading");
button.setPreferredSize(new Dimension(30,30));
button.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
JTableUpdateTask jTableUpdateTask = new JTableUpdateTask(table, rs);
jTableUpdateTask.execute();
}
});
frame.add(button, BorderLayout.SOUTH);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frame.pack();
frame.setVisible(true);
}
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/test";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "root";
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url, userName, password);
PreparedStatement pstmt = conn.prepareStatement("Select id, student_name, street, city, state,zipcode from student");
ResultSet rs = pstmt.executeQuery();
init(rs);
} catch (Exception e) {
e.printStackTrace();
}
}
}
Comments
-
A. K. M. Tariqul Islam over 3 years
I googled the whole day and no luck. I call
getnPrintAllData()
method after pressing OK button. So the code is:public class DatabaseSQLiteConnection { Connection conn = null; PreparedStatement statement = null; ResultSet res = null; public DatabaseSQLiteConnection(){ try{ Class.forName("org.sqlite.JDBC"); conn = DriverManager.getConnection("jdbc:sqlite:test.sqlite"); statement = conn.prepareStatement("SELECT * from product_info;"); } catch(Exception e){ e.printStackTrace(); } } public void getnPrintAllData(){ String name, supplier, id; DefaultTableModel dtm = new DefaultTableModel(); Window gui = new Window(); //My JPanel class try{ res = statement.executeQuery(); testResultSet(res); ResultSetMetaData meta = res.getMetaData(); int numberOfColumns = meta.getColumnCount(); while (res.next()) { Object [] rowData = new Object[numberOfColumns]; for (int i = 0; i < rowData.length; ++i) { rowData[i] = res.getObject(i+1); } dtm.addRow(rowData); } gui.jTable1.setModel(dtm); dtm.fireTableDataChanged(); ////////////////////////// } catch(Exception e){ System.err.println(e); e.printStackTrace(); } finally{ try{ res.close(); statement.close(); conn.close(); } catch(Exception e){ e.printStackTrace(); } } } public void testResultSet(ResultSet res){ try{ while(res.next()){ System.out.println("Product ID: "+ res.getInt("product_id")); System.out.println("Product name: "+ res.getString("product_name")); System.out.println("Supplier: "+ res.getString("supplier")); } } catch(Exception e){ e.printStackTrace(); } } }
My
testResultSet()
method is working properly. Now, how to change my code so that it works, or what is the most simple code to makeDefaultTableModel
fromResultSet
? Thanks in advance.Edit: I am reciving
java.lang.IllegalStateException: SQLite JDBC: inconsistent internal state
error.