package com.devdaily.sqlprocessortests;

import java.sql.*;
import com.missiondata.oss.sqlprocessor.SQLProcessor;
import org.apache.log4j.Category;
import org.apache.log4j.PropertyConfigurator;
import java.util.Properties;
import java.io.FileInputStream;
import java.io.IOException;


/**
 * This class shows how to add Log4J logging to the SQLProcessor.
 */
public class SQLProcessorDemo3
{
  Connection conn;
  static final Category log = Category.getInstance(SQLProcessorDemo3.class);
  static final String LOG_PROPERTIES_FILE = "lib/log4j.properties";

  public static void main(String[] args)
  {
    new SQLProcessorDemo3();
  }

  public SQLProcessorDemo3()
  {
    // new to this demo
    initializeLogger();

    try
    {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      String url = "jdbc:mysql://localhost/coffeebreak";
      conn = DriverManager.getConnection(url,"root", "ibmis4me");
      doTests();
      conn.close();
    }
    catch (ClassNotFoundException ex) {System.err.println(ex.getMessage());}
    catch (IllegalAccessException ex) {System.err.println(ex.getMessage());}
    catch (InstantiationException ex) {System.err.println(ex.getMessage());}
    catch (SQLException ex)           {System.err.println(ex.getMessage());}
  }

  private void doTests()
  {
    doSelectTest();

    doInsertTest();  doSelectTest();
    doUpdateTest();  doSelectTest();
    doDeleteTest();  doSelectTest();
  }

  private void doSelectTest()
  {
    //log.info("JUST ENTERED doSelectTest");
    System.out.println("[OUTPUT FROM SELECT]");
    String query = "SELECT COF_NAME, PRICE FROM COFFEES";
    SQLProcessor sqlProcessor = new SQLProcessor("The Coffee Break SELECT Test", query)
    {
      protected void process(ResultSet rs) throws SQLException
      {
        String s = rs.getString("COF_NAME");
        float n = rs.getFloat("PRICE");
        System.out.println(s + "   " + n);
      }
    };
    sqlProcessor.execute(conn);
  }

  /**
   * This is our first use of the SQLProcessor "set" methods.
   * This is much better than using JDBC statements, as the underlying
   * PreparedStatements keep you from having to worry about SQL Injection hacks.
   * And the syntax is much more obvious, and less prone to errors, than
   * the JDBC PreparedStatement syntax.
   */
  private void doInsertTest()
  {
    System.out.print("\n[Performing INSERT] ... ");
    SQLProcessor sqlProcessor = new SQLProcessor("The Coffee Break INSERT Test",
                "INSERT INTO COFFEES VALUES (|coffeeName|,|supplierID|,|price|,|sales|,|total|)");
    sqlProcessor.set("coffeeName","BREAKFAST BLEND");
    sqlProcessor.set("supplierID",200);
    sqlProcessor.set("price",7.99);
    sqlProcessor.set("sales",0);
    sqlProcessor.set("total",0);
    sqlProcessor.execute(conn);
  }

  private void doUpdateTest()
  {
    System.out.print("\n[Performing UPDATE] ... ");
    SQLProcessor sqlProcessor = new SQLProcessor("The Coffee Break UPDATE Test",
                "UPDATE COFFEES SET PRICE=|price| WHERE COF_NAME=|coffeeName|");
    sqlProcessor.set("price", 4.99);
    sqlProcessor.set("coffeeName", "BREAKFAST BLEND");
    sqlProcessor.execute(conn);
  }

  private void doDeleteTest()
  {
    System.out.print("\n[Performing DELETE] ... ");
    SQLProcessor sqlProcessor = new SQLProcessor("The Coffee Break DELETE Test",
                "DELETE FROM COFFEES WHERE COF_NAME=|coffeeName|");
    sqlProcessor.set("coffeeName", "BREAKFAST BLEND");
    sqlProcessor.execute(conn);
  }

  /**
   * This is the method that is used to initialize Log4J.
   */
  private void initializeLogger()
  {
    Properties logProperties = new Properties();

    try
    {
      logProperties.load(new FileInputStream(LOG_PROPERTIES_FILE));
      PropertyConfigurator.configure(logProperties);
      log.info("Logging initialized.");
    }
    catch(IOException e)
    {
      throw new RuntimeException("Unable to load logging property " + LOG_PROPERTIES_FILE);
    }
  }


}