Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement