How to populate JTable from ResultSet?

Question

I call getnPrintAllData() method after pressing OK button:

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

testResultSet() method is working properly. How to change my code so that it works, or what is the most simple code to make DefaultTableModel from ResultSet? I am receiving:

java.lang.IllegalStateException: SQLite JDBC: inconsistent internal state

-error.

Threonine

Yes, populate the DefaultTableModel from the ResultSet. – Roentgenology

check this post #2193264 – Toddle

Not the recommended approach (doesnt use SwingWorker) but should give you an idea java2s.com/Code/Java/Swing-JFC/… – Toddle

what line is the exception being thrown from? – Philosophism

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

}

Derosa

Answer 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)
    {
    }
}

Hereford

Passing a Query may cause changing a structure. Instead of removeRow() and insertRow() it could be much better to use setDataVector(). It replaces data and list of columns at once. – Bullpen

Column names from the result set missing when table rendered? – Paulapauldron

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

Proportioned

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

Engstrom

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

Toddle

Answer 6

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

Evora

Answer 7

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

Congou

Answer 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.........

Equilateral

Answer 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

Subphylum

Answer 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;

}

Metalepsis