import java.sql.*;
import java.math.BigDecimal;

public class Example {
    // NOTE: Not a good example of object-oriented Java code - just a big
    // main() function showing features of JDBC.

    // ClassNotFoundException needed when loading the driver
    public static void main(String[] args) throws ClassNotFoundException {
        // load the driver - weird Java stuff happening here
        Class.forName("org.postgresql.Driver");

        // the connect string also tells Java JDBC what driver to use,
        // magically
        String connectString = "jdbc:postgresql://flowers.mines.edu/csci403";

        // Note that readLine() and readPassword() don't play well with
        // running this code under Eclipse - may have to run on command line
        System.out.print("Username: ");
        String username = System.console().readLine();
        System.out.print("Password: ");
        String password = new String(System.console().readPassword());

        // connect to database (must handle or throw exception)
        Connection db;
        try {
            db = DriverManager.getConnection(connectString, username, password);
        } 
        catch (SQLException e) {
            System.out.println("Error connecting to database: " + e);
            return;
        }

        // immediate queries use a Statement object - as usual you should
        // only do this for queries you write - don't build up with user
        // input - use a PreparedStatement instead.

        // Statement objects can be re-used with different queries.

        // get all statement types from the Connection object

        // must handle or throw exception for pretty much anything you
        // do in JDBC - even this seemingly innocuous code
        Statement immediate;
        try {
            immediate = db.createStatement();
        }
        catch (SQLException e) {
            System.out.print(e);
            return;
        }

        String query1 = "SELECT course_id, section, title, enrollment "
                    +   "FROM mines_courses "
                    +   "WHERE instructor = 'Painter-Wakefield, Christopher'";

        try {
            ResultSet results = immediate.executeQuery(query1);
            
            // ResultSet objects give you the ability to move forward
            // through the results - there are also methods to move back.
            // There are also more exotic methods to update data, but
            // I've never used them.

            // Note you have to specify the type of the column that you
            // are getting back - use the appropriate method.  Here we
            // use the names of the columns as parameters.
            while (results.next()) {
                String courseId = results.getString("course_id");
                String section = results.getString("section");
                String title = results.getString("title");
                int enrollment = results.getInt("enrollment");

                System.out.println(courseId + " " + section + " " + title + " " + enrollment);
            }
        }
        catch (SQLException e) {
            System.out.print(e);
            return;
        }

        System.out.println("");
          
        // prepared statements use a PreparedStatement object.  For
        // these, you provide the query up front.
        String query2 = "SELECT course_id, section, title, enrollment "
                    +   "FROM mines_courses "
                    +   "WHERE instructor = ?";

        try {
            PreparedStatement prepared = db.prepareStatement(query2);
            System.out.println("Enter an instructor: ");
            String instr = System.console().readLine();

            // use the typed set* methods to fill in the parameters 
            prepared.setString(1, instr);

            // for a PreparedStatement, executeQuery() takes no params
            ResultSet results = prepared.executeQuery();

            // here we'll use positional parameters instead of column
            // names to retrieve the columns of data.  Not zero-based
            // indexing, weirdly.
            while (results.next()) {
                String courseId = results.getString(1);
                String section = results.getString(2);
                String title = results.getString(3);
                int enrollment = results.getInt(4);

                System.out.println(courseId + " " + section + " " + title + " " + enrollment);
            }

            // get column names/types
            ResultSetMetaData rsmd = results.getMetaData();
            int numberOfColumns = rsmd.getColumnCount();
            System.out.println("");
            System.out.println("Columns:");
            for (int i = 1; i <= numberOfColumns; i++) {
                System.out.println(rsmd.getColumnName(i) + " " + rsmd.getColumnTypeName(i));
            }
        }
        catch (SQLException e) {
            System.out.print(e);
            return;
        }

        System.out.println("");


        // DDL - reuse the Statement object from before
        String query3 = "DROP TABLE IF EXISTS foo";
        String query4 = "CREATE TABLE foo (x text, y integer)";
        
        // when doing DDL or modification queries, use executeUpdate()
        try {
            immediate.executeUpdate(query3);
            immediate.executeUpdate(query4);
            System.out.println("Table foo created");
            System.out.println("");
        }
        catch (SQLException e) {
            System.out.print(e);
            return;
        }

        // Modification queries - executeUpdate() returns # of rows
        // modified
        String query5 = "INSERT INTO foo VALUES (?, ?)";
        try {
            PreparedStatement prepared = db.prepareStatement(query5);
            prepared.setString(1, "apple");
            prepared.setInt(2, 42);
            int rows = prepared.executeUpdate();

            System.out.println(rows + " row(s) inserted.");
        }
        catch (SQLException e) {
            System.out.print(e);
            return;
        }

        try {
            // For jdbc, autocommit is true by default (at least for Postgres)
            // Change it with this
            db.setAutoCommit(false);

            PreparedStatement prepared = db.prepareStatement(query5);
            prepared.setString(1, "pear");
            prepared.setInt(2, 17);
            int rows = prepared.executeUpdate();

            System.out.println(rows + " row(s) inserted.");

            // now this line is needed
            db.commit();
        }
        catch (SQLException e) {
            System.out.print(e);
            return;
        }

        // Other types
        String query6 = "SELECT current_date, now(), 3.1415::numeric(10,5)";
        try {
            ResultSet results = immediate.executeQuery(query6);
            results.next(); // just one row

            // can get as string or more relevant type
            String dateAsString = results.getString(1);
            System.out.println(dateAsString);

            Date dateAsDate = results.getDate(1);
            System.out.println(dateAsDate.toString());

            Timestamp now = results.getTimestamp(2);
            System.out.println(now.toString());

            // get numbers in different conversions
            BigDecimal pi = results.getBigDecimal(3);
            System.out.println(pi.toString());

            double piAsDouble = results.getDouble(3);
            System.out.println(piAsDouble);
        }
        catch (SQLException e) {
            System.out.print(e);
            return;
        }

        // Exception example
        try {
            ResultSet results = immediate.executeQuery("SELECT arglbargle FROM blah");
        }
        catch (SQLException e) {
            System.out.println(e);
            return;
        }

        // should do this when done with database connection
        try {
            db.close();
        }
        catch (SQLException e) {
            System.out.println(e);
            return;
        }

    }
}