SupplierSQL.java

import java.sql.*;
import java.sql.SQLException;
import java.text.ParseException;

public class SupplierSQL{

    //This connects to the database by calling the login file
    static Connection connection = Login.connection;
    static Statement statement = Login.statement;
    static ResultSet result = Login.result;

    /**
     * Method is called in Supplier file where user wants to see all the Suppliers in the database. This method
     * will perform the sql statement and generate a result set of all the Suppliers in the database.
     * @return ResultSet
     * @throws ClassNotFoundException
     * @throws SQLException
     * @throws ParseException
     */
    public static ResultSet viewAllSuppliers() throws ClassNotFoundException, SQLException, ParseException{
        //A table of data representing a database result set, which is usually generated by executing a
        //statement that queries the database.
        ResultSet returnSet = null;
        //Object that represents a precompiled SQL statement
        PreparedStatement ps = null;

        try {
            ps = connection.prepareStatement("SELECT * FROM Supplier;");
            returnSet = ps.executeQuery();
            ps.close();
        } catch (SQLException e) {
            System.out.println("SQL Exception");
            e.getStackTrace();
            return null;
        }
        return returnSet;
    }

    /**
     * Method is called in the Supplier file and is used to find a specific supplier.
     * @param supplierID
     * @return ResultSet
     * @throws ClassNotFoundException
     * @throws SQLException
     * @throws ParseException
     */
    public static ResultSet viewSupplier(int supplierID) throws ClassNotFoundException, SQLException, ParseException{
        //A table of data representing a database result set, which is usually generated by executing a
        //statement that queries the database.
        ResultSet returnSet = null;
        //Object that represents a precompiled SQL statement
        PreparedStatement ps = null;

        try {
            ps = connection.prepareStatement("SELECT * FROM Supplier WHERE supplierID = ?;");
            ps.setInt(1, supplierID);
            returnSet = ps.executeQuery();
            ps.close();
        } catch (SQLException e) {
            System.out.println("SQL Exception");
            e.getStackTrace();
            return null;
        }
        return returnSet;
    }

    /**
     * Method is called from Supplier file and is called after the user has finished filling all the attributes for a Supplier
     * @param supplierID
     * @param name
     * @param phone
     * @param email
     * @param location
     * @param amountOwed
     * @throws ParseException
     */
    static void addSupplier(int supplierID, String name, String phone, String email, String location, double amountOwed) throws SQLException, ParseException{
        //Object that represents a precompiled SQL statement
        PreparedStatement ps = null;
        int id = 0;
        try{
            ps = connection.prepareStatement("INSERT INTO Supplier (supplierID, name, phone, email, location, amountOwed) VALUES (?,?,?,?,?,?);");
            ps.setInt(1,supplierID);
            ps.setString(2,name);
            ps.setString(3,phone);
            ps.setString(4,email);
            ps.setString(5,location);
            ps.setDouble(6,amountOwed);

            id = ps.executeUpdate();
            connection.commit();
            ps.close();
            System.out.println(id);

            if(id > 0){
                System.out.println("Supplier added successfully");
            } else{
                System.out.println("Supplier not added");
            }
        }
        catch (SQLException e) {
            System.out.println("SQL Exception");
            connection.rollback();
            e.getStackTrace();
        }
    }
    /**
     * Method is called from Supplier file and is called after the user has finished filling all the attributes that they want to edit
     * for a Supplier
     * @param supplierID
     * @param name
     * @param phone
     * @param email
     * @param location
     * @param amountOwed
     * @throws ParseException
     */
    static void editSupplier( int supplierID, String name, String phone, String email, String location, double amountOwed) throws SQLException, ParseException{
        //Object that represents a precompiled SQL statement
        PreparedStatement ps = null;
        int id = 0;
        try{
            ps = connection.prepareStatement("UPDATE Supplier SET name = ?, phone = ?, email = ?, location = ?, amountOwed = ? WHERE supplierID = ?;");

            ps.setString(1,name);
            ps.setString(2,phone);
            ps.setString(3,email);
            ps.setString(4,location);
            ps.setDouble(5, amountOwed);
            ps.setInt(6,supplierID);

            id = ps.executeUpdate();
            connection.commit();
            ps.close();
            System.out.println(id);


            if(id > 0){
                System.out.println("Supplier updated successfully");
            } else{
                System.out.println("Supplier not updated");
            }
        }
        catch (SQLException e) {
            System.out.println("SQL Exception");
            connection.rollback();
            e.getStackTrace();
        }
    }
    /**
     * Method is called from Supplier file and is called to delete a supplier from the database
     * @param supplierID
     */
    static void deleteSupplier(int supplierID){
        try {
            PreparedStatement ps = connection.prepareStatement("DELETE FROM Supplier WHERE supplierID = ?;");
            ps.setInt(1, supplierID);
            int id = ps.executeUpdate();

            System.out.println(id);

            if (id > 0) {
                System.out.println("Supplier deleted");
            } else {
                System.out.println("Supplier not deleted");
            }
        } catch (SQLException e) {
            System.out.println("SQL Exception");
            e.getStackTrace();
        }
    }
}