Update specific Cell from Jtable in SQL

Question

Hi I am trying to update a specific cell from my JTable. I am doing a reservation where once the selected reservation has been approved, it will be changed from No to Yes. However, after clicking on Approve, All the column in ReservationStatus has been changed from No to Yes, although i want only a specific cell to change(Eg row 0 col 6). This is my code for the Jtable:

table = new JTable(){
        public boolean isCellEditable(int row, int col) {
            return false;
        };
    };
    table.getTableHeader().setReorderingAllowed(false);
    table.addMouseListener(new MouseAdapter() {
        @Override
        public void mouseClicked(MouseEvent e) {
            row = table.rowAtPoint(e.getPoint());
            col = table.columnAtPoint(e.getPoint());
            data = table.getValueAt(row, col);
            System.out.println("Row= " + row + "column =" + col + "Data= " + data);
            DefaultTableModel model = (DefaultTableModel)table.getModel();
            int selectedRowIndex = table.getSelectedRow();
            if(e.getClickCount() == 2) {
                if(col == 6){
                    frame.setContentPane(new approvalReservation(frame));
                    approvalReservation.lblDisplayAdminNo.setText(model.getValueAt(selectedRowIndex, 0).toString());
                    approvalReservation.lblDisplayMusicalInstrument.setText(model.getValueAt(selectedRowIndex, 1).toString());
                    approvalReservation.lblDisplayDuration.setText(model.getValueAt(selectedRowIndex, 2).toString());
                    approvalReservation.lblDisplayExtension.setText(model.getValueAt(selectedRowIndex, 5).toString());
                }
                else if(col == 7) {
                    frame.setContentPane(new approvalAccess(frame));
                    approvalAccess.lblDisplayAdminNo.setText(model.getValueAt(selectedRowIndex, 0).toString());
                    approvalAccess.lblDisplayLockerNumber.setText(model.getValueAt(selectedRowIndex, 3).toString());
                    approvalAccess.lblDisplayLocation.setText(model.getValueAt(selectedRowIndex, 4).toString());
                }

            }
        }
    });


    scrollPane.setViewportView(table);

    JButton btnLoadTable = new JButton("Load Table");
    btnLoadTable.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent e) {
            approvalController ac = new approvalController();
            ArrayList<String> approvalList = approvalController.displayTableReservation();
            for(int i = 0; i < approvalList.size(); i++) {
                approvalList.get(i);
            }

        }
    });

PS: I declared row and col as public static so both can be passed to another JPanel.

And this is the Approval Form codes:

JButton btnApprove = new JButton("Approve");
    btnApprove.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent arg0) {
            Connection connection=null;
            connection = sqliteConnection.dbConnector();
            try {
                String query = "Update approval set reservationStatus = 'Yes' where reservationStatus = '" + approvalMain.data + "'";
                PreparedStatement pst = connection.prepareStatement(query);
                pst.executeUpdate();
                System.out.println("Updated");
                JOptionPane.showMessageDialog(null, "Data Updated");


            }

            catch(Exception e) {
                e.printStackTrace();
            }
        }
    });
    btnApprove.setBackground(SystemColor.info);
    btnApprove.setBounds(650, 361, 135, 50);
    panel.add(btnApprove);

Another Question is, when the ReservationStatus is "No", the user is unable to click onto the next column, AccessStatus. As AccessStatus is only clickable after the ReservationStatus has been approved, from No to Yes. How do i go about doing that? As of now both cells inside the columns can be clicked.

JTable Before Approving

After clicking on Approve, note that all the cells in ReservationStatus has been changed from No to Yes

1) Your "Approve" button doesn't even update the JTable, so who knows how the values in the table change. All your code does is invoke SQL on your database. Also, you are using PreparedStatement incorrectly. you should be setting parameters for the PreparedStatement. For example: stackoverflow.com/questions/15713360/… — camickr
2) Why are you using a MouseListener? It looks like this logic might completely refresh the JTable. If so, then this tells me that your SQL to update the data is setting all the value to "Yes". So fix, the SQL and use the PreparedStatement properly, it will be easier to spot your error. Don't forget to add debug code to display the values of the parameters you pass to the PreparedStatement. — camickr
3) I'm not even sure why you need the MouseListener or why you need to reset the content pane? Editing the table should cause the table to update itself. Also, you would use the setModel(...) method to refresh the table, instead of creating a whole new table. — camickr
@camickr the approve button is supposed to update the cell in the database table which has the word "No" in it, however it updated all the column instead of a specific row in the column — Ruiru
I suggested your SQL was wrong. The means you need to fix your "where" clause to only update the row that you want to update. I don't now the structure of your database but you would need to specify the "key" that uniquely identifies each row. — camickr

Sean Van Gorder Sean Van Gorder · Accepted Answer · 2017-08-01T19:50:51

String query = "Update approval set reservationStatus = 'Yes' where reservationStatus = '" + approvalMain.data + "'";

Assuming approvalMain.data equals 'No', this query is saying to update the status of every row where the status is currently 'No'. If you only want to change one row, you need to specify it in your where clause, preferably using the primary key column(s) of your table.

Update specific Cell from Jtable in SQL

1 Answers