How to use fireTableRowsInserted with CachedRowSet for inserting new row in JTable?

Question

I'm trying to insert new row in the table from a form (composed of text fields ) with CachedRowSet data structure in JTable using a button. The class creating the GIU (including the buttons, namely CoffeesFrame.java ) is as following,

public class CoffeesFrame extends JFrame implements RowSetListener {

    private static Connection myConn = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

   private static JTable table; // The table for displaying data
   private static JFrame frame;

  private static JLabel label_COF_NAME;
  private static JLabel label_SUP_ID;
  private static JLabel label_PRICE;
  private static JLabel label_SALES;
  private static JLabel label_TOTAL;

private static JTextField textField_COF_NAME;
private static JTextField textField_SUP_ID;
private static JTextField textField_PRICE;
private static JTextField textField_SALES;
private static JTextField textField_TOTAL;

private static JButton button_ADD_ROW;
private static JButton button_UPDATE_DATABASE;
private static JButton button_DISCARD_CHANGES;

CoffeesTableModel ctModel;


public CoffeesFrame() {

    url = "jdbc:mysql://localhost:3306/myDemo";
    username = "student";
    password = "student";
}

protected void createAndShowGUI(Connection con) throws SQLException {

    con.setAutoCommit(false);

    System.out.println("The GUI started");
    frame = new JFrame();
    frame.setTitle("My Coffee Frame");

    CachedRowSet crSet = getContentsOfCoffeesTable(con);
    ctModel = new CoffeesTableModel(crSet);
    ctModel.addEventHandelersToRowsSet(this);

    addWindowListener(new WindowAdapter() {

        public void windowClosing(WindowEvent e) {

            try {

                myConn.close();
            }

            catch (SQLException sl) {

                sl.printStackTrace();
            }

            System.exit(0);
        }
    });

    /*
    String[] columnNames = { "First Name", "Last Name", "Sport",
            "# of Years", "Vegetarian" };

    Object[][] data = {
            { "Kathy", "Smith", "Snowboarding", new Integer(5),
                    new Boolean(false) },
            { "John", "Doe", "Rowing", new Integer(3), new Boolean(true) },
            { "Sue", "Black", "Knitting", new Integer(2),
                    new Boolean(false) },
            { "Jane", "White", "Speed reading", new Integer(20),
                    new Boolean(true) },
            { "Joe", "Brown", "Pool", new Integer(10), new Boolean(false) } };

    // table = new JTable(data, columnNames);
    */

    table = new JTable();
    table.setModel(ctModel);

    label_COF_NAME = new JLabel();
    label_SUP_ID = new JLabel();
    label_PRICE = new JLabel();
    label_SALES = new JLabel();
    label_TOTAL = new JLabel();

    textField_COF_NAME = new JTextField(10);
    textField_SUP_ID = new JTextField(10);
    textField_PRICE = new JTextField(10);
    textField_SALES = new JTextField(10);
    textField_TOTAL = new JTextField(10);

    button_ADD_ROW = new JButton();
    button_UPDATE_DATABASE = new JButton();
    button_DISCARD_CHANGES = new JButton();

    label_COF_NAME.setText("Coffee Name:");
    label_SUP_ID.setText("Supplier ID:");
    label_PRICE.setText("Price:");
    label_SALES.setText("Sales:");
    label_TOTAL.setText("Total Sales:");

    textField_COF_NAME.setText("Enter new coffee name");
    textField_SUP_ID.setText("101");
    textField_PRICE.setText("0");
    textField_SALES.setText("0");
    textField_TOTAL.setText("0");

    button_ADD_ROW.setText("Add row to table");
    button_UPDATE_DATABASE.setText("Update database");
    button_DISCARD_CHANGES.setText("Discard changes");

    Container pane = getContentPane();
    pane.setComponentOrientation(ComponentOrientation.LEFT_TO_RIGHT);
    pane.setLayout(new GridBagLayout());
    GridBagConstraints cons = new GridBagConstraints();

    cons.fill = GridBagConstraints.BOTH;
    cons.anchor = GridBagConstraints.CENTER;
    cons.weightx = 0.5;
    cons.weighty = 1.0;
    cons.gridx = 0;
    cons.gridy = 0;
    cons.gridwidth = 2;
    pane.add(new JScrollPane(table), cons);

    cons.fill = GridBagConstraints.HORIZONTAL;
    cons.anchor = GridBagConstraints.LINE_START;
    cons.weightx = 0.25;
    cons.weighty = 0;
    cons.gridx = 0;
    cons.gridy = 1;
    cons.gridwidth = 1;
    pane.add(label_COF_NAME, cons);

    cons.fill = GridBagConstraints.HORIZONTAL;
    cons.anchor = GridBagConstraints.LINE_END;
    cons.weightx = 0.75;
    cons.weighty = 0;
    cons.gridx = 1;
    cons.gridy = 1;
    cons.gridwidth = 1;
    pane.add(textField_COF_NAME, cons);

    cons.fill = GridBagConstraints.HORIZONTAL;
    cons.weightx = 0.25;
    cons.weighty = 0;
    cons.anchor = GridBagConstraints.LINE_START;
    cons.gridx = 0;
    cons.gridy = 2;
    cons.gridwidth = 1;
    pane.add(label_SUP_ID, cons);

    cons.fill = GridBagConstraints.HORIZONTAL;
    cons.anchor = GridBagConstraints.LINE_END;
    cons.weightx = 0.75;
    cons.weighty = 0;
    cons.gridx = 1;
    cons.gridy = 2;
    cons.gridwidth = 1;
    pane.add(textField_SUP_ID, cons);

    cons.fill = GridBagConstraints.HORIZONTAL;
    cons.anchor = GridBagConstraints.LINE_START;
    cons.weightx = 0.25;
    cons.weighty = 0;
    cons.gridx = 0;
    cons.gridy = 3;
    cons.gridwidth = 1;
    pane.add(label_PRICE, cons);

    cons.fill = GridBagConstraints.HORIZONTAL;
    cons.anchor = GridBagConstraints.LINE_END;
    cons.weightx = 0.75;
    cons.weighty = 0;
    cons.gridx = 1;
    cons.gridy = 3;
    cons.gridwidth = 1;
    pane.add(textField_PRICE, cons);

    cons.fill = GridBagConstraints.HORIZONTAL;
    cons.anchor = GridBagConstraints.LINE_START;
    cons.weightx = 0.25;
    cons.weighty = 0;
    cons.gridx = 0;
    cons.gridy = 4;
    cons.gridwidth = 1;
    pane.add(label_SALES, cons);

    cons.fill = GridBagConstraints.HORIZONTAL;
    cons.anchor = GridBagConstraints.LINE_END;
    cons.weightx = 0.75;
    cons.weighty = 0;
    cons.gridx = 1;
    cons.gridy = 4;
    cons.gridwidth = 1;
    pane.add(textField_SALES, cons);

    cons.fill = GridBagConstraints.HORIZONTAL;
    cons.anchor = GridBagConstraints.LINE_START;
    cons.weightx = 0.25;
    cons.weighty = 0;
    cons.gridx = 0;
    cons.gridy = 5;
    cons.gridwidth = 1;
    pane.add(label_TOTAL, cons);

    cons.fill = GridBagConstraints.HORIZONTAL;
    cons.anchor = GridBagConstraints.LINE_END;
    cons.weightx = 0.75;
    cons.weighty = 0;
    cons.gridx = 1;
    cons.gridy = 5;
    cons.gridwidth = 1;
    pane.add(textField_TOTAL, cons);

    cons.fill = GridBagConstraints.HORIZONTAL;
    cons.anchor = GridBagConstraints.LINE_START;
    cons.weightx = 0.5;
    cons.weighty = 0;
    cons.gridx = 0;
    cons.gridy = 6;
    cons.gridwidth = 1;
    pane.add(button_ADD_ROW, cons);

    cons.fill = GridBagConstraints.HORIZONTAL;
    cons.anchor = GridBagConstraints.LINE_END;
    cons.weightx = 0.5;
    cons.weighty = 0;
    cons.gridx = 1;
    cons.gridy = 6;
    cons.gridwidth = 1;
    pane.add(button_UPDATE_DATABASE, cons);

    cons.fill = GridBagConstraints.HORIZONTAL;
    cons.anchor = GridBagConstraints.LINE_START;
    cons.weightx = 0.5;
    cons.weighty = 0;
    cons.gridx = 0;
    cons.gridy = 7;
    cons.gridwidth = 1;
    pane.add(button_DISCARD_CHANGES, cons);

    button_ADD_ROW.addActionListener(new ActionListener() {

        @Override
        public void actionPerformed(ActionEvent e) {

            // System.out.println("The add row");

            JOptionPane.showMessageDialog(frame, textField_COF_NAME.getText()+" "+
                    Integer.parseInt(textField_SUP_ID.getText().trim())+" "+
                    Float.parseFloat(textField_PRICE.getText().trim())+" "+
                    Integer.parseInt(textField_SALES.getText().trim())+" "+
                    Integer.parseInt(textField_TOTAL.getText().trim()));

            try {

                ctModel.insertRow(textField_COF_NAME.getText(),
                                              Integer.parseInt(textField_SUP_ID.getText().trim()),
                                              Float.parseFloat(textField_PRICE.getText().trim()),
                                              Integer.parseInt(textField_SALES.getText().trim()),
                                              Integer.parseInt(textField_TOTAL.getText().trim()));
              } catch (SQLException sqle) {

                  sqle.printStackTrace();
              }
        }
    });

    frame.add(pane);
    frame.pack();
    frame.setVisible(true);
}

private CachedRowSet getContentsOfCoffeesTable(Connection con)
        throws SQLException {

    CachedRowSet cr = null;

    try {

        cr = new CachedRowSetImpl();
        cr.setType(ResultSet.TYPE_SCROLL_INSENSITIVE);
        cr.setConcurrency(ResultSet.CONCUR_UPDATABLE);
        cr.setUsername(username);
        cr.setPassword(password);
        cr.setUrl(url);

        cr.setCommand("select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES");
        cr.execute();

    }

    catch (Exception ex) {

        ex.printStackTrace();
    }

    return cr;
}

@Override
public void rowSetChanged(RowSetEvent event) {

}

@Override
public void rowChanged(RowSetEvent event) {

}

@Override
public void cursorMoved(RowSetEvent event) {

}

public static void main(String[] args) throws SQLException {

    CoffeesFrame cf = new CoffeesFrame();

    try {

        myConn = DriverManager.getConnection(url, username, password);

        if (myConn != null) {

            System.out.println("The app is connected with the database");
        }

        else {

            System.out.println("The connection is not established");
        }
    }

    catch (Exception ex) {

        ex.printStackTrace();
    }

    // launch the swing app
    javax.swing.SwingUtilities.invokeLater(new Runnable() {

        @Override
        public void run() {

            try {
                cf.createAndShowGUI(myConn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    });
}
}

It uses CoffeesTableModel.java for data modeling as following,

public class CoffeesTableModel extends AbstractTableModel {

    public static CachedRowSet coffeeRowSet;
    public static java.sql.ResultSetMetaData rsMetaData;
    public static int ncolms, nrows;

public CoffeesTableModel(CachedRowSet crSet) throws SQLException {

    this.coffeeRowSet = crSet;
    this.rsMetaData = this.coffeeRowSet.getMetaData();
    ncolms = rsMetaData.getColumnCount();

    this.coffeeRowSet.beforeFirst();
    this.nrows = 0;

    while (this.coffeeRowSet != null & this.coffeeRowSet.next()) {

        nrows++;
    }
    this.coffeeRowSet.beforeFirst();
}

public void addEventHandelersToRowsSet(RowSetListener rsListener) {

    this.coffeeRowSet.addRowSetListener(rsListener);
}

public void insertRow(String coffeeName, int supplierID, float price,
        int sales, int total) throws SQLException {

    try {

        this.coffeeRowSet.moveToInsertRow();
        this.coffeeRowSet.updateString("COF_NAME", coffeeName);
        this.coffeeRowSet.updateInt("SUP_ID", supplierID);
        this.coffeeRowSet.updateFloat("PRICE", price);
        this.coffeeRowSet.updateInt("SALES", sales);
        this.coffeeRowSet.updateInt("TOTAL", total);

        this.coffeeRowSet.insertRow();
        this.coffeeRowSet.moveToCurrentRow();
        fireTableRowsInserted(coffeeRowSet.getRow()-1, coffeeRowSet.getRow()-1);

        // I get SQL error after un-commenting this line 
        // this.coffeeRowSet.acceptChanges();
    }

    catch (Exception ex) {

        ex.printStackTrace();
    }
}

public void close() {

    try {

        coffeeRowSet.getStatement().close();
    }

    catch (Exception ex) {

        ex.printStackTrace();
    }
}

public String getColumnName(int columnIndex) {

    String colName = null;

    try {

        colName = this.rsMetaData.getColumnLabel(columnIndex + 1);
    } catch (Exception ex) {

        ex.toString();
    }

    return colName;
}

@Override
public int getRowCount() {

    return nrows;
}

@Override
public int getColumnCount() {

    return ncolms;
}

public Class getColumnClass(int column) {

    return String.class;
  }

@Override
public Object getValueAt(int rowIndex, int columnIndex) {

    Object o = new Object();
    try {

        this.coffeeRowSet.absolute(rowIndex + 1);
        o = this.coffeeRowSet.getObject(columnIndex + 1);
    }

    catch (SQLException ex) {
        ex.printStackTrace();
    }

    if (o == null){

        return null;
    }

    else{

        return o.toString();
    }   
}

public boolean isEdible(int rowIndex , int colIndex ){

    return false; 
}

public void setValueAt(Object o, int col, int row ){

    System.out.println();
}}

JOptionPane shows the data after pushing the "Add row to table" button, but, no data is inserted in the table. How can I improve the code ?

Answer 1

Now that you have added more code to your question, I am wondering why you want to use the CachedRowSet interface (see for example CachedRowSet: can it still be used to hold ResultSet data? and CachedRowSet slower than ResultSet?). I would consider retrieving the data from the database and storing it in regular objects or a table model.

For the table model, you can also use the DefaultTableModel class. This has the advantage that a default implementation has been created for all methods, which makes your work a lot easier. It is probably a good idea to separate the table model as much as possible from the database access.

You could copy all coffee data from the database to the DefaultTableModel instance and close the database connection (you can even do this outside of the table model class). Later, you can start a new database connection when you want to update the database (for example by deleting all the old records and inserting all the current records; you can retrieve the current records in the table model using the getDataVector method).

A table model class could look like this:

import java.sql.*;
import java.util.Vector;
import javax.swing.table.DefaultTableModel;

public class CoffeesTableModelV2 extends DefaultTableModel {
    public CoffeesTableModelV2(Vector data, Vector columnNames) {
        super(data, columnNames);
    }

    public void insertRow(String coffeeName, int supplierID, float price,
                          int sales, int total) {
        final Vector<Object> rowVector = new Vector<>();
        rowVector.add(coffeeName);
        rowVector.add(supplierID);
        rowVector.add(price);
        rowVector.add(sales);
        rowVector.add(total);

        addRow(rowVector);
    }

    @Override
    //public boolean isEdible(int rowIndex, int colIndex) {
    public boolean isCellEditable(int rowIndex, int columnIndex) {
        return false;
    }

    @Override
    public void setValueAt(Object o, int col, int row) {
        final String message = "should not be called for a read-only table model";
        throw new RuntimeException("CoffeesTableModelV2.setValueAt " + message);
    }

    public static CoffeesTableModelV2 createTableModel(ResultSet resultSet)
            throws SQLException {
        return new CoffeesTableModelV2(getData(resultSet), getColumnNames(resultSet));
    }

    private static Vector<Vector<Object>> getData(ResultSet resultSet)
            throws SQLException {
        Vector<Vector<Object>> data = new Vector<>();

        int columnCount = resultSet.getMetaData().getColumnCount();
        while (resultSet.next()) {
            Vector<Object> rowVector = new Vector<>();

            for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
                rowVector.add(resultSet.getObject(columnIndex));
            }

            data.add(rowVector);
        }

        return data;
    }

    private static Vector<String> getColumnNames(ResultSet resultSet) 
            throws SQLException {
        Vector<String> columnNames = new Vector<>();

        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
            columnNames.add(metaData.getColumnName(columnIndex));
        }

        return columnNames;
    }
}