Day 21

package connecting_to_db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class CrudProductListD21 {
    String address = "jdbc:mysql://127.0.0.1:3306/db_mng_batch5?zeroDateTimeBehavior=convertToNull";
    String userName = "root";
    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 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;
    }
}


//-------------------------------
package connecting_to_db;
import java.util.Scanner;

public class Day21B {
    public static void main(String[] args) {
        CrudProductListD21 callCRUD = new CrudProductListD21();
        Scanner sc = new Scanner(System.in);
        String op, txtIn;
        int rowsAffected=0;
        boolean isValid = true;
        String pName, pManufacturer;
        double pPrice = 0;
        int catId = 0, prodId = 0;

        System.out.println("Using tbl_product_list");
        System.out.println("Options:");
        System.out.println("1 - Show all rows");
        System.out.println("2 - Add new row");
        System.out.println("3 - Search by product name");
        System.out.println("4 - Delete by id");
        System.out.print("Enter number of action:");
        op = sc.nextLine();

        switch (op) {
            case "1" :
                callCRUD.getAllRows();
                break;
            case "2" :
                System.out.print("Enter Product Name: ");
                pName = sc.nextLine();
                System.out.print("Enter Price: ");
                txtIn = sc.nextLine();
                if (isNumeric(txtIn)) {
                    pPrice = Double.parseDouble(txtIn);
                } else {
                    isValid = false;
                }
                System.out.print("Enter Manufacturer: ");
                pManufacturer = sc.nextLine();
                callCRUD.getAllCategory();
                System.out.print("Enter Category ID: ");
                txtIn = sc.nextLine();
                if (isNumeric(txtIn)) {
                    catId = Integer.parseInt(txtIn);
                } else {
                    isValid = false;
                }
                if (isValid == true) {
                    rowsAffected = callCRUD.addNewRow(pName, pPrice, pManufacturer, catId);
                    if (rowsAffected == 1) {
                        System.out.println("New row added");
                    } else {
                        System.out.println("Problem with SQL");
                    }
                } else {
                    System.out.println("One of your input is invalid");
                }
                break;
            case "3" :
                System.out.print("Enter Product Name to search: ");
                pName = sc.nextLine();
                callCRUD.getAllRows(pName);
                break;
            case "4" :System.out.print("Enter Category ID: ");
                txtIn = sc.nextLine();
                if (isNumeric(txtIn)) {
                    prodId = Integer.parseInt(txtIn);
                } else {
                    isValid = false;
                }
                if (isValid == true) {
                    rowsAffected = callCRUD.deleteProductRow(prodId);
                    if (rowsAffected == 1) {
                        System.out.println("Row deleted");
                    } else {
                        System.out.println("Problem with SQL");
                    }
                } else {
                    System.out.println("One of your input is invalid");
                }
                break;
        }
    }

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

}