/**
 * This simple JDBC example will connect to a users schema, create a table
 * with the name specified by the user, insert 2 rows into the table, query
 * it and output the results and then drop the table
 * Please look at the usage method, before trying to run the program.
 */

import  java.sql.DriverManager;
import  java.sql.Connection;
import  java.sql.PreparedStatement;
import  java.sql.ResultSet;
import  java.sql.Statement;


public class JDBCExample {

  /**
   * the only constructor
   * @param     String userId
   * @param     String userPassword
   * @param     String databaseURL
   * @param     String tableName
   */
  public JDBCExample (String userId, String userPassword, String databaseURL,
    String tableName) {
    m_dbUser = userId;
    m_dbPassword = userPassword;
    m_dbURL = databaseURL;
    m_tableName = tableName;
  }

  /**
   * usage method (static)
   */
  public static void usage () {
    System.out.println("JDBCExample -dbUser dbUserId - dbPassword dbPasswd "
      + "-dbURL dbURL -tableName tableName");
  }

  /**
   * main method, will parse the parameters, create a JDBCExample instance
   * and call the jdbcTest method
   * @param args[]
   */
  public static final void main (String args[]) {
    if (args.length != 8) {
      usage();
      System.exit(-1);
    }
    int numArgs = 0;
    String userId = null;
    String userPassword = null;
    String databaseURL = null;
    String tableName = null;
    //parse the parameters
    while (numArgs < args.length) {
      if ("-DBUSER".equalsIgnoreCase(args[numArgs])) {
        userId = args[numArgs + 1];
        numArgs += 2;
      }
      else if ("-DBPASSWORD".equalsIgnoreCase(args[numArgs])) {
        userPassword = args[numArgs + 1];
        numArgs += 2;
      }
      else if ("-DBURL".equalsIgnoreCase(args[numArgs])) {
        databaseURL = args[numArgs + 1];
        numArgs += 2;
      }
      else if ("-TABLENAME".equalsIgnoreCase(args[numArgs])) {
        tableName = args[numArgs + 1];
        numArgs += 2;
      }
      else {
        System.out.println("Unknown option " + args[numArgs]);
        usage();
        System.exit(-1);
      }
    }
    if (userId != null && userPassword != null && databaseURL != null &&
            tableName != null) {
      JDBCExample jdbcEx = null;
      try {
        jdbcEx = new JDBCExample(userId, userPassword, databaseURL, tableName);
        jdbcEx.jdbcTest();
      }
      catch (Exception ex) {
        System.out.println("Exception caught");
        ex.printStackTrace();
        try {
          jdbcEx.disconnect();
        }
        catch (Exception ignored) {;}
        System.exit(-1);
      }
    }
    else {
      System.out.println("Insufficient arguments specified");
      usage();
      System.exit(-1);
    }
  }

    /**
     * the driver method
     * @exception Exception
     */
    public void jdbcTest () throws Exception {
        connect();
        createTable();
        insertIntoTable();
        queryTable();
        dropTable();
        disconnect();
    }

    /**
     * This method will establish a database connection
     * @exception Exception
     */
    private void connect () throws Exception {
      //load the JDBC driver class, in this case oracle
      Class.forName("oracle.jdbc.driver.OracleDriver");
      m_connection = DriverManager.getConnection(m_dbURL, m_dbUser, m_dbPassword);
      //usually a good thing, DML operations will have to call commit or rollback
      m_connection.setAutoCommit(false);
      System.out.println("Connection obtained");
    }

    /**
     * Creates a table with the input name, having two columns
     * @exception Exception
     */
  private void createTable () throws Exception {
    Statement createTableStatement = m_connection.createStatement();
    createTableStatement.executeUpdate("create table " + m_tableName +
      " ( column1 NUMBER, column2 VARCHAR2(64))");
    createTableStatement.close();
    System.out.println("Table " + m_tableName + " created");
  }

    /**
     * Will insert 2 rows into the table
     * @exception Exception
     */
  private void insertIntoTable () throws Exception {
    //this time use a preparedStatment
    PreparedStatement insertStatement =
      m_connection.prepareStatement("insert into " + m_tableName +
        " values (?, ?)");
    //insert two rows
    //substituting the first parameter
    insertStatement.setInt(1, 1);
    //substituting the second parameter
    insertStatement.setString(2, "String 1");
    insertStatement.executeUpdate();
    //substituting the first parameter
    insertStatement.setInt(1, 2);
    //substituting the second parameter
    insertStatement.setString(2, "String 2");
    insertStatement.executeUpdate();
    insertStatement.close();
    //we have to commit the connection
    m_connection.commit();
  }

  /**
   * The query method will run a simple query selecting both columns
   * @exception Exception
   */
  private void queryTable () throws Exception {
    Statement queryStatement = m_connection.createStatement();
    ResultSet rs = queryStatement.executeQuery("select column1, column2 from "
      + m_tableName);
    if (rs != null) {
      int rowNumber = 1;
      while (rs.next()) {
        //observe that the first parameter we get by name,
        //and the second by position
        System.out.println("Row " + rowNumber++ + " - Column1: " +
          rs.getInt("column1") + " Column2: " + rs.getString(2));
      }
      rs.close();
    }
    queryStatement.close();
  }

  /**
   * This method drops the table
   * @exception Exception
   */
  private void dropTable () throws Exception {
    Statement dropTableStatement = m_connection.createStatement();
    dropTableStatement.executeUpdate("drop table " + m_tableName);
    dropTableStatement.close();
    System.out.println("Table " + m_tableName + " dropped");
  }

  /**
   * This method will close the database connection
   * @exception Exception
   */
  private void disconnect () throws Exception {
    if (m_connection != null) {
      m_connection.close();
      System.out.println("Connection closed");
    }
  }

  private String m_dbUser;
  private String m_dbPassword;
  private String m_dbURL;
  private Connection m_connection;
  private String m_tableName;
}