most updated

/**
 * Created by ksbugbee & aGelinas on 10/5/16.
 */

import java.sql.*;
import java.util.*;

public class p3 {
    // Replace the "USERID" and "PASSWORD" with your username and password to get this to work.
    // Note: Remember that your Oracle USERID for many of you is different than your regular login name
    private static final String USERID = "ksbugbee";
    private static final String PASSWORD = "KSBUGBEE";
    static Statement stmt;


    public void display_menu() {
        System.out.println( "Please type your choice and press enter: \n" +
                "1 – Report Health Provider Information\n" +
                "2 – Report Health Service Information \n" +
                "3 – Report Path Information \n" +
                "4 – Update Health Service Information");
        System.out.print ( "Selection: " );
    }

    public p3() throws SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.println("-------Oracle JDBC COnnection Testing ---------");

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");

        } catch (ClassNotFoundException e){
            System.out.println("Where is your Oracle JDBC Driver?");
            e.printStackTrace();
            return;
        }

        System.out.println("Oracle JDBC Driver Registered!");
        Connection connection = null;

        try {
            connection = DriverManager.getConnection(
                    "jdbc:oracle:thin:@oracle.wpi.edu:1521:orcl", USERID, PASSWORD);
        } catch (SQLException e) {
            System.out.println("Connection Failed! Check output console");
            e.printStackTrace();
            return;
        }

        System.out.println("Oracle JDBC Driver Connected!");
        Scanner in = new Scanner(System.in);
        display_menu();
        switch(in.nextInt()) {
            case 1:
                System.out.println("Option 1 picked");
                System.out.println("Enter Provider ID:");
                int custID = scanner.nextInt();
                //creates query based on the given customer ID
                String query = "SELECT P.ProviderID, P.FirstName, P.LastName, Prov.Acronym, L.LocationName "
                        + "from Office O, Provider P, ProviderTitle Prov, Location L "
                        + "where O.ProviderID = P.ProviderID "
                        + "and P.ProviderID = Prov.ProviderID "
                        + "and O.LocationID = L.LocationID "
                        + "and P.ProviderID =" + custID ;
                stmt = connection.createStatement();
                ResultSet rset = stmt.executeQuery(query);
                int provID=0;
                String fname ="", lname="", title = "", officeLoc = "";
                //Below processes the results
                while(rset.next()) {
                    provID = rset.getInt("ProviderID");
                    fname = rset.getString("FirstName");
                    lname = rset.getString("LastName");
                    title = rset.getString("Acronym");
                    officeLoc = rset.getString("LocationName");
                    System.out.println("ID:" + provID + "First Name:" + fname + "Last Name:" + lname
                            + "Title:" + title + "Office Location:" + officeLoc);
                }
                break;


            case 2:
                System.out.println("Option 2 picked");
                System.out.println("Enter Health Service Name WITHIN SINGLE QUOTES:");
                String sname = scanner.nextLine();
                String servicename ="", htype ="", locname="";
                String floorid;
                //below creates query
                String query2 = "select S.ServiceName, S.HealthType, L.LocationName, L.FloorID "
                        + "from Services S, Location L, ResidesIn R "
                        + "where S.ServiceName = R.ServiceName "
                        + "and R.LocationID = L.LocationID "
                        + "and S.ServiceName =" + sname;
                stmt = connection.createStatement();
                ResultSet rset2 = stmt.executeQuery(query2);
                while(rset2.next()) {
                    servicename = rset2.getString("ServiceName");
                    htype = rset2.getString("HealthType");
                    locname = rset2.getString("LocationName");
                    floorid = rset2.getString("FloorID");
                    System.out.println("Service Name: " + servicename);
                    System.out.println("Health Type: " + htype);
                    System.out.println("Location Name: " + locname);
                    System.out.println("Floor ID: " + floorid);
                }
                break;


            case 3:
                System.out.println("Option 3 picked");
                String sloc="", eloc="";
                System.out.println("Enter Starting Location:");
                sloc = scanner.nextLine();
                System.out.println("Enter Ending Location:");
                eloc = scanner.nextLine();

                String query3 = "SELECT P.PathID " +
                        "from Path P, PathContains PC " +
                        "where P.PathID = PC.PathID " +
                        "and P.PathStart=" + "'" + sloc + "'" +
                        " and P.PathEnd=" + "'" + eloc + "'";
                String pathID = "";
                stmt = connection.createStatement();
                ResultSet rset3 = stmt.executeQuery(query3);
                while(rset3.next()) {
                    pathID = rset3.getString("PathID");
                }
                //checks if given values are legit
                String qstart = "SELECT PC.EndPointType "
                        + "from PathContains PC, Path P "
                        + "where P.PathID =" + pathID
                        + " and P.PathID = PC.PathID "
                        + "and P.PathStart = " + "'" +sloc + "'"
                        + " and PC.EndPointType = 'Start'";
                ResultSet rstart = stmt.executeQuery(qstart);
                String spoint="";
                while(rstart.next()) {
                    spoint = rstart.getString("EndPointType");
                }

                String qend = "SELECT PC.EndPointType "
                        + "from PathContains PC, Path P "
                        + "where P.PathID =" + pathID
                        + " and P.PathID = PC.PathID "
                        + "and P.PathEnd = " + "'" + eloc +"'"
                        + " and PC.EndPointType = 'End'";
                ResultSet rend = stmt.executeQuery(qend);
                String epoint="";
                while(rend.next()) {
                    epoint = rend.getString("EndPointType");
                }
                //for testing
                System.out.println("Should be start:" + spoint);
                System.out.println("Should be end:" + epoint);

                //throw error message if the given starting location is not a starting location
                // or if the ending location is not a valid ending location in PathContains
                if(spoint.equals("Start") && epoint.equals("End")) {
                    System.out.println("Path ID for this given path is: "+ pathID);
                    String query4 = "SELECT L.LocationName, PC.PathOrder, L.FloorID "
                            + "from Location L, PathContains PC, Path P "
                            + "where PC.PathID = P.PathID "
                            + "and P.PathStart = L.LocationName "
                            + " and PC.PathOrder = " + pathID
                            + " and PC.PathID = " + pathID;
                    String pOrder = "";
                    String locName = "";
                    String fID = "";
                    stmt = connection.createStatement();
                    ResultSet rset4 = stmt.executeQuery(query4);

                    while(rset4.next()) {
                        pOrder = rset4.getString("PathOrder");
                        locName = rset4.getString("LocationName");
                        fID = rset4.getString("FloorID");
                        System.out.println("     " + pOrder + "     " + locName + "     " + fID + "     ");

                    }

                }
                else {
                    System.out.println("Error: The given locations must be valid starting and ending points");
                }
                break;


            case 4:
                System.out.println ( "Option 4 picked" );
                String servname="", locID="";
                System.out.println("Enter Health Service Name WITHIN SINGLE QUOTES:");
                servname = scanner.nextLine();
                System.out.println("Enter the new Location ID WITHIN SINGLE QUOTES:");
                locID = scanner.nextLine();
                query = "update ResidesIn "
                        + "set LocationID =" +locID
                        + " where ServiceName=" + servname;
                stmt = connection.createStatement();
                stmt.executeQuery(query);
                break;
            default:
                System.err.println ( "Unrecognized option" );
                break;
        }
    }

    public static void main(String[] args) throws SQLException {
        new p3();


    }

}