MemberSQL.java

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

public class MemberSQL{

    //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 Member file where user wants to see all the Members in the database. This method
     * will perform the sql statement and generate a result set of all the Members in the database.
     * @return ResultSet
     * @throws ClassNotFoundException
     * @throws SQLException
     * @throws ParseException
     */
    public static ResultSet viewAllMembers() 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 Member;");
            returnSet = ps.executeQuery();
            ps.close();
        } catch (SQLException e) {
            System.out.println("SQL Exception");
            e.getStackTrace();
            return null;
        }
        return returnSet;
    }

    /**
     * Method is called in the Member file and is used to find a specific member.  
     * @param memberID
     * @return ResultSet
     * @throws ClassNotFoundException
     * @throws SQLException
     * @throws ParseException
     */
    public static ResultSet viewMember(int memberID) 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 Member WHERE memberID = ?;");
            ps.setInt(1, memberID);
            returnSet = ps.executeQuery();
            ps.close();
        } catch (SQLException e) {
            System.out.println("SQL Exception");
            e.getStackTrace();
            return null;
        }
        return returnSet;
    }

    /**
     * Method is called from Member file and is called after the user has finished filling all the attributes for a Member
     * @param memberID
     * @param firstName
     * @param lastName
     * @param level
     * @param email
     * @param phone
     * @param address
     * @param activeStatus
     * @param rewardAmount
     * @throws ParseException
     */
    static void addMember(int memberID, String firstName, String lastName, String level, String email, String phone, String address, boolean activeStatus, double rewardAmount) throws SQLException, ParseException{
        //Object that represents a precompiled SQL statement
        PreparedStatement ps = null;
        int id = 0;
        try{
            ps = connection.prepareStatement("INSERT INTO Member (memberID, firstname, lastname, level, email, phone, address, activeStatus, rewardAmount) VALUES (?,?,?,?,?,?,?,?,?);");
            ps.setInt(1,memberID);
            ps.setString(2,firstName);
            ps.setString(3,lastName);
            ps.setString(4,level);
            ps.setString(5, email);
            ps.setString(6,phone);
            ps.setString(7,address);
            ps.setBoolean(8, activeStatus);
            ps.setDouble(9, rewardAmount);
            
            id = ps.executeUpdate();
            connection.commit();
            ps.close();
            System.out.println(id);
            
            if(id > 0){
                System.out.println("Member added successfully");
            } else{
                System.out.println("Member not added");
            }
        }
        catch (SQLException e) {
            System.out.println("SQL Exception");
            connection.rollback();
            e.printStackTrace();
        }
    }
    /**
     * Method is called from Member file and is called after the user has finished filling all the attributes that they want to edit 
     * for a Member
     * @param memberID
     * @param firstName
     * @param lastName
     * @param level
     * @param email
     * @param phone
     * @param address
     * @param activeStatus
     * @param rewardAmount
     * @throws ParseException
     */
    static void editMember( int memberID,String firstName, String lastName, String level, String email, String phone, String address,  boolean activeStatus, double rewardAmount) throws SQLException, ParseException{
        //Object that represents a precompiled SQL statement
        PreparedStatement ps = null;
        int id = 0;
        try{
            ps = connection.prepareStatement("UPDATE Member SET firstname = ?, lastname = ?, level = ?, email = ?, phone = ?, address = ?, rewardAmount = ?, activeStatus = ? WHERE memberID = ?;");
            
            ps.setString(1,firstName);
            ps.setString(2,lastName);
            ps.setString(3,level);
            ps.setString(4, email);
            ps.setString(5,phone);
            ps.setString(6,address);
            ps.setBoolean(7, activeStatus);
            ps.setDouble(8, rewardAmount);
            ps.setInt(9,memberID);

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

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

            System.out.println(id);

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

    /**
     * Method is called in Member file under rewardCheck. This method will perform the sql query in order to
     * generate a members yearly rewards check. Before the query is generated, it will check if the member is 
     * Platinum level.
     * @param memberID
     * @throws SQLException
     * @throws ParseException
     */
    static ResultSet rewardCheck(int memberID) throws SQLException, ParseException{
        //Object that represents a precompiled SQL statement
        PreparedStatement ps = null;
        PreparedStatement memberSearch = null;
        //A table of data representing a database result set, which is usually generated by executing a 
        //statement that queries the database.
        ResultSet member = null;
        ResultSet resultSet = null;

        try{
            // Check if member is Platinum level
            memberSearch = connection.prepareStatement("SELECT * FROM Member WHERE memberID = ?;");
            memberSearch.setInt(1, memberID);
            member = memberSearch.executeQuery();
            if (!member.next()) {
                return null;
            }
            boolean isPlatinum = member.getString("level").toLowerCase().equals("platinum");

            if (isPlatinum) {
                ps = connection.prepareStatement("SELECT rewardAmount FROM Member WHERE memberID = ?;");
                ps.setInt(1,memberID);
                
                resultSet = ps.executeQuery();
            }
            ps.close();
        } 
        catch(SQLException e){
            System.out.println("SQL Exception");
            e.printStackTrace();
            return null;
        }
        return resultSet;
    }
}