/**
 * This is a simple java program that will connect to UNCC's oracle server.
 * You will be prompted for you oracle server username and password. Once
 * entered the program will create 3 table, populate them, issue a "select" query
 * on one of the created tables and display the result.
 * The created tables are then dropped from the database.
 */
import java.sql.*;
import java.io.*;

//This example uses JDBC
public class SampleJDBC {

    Connection con = null;
    Statement stmt = null;

    public SampleJDBC() {
    }   //Given a username and password Connects  to Oracle database .

    public void connect(String jdbcUsername, String jdbcPassword) {
        //UNCC orarcle server URL
        String jdbcUrl = "jdbc:oracle:thin:@cci-ora02.uncc.edu:1521:class";

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");

            System.out.println("before get connection");
            System.out.println(jdbcUrl + "," + jdbcUsername + "," + jdbcPassword);

            con = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword);
            System.out.println("Connected to Oracle");
        } catch (Exception e) {
            System.err.println("Unable to get oracle connection");
            System.err.println(e + "Occured");
        }

    }

    public void createTable() {
        /* Here we will create 3 example tables:
        1. Sailor table
        Attribute Name      Data Type       Description
        sid              integer         sailor ID  
        sname            varchar2(20)    sailor name
        rating           integer         sailor rating
        age              number(4,1)     sailor age
        
        2. Boat
        Attribute Name      Data Type       Description
        bid              integer         boat id
        bname            varchar2(20)    boat name
        bcolor           varchar2(10)     boat color
        
        3. Reserve
        Attribute Name      Data Type       Description
        sid              integer         sailor ID
        bid              integer         boat id
        day              date            reserve day
         */
        try {

            stmt = con.createStatement();
            String table = " CREATE TABLE sailor ( " + " sid int," + " sname varchar2(20)," + "rating int," + "age number(4,1)," + "Primary Key (sid)" + ")";
            stmt.executeUpdate(table);
            System.out.println("Sailor Table Created. \n");

            stmt.executeUpdate("CREATE TABLE boat (bid int," +
                    "  bname varchar2(20)," +
                    " bcolor varchar2(10)," +
                    " Primary Key (bid) " +
                    " )");
            System.out.println("Boat Table Created. \n");

            stmt.executeUpdate(
                    "CREATE TABLE reserve ( sid int," +
                    "                       bid int," +
                    "                       day date," +
                    "     				     Primary Key (sid,bid)," +
                    "                       Foreign Key (sid) references sailor," +
                    "                       Foreign Key (bid) references boat" +
                    "                     )");
            System.out.println("Reserve Table Created. \n");
        } catch (SQLException e) {
            System.err.println("Unable to Create Table:");
            System.err.println(e + "Occured");
        }
    }

    public void insertRows() {
        try {
            stmt = con.createStatement();
            //inserts 5 rows in the sailor table
            stmt.executeUpdate("INSERT INTO sailor VALUES(22,'dustin',7,45.0)");
            stmt.executeUpdate("INSERT INTO sailor VALUES(31,'lubber',8,55.5)");
            stmt.executeUpdate("INSERT INTO sailor VALUES(58,'rusty',10,35.0)");
            stmt.executeUpdate("INSERT INTO sailor VALUES(28,'yuppy',9,35.0)");
            stmt.executeUpdate("INSERT INTO sailor VALUES(44,'guppy',5,35.0)");

            System.out.println(" 5 Rows inserted into the sailor");

            //insert 2 rows into the Boat table
            stmt.executeUpdate("INSERT INTO boat VALUES(101,'Gippy','red')");
            stmt.executeUpdate("INSERT INTO boat VALUES(103,'Fullsail','green')");
            System.out.println(" 2 Rows inserted into the boat table");

            //insert 2 rows into the Reserve table
            stmt.executeUpdate("INSERT INTO reserve VALUES(22,101,'10-OCT-96')");
            stmt.executeUpdate("INSERT INTO reserve VALUES(58,103,'11-DEC-96')");
            System.out.println(" 2 Rows inserted into the reserve");
        } catch (SQLException e) {
            System.err.println("Unable to Insert rows:");
            System.err.println(e + "Occured");
        }
    }

    public void select() {
        ResultSet rs = null;
        int cust_id = 0;
        String cust_name = null;
        try {
            stmt = con.createStatement();

            //fetch all records in the sailor table
            rs = stmt.executeQuery("SELECT * FROM sailor");
            System.out.println("Sailor Name\t" + " \t" + "age" + "\n");
            while (rs.next()) {
                String sailorName = rs.getString("sname");
                float age = rs.getFloat("age");
                //Similarly other columns of the table can be obtained using getXXX("COLUMN_NAME")
                //where XXX in the getXXX() method relates to the datatype of the column contents.
                System.out.println(sailorName + "\t \t" + age + "\n");
            }
            rs.close();
            System.out.println(" Query executed");
        } catch (SQLException e) {
            System.err.println("Unable to get the query:");
            System.err.println(e + "Occured");
        }

    }

    public void dropTable() {
        try {
            stmt = con.createStatement();
            //drops the reserve table
            stmt.executeUpdate("DROP TABLE reserve");
            System.out.println("Table reserve dropped");
            //drops the boat table
            stmt.executeUpdate("DROP TABLE boat");
            System.out.println("Table boat dropped");
            //drops the sailor table
            stmt.executeUpdate("DROP TABLE sailor");
            System.out.println("Table sailor dropped");
        } catch (SQLException e) {
            System.err.println("Unable to drop:");
            System.err.println(e + "Occured");
        }
    }

    public void disconnect() {
        try {
            //closes the database connection
            stmt.close();
            con.close();
            System.out.println("Closed the connection");
        } catch (SQLException e) {
            System.err.println("Unable to close connection");
            System.err.println("Exception" + e + "occured");
        }
    }

    public static void main(String args[]) {
        String username = "";
        String password = "";
        SampleJDBC example = new SampleJDBC();
        try {
            BufferedReader bf = new BufferedReader(new InputStreamReader(System.in));
            System.out.println("Enter the Username");
            username = bf.readLine();
            System.out.println("Enter the password");
            password = bf.readLine();
        } catch (Exception e) {
            System.err.println("exception while reading from buffered Reader");
            System.err.println(e + "Occured");
        }

        example.connect(username, password);
        example.createTable();
        example.insertRows();
        example.select();
        example.dropTable();
        example.disconnect();
    }
}