Question

Looking for some help with this Java program I am totally lost on how to go about this.

You have been approached by a local grocery store to write a program that will track the progress of their sales people (SalesPerson.java) The information needed are: D Number integer Month 1 Sales Amount-Double Month 2 Sales Amount-Double Month 3 Sales Amount-Double Write a Java program that allows you to store the information of any salesperson up to 20 While the user decides to continue offer 3 options. To add a record to the database To delete a record from the database To change a record in the database 1. If the user selects the add option, issue an error message if the database is full Otherwise prompt the user for an ID number. If the ID number already exists in the database issue an error message Otherwise have the user input the sales for months 1, 2, and 3 and add a new record to the database. Use a Method to add a new user to the database If the user selects the delete option, issue an error message if the database is empty. Otherwise, prompt the user for an ID number If the ID number does not exist, issue an error message. Use a Method to delete the record and move the remaining records up one row. If the user selects the change option, issue an error if the database is empty. Otherwise prompt the user for an ID number, If the requested record does not exist, issue an error message, Use a Method to edit the sales amount for the requested ID. 2. 3. After each option executes, display the updated database in ascending order by ID number and prompt the user to select the next action The program should be tested and displayed with the following sales persons. 1993 2504 5441 3442.20 2834.32 1778.33 1409.99 1138.50 1987.13 3010.40 2879.05 3482.10

Answer 1

The program is written in Java 8 using Eclipse IDE and MySQL for database

Program

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SalesPerson {
   public static void main(String[] args) throws IOException, SQLException {
       BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
       int choice;
       Connection con = getConnection();
       System.out.println("1. To add record");
       System.out.println("2. To delete record");
       System.out.println("3. To change record");
       System.out.print("Enter your choice: ");
       choice = Integer.parseInt(br.readLine());

       switch (choice) {
       case 1:
           toAdd(con, br);
           break;
       case 2:
           toDelete(con, br);
           break;
       case 3:
           toUpdate(con, br);
           break;
       default:
           System.out.println("Invalid Choice");
           break;
       }
   }
   //updating the database
   private static void toUpdate(Connection con, BufferedReader br)throws SQLException, IOException {
       System.out.print("Enter ID: ");
       int id = Integer.parseInt(br.readLine());
       Statement stmt = con.createStatement();
       ResultSet rs = stmt.executeQuery("SELECT * FROM salesPerson where ID = " + id);

       if (rs.next() == true) {
           System.out.print("Enter month 1: ");
           double month1 = Double.parseDouble(br.readLine());
           System.out.print("Enter month 2: ");
           double month2 = Double.parseDouble(br.readLine());
           System.out.print("Enter month 3: ");
           double month3 = Double.parseDouble(br.readLine());

           stmt.executeUpdate("UPDATE salesPerson SET month1="+month1
                   + ", month2 = "+month2
                   + ", month3 = "+month3
                   + "WHERE id="+id);
           showResult(con);
       }
       else
           System.out.println("ID does not exist in the database");
      
   }
   //deleteing data from the database
   private static void toDelete(Connection con, BufferedReader br) throws SQLException, IOException {
       System.out.print("Enter ID: ");
       int id = Integer.parseInt(br.readLine());
       Statement stmt = con.createStatement();
       ResultSet rs = stmt.executeQuery("SELECT * FROM salesPerson where ID = " + id);

       if (rs.next() == true) {

           stmt.executeUpdate("Delete From salesPerson Where id = "+id);
           showResult(con);
       }
       else
           System.out.println("ID does not exist in the database");
      
   }
   //inserting data to the database
   private static void toAdd(Connection con, BufferedReader br) throws SQLException, IOException {
       System.out.print("Enter ID: ");
       int id = Integer.parseInt(br.readLine());
       Statement stmt = con.createStatement();
       ResultSet rs = stmt.executeQuery("SELECT * FROM salesPerson where ID = " + id);

       if (rs.next() == false) {
           System.out.print("Enter month 1: ");
           double month1 = Double.parseDouble(br.readLine());
           System.out.print("Enter month 2: ");
           double month2 = Double.parseDouble(br.readLine());
           System.out.print("Enter month 3: ");
           double month3 = Double.parseDouble(br.readLine());

           stmt.executeUpdate(
                   "Insert Into salesPerson values (" + id + ", " + month1 + ", " + month2 + ", " + month3 + ")");
           showResult(con);
       }
       else
           System.out.println("ID exists in the database");
   }
   //fecthing data from the database
   private static void showResult(Connection con) throws SQLException {
       System.out.println();
       Statement stmt = con.createStatement(); //for creating statement to fetch and retrieve data from DB
       ResultSet rs = stmt.executeQuery("SELECT * FROM salesPerson");//resultset hold the data
       System.out.println("ID Month 1 Month 2 Month 3");
       while(rs.next()) {
           System.out.print(rs.getString("ID")+" ");//the column name to get the value from that particular column
           System.out.print(rs.getString("month1")+" ");
           System.out.print(rs.getString("month2")+" ");
           System.out.print(rs.getString("month3"));
           System.out.println();
       }
       stmt.close();
      
   }
   //creating connection with the database
   public static Connection getConnection() {
       Connection con = null;
       try {

           Class.forName("com.mysql.cj.jdbc.Driver");// this is the name of the mysql driver
                                                       // Database Driver, server address, port no., database name,
                                                       // username, password
           con = DriverManager.getConnection("jdbc:mysql://localhost:3306/javatest?autoReconnect=true&useSSL=false",
                   "root", "Aman@123");
           // supress the SSL error
           System.out.println("Database Connected Successfully");
       } catch (ClassNotFoundException e) {

           System.err.println("Load Falied: " + e);

       } catch (SQLException e) {
           System.err.println("Connection Falied: " + e);
       }
       return con;
   }
}

Output