How to Fill Data in a Jtable with Database

How to fill data in a JTable with database?

I would recommend taking the following approach:

• Create a Row class to represent a row read from your ResultSet. This could be a simple wrapper around an Object[].
• Create a List<Row> collection, and subclass AbstractTableModel to be backed by this collection.
• Use a SwingWorker to populate your List<Row> by reading from the underlying ResultSet on a background thread (i.e. within the doInBackground() method). Call SwingWorker's publish method to publish Rows back to the Event Dispatch thread (e.g. every 100 rows).
• When the SwingWorker's process method is called with the latest chunk of Rows read, add them to your List<Row> and fire appropriate TableEvents to cause the display to update.
• Also, use the ResultSetMetaData to determine the Class of each column within the TableModel definition. This will cause them to be rendered correctly (which won't be the case if you simply use a 2D Object[][] array).

The advantage of this approach is that the UI will not lock up when processing large ResultSets, and that the display will update incrementally as results are processed.

EDIT

Added example code below:

/**
 * Simple wrapper around Object[] representing a row from the ResultSet.
 */
private 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 final List<Row> rows;

  public ResultSetTableModel(ResultSetMetaData rsmd) {
    this.rsmd = rsmd;
    this.rows = new ArrayList<Row>();
  }

  public int getRowCount() {
    return rows.size();
  }

  public int getColumnCount() {
    return rsmd.getColumnCount();
  }

  public Object getValue(int row, int column) {
    return rows.get(row).getValue(column);
  }

  public String getColumnName(int col) {
    return rsmd.getColumnName(col - 1); // ResultSetMetaData columns indexed from 1, not 0.
  }

  public Class<?> getColumnClass(int col) {
    // TODO: Convert SQL type (int) returned by ResultSetMetaData.getType(col) to Java Class.
  }
}

// SwingWorker implementation
new SwingWorker<Void, Row>() {
  public Void doInBackground() {
    // TODO: Process ResultSet and create Rows.  Call publish() for every N rows created.
  }

  protected void process(Row... chunks) {
    // TODO: Add to ResultSetTableModel List and fire TableEvent.
  }
}.execute();

Fill JTable with data from database

At every iteration, you're creating a new matrix data with only the info of the last product iterated. This is why you're seeing only the last item. This is just a syntax problem.

Assuming the productList already contains the results from your database, you could initialize the rows of your matrix data with the number of products in your list, while the number of columns with the fixed value 4, which is the number of fields you want to display (id, name, price and category).

ArrayList<Produkt> productList;

private void createTable() {
    String col[] = {"ID", "name", "price", "category"};
    
    //Initialize the number of rows of your matrix with the number of products in your list
    Object[][] data = new Object[productList.size()][4];

    //Copy every i-th product field in its corresponding column
    for (int i = 0; i < productList.size(); i++) {
        data[i][0] = productList.get(i).getId();
        data[i][1] = productList.get(i).getName();
        data[i][2] = productList.get(i).getPrice();
        data[i][3] = productList.get(i).getCategory();
    }
    
    productTable.setModel(new DefaultTableModel(data, col));
}

Populate JTable from database on login

I ended up solving the problem after alot of hours of research and many failed attempts. The introduction of a helper method that returns a table model to fire the update to the table was the key to the solution.

import javax.swing.*;
import javax.swing.table.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.util.Vector;

@SuppressWarnings("unchecked")
public class FillTable extends JFrame
{
    private Vector columnNames, data, row;
    private Connection connection;
    private final String dbUsername = "aUsername";
    private final String dbPassword = "aPass";

    private JTable groceryTable;
    private TableColumn column;
    private JScrollPane pane;
    private JPanel panel;

    public FillTable()
    {
        createComponents();
        setSize(800,800);
        setTitle("A filled Table");
    }

    public static void main(String[] args)
    {
        JFrame frame = new FillTable();
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frame.setUndecorated(false);
        frame.setLocationRelativeTo(null);
        frame.setResizable(true);
        frame.setVisible(true);
    }

class ButtonListener implements ActionListener
{
    public void actionPerformed(ActionEvent e)
    {
        try {
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
            connection = DriverManager.getConnection("redactedDBINFO", dbUsername, dbPassword);
            String sql = "select upper(name) as Grocery, fname as ordered_by, dateordered as date_ordered from groceries inner join grocerylist on groceries.groceryid=grocerylist.groceryid inner join users on grocerylist.orderedby= users.userid";  
            Statement statement = connection.prepareStatement(sql);
            ResultSet rset = statement.executeQuery(sql);            
            groceryTable.setModel(updateModel(rset));
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}

public DefaultTableModel updateModel(ResultSet rset) throws SQLException
{

    ResultSetMetaData metaData = rset.getMetaData();

    Vector<String> columnNames = new Vector<String>();

    int columnCount = metaData.getColumnCount();
    for (int column = 1; column <= columnCount; column++) {
        columnNames.add(metaData.getColumnName(column));
    }

    Vector<Vector<Object>> data = new Vector<Vector<Object>>();
    while (rset.next()) {
        Vector<Object> vector = new Vector<Object>();
        for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
            vector.add(rset.getObject(columnIndex));
        }
        data.add(vector);
    }
    return new DefaultTableModel(data, columnNames);
}

private void createComponents()
{
    JButton button = new JButton("Press me");
    ActionListener buttonListener = new ButtonListener();
    button.addActionListener(buttonListener);

    groceryTable = new JTable();

    pane = new JScrollPane(groceryTable);

    panel = new JPanel();
    panel.add(button);
    panel.add(pane);
    add(panel);
}   
}

How to populate JTable from database on button event

Ive removed the local variable

No, you removed the instance variable. Did you actually try this with your real code or just edit the question?

I have no idea how to provide a testable database for this question

I already suggested you create a method to simplify the logic. Somethng like:

public TableModel getDataFromDatabase()
{
    DefaultTableModel model = new DefaultTableModel(5, 5);
    model.setValueAt("Hard", 0, 0);
    model.setValueAt("Coded", 1, 1);
    model.setValueAt("Data", 2, 2);

    return model;
}

Then in your ActionListener you simple do something like:

table_ResQues.setModel( getDataFromDataBase() );

Once you get this basic logic working you move the SQL logic into the getDataFromDatabase() method.

So now you create your SSCCE showing how you actually create the frame and add the components to the frame. The code should be compilable and executable.

Edit:

You have been told to display a table is a scrollpane. It is no extra effort to do this. Instead of using:

panel.add(table);

you use:

panel.add( new JScrollPane( table ) );

I would also suggest that to test your layout you can use code like the following to display a dummy table:

//JTable table_ResQues = new JTable();
JTable table_ResQues = new JTable(5,5);

Then when you use the setModel() method, only the data is affected, not the layout of the table.

I cant help but feel this is a fireTableDataChanged problem though.

I doubt it. That method is invoked by the TableModel when you change the data in the model. It is not used in this case because you are using the setModel(...) method. This will cause the table to repaint() itself automatically.

How to populate JTable from ResultSet?

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

}

How can I populate my JTable with my embedded database?

I have found the solution - I've been trying to wrap my head around the problem for way too long, and I finally fixed it.

Try setting the table model manually first.

JTable tigerTable = new JTable();
tigerTable.setModel(new DefaultTableModel(0, numberOfColumns));

When you don't specify a model, the JTable creates an anonymous inner class and generally doesn't behave how you want it to.

Populating jTable using database data

So, you need some way to "tell" the table that the model has been loaded. You could use a listener call back mechanism, but it might be easier to use a SwingWorker instead.

This will allow you to make a call to the database in a background thread and when it's finished, update the UI from within the EDT.

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Vector;
import java.util.concurrent.ExecutionException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JTable;
import javax.swing.SwingWorker;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;
import sun.applet.Main;

public class DataLoadWorker extends SwingWorker<TableModel, TableModel> {

    private final JTable table;

    public DataLoadWorker(JTable table) {
        this.table = table;
    }

    @Override
    protected TableModel doInBackground() throws Exception {
        Vector data = new Vector();
        Vector columns = new Vector();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String stmt = "SELECT * FROM APP.DATAVAULT";
            ps = Main.getPreparedStatement(stmt);
            rs = ps.executeQuery();
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            //store column names  
            for (int i = 1; i <= columnCount; i++) {
                columns.add(md.getColumnName(i));
            }

            columns.ensureCapacity(columnCount);

            Vector row;
            while (rs.next()) {

                row = new Vector(columnCount);
                for (int i = 1; i <= columnCount; i++) {
                    row.add(rs.getString(i));
                }
                data.add(row);

                //Debugging                
            }

            // List.setModel(tableModel);

        } finally {
            try {
                ps.close();
            } catch (Exception e) {
            }
            try {
                rs.close();
            } catch (Exception e) {
            }
        }

        DefaultTableModel tableModel = new DefaultTableModel(data, columns);
        return tableModel;
    }

    @Override
    protected void done() {
        try {
            TableModel model = get();
            table.setModel(model);
        } catch (InterruptedException | ExecutionException ex) {
            ex.printStackTrace();
        }
    }
}

You're example won't work either.

The Vectors columns and data are declared withing the context of the try-catch, which means they won't be visible to the remainder of the method, so DefaultTableModel tableModel = new DefaultTableModel(data, columns); won't compile.

You should be, at the very least, dumping the stack trace of any exceptions, it will help you find where the actually error is, so instead of System.out.println(e.getMessage());, you should use e.printStackTrace();. A better solution is to use the Logger either from the JDK or a third party logger like log4j.

You are also resources, that is, if you open, you should close it. While you do call ps.close() and rs.close(), if, for what ever reason, an exception occurs, they will not be called, leaving resources open.

Add these to the finally block of your try-catch to ensure that they are closed and make all best efforts to close them.

---

Related Topics