CS 318 - Week 8 Lab - 3-11-13
(these notes were made after lab...)

INTRO to JDBC

*   JDBC - Java DataBase Connectivity

package: java.sql
*   provides the API (abstract programming interface) for accessing and
    processing data from a (relational) database using Java

*   so, using JDBC, you'll very typically import:

import java.sql.*;

    ...so we don't have to proceed everything from java.sql with
    java.sql.blah...

*   BASIC steps for using JDBC:

*   HERE ON CAMPUS - to AVOID a 2-minute-plus WAIT for database connections --
    INCLUDE this line early in a method using JDBC:

    System.setProperty("java.net.preferIPv4Stack", "true");

*   INSIDE a try block... (because some of JDBC involves CHECKED exceptions
    that must be caught or explicitly thrown...)

    try
    {    
         // NEXT: load the the JDBC driver for Oracle

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

         ...

         // NEXT: set up a Connection object

         Connection con = DriverManager.getConnection(
             "jdbc:oracle:thin:@cedar:1521:student",
             username, password);

         // the Connection class has a method
         //     createStatement that returns a Statement object

         Statement stmt = con.createStatement();

         // I am choosing to set up a query String before
         //     proceeding

         String dateQuery = "select sysdate " +
                            "from dual";    // NO ; IN QUERY STRING

         // the Statement class has a method named executeQuery --
         //     it expects a String containing a SQL query,
         //     it produces a ResultSet object

         ResultSet rS = stmt.executeQuery(dateQuery);

         // ResultSet rS now contains the result of running that
         //     query. We need to call its next() to make the
         //     first/next result row in the ResultSet available

         // note: next returns true if there is a first/next row,
         //     and returns false otherwise -- so it CAN be
	  //     used to control a loop

         rS.next();
    
         // ResultSet has a number of methods for grabbing information
         //     from the "current" row;
         // getString is a method that, given an int argument,
         //     tries to give a String depiction of the Nth column
         //     in the current row (STARTING WITH 1!!!!!)

         // I have a 1-column, 1 row result here -- this gets the
         //     the 1st column in the current row and tries to
         //     give it as a String

         String todaysDate = rS.getString(1);

         ...

         // WHEN YOU ARE DONE WITH THE Statement AND Connection objects,
         //     CLOSE them with their close() method!!!!!!

         stmt.close();
         con.close();

    }
    
    // I'm going to just print the String depiction of any exception
    //     thrown above...

    catch (Exception exc)
    {
        System.out.println(exc);
    }
    
*   NOTE: you need to make sure the Java CLASSPATH looks in the right place to
    find the Oracle DBMS implementations of the driver software JDBC needs to
    actually connect to Oracle -- thus, in lab we added the following:

export CLASSPATH=$CLASSPATH:/opt/oracle/product/11.2.0/client_1/oui/jlib/classes12.jar:.

    ...to the end of our nrs-projects .bashrc files. (That is a single command, on
    1 line of the .bashrc)

*   Consider examples GetDate.java and EmpSal.java.

    *   To run them using the Oracle driver software, you will probably have to
        run them *from* nrs-projects.

    *   but, with the above change made to your .bashrc (and either run using
source ~/.bashrc
        ...or by logging in again), you can compile and run these examples
	from nrs-projects using:

javac GetDate.java
java GetDate

javac EmpSal.java
java EmpSal

*   EmpSal2.java adds a little static method for grabbing the
    password from pwd-file.txt,
    and a little static method for more-prettily-formattng the
    employee's last name and salary.