Most simple code to populate JTable from ResultSet

问题:

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 make DefaultTableModel from ResultSet ? Thanks in advance.

Edit: I am reciving java.lang.IllegalStateException: SQLite JDBC: inconsistent internal state error.

回答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\");
    }

}

回答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)
    {
    }
}

回答3:

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());
    }

回答4:

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);
    }

回答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();
        }
    }
}

回答6:

The JTable constructor accepts two arguments 2dimension Object Array for the data, and String Array for the column names.

eg:

import java.awt.BorderLayout;
import java.awt.Color;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;

public class Test6 extends JFrame {

    public Test6(){     
        this.setSize(300,300);
        this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        Mpanel m = new Mpanel();
        this.add(m,BorderLayout.CENTER);        
    }


    class Mpanel extends JPanel {       

        JTable mTable;
        private Object[][] cells = {{\"Vivek\",10.00},{\"Vishal\",20.00}};
        private String[] columnNames = { \"Planet\", \"Radius\" };
        JScrollPane mScroll;

        public Mpanel(){
            this.setSize(150,150);
            this.setComponent();
        }

        public void setComponent(){
            mTable = new JTable(cells,columnNames);
            mTable.setAutoCreateRowSorter(true);
            mScroll = new JScrollPane(mTable);

            this.add(mScroll);
        }
    }

    public static void main(String[] args){     
        new Test6().setVisible(true);
    }
}

回答7:

This is my approach:

String[] columnNames = {\"id\", \"Nome\", \"Sobrenome\",\"Email\"};

List<Student> students = _repo.getAll();

Object[][] data = new Object[students.size()][4];

int index = 0;
for(Student s : students) {
    data[index][0] = s.getId();
    data[index][1] = s.getFirstName();
    data[index][2] = s.getLastName();
    data[index][3] = s.getEmail();
    index++;
}

DefaultTableModel model = new DefaultTableModel(data, columnNames);

table = new JTable(model);

回答8:

go here java tips weblog

then,put in your project : listtabelmodel.java and rowtablemodel.java add another class with this code:

    enter code here
/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package comp;

import java.awt.*;
import java.sql.*;
import java.util.*;
import javax.swing.*;
import static javax.swing.JFrame.EXIT_ON_CLOSE;
import javax.swing.table.*;

public class TableFromDatabase extends JPanel {

    private Connection conexao = null;

    public TableFromDatabase() {
        Vector columnNames = new Vector();
        Vector data = new Vector();

        try {
            //  Connect to an Access Database
            conexao = DriverManager.getConnection(\"jdbc:mysql://\" + \"localhost\"
                    + \":3306/yourdatabase\", \"root\", \"password\");

            //  Read data from a table
            String sql = \"select * from tb_something\";
            Statement stmt = conexao.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            ResultSetMetaData md = rs.getMetaData();
            int columns = md.getColumnCount();

            //  Get column names
            for (int i = 1; i <= columns; i++) {
                columnNames.addElement(md.getColumnName(i));
            }

            //  Get row data
            while (rs.next()) {
                Vector row = new Vector(columns);

                for (int i = 1; i <= columns; i++) {
                    row.addElement(rs.getObject(i));
                }

                data.addElement(row);
            }

            rs.close();
            stmt.close();
            conexao.close();
        } catch (Exception e) {
            System.out.println(e);
        }

        //  Create table with database data
        JTable table = new JTable(data, columnNames) {
            public Class getColumnClass(int column) {
                for (int row = 0; row < getRowCount(); row++) {
                    Object o = getValueAt(row, column);

                    if (o != null) {
                        return o.getClass();
                    }
                }

                return Object.class;
            }
        };

        JScrollPane scrollPane = new JScrollPane(table);
        add(scrollPane);

        JPanel buttonPanel = new JPanel();
        add(buttonPanel, BorderLayout.SOUTH);
    }

    public static void main(String[] args) {

        javax.swing.SwingUtilities.invokeLater(new Runnable() {
            public void run() {
                JFrame frame = new JFrame(\"any\");
                frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

                //Create and set up the content pane.
                TableFromDatabase newContentPane = new TableFromDatabase();
                newContentPane.setOpaque(true); //content panes must be opaque
                frame.setContentPane(newContentPane);

                //Display the window.
                frame.pack();
                frame.setVisible(true);
            }
        });

    }
}

---

then drag this class to you jframe,and it\'s done

it\'s deprecated,but it works.........

回答9:

You have to Download the Jar called rs2xml by click this link rs2xml.jar Then Add it to your project libraries then import

import net.proteanit.sql.DbUtils;

I prepare Only a method to do so as a sample

private void Update_table(){
    try{
        // fetch a connection
        connection = your_connection_class_name_here.getInstance().getConnection();
        if (connection != null) {
        String sql=\"select name,description  from sell_mode\";
        pst=connection.prepareStatement(sql);
        rs=pst.executeQuery();
         your_table_name_for_populating_your_Data.setModel(DbUtils.resultSetToTableModel(rs));
        }
    }
    catch(IOException | SQLException | PropertyVetoException e){
    JOptionPane.showMessageDialog(null,e);
    }
    finally {
          if (rs!= null) try { rs.close(); } catch (SQLException e) {}
          if (pst != null) try { pst.close(); } catch (SQLException e) {}
          if (connection != null) try { connection.close(); } catch (SQLException e) {}
      }
}

Hope this will help more

回答10:

With all factors put into consideration, with regard to code architecture using modular programming would work very well and with more simplicity in the code. Write a simple

getData() function @returns a 2D array of the data from the database.

pass this funtion to the constructor of the JTabel() constructor i.e

JTabel myTable = new JTable(getData(),columnsArray);

In this case the second argument: columnsArray is a single dimensional array that has the column names

String[] columns = {{\"ID\",\"DataOfBirth\",\"Age\",\"Grade\",\"Marks\",\"RegNumber\"}};

pass the JTable object to a JScrollPane then you are done, right after adding the ScrollPane to the container ofcourse

JScrollPane scrollPane = new JScrollPane(myTable);

JFrame myFrame = new JFrame();
myFrame.add(scrollPane);

Here is a sample function getData() that queries the database for the data that later is passed on to the JTable

public String[][] getRecords() {
    try (Connection conect = DriverManager.getConnection(\"jdbc:mysql://localhost:3306/online_students_registration\", \"root\", \"\")) {
        Statement stm = conect.createStatement();
        String SELECT_QUERY = \"SELECT COUNT(*) FROM medicalrecords ;\";
        ResultSet cursor = stm.executeQuery(SELECT_QUERY);
        while (cursor.next()) {
            rows = cursor.getInt(\"COUNT(*)\");
            System.out.println(\"Table will have \" + rows + \" Rows\");
        }
        System.err.println(\"Contacts row count is obtained!!\");
        if (rows < 1) {
            JOptionPane.showMessageDialog(null, \"There is NO DATA\");
            //contactsRowsCount = 1;
            //System.out.println(\"Table rows succefully reset to  \" + contactsRowsCount + \" Rows\");
            dataValues = new String[1][8];
            //dataValues[1][5] = \"No Values\";

            for (int i = 0; i < 1; i++) {
                for (int j = 0; j < 8; j++) {
                    if (j == 0) {
                        dataValues[i][j] = \"No Details Available\";
                        System.out.println(\"\" + dataValues);
                    } else {
                        dataValues[i][j] = \"...\";
                        System.out.println(\"Contacts\" + dataValues[i][j]);
                    }
                }
            }

            System.out.println(\"Return statement is being executed on 0 rows \");
            //return doctoredDataValues;
        } else if (rows > 0) {

            System.out.println(\"obtain contacts code is being run under  \" + rows + \" Rows\");
            dataValues = new String[rows][8];    //declare array for contacts table data
            System.out.println(\"[ Line 1584 ]The dataValues object for the JTable succefully set\");

            String SELECT_QUERY_CONTACT = \"SELECT * FROM medicalrecords; \";

            //OBTAIN CONTACTS FROM DB WITH REGARD TO CONTACT CATEGORY SPECIFIED
            ResultSet contactsTableCursor = stm.executeQuery(SELECT_QUERY_CONTACT);
            //use iterator-algorithm to insert values into the JTable
            for (int i = 0; contactsTableCursor.next() && i < rows; i++) {
                for (int j = 0; j < 8; j++) {
                    dataValues[i][j] = contactsTableCursor.getString(j + 1);
                    System.out.println(\"Contacts\" + dataValues[i][j]);
                }
            }
        }
        JOptionPane.showMessageDialog(null, \"Medical Details Added Succefully!!\");
    } catch (SQLException e) {

        JOptionPane.showMessageDialog(null, \"Unable to Obtain contacts:Server is Offline(LINE 1568)\" + e.getMessage());
    }

    return dataValues;

}