/********************************************************/
/*     Script   :  DatabaseSelect.java                  */
/*     Author   :  Chandini Paterson                    */
/*     Created  :  15/06/2001                           */
/*     Purpose  :  A sample script to show how to use   */
/*                 JDBC to query a database.  This is   */
/*                 based on the Oracle database         */
/********************************************************/

import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;


public class DatabaseSelect {
  // we are using the Oracle thin client, hence the dbURL will be 
  private String dbURL = "jdbc:oracle:thin:@iona:1521:john";

  private String userName = "scott";
  private String password = "tiger";
  private Connection conn;
  private String queryString;


  /* The Class.forName could raise the ClassNotFoundException and the
     getConnection method could raise SQLException.  Hence we need to
     handle or declare them in the method signature */

  public DatabaseSelect() throws SQLException, ClassNotFoundException {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    conn = DriverManager.getConnection(dbURL, userName, password);
  }


  // set the queryString.
  public void setQueryString(String queryString) {
    this.queryString = queryString;
  }


  public ResultSet getQueryResults() throws SQLException{
    PreparedStatement pstmt = conn.prepareStatement(queryString);
    //ResultSet class holds all rows from the query result.
    ResultSet rset = pstmt.executeQuery();

    return ((rset == null)? null: rset);
  }

  public void executeQuery() throws SQLException {
    ResultSet rset = getQueryResults();
    //Use ResultSetMetaData to get the meta data info, such as column Names etc
    ResultSetMetaData rsmd = rset.getMetaData();

    int colCount = rsmd.getColumnCount();

    System.out.println("Results of your query :  \n\n");
    for (int i=1; i<colCount + 1; i++) {
      System.out.print(rsmd.getColumnName(i)+ "     " );
    }

    System.out.println("\n\n");

    while (rset.next()) {
      for (int i=1; i<colCount+1; i++) {
        System.out.print(rset.getString(i) + "     ");
      }
      System.out.println("");
    }
  }

  // we need to free the connection once we are done
  public void freeConnection() throws SQLException {
    conn.close();
  }


  // Now we test our code using the main method.
  public static void main(String[] args) {
    try {
      DatabaseSelect dbSelect = new DatabaseSelect();
      dbSelect.setQueryString("SELECT empno, ename, sal, job , deptno FROM emp");
      dbSelect.executeQuery();
      if (dbSelect.conn != null) {
        dbSelect.freeConnection();
      }
    } catch (SQLException sqle) {
      System.out.println("SQL Exception Raised:  " + sqle.toString());
    } catch (ClassNotFoundException cnfe){
      System.out.println("JDBC Class Not Found:   " + cnfe.toString());
    } catch (Exception e) {
      System.out.println("General Exception Raised:  " + e.toString());
    }
  }
}