Day 26 update DELETE row search table

// ang silbi nito para makuha ang laman ng row para sa update/delete
    private void btnForUpdateDeleteActionPerformed(java.awt.event.ActionEvent evt) {
        int tableRow = jTable1.getSelectedRow();
        lblNumID.setText(jTable1.getValueAt(tableRow, 0).toString());
        txtString1.setText(jTable1.getValueAt(tableRow, 1).toString());
        cmbString2.setSelectedItem(jTable1.getValueAt(tableRow, 2).toString());
        txtNum1.setText(jTable1.getValueAt(tableRow, 3).toString());
        txtNum2.setText(jTable1.getValueAt(tableRow, 4).toString());
        String dateIn;
        try {
            dateIn = jTable1.getValueAt(tableRow, 5).toString();
        } catch (Exception e) {
            Date date = new Date();
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
            dateIn = formatter.format(date);
        }
        txtDate.setText(dateIn);
        // para i enable ang update or delete button
        toUpdateDelete();
    }
// ginawa para mapigilan ma add ulit ang i a update na row
    public void toUpdateDelete() {
        btnAddRow1.setEnabled(false);
        btnForUpdateDelete.setEnabled(false);
        btnConfirmUpdate.setEnabled(true);
        btnConfirmDelete.setEnabled(true);
    }

// button na mag ra run ng update:
    private void btnConfirmUpdateActionPerformed(java.awt.event.ActionEvent evt) {
// -----setup
        Day25MethodInfoList callMethod = new Day25MethodInfoList();
        String boxMessage = "";
        int boxIcon = 0;
// -----continuation of the setup inside try catch
        try {
            String txt1;
            String txt2;
            int num1;
            double num2;
            java.sql.Date dateIn;
            int rowID;

            txt1 = txtString1.getText();
            txt2 = cmbString2.getSelectedItem().toString();
            num1 = Integer.parseInt(txtNum1.getText());
            num2 = Double.parseDouble(txtNum2.getText());
            dateIn = java.sql.Date.valueOf(txtDate.getText());
            rowID = Integer.parseInt(lblNumID.getText());
// ---------execution
            int result = callMethod.updateRow(txt1, txt2, num1, num2, dateIn, rowID);
// ---------printing a result
            switch (result) {
                case 1:
                    boxMessage = "Row Updated";
                    boxIcon = JOptionPane.INFORMATION_MESSAGE;
                break;
                case 0:
                    boxMessage = "Update Failed";
                    boxIcon = JOptionPane.WARNING_MESSAGE;
                break;
                case -1:
                    boxMessage = "Connection Error";
                    boxIcon = JOptionPane.ERROR_MESSAGE;
                break;
            }
        } catch (Exception e) {
            boxMessage = "One of the input is invalid";
            boxIcon = JOptionPane.ERROR_MESSAGE;
        }
        callMethod.getAllRows(jTable1);
        // para malinis ulit ang mga text box
        cleanForm();
        JOptionPane.showMessageDialog(this, boxMessage
                , "Input Result", boxIcon);
    }

// pang linis lang talaga ng mga text box
    public void cleanForm() {
        lblNumID.setText("0");
        txtString1.setText("");
        cmbString2.setSelectedIndex(0);
        txtNum1.setText("");
        txtNum2.setText("");
        Date date = new Date();
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        String strDate= formatter.format(date);
        txtDate.setText(strDate);
        btnAddRow1.setEnabled(true);
        btnForUpdateDelete.setEnabled(true);
        btnConfirmUpdate.setEnabled(false);
        btnConfirmDelete.setEnabled(false);
    }

// ang method na nag a update ng row mismo
    public int updateRow(String txt1, String txt2, int num1, double num2, java.sql.Date dateIn, int ID){
        int rowsAffected = 0;
        String sqlQuery = "UPDATE tbl_infolist SET fld_text1=?, fld_text2=?, fld_num_i=?"
                + ", fld_num_d=?, fld_date_manual=?, fld_date_auto=CURRENT_TIMESTAMP WHERE fld_id=?";

        try {
            Connection conn = DriverManager.getConnection(address, userName, passWord);
            PreparedStatement stmt = conn.prepareStatement(sqlQuery);

            stmt.setString(1, txt1);
            stmt.setString(2, txt2);
            stmt.setInt(3, num1);
            stmt.setDouble(4, num2);
            stmt.setDate(5, dateIn);
            stmt.setInt(6, ID);

            rowsAffected = stmt.executeUpdate();

            conn.close();
        } catch (Exception e) {
            rowsAffected = -1;
            System.out.println(e.getMessage());
        }

        return rowsAffected;
    }

    public void getAllRows(JTable tableIn) {
        DefaultTableModel tblModel = (DefaultTableModel) tableIn.getModel();
        tblModel.setRowCount(0);
        String sqlQuery = "SELECT * FROM tbl_infolist;";

        try {
            Connection conn = DriverManager.getConnection(address, userName, passWord);
            PreparedStatement stmt = conn.prepareStatement(sqlQuery);

            ResultSet rs = stmt.executeQuery();
            //result set starts with 1 or field name
            while (rs.next()) {
                Object[] newRow = {
                    rs.getInt(1)
                    ,rs.getString(2)
                    ,rs.getString(3)
                    ,rs.getInt(4)
                    ,rs.getDouble(5)
                    ,rs.getString(6)
                    ,rs.getString(7)};
                tblModel.addRow(newRow);
            }
            conn.close();
        } catch (Exception e) {
            Object[] error = new Object[7];
            error[0] = "Connection error:\n" + e.getMessage();
            tblModel.addRow(error);
        }
    }

// --------DELETE----------------------
private void btnDeleteActionPerformed(java.awt.event.ActionEvent evt) {
        Day25MethodInfoList callMethod = new Day25MethodInfoList();
        String boxMessage = "";
        int boxIcon = 0;

        int tableRow = jTable1.getSelectedRow();
        int ID = Integer.valueOf(jTable1.getValueAt(tableRow, 0).toString());
        String txt1 = jTable1.getValueAt(tableRow, 1).toString();
        String txt2 = jTable1.getValueAt(tableRow, 2).toString();

        String deleteInfo = "ID: " + ID
                + "\nText1: " + txt1
                + "\nText2: " + txt2;
        int result = JOptionPane.showConfirmDialog(this
                , String.format("Do you want to delete:%n%s",deleteInfo)
                , "DELETING ROW"
                , JOptionPane.YES_NO_OPTION
                , JOptionPane.WARNING_MESSAGE);
        if (result == JOptionPane.YES_OPTION) {
            int rowDeleted = callMethod.deleteRow(ID);
            switch (rowDeleted) {
                case 1:
                    boxMessage = "Row Deleted";
                    boxIcon = JOptionPane.INFORMATION_MESSAGE;
                break;
                case 0:
                    boxMessage = "Delete Failed";
                    boxIcon = JOptionPane.WARNING_MESSAGE;
                break;
                case -1:
                    boxMessage = "Connection Error";
                    boxIcon = JOptionPane.ERROR_MESSAGE;
                break;
            }
            JOptionPane.showMessageDialog(this, boxMessage
                    , "Input Result", boxIcon);
        }
        callMethod.getAllRows(jTable1);
    }
//  YUNG METHOD:
    public int deleteRow(int ID){
        int rowsAffected = 0;
        String sqlQuery = "DELETE FROM tbl_infolist WHERE fld_id=?";

        try {
            Connection conn = DriverManager.getConnection(address, userName, passWord);
            PreparedStatement stmt = conn.prepareStatement(sqlQuery);

            stmt.setInt(1, ID);

            rowsAffected = stmt.executeUpdate();

            conn.close();
        } catch (Exception e) {
            rowsAffected = -1;
            System.out.println(e.getMessage());
        }

        return rowsAffected;
    }

// --------SELECT WHERE----------------------
//code ng search button
    private void btnSearchActionPerformed(java.awt.event.ActionEvent evt) {
        String refColumn = cmbSearchCol.getSelectedItem().toString();
        String refValue = txtSearchVal.getText();

        Day26MethodInfoList callMethod = new Day26MethodInfoList();
        callMethod.getAllRows(jTable1, refColumn, refValue);
    }

//  YUNG METHOD:
// PANG VARCHAR NA FIELD LANG TONG WHERE NA ITO
    public void getAllRows(JTable tableIn,String refColumn, String refValue) {
        DefaultTableModel tblModel = (DefaultTableModel) tableIn.getModel();
        tblModel.setRowCount(0);
        String searchField = "fld_text1";
        String searchValue = "%"+refValue+"%";
        // yung mga case ay yung nasa pilian na combo box
        switch (refColumn) {
            case "Text1":
                searchField = "fld_text1";
                break;
            case "Text2":
                searchField = "fld_text1";
                break;
        }

        String sqlQuery = String.format("SELECT * FROM tbl_infolist"
                + " WHERE LOWER(%s) LIKE LOWER(?) ;",searchField);

        try {
            Connection conn = DriverManager.getConnection(address, userName, passWord);
            PreparedStatement stmt = conn.prepareStatement(sqlQuery);
            //lalagyan ng laman yung ? placeholder
            stmt.setString(1, searchValue);

            ResultSet rs = stmt.executeQuery();
            //result set starts with 1 or field name
            while (rs.next()) {
                Object[] newRow = {
                    rs.getInt(1)
                    ,rs.getString(2)
                    ,rs.getString(3)
                    ,rs.getInt(4)
                    ,rs.getDouble(5)
                    ,rs.getString(6)
                    ,rs.getString(7)};
                tblModel.addRow(newRow);
            }
            conn.close();
        } catch (Exception e) {
            Object[] error = new Object[7];
            error[0] = "Connection error:\n" + e.getMessage();
            tblModel.addRow(error);
        }
    }