/*
 * banking.java - creating and querying banking database using JDBC 
 *
 */

import java.sql.*;
//import java.util.Enumeration;
import java.util.*;
import java.io.*;

public class banking {
  public static void main(String [] args) {
    final String tnspre = "jdbc:oracle:oci8:@";
    String user; 
    String pass; 
    
    user = readEntry("userid :");
    pass = readEntry("password :");

    try { //to catch SQLException
      //the outputs here explain these steps...
      System.out.println("Registering driver...");
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      System.out.println("Attempting connection...");
      Connection x = DriverManager.getConnection(tnspre, user, pass);
      
      System.out.println("Creating statement...");
      Statement stmt = x.createStatement();
      
      System.out.println("Submitting query...");
      //note no semicolon in the SQL query

      //drop the table if exists
      stmt.execute("DROP TABLE jdbc_account cascade constraints");

      //first set up a table
      stmt.execute("CREATE TABLE jdbc_account (" +
		"account_number varchar(5) not null," +
		"branch_name varchar(12) not null," + 
		"balance int not null check(balance >= 0), " +
		"primary key (account_number))");
      
      //then add a row to the table
      stmt.execute("INSERT INTO jdbc_account VALUES ('A-101', 'Downtown', 500)");

      // or execute any SQL statement another way
      String account_number, branch_name, balance, query;
      account_number = readEntry("Account Number :");
      branch_name = readEntry("Branch Name :");
      balance = readEntry("Balance :");
      query = "INSERT INTO jdbc_account VALUES ('" +
               account_number + "', '" + branch_name + "', " + balance + ")";
      System.out.println(query);
      stmt.execute(query);
      
      //now fetch that row
      ResultSet result = stmt.executeQuery("SELECT * FROM jdbc_account");
      
      String AccountNumber;
      String BranchName;
      int Balance;

      System.out.print("Query Result: \nAccountNumber  BranchName  Balance\n");
      while (result.next()) {
          AccountNumber = result.getString("account_number");
          BranchName = result.getString("branch_name");
          Balance = result.getInt("balance");
          System.out.println(AccountNumber + "        " + BranchName + "     " + Balance);
      }
  
      //don't hold up resources:
      result.close();
      x.close();

    } catch (SQLException se) {
      System.err.println("Caught an SQLException!");
      se.printStackTrace();
      System.err.println("Available drivers:");
      Enumeration e = DriverManager.getDrivers();
      while (e.hasMoreElements()) {
        System.err.println(e.nextElement());
      }
      System.exit(1);
    }
  }

  // readEntry function -- to read input string
  static String readEntry(String prompt) {
    try {
      StringBuffer buffer = new StringBuffer();
      System.out.print(prompt);
      System.out.flush();
      int c = System.in.read();
      while (c != '\n' && c != -1) {
        buffer.append((char) c);
        c = System.in.read();
      }
      return buffer.toString().trim();
    } catch (IOException e) {
      return "";
      }
  }

  // readNumber function -- to read input number 
  // could be found in the Primer book

}