Day 22

package connecting_to_db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.swing.table.DefaultTableModel;

public class CrudProductListD21 {
    private String address = "jdbc:mysql://127.0.0.1:3306/db_mng_batch5?zeroDateTimeBehavior=convertToNull";
    private String userName = "root";
    private String passWord = "";

    public void getAllCategory(){
        String sqlQuery = "SELECT * FROM tbl_category_list ORDER BY fld_aisle_num";

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

            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                System.out.printf("%3s - ",rs.getString("fld_catid"));
                System.out.printf("%-20s%n",rs.getString("fld_catname"));
//                System.out.printf("%-3s%n",rs.getString("fld_aisle_num"));
            }

            conn.close();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }

    public void getAllRows(){
        String sqlQuery = "SELECT * FROM tbl_product_list ORDER BY fld_pid DESC LIMIT 0,15";
        // REMOVE  ORDER BY fld_pid DESC LIMIT 0,15  TO SHOW ALL ROWS
        try {
            Connection conn = DriverManager.getConnection(
                    address,userName,passWord);
            PreparedStatement stmt = conn.prepareStatement(sqlQuery);

            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                System.out.printf("%3s | ",rs.getString(1));
                System.out.printf("%-35s | ",rs.getString(2));
                System.out.printf("%8.2f | ",rs.getDouble(3));
                System.out.printf("%-35s | ",rs.getString(4));
                System.out.printf("%-3s%n",rs.getString(5));
            }

            conn.close();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }

    public void getAllRows(String searchName){
        String sqlQuery = "SELECT * FROM tbl_product_list WHERE LOWER(fld_pname) LIKE LOWER(?)";

        searchName = "%"+searchName+"%";
        // REMOVE  ORDER BY fld_pid DESC LIMIT 0,15  TO SHOW ALL ROWS
        try {
            Connection conn = DriverManager.getConnection(
                    address,userName,passWord);
            PreparedStatement stmt = conn.prepareStatement(sqlQuery);

            stmt.setString(1, searchName);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                System.out.printf("%3s | ",rs.getString(1));
                System.out.printf("%-35s | ",rs.getString(2));
                System.out.printf("%8.2f | ",rs.getDouble(3));
                System.out.printf("%-35s | ",rs.getString(4));
                System.out.printf("%-3s%n",rs.getString(5));
            }

            conn.close();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }

    public int addNewRow(String pName, double pPrice, String pManufacturer, int catId) {
        int rowsAffected = 0;
        String sqlQuery = "INSERT INTO tbl_product_list ( fld_pname, fld_price, fld_manufacturer, fld_catid) VALUES (?, ?, ?, ?);";
        try {
            Connection conn = DriverManager.getConnection(
                    address,userName,passWord);
            PreparedStatement stmt = conn.prepareStatement(sqlQuery);

            stmt.setString(1, pName);
            stmt.setDouble(2, pPrice);
            stmt.setString(3, pManufacturer);
            stmt.setInt(4, catId);

            rowsAffected = stmt.executeUpdate();

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

    public int updateRow(String pName, double pPrice, String pManufacturer, int catId, int prodId) {
        int rowsAffected = 0;
        String sqlQuery = "UPDATE tbl_product_list SET fld_pname=? ,fld_price=?"
                + " ,fld_manufacturer=?,fld_catid=?"
                + " WHERE fld_pid=?";

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

            stmt.setString(1, pName);
            stmt.setDouble(2, pPrice);
            stmt.setString(3, pManufacturer);
            stmt.setInt(4, catId);
            stmt.setInt(5, prodId);

            rowsAffected = stmt.executeUpdate();

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

    public int deleteProductRow(int pid) {
        int rowsAffected = 0;String sqlQuery = "DELETE FROM tbl_product_list WHERE fld_pid = ?";

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

            stmt.setInt(1, pid);

            rowsAffected = stmt.executeUpdate();

            conn.close();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }

        return rowsAffected;
    }
// ------- METHODS FORM USAGE -------
    public String[] setCatComboBox() {
        boolean isComplete = false;
        String[] returnedValue;
        String[] dummy = {"1|sample"};
        String[] categoryList;
        int count=0;
        String sqlQuery = "SELECT COUNT(*) FROM tbl_category_list";
        try {
            Connection conn = DriverManager.getConnection(
                    address,userName,passWord);
            PreparedStatement stmt = conn.prepareStatement(sqlQuery);
            ResultSet rs = stmt.executeQuery();
            rs.next();
            categoryList = new String[rs.getInt(1)];
            System.out.println(categoryList.length);
//            conn.close();
            sqlQuery = "SELECT * FROM tbl_category_list";

            try {
                PreparedStatement stmt1 = conn.prepareStatement(sqlQuery);

                ResultSet rs2 = stmt1.executeQuery();

                while (rs2.next()) {
                    categoryList[count] = rs2.getString(1) + "|" + rs2.getString(2);
                    count++;
                }
                conn.close();
                return categoryList;
            } catch (Exception e) {
                System.out.println("t2"+e.getMessage());
            }
        } catch (Exception e) {
            System.out.println("t1"+e.getMessage());
        }
        return dummy;
    }

    public void setRowsToTable(DefaultTableModel tableModel){
        String sqlQuery = "SELECT * FROM tbl_product_list ORDER BY fld_pid";
        // REMOVE  ORDER BY fld_pid DESC LIMIT 0,15  TO SHOW ALL ROWS
        try {
            Connection conn = DriverManager.getConnection(
                    address,userName,passWord);
            PreparedStatement stmt = conn.prepareStatement(sqlQuery);

            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                tableModel.addRow(new Object[]{
                    rs.getInt(1)
                    ,rs.getString(2)
                    ,rs.getDouble(3)
                    ,rs.getString(4)
                    ,rs.getInt(5)
                });
            }

            conn.close();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
}
//-------methods from insert form--------------------------------------

    public static boolean isNumeric(String strNum) {
        if (strNum == null) {
            return false;
        }
        try {
            double d = Double.parseDouble(strNum);
        } catch (NumberFormatException nfe) {
            return false;
        }
        return true;
    }

    public Color isNumericColor(String strNum){
        if (strNum == null) {
            return Color.RED;
        }
        try {
            double d = Double.parseDouble(strNum);
        } catch (NumberFormatException nfe) {
            return Color.RED;
        }
        return Color.white;
    }

    public Color isEmptyColor(String strNum){
        if (strNum == null || strNum.trim().isEmpty()) {
            return Color.RED;
        } else {
            return Color.white;
        }
    }

    public int getCategoryId(String catSelected) {
        int catId = 0;
        int barLoc = catSelected.indexOf("|");
        catId = Integer.parseInt(catSelected.substring(0,barLoc));
        return catId;
    }

//-------event methods from insert form--------------------------------------
private void formWindowActivated(java.awt.event.WindowEvent evt) {
        // TODO add your handling code here:
        CrudProductListD21 callCrud = new CrudProductListD21();
        String[] categoryList = callCrud.setCatComboBox();
        for (String string : categoryList) {
            cmbCategory.addItem(string);
        }
    }

    private void txtPriceKeyReleased(java.awt.event.KeyEvent evt) {
        // TODO add your handling code here:
        String txtIn = txtPrice.getText();
        txtPrice.setBackground(isNumericColor(txtIn));
    }

    private void txtPNameFocusLost(java.awt.event.FocusEvent evt) {
        // TODO add your handling code here:
        String txtIn = txtPName.getText();
        txtPName.setBackground(isEmptyColor(txtIn));
    }

    private void txtManufacturerFocusLost(java.awt.event.FocusEvent evt) {
        // TODO add your handling code here:
        String txtIn = txtManufacturer.getText();
        txtManufacturer.setBackground(isEmptyColor(txtIn));
    }

    private void btnAddActionPerformed(java.awt.event.ActionEvent evt) {
        // TODO add your handling code here:
        boolean isValid = true;
        if (txtPName.getBackground() == Color.RED
            || txtPrice.getBackground() == Color.RED
            || txtManufacturer.getBackground() == Color.RED) {
            isValid = false;
            JOptionPane.showMessageDialog(this, "One or more of the values is invalid");
        } else {
            String pName = txtPName.getText();
            String pManufacturer = txtManufacturer.getText();
            double pPrice = Double.parseDouble(txtPrice.getText());
            int catId = getCategoryId(cmbCategory.getSelectedItem().toString());
            CrudProductListD21 callCRUD = new CrudProductListD21();
            int rowAffected = callCRUD.addNewRow(pName, pPrice, pManufacturer, catId);
            if (rowAffected == 1) {
                JOptionPane.showMessageDialog(this, "New Row Added");
            } else {
                JOptionPane.showMessageDialog(this, "Add row failed");
            }
        }
    }