Day 24 discussion

package week4training;
import java.util.Scanner;

public class Day24A {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        RunQuery02 callQuery = new RunQuery02();
        String opt;
        String prodName,prodCat;
        double prodPrice;
        int proQty, rowsAffected = 0;


        System.out.println("Options:");
        System.out.println("1 - View all items");
        System.out.println("2 - Add new item");
        System.out.print("Type number of action: ");
        opt = sc.nextLine();

        switch (opt) {
            case "1":
                callQuery.viewProdList();
                break;
            case "2":
                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;
        }
    }
}


//------

package week4training;
import java.sql.*;

public class RunQuery02 {
    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 (?, ?, ?, ?)";
        // ? IS A PLACEHOLDER
        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();
            // O RETURNED = INSERT INTO FAILED

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

    public void viewProdList() {
        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";

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

            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());
        }
    }
}