DAY28

import java.util.Scanner;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;


public class Day28A {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        Day28A callMe = new Day28A();
        String pname;
        String pcat;
        double price;
        int qty;
        int rowsAffected;

        System.out.println("Enter item to add to row");
        System.out.print("Product name: ");
        pname = sc.nextLine();
        System.out.print("Product category: ");
        pcat = sc.nextLine();
        System.out.print("Product price: ");
        price = sc.nextDouble();
        sc.nextLine();
        System.out.print("Product quantity: ");
        qty = sc.nextInt();
        sc.nextLine();

        rowsAffected = callMe.addToTable(pname, pcat, price, qty);

        System.out.println(rowsAffected);
    }

    int addToTable(String pname, String pcat, double price, int qty){
        int rowsAffected = 0;
        String connString = "jdbc:mysql://localhost:3306/db_sg_b2_24";
        String userName = "root";
        String passWord = "";
        String sqlQuery = "INSERT INTO tbl_price_list(fld_pname, fld_pcategory"
                + ", fld_price, fld_qty)"
                + " VALUES (?,?,?,?)";

        try {
            Connection conn = DriverManager.getConnection(connString, userName, passWord);

            PreparedStatement stmt = conn.prepareStatement(sqlQuery);
            stmt.setString(1, pname);
            stmt.setString(2, pcat);
            stmt.setDouble(3, price);
            stmt.setInt(4, qty);

            rowsAffected = stmt.executeUpdate();

            conn.close();
        } catch (Exception e) {
            rowsAffected = -1;

        }

        return rowsAffected;
    }
}