package cs610;

/**
 * Simple application that will run a specific query (or set of queries,
 * if modified). It uses the same methods to connect to the database
 * (factorization anyone?). The output will be in xml format. The tag of the
 * outer node will be <resultset>. The resultset node will contain a collection of
 * <result> nodes. The contents of the <result> nodes will be query specific
 * We are not using node attributes in this example.
 */

import  java.io.PrintStream;
import  java.sql.DriverManager;
import  java.sql.Connection;
import  java.sql.ResultSetMetaData;
import  java.sql.ResultSet;
import  java.sql.Statement;

public class QueryXmlExample {

  public QueryXmlExample(String userId, String userPassword,
    String databaseURL) throws Exception {
    m_dbUser = userId;
    m_dbPassword = userPassword;
    m_dbURL = databaseURL;
  }

  private void query() throws Exception {
    connect();
    executeQuery();
    disconnect();
  }

  public static void main(String args[]) {
    if (args.length != 6) {
      usage();
      System.exit(-1);
    }
    int numArgs = 0;
    String userId = null;
    String userPassword = null;
    String databaseURL = null;
    String xmlFileName = 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 {
        System.out.println("Unknown option " + args[numArgs]);
        usage();
        System.exit(-1);
      }
    }
    if (userId != null && userPassword != null && databaseURL != null) {
      QueryXmlExample xmlQueryEx = null;
      try {
        xmlQueryEx = new QueryXmlExample(userId, userPassword, databaseURL);
        xmlQueryEx.query();
      }
      catch (Exception ex) {
        System.out.println("Exception caught");
        ex.printStackTrace();
        try {
          xmlQueryEx.disconnect();
        }
        catch (Exception ignored) {;}
        System.exit(-1);
      }
    }
    else {
      System.out.println("Insufficient arguments specified");
      usage();
      System.exit(-1);
    }
  }

  public static void usage() {
    System.out.println("cs610.XmlQueryExample -dbUser dbUserId - dbPassword dbPasswd "
      + "-dbURL dbURL");
  }

  /**
   * 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);
  }

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

  private void executeQuery() throws Exception {
    Statement queryStatement = m_connection.createStatement();
    ResultSet rs = queryStatement.executeQuery(
      "select name, classid, grade from enrollment, student, class where " +
      "enrollment.studentkey = student.studentkey and " +
      "enrollment.classkey = class.classkey");
    ResultSetToXMLConverter.convertResultSet(rs, System.out);
    rs.close();
    queryStatement.close();
  }


  private static class ResultSetToXMLConverter {
    /**
     * Observe how generic this method actually is. It takes any ResultSet and
     * a PrintStream (System.out is the most popular) and it will generated
     * an XML datagram. Some simple indentation is used.
     */

    public static void convertResultSet(ResultSet resultSet,
      PrintStream out) throws Exception {
      out.println("<?xml version=\"1.0\"?>");
      out.println("<ResultSet>");
      while(resultSet.next()) {
        printIndent(out, 1);
        out.println("<Result>");
        ResultSetMetaData rsm = resultSet.getMetaData();
        int columnCount = rsm.getColumnCount();
        //indexing for database columns always starts with 1
        for(int i = 1; i <= columnCount; i++) {
          String columnName = rsm.getColumnName(i);
          printIndent(out, 2);
          out.print("<");
          out.print(columnName);
          out.print(">");
          //get the actual value of the column, print as Object, most of the
          //database types will have cooperating toString() methods
          //this value will be the value of the node having the column name as tag
          out.print(resultSet.getObject(i));
          out.print("</");
          out.print(columnName);
          out.println(">");
        }
        printIndent(out, 1);
        out.println("</Result>");
      }
      out.println("</ResultSet>");
    }

    public static void printIndent(PrintStream out, int numberOfIndentations) {
      for(int i = 0; i < numberOfIndentations; i++) {
        out.print("  "); //two spaces
      }
    }
  }

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