Day 25 Discussion

package week4training;

import java.util.Scanner;

public class Day25A {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        Day25ACodeBehind callQuery = new Day25ACodeBehind();
        //variable ng table
        String prodName,prodCat;
        double prodPrice;
        int pID = 0, proQty;
        String[] prodArray;
        //variable ng java code
        String opt, confirm;
        int rowsAffected = 0;

        System.out.println("Options:");
        System.out.println("1 - View all items");
        System.out.println("2 - Search by product name");
        System.out.println("3 - Search by category");
        System.out.println("4 - Add new item");
        System.out.println("5 - Update item by Product ID");
        System.out.println("6 - Remove item by Product ID");
        System.out.print("Type number of action: ");
        opt = sc.nextLine();

        switch (opt) {
            case "1":
                callQuery.viewProdList("");
                break;
            case "2":
                System.out.print("Product Name: ");
                prodName = sc.nextLine();
                callQuery.viewProdList(prodName);
                System.out.println("---Search End---");
                break;
            case "3":
                System.out.print("Category: ");
                prodCat = sc.nextLine();
                callQuery.searchByCategory(prodCat);
                System.out.println("---Search End---");
                break;
            case "4":
                System.out.println("Enter info for new row:");
                try {
                    System.out.print("Product Name: ");
                    prodName = sc.nextLine();
                    System.out.print("Product Price: ");
                    prodPrice = sc.nextDouble();
                    sc.nextLine();
                    System.out.print("Category: ");
                    prodCat = sc.nextLine();
                    System.out.print("Quantity: ");
                    proQty = sc.nextInt();
                    sc.nextLine();
                    rowsAffected = callQuery.addNewRow(prodName, prodPrice, prodCat, proQty);
                } catch (Exception e) {
                    System.out.println("invalid input please try again");
                }

                if (rowsAffected == 1) {
                    System.out.println("Record Complete");
                } else {
                    System.out.println("Record failed");
                }
                break;
            case "5":
                callQuery.viewProdList("");
                try {
                    System.out.print("Enter Product ID of row to be updated: ");
                    pID = sc.nextInt();
                    sc.nextLine();
                    prodArray = callQuery.getSelectedRow(pID);
                    System.out.print("Updated Product Name: ");
                    prodName = sc.nextLine();
                    if (prodName.isEmpty()) {
                        prodName = prodArray[0];
                    }
                    System.out.print("Updated Product Price: ");
                    prodPrice = sc.nextDouble();
                    sc.nextLine();
                    if (prodPrice == 0) {
                        prodPrice = Double.parseDouble(prodArray[1]);
                    }
                    System.out.print("Updated Category: ");
                    prodCat = sc.nextLine();
                    if (prodCat.isEmpty()) {
                        prodCat = prodArray[2];
                    }
                    System.out.print("Updated Quantity: ");
                    proQty = sc.nextInt();
                    sc.nextLine();
                    if (proQty == 0) {
                        proQty = Integer.parseInt(prodArray[3]);
                    }
                    rowsAffected = callQuery.updateRow(prodName, prodPrice, prodCat, proQty, pID);
                } catch (Exception e) {
                    System.out.println("invalid input please try again");
                }

                if (rowsAffected == 1) {
                    System.out.println("Update Successful");
                } else {
                    System.out.println("Update failed");
                }
                break;
            case "6":
                callQuery.viewProdList("");
                System.out.print("Enter Product ID of row to be deleted: ");
                pID = sc.nextInt();
                sc.nextLine();
                System.out.println("Are you sure you want to delete the row?(Y/N)");
                confirm = sc.nextLine();
                switch (confirm) {
                    case "Y":
                        rowsAffected = callQuery.deleteRowByID(pID);
                        if (rowsAffected == 1) {
                            System.out.println("Row deleted");
                        } else {
                            System.out.println("Action failed");
                        }
                        break;
                }
                break;
        }
    }
}
//------------------------------
package week4training;
import java.sql.*;

public class Day25ACodeBehind {
    public int addNewRow(String prodName
            , double prodPrice, String prodCat, int proQty) {
        int rowAffected = 0;
        String address = "jdbc:mysql://localhost:3306/db_mng_b2";
        String userName = "root";
        String passWord = "";
        String sqlQuery = "INSERT INTO tbl_prod_list"
                + "(fld_prod_name, fld_prod_price, fld_category, fld_quantity) "
                + "VALUES (?, ?, ?, ?)";
        try {
            Connection conn = DriverManager.getConnection(
                    address,userName,passWord);
            PreparedStatement stmt = conn.prepareStatement(sqlQuery);

            stmt.setString(1, prodName);
            stmt.setDouble(2, prodPrice);
            stmt.setString(3, prodCat);
            stmt.setInt(4, proQty);

            rowAffected = stmt.executeUpdate();

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

    public void viewProdList(String pName) {
        String address = "jdbc:mysql://localhost:3306/db_mng_b2";
        String userName = "root";
        String passWord = "";
        String sqlQuery = "SELECT fld_plist_id"
                + ", fld_prod_name"
                + ", fld_prod_price"
                + ", fld_category"
                + ", fld_quantity "
                + " FROM tbl_prod_list"
                + " WHERE fld_prod_name LIKE ?";
        String searchParam = "%"+pName+"%";

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

            stmt.setString(1,searchParam);

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println(String.format(
                    "%-4d | %-30s | %8.2f | %-30s | %4d"
                    ,rs.getInt(1)
                    ,rs.getString(2)
                    ,rs.getFloat(3)
                    ,rs.getString(4)
                    ,rs.getInt(5)
                ));
            }
            conn.close();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }

    public void searchByCategory(String pCategory) {
        String address = "jdbc:mysql://localhost:3306/db_mng_b2";
        String userName = "root";
        String passWord = "";
        String sqlQuery = "SELECT fld_plist_id"
                + ", fld_prod_name"
                + ", fld_prod_price"
                + ", fld_category"
                + ", fld_quantity "
                + " FROM tbl_prod_list"
                + " WHERE fld_category LIKE ?";
        String searchParam = "%"+pCategory+"%";

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

            stmt.setString(1,searchParam);

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println(String.format(
                    "%-4d | %-30s | %8.2f | %-30s | %4d"
                    ,rs.getInt(1)
                    ,rs.getString(2)
                    ,rs.getFloat(3)
                    ,rs.getString(4)
                    ,rs.getInt(5)
                ));
            }
            conn.close();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }

    public int deleteRowByID(int pID) {
        int rowAffected = 0;
        String address = "jdbc:mysql://localhost:3306/db_mng_b2";
        String userName = "root";
        String passWord = "";
        String sqlQuery = "DELETE FROM tbl_prod_list WHERE fld_plist_id = ?";

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

            stmt.setInt(1, pID);
            rowAffected = stmt.executeUpdate();

            conn.close();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return rowAffected;
    }
    public String[] getSelectedRow(int pID) {
        // ang silbi lang nito sa update ay para di mawala yung laman kung hindi babaguhin
        String[] rowValue = new String[5];
        String address = "jdbc:mysql://localhost:3306/db_mng_b2";
        String userName = "root";
        String passWord = "";
        String sqlQuery = "SELECT * FROM tbl_prod_list WHERE fld_plist_id = ?";

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

            stmt.setInt(1, pID);
            ResultSet rs = stmt.executeQuery();
            rs.next();
            rowValue[4] = rs.getString(1);
            rowValue[0] = rs.getString(2);
            rowValue[1] = rs.getString(3);
            rowValue[2] = rs.getString(4);
            rowValue[3] = rs.getString(5);

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

        return rowValue;
    }
    public int updateRow(String prodName
            , double prodPrice, String prodCat, int proQty, int pID) {
        int rowAffected = 0;
        String address = "jdbc:mysql://localhost:3306/db_mng_b2";
        String userName = "root";
        String passWord = "";
        String sqlQuery = "UPDATE tbl_prod_list"
                + " SET fld_prod_name=?"
                + ", fld_prod_price=?"
                + ", fld_category=?"
                + ", fld_quantity=?"
                + " WHERE fld_plist_id=?";
        try {
            Connection conn = DriverManager.getConnection(
                    address,userName,passWord);
            PreparedStatement stmt = conn.prepareStatement(sqlQuery);

            stmt.setString(1, prodName);
            stmt.setDouble(2, prodPrice);
            stmt.setString(3, prodCat);
            stmt.setInt(4, proQty);
            stmt.setInt(5, pID);

            rowAffected = stmt.executeUpdate();

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

}