=====
CS 235 - Week 13 Lecture - 2021-11-15
=====

=====
TODAY WE WILL
=====
*   announcements
*   a quick example of reading from a file
*   start our intro to Java Database Connectivity, JDBC
*   prep for next class

*   Should be working on Homework 8! due Friday at 11:59 pm

*   please register for Spring 2022 when your registration window
    opens!

=====
*   we'll be trying out connecting JDBC to the HSU Oracle Student database

    ...so we need to do so from nrs-projects, because the Oracle middleware
       we need is there; (and does work, at least as of yesterday...!)

    *   if you don't already have an nrs-projects.humboldt.edu account,
        you will by Friday's lab...!

    *   (and you will get to practice ssh'ing to it in Friday's lab!)

    *   And (um) it's Java 8 on nrs-projects, so just keep that in mind;

SO our reading-from-a-file won't be using the LATEST approach available
in Java, let's just say...

=====
a demo of ONE of several ways to read from file in Java...!
=====
...see FileInputEx.java, built during class!

=====
intro to Java Database Connectivity, JDBC
=====
*   package java.sql provides the API for accessing and processing
    data stored in a data source (often a relational database, but
    not limited to those) using Java!

    import java.sql.*;

*   BASIC steps:
    *   create a Connection object, represents the connection
        between Java and the DBMS server

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

    *   create a Statement object -- this represents the SQL statement
        you'd like the DBMS to execute for you
	(and note: there are SEVERAL very useful subclasses of Statement as
	well!)

        Statement stmt = con.createStatement();

    *   the Statement object includes methods for setting up and
        executing the desired SQL statement

        String dateQuery = "select sysdate " +
	                   "from dual";        // DO NOT PUT ; in QUERY STRING

        ResultSet rS = stmt.executeQuery(dateQuery);

    *   Now, IF your SQL statement was something like a select statement,
        the Statement method executeQuery returns a ResultSet object
	representing the rows in the table resulting from that query

        AND ResultSet provides methods for "walking through" and
	grabbing the data from the resulting rows!

        for example,
	its next method returns true if there IS a next row and gives
	    you access TO the data in that row

        which means LOOPING through it works nicely:

        while (rS.next())
	{
	    String aThing = rS.getString(1);  // treat 1st data in current
	                                      //    row as a string

            int anotherThing = rS.getInt(2);  // treat 2nd data in current
	                                      //    row as an integer

            ...
        }

    *   WHEN you are done,
        CLOSE the Statement,
	CLOSE the Connection! <-- CLASS CODING STANDARD!!!!!!!!!

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

*   what would be some additional things you'd need to know
    if you wanted to, say, CHANGE something in a database?
    via Java JDBC?

    *   For SQL commands that do not return tabular results,
        use Statement method executeUpdate, not executeQuery

        ^ for example, for SQL update commands
	               AND SQL insert commands
		       AND SQL delete commands...!

        executeUpdate returns an int instead of a ResultSet

    *   AH -- BY DEFAULT, each change made by JDBC is AUTO-committed
        as you make it -- BUT you can turn OFF auto-commit if you are
	doing several actions that together MAY need to be undone,
	or rolled back;

        to turn OFF auto-commit (method of the Connection object):

        con.setAutoCommit(false);

        then, to ACTUALLY commit WHEN you are ready (another method
	of the Connection object):

        con.commit();

        (and if something goes awry you can undo back to the previous
	commit using:

        con.rollback();