/**
    Compile the program with the following command:
       javac JavaExample.java
    When running:
       export CLASSPATH=$CLASSPATH:/usr/share/java/mysql.jar:./
       java JavaExample

*/

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Set;
import java.util.Vector;

public class JavaExample {

	/** The database server */
	private String server = "nellans.org";

	/** The user for this database */
	private String user = "utah";

	/** The password for this database */
	private String password = "arch";

	/** The database this stock reader uses */
	private String database = "eoddata_v1";

	/** The mysql connection */
	private Connection mysql = null;

	/** Constructor */
	public JavaExample() {

		// do nothing on construction - use explicit function calls
	}

	/* Connect to the server and database within the server */
	public void connectToDatabase() {

		try {
			String connectionString = "jdbc:mysql://" + this.server + "/" + this.database;
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection connection = DriverManager.getConnection(connectionString, this.user, this.password);

			this.mysql = connection;

			System.out.println("Connection Initialized\n\n");
		} catch (Exception e) {

			System.out.println("Connection Failed: " + e);
		}
	}

	/* Returns the tables available in this database */
	public void showTables() {

		final String query = "show tables";

		try {

			final Statement stmt = mysql.createStatement();
			final ResultSet rs = stmt.executeQuery(query);

			System.out.println("Tables in this database...");

			while (rs.next()) {
				System.out.println(rs.getString(1));
			}

			rs.close();
			stmt.close();

		} catch (final Exception e) {
			System.out.println("Could not execute query: " + e);
		}
	}

	/* Returns all unique stock tickers listed on the NYSE and NASDAQ that are in the database */
	public void getTickers() {

		final String query = "select ticker, exchange from HistoricalEquity where snapshotDate = '2008-01-02' and (exchange='NYSE' or exchange='NASDAQ') group by (ticker)";

		try {

			final Statement stmt = mysql.createStatement();
			final ResultSet rs = stmt.executeQuery(query);

			System.out.println("Ticker, Exchange");

			while (rs.next()) {
				System.out.println(rs.getString(1) + ", " + rs.getString(2));
			}

			rs.close();
			stmt.close();

		} catch (final Exception e) {
			System.out.println("Could not execute query: " + e);
		}
	}

	/* Returns the data for a single stock */
	public void getSingleStockData() {

		final String query = "select ticker, exchange, date, close, volume from HistoricalEquity where snapshotDate = '2008-01-02' and ticker='AAPL' order by date desc";

		try {

			final Statement stmt = mysql.createStatement();
			final ResultSet rs = stmt.executeQuery(query);

			System.out.println("Ticker, Exchange, Date, Close, Volume");

			while (rs.next()) {
				System.out.println(rs.getString(1) + ", " + rs.getString(2) + ", " + rs.getString(3) + ", "
						+ rs.getString(4) + ", " + rs.getString(5));
			}

			rs.close();
			stmt.close();

		} catch (final Exception e) {
			System.out.println("Could not execute query: " + e);
		}
	}

	/* Closes the connection */
	public void closeConnection() {
		try {
			mysql.close();
		} catch (Exception e) {
			System.out.println("Unable to close connection: " + e);

		}
	}

	/** Main */
	public static void main(final String[] args) {

		JavaExample javaExample = new JavaExample();

		// initialize our database connection
		javaExample.connectToDatabase();

		// show the tables available in the database
		javaExample.showTables();

		// returns a set of tickers
		javaExample.getTickers();

		// gets data for a single ticker
		javaExample.getSingleStockData();

		// close the connection to the database when complete
		javaExample.closeConnection();
	}

}