RR2

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class TableReader  {

    static Connection getConnection() {
        Connection connection = null;

        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection(
                    "jdbc:mysql://184.154.73.113/davidlno_StockHistory?" + "user=davidlno_student&password=ez24get!");

        } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
            e.printStackTrace();
        }

        return connection;
    }

    static void getAllTickers() {
        Connection connection = getConnection();
        String query = "select ticker from TickerHistory group by ticker order by ticker";

        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);

            while (rs.next()) {

                String ticker = rs.getString("Ticker");
                System.out.print(ticker + ", ");
            }

            rs.close();
            statement.close();
            connection.close();

            rs = null;
            statement = null;
            connection = null;

        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());

        }
    }

    // Change your methods to pass the scanner you opened in main into the functions
    //static void recentData() {
    static void recentData(Scanner scann) {

        String line;

        // Now that we are passing the scanner in we don't need to define it any longer.
        //Scanner scann = new Scanner(System.in);
        System.out.println("Enter a valid ticker: ");
        line = scann.nextLine();

        Connection connection = getConnection();
        String query = "select * from TickerHistory where Ticker = '" + line + "' order by HistoryDate  desc limit 1";
        //String query = "select * from TickerHistory where Ticker = 'line'";

        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);

            int counter = 0;
            while (rs.next()) {
                String ticker = rs.getString("Ticker");
                String companyName = rs.getString("CompanyName");
                int volume = rs.getInt("Volume");
                double openPrice = rs.getDouble("OpenPrice");
                double highPrice = rs.getDouble("HighPrice");
                double lowPrice = rs.getDouble("LowPrice");
                double closePrice = rs.getDouble("ClosePrice");

                counter++;
                System.out.println(counter + "  " + ticker + "  " + companyName + "  Volume: " + volume);
                System.out.println("Open:   " + openPrice);
                System.out.println("High:   " + highPrice);
                System.out.println("Low:    " + lowPrice);
                System.out.println("Close:  " + closePrice);
            }

            // Because we are passing the scanner into the method we can't close it--it needs to remain open until main() closes it.
            //scann.close();
            rs.close();
            statement.close();
            connection.close();

            rs = null;
            statement = null;
            connection = null;

        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());

        }
    }

    // Pass the scanner into the method.
    static void closePrice(Scanner scann) {
        String line;
        //Scanner scann = new Scanner(System.in);
        System.out.println("Enter a valid ticker: ");
        line = scann.nextLine();

        Connection connection = getConnection();
        String query = "select HistoryDate and Ticker and HighPrice and ClosePrice from TickerHistory order by HighPrice where Ticker = 'line'";

        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);

            while (rs.next()) {
                for (int i = 1; i > 1; i++) {
                    double high = rs.getDouble("HighPrice");
                    double close = rs.getDouble("ClosePrice");
                    String date = rs.getString("HistoryDate");
                    System.out.println("High Price: " + high);
                    System.out.println("2017 03 10  " + close);
                }
            }

            rs.close();
            statement.close();
            connection.close();

            rs = null;
            statement = null;
            connection = null;

        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());

        }
    }

    // Pass the scanner into the method.
    static void currentDate(Scanner scan) {
        String date;
        //Scanner scan = new Scanner(System.in);
        System.out.println("Enter a valid Date (yyyy-mm-dd): ");
        date = scan.nextLine();

        Connection connection = getConnection();
        String query = "select Ticker and Volume from TickerHistory where HistoryDate = 'date' order by Volume desc limit 5";

        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);

            while (rs.next()) {

                String ticker = rs.getString("Ticker");
                int volume = rs.getInt("Volume");
                System.out.print(ticker + "     " + volume);
            }

            rs.close();
            statement.close();
            connection.close();

            rs = null;
            statement = null;
            connection = null;

        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());

        }
    }

    public static void main(String[] args) {

        String line = "";
        Scanner scan = new Scanner(System.in);

        // Get rid of this extra scanner.
        //Scanner scanin = new Scanner(System.in);
        do {
            System.out.println("");
            System.out.println("");
            System.out.println("----------Stock Reporting Menu--------------");
            System.out.println(" ");
            System.out.println("");
            System.out.println("");
            System.out.println("MENU OPTIONS:");
            System.out.println("1. List all tickers");
            System.out.println("2. Display most recent data for ticker");
            System.out.println("3. Display high and current close price for ticker");
            System.out.println("4. Display top five active tickers for date");
            System.out.println("");
            System.out.print("Select number or type quit:");

            // Use your scan variable instead.
            //line = scanin.nextLine();
            line = scan.nextLine();

            // We modified your methods to accept the scanner that is being created and used here in main.
            // So, the signatures of your calls below had to be modified to pass the scanner to each method with the exception of getAllTickers
            // which doesn't prompt for any additional information.
            if (line.equals("1")) {
                getAllTickers();
            } else if (line.equals("2")) {
                recentData(scan);
            } else if (line.equals("3")) {
                closePrice(scan);
            } else if (line.equals("4")) {
                currentDate(scan);
            }

        } while (!line.equalsIgnoreCase("quit"));

        // Close only scan
        //scanin.close();
        scan.close();
    }

}