/*
** +-------------------------------------------------------+
** | PROGRAM NAME  : JServerApplication.java               |
** | AUTHOR        : Jeffrey Hunter                        |
** | DATE          : 21-OCT-2001                           |
** | ----------------------------------------------------- |
** | LOAD          : % LoadJServerApplication.sh           |
** | CALL SPEC     : SQL> @CallSpecJServerApplication.sql  |
** | RUN           : SQL> @RunJServerApplication.sql       |
** +-------------------------------------------------------+
*/

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
 
public class JServerApplication {

  static final String connectionURL = "jdbc:default:connection:";

  // +--------------------------------------+
  // | CREATE ALL TABLES                    |
  // +--------------------------------------+
  public static void createTables ()
    throws SQLException {

    // Declare method variables
    Connection conn = null;
    Statement stmt = null;
    String sql;

    System.out.print("\n");
    System.out.print("+-------------------------------+\n");
    System.out.print("| METHOD: createTables          |\n");
    System.out.print("+-------------------------------+\n");
    System.out.print("\n");

    // Build SQL statement
    sql = "CREATE TABLE jserver_demo_table (" +
          "    test_jserver_intr_no          NUMBER(15) " +
          "  , test_jserver_customer_name    VARCHAR2(20) " +
          "  , test_jserver_company_name     VARCHAR2(20))";

    try {

      // Connect to Oracle using JDBC driver
      System.out.print("*** -> Connecting to        -> " + connectionURL + "\n");
      conn = DriverManager.getConnection(connectionURL);
      System.out.print("*** -> Connected as URL     -> " + connectionURL + "\n");

      // TURN OFF AutoCommit
      System.out.print("*** -> Turning Off AutoCommit...\n");
      conn.setAutoCommit(false);

      System.out.print("*** -> Creating Statement...\n");
      stmt = conn.createStatement();

      stmt.executeUpdate(sql);
      System.out.print("*** -> Created Table: jserver_demo_table ...\n");

      stmt.close();

    } catch (SQLException e) {
      System.err.println(e.getMessage());
    }

  } // END: createTables


  // +--------------------------------------+
  // | INSERT ALL VALUES                    |
  // +--------------------------------------+
  public static void insertValues (int custIntrNo, String custName, String custCompany)
    throws SQLException {

    // Declare method variables
    Connection conn = null;
    PreparedStatement pstmt = null;
    int insertResults;
    String sql;

    System.out.print("\n");
    System.out.print("+-------------------------------+\n");
    System.out.print("| METHOD: insertValues          |\n");
    System.out.print("+-------------------------------+\n");
    System.out.print("\n");

    // Build SQL statement
    sql = "INSERT INTO jserver_demo_table VALUES (?,?,?)";

    try {

      // Connect to Oracle using JDBC driver
      System.out.print("*** -> Connecting to        -> " + connectionURL + "\n");
      conn = DriverManager.getConnection(connectionURL);
      System.out.print("*** -> Connected as URL     -> " + connectionURL + "\n");

      // TURN OFF AutoCommit
      System.out.print("*** -> Turning Off AutoCommit...\n");
      conn.setAutoCommit(false);

      // PREPARE STATEMENT
      System.out.print("*** -> Prepare Statement...\n");
      pstmt = conn.prepareStatement(sql);

      // BIND VARIABLES
      System.out.print("*** -> Binding Variables...\n");
      pstmt.setInt(1, custIntrNo);
      pstmt.setString(2, custName);
      pstmt.setString(3, custCompany);

      insertResults = pstmt.executeUpdate();
      System.out.print("*** ->   RESULTS            -> " + insertResults + " row created.\n");
      pstmt.close();

      // COMMIT TRANSACTION
      System.out.print("*** -> Commiting Transaction...\n");
      conn.commit();

    } catch (SQLException e) {
      System.err.println(e.getMessage());
    }

  } // END: insertValues


  // +--------------------------------------+
  // | QUERY ALL VALUES: ( forward >>> )    |
  // +--------------------------------------+
  public static void queryForward ()
    throws SQLException {

    // Declare method variables
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
    String sql;

    System.out.print("\n");
    System.out.print("+-------------------------------+\n");
    System.out.print("| METHOD: queryForward          |\n");
    System.out.print("+-------------------------------+\n");
    System.out.print("\n");

    // Build SQL statement
    sql = "SELECT * FROM jserver_demo_table ORDER BY test_jserver_intr_no";

    try {

      // Connect to Oracle using JDBC driver
      System.out.print("*** -> Connecting to        -> " + connectionURL + "\n");
      conn = DriverManager.getConnection(connectionURL);
      System.out.print("*** -> Connected as URL     -> " + connectionURL + "\n");

      // TURN OFF AutoCommit
      System.out.print("*** -> Turning Off AutoCommit...\n");
      conn.setAutoCommit(false);

      System.out.print("*** -> Executing Forward (NORMAL) Query...\n");

      System.out.print("*** ->   USING DEFAULTS:\n");
      System.out.print("*** ->   - (P1) ResultSet.TYPE_FORWARD_ONLY\n");
      System.out.print("*** ->   - (P2) ResultSet.CONCUR_READ_ONLY\n");
      stmt = conn.createStatement (ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

      System.out.print("*** -> Opening ResultsSet...\n");
      rset = stmt.executeQuery (sql);

      while (rset.next ()) {

        int     rowNumber;
        int     test_jserver_intr_no;
        String  test_jserver_customer_name;
        String  test_jserver_company_name;

        rowNumber = rset.getRow();

        test_jserver_intr_no = rset.getInt(1);
        if ( rset.wasNull() ) {
          test_jserver_intr_no = -1;
        }

        test_jserver_customer_name = rset.getString(2);
        if ( rset.wasNull() ) {
          test_jserver_customer_name = "<null>";
        }

        test_jserver_company_name = rset.getString(3);
        if ( rset.wasNull() ) {
          test_jserver_company_name = "<null>";
        }

        System.out.print("*** ->   RESULTS            -> [R" + rowNumber + "] " + test_jserver_intr_no + " - " + test_jserver_customer_name + " - " + test_jserver_company_name + "\n");
      }

      System.out.print("*** -> Closing ResultSet...\n");
      rset.close();

      System.out.print("*** -> Closing Statement...\n");
      stmt.close();

    } catch (SQLException e) {
      System.err.println(e.getMessage());
    }

  } // END: queryForward


  // +--------------------------------------+
  // | QUERY ALL VALUES: ( reverse <<< )    |
  // +--------------------------------------+
  public static void queryReverse ()
    throws SQLException {

    // Declare method variables
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
    String sql;

    System.out.print("\n");
    System.out.print("+-------------------------------+\n");
    System.out.print("| METHOD: queryReverse          |\n");
    System.out.print("+-------------------------------+\n");
    System.out.print("\n");

    // Build SQL statement
    sql = "SELECT * FROM jserver_demo_table ORDER BY test_jserver_intr_no";

    try {

      // Connect to Oracle using JDBC driver
      System.out.print("*** -> Connecting to        -> " + connectionURL + "\n");
      conn = DriverManager.getConnection(connectionURL);
      System.out.print("*** -> Connected as URL     -> " + connectionURL + "\n");

      // TURN OFF AutoCommit
      System.out.print("*** -> Turning Off AutoCommit...\n");
      conn.setAutoCommit(false);

      System.out.print("*** -> Executing Forward (SCROLLABLE) Query...\n");

      System.out.print("*** ->   USING DEFAULTS:\n");
      System.out.print("*** ->   - (P1) ResultSet.TYPE_SCROLL_INSENSITIVE\n");
      System.out.print("*** ->   - (P2) ResultSet.CONCUR_READ_ONLY\n");
      stmt = conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

      System.out.print("*** -> Opening ResultsSet...\n");
      rset = stmt.executeQuery (sql);

      System.out.print("*** ->\n");
      System.out.print("*** -> NOTE:\n");
      System.out.print("*** -> --------------------------------------------------------------\n");
      System.out.print("*** -> Got results. The ResultPointer Pointer is before the first row.\n");
      System.out.print("*** -> Position ResultSet Pointer after the last row.\n");
      System.out.print("*** -> --------------------------------------------------------------\n");
      rset.afterLast();

      while (rset.previous ()) {

        int     rowNumber;
        int     test_jserver_intr_no;
        String  test_jserver_customer_name;
        String  test_jserver_company_name;

        rowNumber = rset.getRow();

        test_jserver_intr_no = rset.getInt(1);
        if ( rset.wasNull() ) {
          test_jserver_intr_no = -1;
        }

        test_jserver_customer_name = rset.getString(2);
        if ( rset.wasNull() ) {
          test_jserver_customer_name = "<null>";
        }

        test_jserver_company_name = rset.getString(3);
        if ( rset.wasNull() ) {
          test_jserver_company_name = "<null>";
        }

        System.out.print("*** ->   RESULTS            -> [R" + rowNumber + "] " + test_jserver_intr_no + " - " + test_jserver_customer_name + " - " + test_jserver_company_name + "\n");
      }

      System.out.print("*** -> Closing ResultSet...\n");
      rset.close();

      System.out.print("*** -> Closing Statement...\n");
      stmt.close();

    } catch (SQLException e) {
      System.err.println(e.getMessage());
    }

  } // END: queryReverse


  // +--------------------------------------+
  // | DELETE ALL VALUES                    |
  // +--------------------------------------+
  public static void deleteValues ()
    throws SQLException {

    // Declare method variables
    Connection conn = null;
    Statement stmt = null;
    int deleteResults;
    String sql;

    System.out.print("\n");
    System.out.print("+-------------------------------+\n");
    System.out.print("| METHOD: deleteValues          |\n");
    System.out.print("+-------------------------------+\n");
    System.out.print("\n");

    // Build SQL statement
    sql = "DELETE FROM jserver_demo_table";

    try {

      // Connect to Oracle using JDBC driver
      System.out.print("*** -> Connecting to        -> " + connectionURL + "\n");
      conn = DriverManager.getConnection(connectionURL);
      System.out.print("*** -> Connected as URL     -> " + connectionURL + "\n");

      // TURN OFF AutoCommit
      System.out.print("*** -> Turning Off AutoCommit...\n");
      conn.setAutoCommit(false);

      System.out.print("*** -> Creating Statement...\n");
      stmt = conn.createStatement ();

      deleteResults = stmt.executeUpdate(sql);
      System.out.print("*** ->   RESULTS            -> " + deleteResults + " rows deleted.\n");

      System.out.print("*** -> Closing Statement...\n");
      stmt.close();

      // COMMIT TRANSACTION
      System.out.print("*** -> Commiting Transaction...\n");
      conn.commit();

    } catch (SQLException e) {
      System.err.println(e.getMessage());
    }

  } // END: deleteValues


  // +--------------------------------------+
  // | DROP OBJECT                          |
  // +--------------------------------------+
  public static void dropObject (String object_type, String object_name)
    throws SQLException {

    // Declare method variables
    Connection conn = null;
    String sql;

    System.out.print("\n");
    System.out.print("+-------------------------------+\n");
    System.out.print("| METHOD: dropObject            |\n");
    System.out.print("+-------------------------------+\n");
    System.out.print("\n");

    // Build SQL statement
    sql = "DROP " + object_type + " " + object_name;

    try {

      // Connect to Oracle using JDBC driver
      System.out.print("*** -> Connecting to        -> " + connectionURL + "\n");
      conn = DriverManager.getConnection(connectionURL);
      System.out.print("*** -> Connected as URL     -> " + connectionURL + "\n");

      // TURN OFF AutoCommit
      System.out.print("*** -> Turning Off AutoCommit...\n");
      conn.setAutoCommit(false);

      System.out.print("*** -> Creating Statement...\n");
      Statement stmt = conn.createStatement();

      stmt.executeUpdate(sql);
      System.out.print("*** -> Drop " + object_type + " " + object_name + "...\n");

      stmt.close();

    } catch (SQLException e) {
      System.err.println(e.getMessage());
    }

  } // END: dropObject


  // +--------------------------------------+
  // | SHOW USER AND SYSDATE                |
  // +--------------------------------------+
  public static void showUser ()
    throws SQLException {

    // Declare method variables
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
    String sql;

    System.out.print("\n");
    System.out.print("+-------------------------------+\n");
    System.out.print("| METHOD: showUser              |\n");
    System.out.print("+-------------------------------+\n");
    System.out.print("\n");

    // Build SQL statement
    sql = "SELECT user, TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') FROM dual";

    try {

      // Connect to Oracle using JDBC driver
      System.out.print("*** -> Connecting to        -> " + connectionURL + "\n");
      conn = DriverManager.getConnection(connectionURL);
      System.out.print("*** -> Connected as URL     -> " + connectionURL + "\n");

      // TURN OFF AutoCommit
      System.out.print("*** -> Turning Off AutoCommit...\n");
      conn.setAutoCommit(false);

      System.out.print("*** -> Executing Forward (NORMAL) Query...\n");

      System.out.print("*** ->   USING DEFAULTS:\n");
      System.out.print("*** ->   - (P1) ResultSet.TYPE_FORWARD_ONLY\n");
      System.out.print("*** ->   - (P2) ResultSet.CONCUR_READ_ONLY\n");
      stmt = conn.createStatement (ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

      System.out.print("*** -> Opening ResultsSet...\n");
      rset = stmt.executeQuery (sql);

      while (rset.next ()) {

        int     rowNumber;
        String  user;
        String  sysdate;

        rowNumber = rset.getRow();

        user = rset.getString(1);
        if ( rset.wasNull() ) {
          user = "<null>";
        }

        sysdate = rset.getString(2);
        if ( rset.wasNull() ) {
          sysdate = "<null>";
        }

        System.out.print("*** ->   RESULTS            -> [R" + rowNumber + "] " + user + " - " + sysdate + "\n");
      }

      System.out.print("*** -> Closing ResultSet...\n");
      rset.close();

      System.out.print("*** -> Closing Statement...\n");
      stmt.close();

    } catch (SQLException e) {
      System.err.println(e.getMessage());
    }

  } // END: showUser

}