prog3

import java.io.*;
import java.sql.*; // For access to the SQL interaction methods
import java.util.Scanner;

public class Prog3 {
	public static void main(String[] args) {

		final String oracleURL = // Magic lectura -> aloe access spell
				"jdbc:oracle:thin:@aloe.cs.arizona.edu:1521:oracle";

		String username = null, // Oracle DBMS username
				password = null; // Oracle DBMS password

		Statement stmt = null;

		if (args.length == 2) { // get username/password from cmd line args
			username = args[0];
			password = args[1];
		} else {
			System.out.println("\nUsage:  java JDBC <username> <password>\n"
					+ "    where <username> is your Oracle DBMS" + " username,\n    and <password> is your Oracle"
					+ " password (not your system password).\n");
			System.exit(-1);
		}

		// load the (Oracle) JDBC driver by initializing its base
		// class, 'oracle.jdbc.OracleDriver'.

		try {

			Class.forName("oracle.jdbc.OracleDriver");

		} catch (ClassNotFoundException e) {

			System.err.println("*** ClassNotFoundException:  " + "Error loading Oracle JDBC driver.  \n"
					+ "\tPerhaps the driver is not on the Classpath?");
			System.exit(-1);

		}

		// make and return a database connection to the user's
		// Oracle database

		Connection dbconn = null;

		try {
			dbconn = DriverManager.getConnection(oracleURL, username, password);

		} catch (SQLException e) {

			System.err.println("*** SQLException:  " + "Could not open JDBC connection.");
			System.err.println("\tMessage:   " + e.getMessage());
			System.err.println("\tSQLState:  " + e.getSQLState());
			System.err.println("\tErrorCode: " + e.getErrorCode());
			System.exit(-1);

		}

		// PRINT QUERY OPTIONS FOR USER

		System.out.println("Queries: ");
		System.out.println(
				"\t (1) For each of the four years, what are the top five longest road segments, as measured by the distance between BMP and EMP values?");
		System.out.println(
				"\t (2) For two years provided by the user, which road segments (identified by their location ids) were added and which were removed between those years?");
		System.out.println(
				"\t (3) For a year and a route given by the user, which TCSMP is closest to the midpoint of the road segment?");
		System.out.println("\t (4) TBD");

		Scanner scanner = new Scanner(System.in);

		System.out.println("Enter the number of the query you would like to execute: ");
		String option = scanner.nextLine();

		if (option.equals("1")) {
			String query2012 = "select CNTLOCID, Route, abs(EMP - BMP) as Dist from hheavlin.ADOT_2012 where emp is not null and bmp is not null order by abs(EMP - BMP) desc";
			ResultSet result2012 = execute_query(dbconn, query2012);
            System.out.println("2012: ");
			processQuery1Output(result2012);
            System.out.println();

			try {
				result2012.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

			String query2013 = "select CNTLOCID, Route, abs(EMP - BMP) as Dist from hheavlin.ADOT_2013 where emp is not null and bmp is not null order by abs(EMP - BMP) desc";
			ResultSet result2013 = execute_query(dbconn, query2013);
            System.out.println("2013: ");
			processQuery1Output(result2013);
            System.out.println();

			try {
				result2013.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

			String query2014 = "select CNTLOCID, Route, abs(EMP - BMP) as Dist from hheavlin.ADOT_2014 where emp is not null and bmp is not null order by abs(EMP - BMP) desc";
			ResultSet result2014 = execute_query(dbconn, query2014);
            System.out.println("2014: ");
			processQuery1Output(result2014);
            System.out.println();

			try {
				result2014.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

			String query2015 = "select CNTLOCID, Route, abs(EMP - BMP) as Dist from hheavlin.ADOT_2015 where emp is not null and bmp is not null order by abs(EMP - BMP) desc";
			ResultSet result2015 = execute_query(dbconn, query2015);
            System.out.println("2015: ");
			processQuery1Output(result2015);
            System.out.println();

			try {
				result2015.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (option.equals("2")) {
			System.out.println("Enter start year: ");
            String startYear = scanner.nextLine();
			System.out.println("Enter end year: ");
			String endYear = scanner.nextLine();

			String queryStartYear = "select CNTLOCID, Route, Start_, End from hheavlin.ADOT_" + startYear;
            ResultSet resultStartYear = execute_query(dbconn, queryStartYear);

            String queryEndYear = "select CNTLOCID, Route, Start_, End from hheavlin.ADOT_" + endYear;
            ResultSet resultEndYear = execute_query(dbconn, queryEndYear);

            System.out.println("Added: ");
            processQuery2Output(resultEndYear, resultStartYear);
            System.out.println();

            System.out.println("------------------------------------------------------");

            System.out.println("Removed: ");
            processQuery2Output(resultStartYear, resultEndYear);
            System.out.println();

            try {
                resultStartYear.close();
				resultEndYear.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (option.equals("3")) {
			System.out.println("Enter a year: ");
            String year = scanner.nextLine();

			System.out.println("Enter a route: ");
			String route = scanner.nextLine();

			String query = "";
			ResultSet result = execute_query(dbconn, query);
		}

		if (option.equals("4")) {
			String query = "";
			ResultSet result = execute_query(dbconn, query);
		}

		try {
			dbconn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static ResultSet execute_query(Connection dbconn, String query) {
		// Send the query to the DBMS, and get the results
		Statement stmt = null;
		ResultSet answer = null;

		try {
			stmt = dbconn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			answer = stmt.executeQuery(query);
			// Shut down the connection to the DBMS.

		} catch (SQLException e) {
			System.err.println("*** SQLException:  " + "Could not fetch query results.");
			System.err.println("\tMessage:   " + e.getMessage());
			System.err.println("\tSQLState:  " + e.getSQLState());
			System.err.println("\tErrorCode: " + e.getErrorCode());
			System.exit(-1);
		}
		return answer;
	}

	// prints out top 5 results in a given ResultSet
	public static void processQuery1Output(ResultSet result) {
		if (result != null) {
			try {
				int i = 0;
				while (result.next() && i < 5) {
					System.out.println(result.getInt("CNTLOCID") + "\t" + result.getString("Route") + "\t"
							+ result.getFloat("Dist"));
					i++;
				}

				float current = result.getFloat("Dist");
				while (result.next()) {
					if (Float.compare(current, result.getFloat("Dist")) == 0) {
						System.out.println(result.getInt("CNTLOCID") + "\t" + result.getString("Route") + "\t"
								+ result.getFloat("Dist"));
					} else {
						break;
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

    public static void processQuery2Output(ResultSet result1, ResultSet result2) {
        try{
            if (result1 != null && result2 != null) {

                while(result1.next()){
                    boolean notFound = true;
                    while (result2.next()) {
                        if (result1.getInt("CNTLOCID") != result2.getInt("CNTLOCID")) {
                            notFound = false;
                            break;
                        }
                    }
                    if (notFound == true) {
                        System.out.println(result1.getInt("CNTLOCID") + "\t" + result1.getString("Route") + "\t"
								+ result1.getString("Start_") + "\t" + result1.getString("End"));
                    }

                    result2.beforeFirst();
                }
		    }
        } catch (SQLException e) {
				e.printStackTrace();
			}
		}
}