Day 22 Methods

package week4;

import java.util.Scanner;
public class Day22A {
//    Pwede natin paghiwalayin
//    pwede magsiksikan sila sa psvm

    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        Day22MethodsLine callMethods = new Day22MethodsLine();
        String pName;
        double pPrice;
        String pMfg;
        String pCategory;
        String opt;
        String searchColumn;
        String searchValue;
        int rowsAffected;

        System.out.println("Options:");
        System.out.println("1 - Add New Row");
        System.out.println("2 - View All Row");
        System.out.println("3 - Search by Product Name");
        System.out.println("any other character - View Table");
        System.out.print(">> ");
        opt = sc.nextLine();

        switch (opt) {
            case "1" :
                try {
                    System.out.println("Add New Row!!");
                    System.out.print("Enter Product Name: ");
                    pName = sc.nextLine();
                    System.out.print("Enter Price: ");
                    pPrice = sc.nextDouble();
                    sc.nextLine();
                    System.out.print("Enter Product Manufacturer: ");
                    pMfg = sc.nextLine();
                    System.out.print("Enter Product Category: ");
                    pCategory = sc.nextLine();

                    rowsAffected = callMethods.addNewRow(pName, pPrice, pMfg, pCategory);

//                    System.out.println(">" + pName);
//                    System.out.println(">" + pPrice);
//                    System.out.println(">" + pMfg);
//                    System.out.println(">" + pCategory);
//                    System.out.println(">" + rowsAffected);
                    if (rowsAffected == 1) {
                        System.out.println("New row added");
                    } else {
                        System.out.println("There is problem in adding new row");
                    }
                } catch (Exception e) {
                    System.out.println(e.toString());
                }
                break;
            case "2" :
                callMethods.printAllRows();
                break;
            case "3" :
                System.out.println("type the option of which column to:(default by name)");
                System.out.println("A - search by product name");
                System.out.println("B - search by product manufacturer");
                System.out.println("C - search by product category");
                System.out.print(">>> ");
                searchColumn = sc.nextLine();
                System.out.print("Search Name: ");
                searchValue = sc.nextLine();
                callMethods.printAllRows(searchColumn, searchValue);
                break;
            default:
                System.out.println("your selection is not an action");
        }
    }
}

//--------------------------------------------------

package week4;

import java.sql.*;
public class Day22MethodsLine {
    private String address = "jdbc:mysql://localhost:3306/db_jtvi_b8_23";
    private String userName = "jtvi23b8"; //default "root"
    private String passWord = "demo";

    int addNewRow(String name, double price, String mfg, String catName) {
        int rowsAffected = 0;
        try {
            Connection conn = DriverManager.getConnection(address, userName, passWord);
            String sqlQuery = "INSERT INTO tbl_price_list (fld_pname ,fld_price"
                    + " ,fld_manufacturer ,fld_cat_name)"
                    + " VALUES (? ,? ,? ,? );";

            PreparedStatement stmt = conn.prepareStatement(sqlQuery);
            stmt.setString(1, name);
            stmt.setDouble(2, price);
            stmt.setString(3, mfg);
            stmt.setString(4, catName);
            rowsAffected = stmt.executeUpdate();

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

    void printAllRows() { // pang command line na result
        try {
            Connection conn = DriverManager.getConnection(address, userName, passWord);
            String sqlQuery = "SELECT fld_pid ,fld_pname ,fld_price "
                    + ",fld_manufacturer ,fld_cat_name FROM tbl_price_list;";

            PreparedStatement stmt = conn.prepareStatement(sqlQuery);

            ResultSet rs = stmt.executeQuery();

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

        } catch (Exception e) {
            System.out.println(e.toString());
        }
    }

    // pwede na sana ito
    void printAllRows(String pName) { // pang command line na result
        String searchVal = "%" + pName + "%";
        try {
            Connection conn = DriverManager.getConnection(address, userName, passWord);
            String sqlQuery = "SELECT fld_pid ,fld_pname ,fld_price "
                    + ",fld_manufacturer ,fld_cat_name FROM tbl_price_list"
                    + " WHERE fld_pname LIKE ?;";

            PreparedStatement stmt = conn.prepareStatement(sqlQuery);
            stmt.setString(1, searchVal);

            ResultSet rs = stmt.executeQuery();

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

        } catch (Exception e) {
            System.out.println(e.toString());
        }
    }

    // what if di lang pang isang field na where
    void printAllRows(String pField, String pSearch) { // pang command line na result
        String searchVal = "%" + pSearch + "%";
        String searchField;
        switch (pField) {
            case "A":
                searchField = "fld_pname";
                break;
            case "B":
                searchField = "fld_manufacturer";
                break;
            case "C":
                searchField = "fld_cat_name";
                break;
            default:
                searchField = "fld_pname";
        }
        try {
            Connection conn = DriverManager.getConnection(address, userName, passWord);
            String sqlQuery = "SELECT fld_pid ,fld_pname ,fld_price "
                    + ",fld_manufacturer ,fld_cat_name FROM tbl_price_list"
                    + " WHERE " + searchField + " LIKE ?;";

            PreparedStatement stmt = conn.prepareStatement(sqlQuery);
            stmt.setString(1, searchVal);

            ResultSet rs = stmt.executeQuery();

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

        } catch (Exception e) {
            System.out.println(e.toString());
        }
    }
}

//CREATE TABLE tbl_price_list (
//    fld_pid INT NOT NULL AUTO_INCREMENT PRIMARY KEY
//    ,fld_pname VARCHAR (35) NOT NULL
//    ,fld_price DECIMAL (20, 4) NOT NULL
//    ,fld_manufacturer VARCHAR (35) NOT NULL
//    ,fld_cat_name VARCHAR (20) NOT NULL
//);