How to insert, update and delete items from JTable that is loaded from (SQLite) Database

Question

I currently have 2 classes, one displaying the GUI and one is to get items from the database. My code is as follows:

This code is to display the JTable in my GUI

public void table() {
    if(SOMR.tableCall() == true) {
        this.columnNames = SOMR.getCol();
        this.data = SOMR.getData();
    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, BorderLayout.CENTER );
    }

}

and this code is to retrieve the items and pass them to the above codes to display the items retrieved to the JTable

public boolean table() {

    Connection connection = null;  
     ResultSet resultSet = null;  
     PreparedStatement preparedStatement = null;  
     try 
     {  
         Class.forName("org.sqlite.JDBC");  
         connection = DriverManager.getConnection("jdbc:sqlite:db");
         preparedStatement = connection.prepareStatement("SELECT item1, item2, item3 FROM menu WHERE can = ? AND id = ?");
         preparedStatement.setInt(1, can);
         preparedStatement.setInt(2, id);
         resultSet =  preparedStatement.executeQuery();
         ResultSetMetaData md = resultSet.getMetaData();
         columns = 3;//md.getColumnCount();
         for(int i = 1; i<=columns; i++) {
             columnNames.addElement(md.getColumnName(i));
         }
         while(resultSet.next()) {
             row = new Vector(columns);
             for (int i = 1; i<=columns; i++) {
                 row.addElement(resultSet.getObject(i));
             }
             data.addElement(row);
         }
         tablecall = true;
         return tablecall;
     }
     catch (Exception ex) 
     {  
         tablecall = false;
         ex.printStackTrace();  
     }
     finally 
     {  
         try 
         {  
             resultSet.close();  
             preparedStatement.close();  
             connection.close();  
         } 
         catch (Exception ex) 
         {  
             ex.printStackTrace();  
         }  
     }
    return tablecall;
}

I have followed the way of displaying the JTable from Table From Database, however I do not really know how to go about inserting, updating and deleting a row from the JTable and update in the database, then refresh the JTable to display from the newly updated database.

I would like to add an 'add item' button, then it will popup a frame/window with fields to enter, then after clicking 'add' in the popup frame, the JTable and the database will be updated at the same time.

Could anyone please help me in this? I'm lost.. Thank you very much!

Answer 1

I am still unclear about your requirements, but lets start answering as much as i have got to:

Every JTable object uses a table model object to manage the actual table data. A table model object must implement the TableModel interface. However the DefaultTableModel it self is enough to work with table data interaction. Check out it's java doc for more details.

However, To detect changes to the data managed by a table model object, the model associating with JTable instance needs to get registered to an implementation of TableModelListener interface using addTableModelListener() function. The listener will be notified with an event TableModelEvent e to inspect the changes in data rows and the type of changes using:

• e.getFirstRow(): Return the index of the first row that changed, including the table header which is specified by TableModelEvent.HEADER_ROW
• e.getLastRow(): The last row that changed
• e.getType(): What happened to the changed cells:
  1. If data rows inserted: e.getType() == TableModelEvent.INSERT
  2. If data rows deleted: e.getType() == TableModelEvent.DELETE
  3. If data rows updated: e.getType() == TableModelEvent.UPDATE.

A Short example:

model.addTableModelListener(new TableModelListener() {

     @Override
     public void tableChanged(TableModelEvent e) {
          int rowFirstIndex = e.getFirstRow();
          int rowLastIndex = e.getLastRow();

          DefaultTableModel model = (DefaultTableModel) e.getSource();
           if(e.getType()==TableModelEvent.UPDATE)
            {
                int updatedColIndex = e.getColumn();
                String updateColmn = table.getColumnName(updatedColIndex);
                String updatedValue = (String) model.getValueAt(rowFirstIndex, updatedColIndex);
                System.out.println("column: "+updateColmn+" value: "+updatedValue);
                updateDB(updateColmn, updatedValue);
            }

            else if(e.getType()==TableModelEvent.INSERT)
            {
             for(int i= rowFirstIndex; i <= rowLastIndex ; i++)  
              {   
                  Vector rowData = (Vector) model.getDataVector().get(i);

                  Map<String, String>dataMap = new HashMap<>();

                  for(int j=0; j < rowData.size() ; j++)
                    dataMap.put(table.getColumnName(j), (String) rowData.get(j));

                  InsertToDB(dataMap); // now it contains columndName corresponding to row value

              }
          }
         }
     });

Edit (on per your comment) : I am getting [value1, value2, value3]. How do I then use this with my SQL statement?

If you are interested to map the row value with column name, as is the case for building SQL query syntax: the vector contains the row data with maintaining column index. That is the row.get(i) has the data with column index i. You can use table.getColumnName(i) to get the name of the column at index i corresponding to the row vector value at index i.

Tutorial:

1. How to write a table model listener