Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /**
- * 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
- + " where P.PathID = PC.PathID "
- + "and P.PathStart = " +sloc
- + " and P.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.LocationID = PC.LocationID "
- + "and P.PathEnd = " +eloc
- + " and P.EndPointType = 'End'";
- ResultSet rend = stmt.executeQuery(qstart);
- String epoint="";
- while(rend.NEXT()) {
- epoint = rend.getString("EndPointType");
- }
- //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(epoint == "End" && spoint == "Start") {
- 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 PC.PathOrder = " + 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();
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement