/*
 *  $Source: v:/cvsroot/open/projects/WebARTS/ca/bc/webarts/widgets/JDBCAdapter.java,v $
 *  $Name:  $
 *  $Revision: 567 $
 *  $Date: 2012-11-03 20:36:02 -0700 (Sat, 03 Nov 2012) $
 *  $Locker:  $
 */
/*
 *  Copyright (C) 2001 WebARTS Design, North Vancouver Canada
 *  http://www..webarts.bc.ca
 *
 *  This program is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program; if not, write to the Free Software
 *  Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
 */
package ca.bc.webarts.widgets;


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

import java.util.Vector;
import javax.swing.event.TableModelEvent;
import javax.swing.table.AbstractTableModel;

/**
 * JDBCAdapter is an encapsulation class, transforming the JDBC interface to the
 * <code>TableModel</code> interface. This class attempts to abstract the details
 * of the JDBC DB connection and query specifics. It presents all the results to
 * the user in the form of a Java <code>TableModel</code> by extending/overiding
 * the <code>AbstractTableModel</code>. This an then be used to as the basis for
 * a JTable.<BR>
 * <BR>
 * <U>example usage</U> <BR>
 * <code><PRE>
 *      JDBCAdapter       jdbcTableAdapter;
 *      private String    dbVendorParam = "oracle",           // this can be oracle or db2
 *                        dbNameParam = "someDB",           // this is the name of the DB to hit
 *                        dbUsernameParam = "myUsername",   // duh
 *                        dbPasswordParam = "myPassword,   // duh
 *                        dbServerParam = "172.118.88.2",  // the name or IP address of the server runing the DB
 *                        dbPortParam = "1521";             // the port to use for JDBC access (oracle does not have a seperate listening port it uses the main port)
 *                                                      // see the vendor docs for the specific port to access
 *      boolean          dbConnected = false;
 *      String           sampleQuery = "SELECT * from employee";
 *      TableSorter      dataSorter     = new TableSorter();
 *      Table            table;
 *
 *      try {
 *          jdbcTableAdapter = new JDBCAdapter(dbVendorParam, dbNameParam, dbUsernameParam, dbPasswordParam, dbServerParam, dbPortParam);
 *          dbConnected = true;
 *      }
 *      catch (java.security.AccessControlException ex) {
 *             System.out.println("Security Exception Connecting to " + dbVendorParam +"'s Database." );
 *         dbConnected = false;
 *      }
 *            // retrieve data from the database
 *           if (jdbcTableAdapter.isDbConnected()) {
 *              System.out.println("Retrieve Raw data from the database");
 *                      jdbcTableAdapter.executeQuery(sampleQuery);
 *
 *            // now get the table set up.
 *                      dataSorter.setModel(jdbcTableAdapter);
 *          table = new JTable(dataSorter);
 *           }
 *</PRE> </code>
 *
 * @author    Philip Milne
 * @author    Tom Gutwin
 * @created   October 23, 2001
 * @version   1.30 09/25/97
 * @version   1.90 09/06/00
 * @see       javax.swing.table.AbstractTableModel
 */
public class JDBCAdapter extends AbstractTableModel
{
  /**
   * The JDBC Connection used for queries.
   */
  private Connection connection;
  /**
   * Description of the Field
   */
  private Statement statement;
  /**
   * Description of the Field
   */
  private String driverName;
  /**
   * Description of the Field
   */
  private String JDBCconnectString;
  /**
   * Description of the Field
   */
  private String JDBCconnectString2;
  /**
   * Description of the Field
   */
  private String dbConnectString;
  /**
   * Description of the Field
   */
  private String oracleDriverName = "oracle.jdbc.driver.OracleDriver";
  /**
   * Description of the Field
   */
  private String db2DriverName = "COM.ibm.db2.jdbc.net.DB2Driver";
  /**
   * Description of the Field
   */
  private ResultSet resultSet;
  /**
   * Description of the Field
   */
  private String[] columnNames = {};
  /**
   * Description of the Field
   */
  private Vector rows = new Vector();
  /**
   * Description of the Field
   */
  private ResultSetMetaData metaData;
  /**
   * Description of the Field
   */
  private String dbVendor;
  /**
   * Description of the Field
   */
  private String dbName;
  /**
   * Description of the Field
   */
  private String dbUsername;
  /**
   * Description of the Field
   */
  private String dbPassword;
  /**
   * Description of the Field
   */
  private String dbServer;
  /**
   * Description of the Field
   */
  private String dbPort;
  /**
   * Description of the Field
   */
  private boolean errorCondition = false;


  /**
   * Main Constructor for the class. It takes all the parameters to get the connection
   * set up. It also prints error messages for problems that might be encountered
   * when getting set up. To do the actaual query call the executeQuery method.
   *
   * @param dbV     DB Vendor
   * @param dbN     DB Name
   * @param dbU     Username
   * @param dbPass  Password
   * @param dbS     The DB Server Name.
   * @param dbP     The JDBC Port on the DB Server
   */
  public JDBCAdapter(String dbV, String dbN, String dbU, String dbPass, String dbS, String dbP)
  {

    dbVendor = dbV;
    dbName = dbN;
    dbUsername = dbU;
    dbPassword = dbPass;
    dbServer = dbS;
    dbPort = dbP;

    if (dbVendor.equals("oracle"))
    {
      driverName = oracleDriverName;
      try
      {
        System.out.println("Registering the " + dbVendor + " JDBC Driver.");
        //DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        Class.forName(driverName);

        /*
         *  Form the database connect string(TNSNAMES entry) as a name-value pair
         */
        dbConnectString = dbServer + ":" + dbPort + ":" + dbName;
        JDBCconnectString = new String("jdbc:" + dbVendor + ":thin:@" +
                                       dbConnectString);
        JDBCconnectString2 = new String("jdbc:oracle:thin:" + dbUsername +
                                      "/" + dbPassword + "@" + dbConnectString);

        System.out.println("Opening db connection: " + JDBCconnectString);

        connection = DriverManager.getConnection(JDBCconnectString, dbUsername, dbPassword);
        statement = connection.createStatement();
      }
      catch (ClassNotFoundException ex)
      {
        System.err.println("Cannot find the database driver classes.");
        System.err.println(ex);
      }
      catch (SQLException ex)
      {
        System.err.println("Cannot connect to this database.");
        System.err.println(ex);
        ex.printStackTrace();
      }
      /*
       *  catch (java.security.AccessControlException ex) {
       *  System.err.println("Cannot connect to this database-Security Permission Error");
       *  System.err.println(ex);
       *  }
       */
    }
    else
    {
      if (dbVendor.equals("db2"))
      {
        driverName = db2DriverName;
        /*
         *  Form the database connect string(TNSNAMES entry) as a name-value pair
         */
        dbConnectString = dbServer + ":" + dbPort + "/" + dbName;
        JDBCconnectString = new String("jdbc:" + dbVendor + "://" + dbConnectString);
        try
        {
          System.out.println("Registering the " + dbVendor + " JDBC Driver.");
          Class.forName(driverName);
          System.out.println("Opening db connection: " + JDBCconnectString);
          System.out.println("With User: " + dbUsername + " : " + dbPassword);

          connection = DriverManager.getConnection(JDBCconnectString, dbUsername, dbPassword);
          statement = connection.createStatement();
        }
        catch (ClassNotFoundException ex)
        {
          System.err.println("Cannot find the database driver classes.");
          System.err.println(ex);
        }
        catch (SQLException ex)
        {
          System.err.println("Cannot connect to this database.");
          System.err.println(ex);
        }
        /*
         *  catch (java.security.AccessControlException ex) {
         *  System.err.println("Cannot connect to this database-Security Permission Error");
         *  System.err.println(ex);
         *  }
         */
      }
      else
      {
        System.out.println("Sorry Unsupported DBVendor <Supports - Oracle or IBM DB2> ");
      }
    }

  }


  /**
   * Copy constructor for this class. It takes an existing JDBCAdapter and returns
   * a new instantiated JDBCAdapter
   *
   * @param jdbcAdapter  Description of Parameter
   */
  public JDBCAdapter(JDBCAdapter jdbcAdapter)
  {
    dbVendor = jdbcAdapter.getdbVendor();
    dbName = jdbcAdapter.getdbName();
    dbUsername = jdbcAdapter.getdbUsername();
    dbPassword = jdbcAdapter.getdbPassword();
    dbServer = jdbcAdapter.getdbServer();
    dbPort = jdbcAdapter.getdbPort();

    if (dbVendor.equals("oracle"))
    {
      driverName = oracleDriverName;
      try
      {
        System.out.println("Registering the " + dbVendor + " JDBC Driver.");
        //DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        Class.forName(driverName);

        /*
         *  Form the database connect string(TNSNAMES entry) as a name-value pair
         */
        dbConnectString = dbServer + ":" + dbPort + ":" + dbName;
        JDBCconnectString = new String("jdbc:" + dbVendor + ":thin:@" + dbConnectString);
        JDBCconnectString2 = new String("jdbc:oracle:thin:" + dbUsername + "/" + dbPassword + "@" + dbConnectString);

        System.out.println("Opening db connection: " + JDBCconnectString);

        connection = DriverManager.getConnection(JDBCconnectString, dbUsername, dbPassword);
        statement = connection.createStatement();
      }
      catch (ClassNotFoundException ex)
      {
        System.err.println("Cannot find the database driver classes.");
        System.err.println(ex);
      }
      catch (SQLException ex)
      {
        System.err.println("Cannot connect to this database.");
        System.err.println(ex);
        ex.printStackTrace();
      }
      /*
       *  catch (java.security.AccessControlException ex) {
       *  System.err.println("Cannot connect to this database-Security Permission Error");
       *  System.err.println(ex);
       *  }
       */
    }
    else
    {
      if (dbVendor.equals("db2"))
      {
        driverName = db2DriverName;
        /*
         *  Form the database connect string(TNSNAMES entry) as a name-value pair
         */
        dbConnectString = dbServer + ":" + dbPort + "/" + dbName;
        JDBCconnectString = new String("jdbc:" + dbVendor + "://" + dbConnectString);
        try
        {
          System.out.println("Registering the " + dbVendor + " JDBC Driver.");
          Class.forName(driverName);
          System.out.println("Opening db connection: " + JDBCconnectString);
          System.out.println("With User: " + dbUsername + " : " + dbPassword);

          connection = DriverManager.getConnection(JDBCconnectString, dbUsername, dbPassword);
          statement = connection.createStatement();
        }
        catch (ClassNotFoundException ex)
        {
          System.err.println("Cannot find the database driver classes.");
          System.err.println(ex);
        }
        catch (SQLException ex)
        {
          System.err.println("Cannot connect to this database.");
          System.err.println(ex);
        }
        /*
         *  catch (java.security.AccessControlException ex) {
         *  System.err.println("Cannot connect to this database-Security Permission Error");
         *  System.err.println(ex);
         *  }
         */
      }
      else
      {
        System.out.println("Sorry Unsupported DBVendor <Supports - Oracle or IBM DB2> ");
      }
    }
  }


  /**
   * This constructor takes a URL (in the form of a String) as well as the Drivername,
   * username, password and then creates the Adapter. Thgis allows the constructor
   * to set up the connection without having to pass the vendor/DB/server info
   * seperately.
   *
   * @param url         Description of Parameter
   * @param driverName  Description of Parameter
   * @param user        Description of Parameter
   * @param passwd      Description of Parameter
   */
  public JDBCAdapter(String url, String driverName,
    String user, String passwd)
  {
    try
    {
      Class.forName(driverName);
      System.out.println("Opening db connection");

      connection = DriverManager.getConnection(url, user, passwd);
      statement = connection.createStatement();
    }
    catch (ClassNotFoundException ex)
    {
      System.err.println("Cannot find the database driver classes.");
      System.err.println(ex);
    }
    catch (SQLException ex)
    {
      System.err.println("Cannot connect to this database.");
      System.err.println(ex);
    }
  }


  /**
   * Sets the ErrorCondition attribute of the JDBCAdapter object
   *
   * @param val  The new ErrorCondition value
   */
  public void setErrorCondition(boolean val)
  {
    errorCondition = val;
  }


  /**
   * Sets the value at the specified cell in the DB to the object supplied. It
   * will take care of any casting/ type changing required to get it into the speced
   * column. this method is for the Implementation of the TableModel Interface
   *
   * @param value   The new ValueAt value
   * @param row     The new ValueAt value
   * @param column  The new ValueAt value
   */
  public void setValueAt(Object value, int row, int column)
  {
    try
    {
      String tableName = metaData.getTableName(column + 1);
      // Some of the drivers seem buggy, tableName should not be null.
      if (tableName == null)
      {
        System.out.println("Table name returned null.");
      }
      String columnName = getColumnName(column);
      String query =
        "update " + tableName +
        " set " + columnName + " = " + dbRepresentation(column, value) +
        " where ";
      // We don't have a model of the schema so we don't know the
      // primary keys or which columns to lock on. To demonstrate
      // that editing is possible, we'll just lock on everything.
      for (int col = 0; col < getColumnCount(); col++)
      {
        String colName = getColumnName(col);
        if (colName.equals(""))
        {
          continue;
        }
        if (col != 0)
        {
          query = query + " and ";
        }
        query = query + colName + " = " +
          dbRepresentation(col, getValueAt(row, col));
      }
      System.out.println(query);
      System.out.println("Not sending update to database");
      // statement.executeQuery(query);
    }
    catch (SQLException e)
    {
      //     e.printStackTrace();
      System.err.println("Update failed");
    }
    Vector dataRow = (Vector) rows.elementAt(row);
    dataRow.setElementAt(value, column);

  }


  /**
   * Gets the ErrorCondition attribute of the JDBCAdapter object
   *
   * @return   The ErrorCondition value
   */
  public boolean getErrorCondition()
  {
    return errorCondition;
  }


  /**
   * Tells whether there is an active and usable DB connection
   *
   * @return   boolean flags if there is an active and usable DB connection
   */
  public boolean isDbConnected()
  {
    boolean retVal = false;
    if (connection != null)
    {
      retVal = true;
    }
    return retVal;
  }


  //////////////////////////////////////////////////////////////////////////
  //
  //             Implementation of the TableModel Interface
  //
  //////////////////////////////////////////////////////////////////////////

  // MetaData

  /**
   * Returns a String holding the name of the column name for the column number
   * that was passed in as a parameter this method is for the Implementation of
   * the TableModel Interface
   *
   * @param column  Description of Parameter
   * @return        String the name for the indicated column
   */
  public String getColumnName(int column)
  {
//        System.err.println("getColumnName for -->" + (new Integer(column)).toString() + " is --->" + columnNames[column]);
    if (columnNames[column] != null)
    {
      return columnNames[column];
    }
    else
    {
      return "";
    }
  }


  /**
   * This method returns the Java Class type held in the specified column. it will
   * be String.class, Boolean.class, Integer.class etc. this method is for the
   * Implementation of the TableModel Interface
   *
   * @param column  Description of Parameter
   * @return        Class the Java Class representation of the object held in the
   *      specified column.
   */
  public Class getColumnClass(int column)
  {
    int type;
    try
    {
      type = metaData.getColumnType(column + 1);
    }
    catch (SQLException e)
    {
      return super.getColumnClass(column);
    }

    switch (type)
    {
      case Types.CHAR:
      case Types.VARCHAR:
      case Types.LONGVARCHAR:
        return String.class;
      case Types.BIT:
        return Boolean.class;
      case Types.TINYINT:
      case Types.SMALLINT:
      case Types.INTEGER:
        return Integer.class;
      case Types.BIGINT:
        return Long.class;
      case Types.FLOAT:
      case Types.DOUBLE:
        return Double.class;
      case Types.DATE:
        return java.sql.Date.class;
      default:
        return Object.class;
    }
  }


  /**
   * This tells you if the specified cell is editable(ie writeable). this method
   * is for the Implementation of the TableModel Interface
   *
   * @param row     Description of Parameter
   * @param column  Description of Parameter
   * @return        The CellEditable value
   */
  public boolean isCellEditable(int row, int column)
  {
    try
    {
      return metaData.isWritable(column + 1);
    }
    catch (SQLException e)
    {
      return false;
    }
  }


  /**
   * Provides and accessor for the number of columns in the table model. this method
   * is for the Implementation of the TableModel Interface
   *
   * @return   int the number of columns in the table model
   */
  public int getColumnCount()
  {
    return columnNames.length;
  }

  // Data methods

  /**
   * Provides and accessor for the number of rows in the table model. this method
   * is for the Implementation of the TableModel Interface
   *
   * @return   int the number of rows in the table model
   */
  public int getRowCount()
  {
    return rows.size();
  }


  /**
   * Provides and accessor for the object at a specific cell in the table model.
   * this method is for the Implementation of the TableModel Interface
   *
   * @param aRow     Description of Parameter
   * @param aColumn  Description of Parameter
   * @return         Object the Object at the specified cell in the table model
   */
  public Object getValueAt(int aRow, int aColumn)
  {
    Vector row = (Vector) rows.elementAt(aRow);
    return row.elementAt(aColumn);
  }


  /**
   * This method takes a String and executes as an SQL statement on the connected
   * Database. It enters all the results as rows in the table model that this class
   * represents
   *
   * @param query   Description of Parameter
   * @return        boolean flag to specify if the query was successful
   */
  public boolean executeQuery(String query)
  {
    boolean retVal = true;
    if (connection == null)
    {
      System.err.println("There is no database to execute the query.");
      return false;
    }
    if (statement == null)
    {
      System.err.println("The query string that was passed to the JDBCAdapter was null.");
      return false;
    }
    if (getErrorCondition())
    {
      return false;
    }

    try
    {
      resultSet = statement.executeQuery(query);
      metaData = resultSet.getMetaData();

      int numberOfColumns = metaData.getColumnCount();
      columnNames = new String[numberOfColumns];
      // Get the column names and cache them.
      // Then we can close the connection.
//            System.err.println("!!Debug!! Columns Retrieved");
      for (int column = 0; column < numberOfColumns; column++)
      {
        columnNames[column] = metaData.getColumnLabel(column + 1);
//              System.err.println(columnNames[column]);
      }

      // Get all rows.
      rows = new Vector();
      while (resultSet.next())
      {
        Vector newRow = new Vector();
        for (int i = 1; i <= getColumnCount(); i++)
        {
          newRow.addElement(resultSet.getObject(i));
        }
        rows.addElement(newRow);
      }
      //  close(); Need to copy the metaData, bug in jdbc:odbc driver.
      fireTableChanged(null); // Tell the listeners a new table has arrived.
    }
    catch (SQLException ex)
    {
      retVal = false;
      setErrorCondition(true);
      System.err.println("JDBCAdapter: SQL Error");
      System.err.println(ex);
    }
    return retVal;
  }


  /**
   * This method performs some clean up for the class. If there is still JDBC objects
   * open... it closes them so we can efinish up with this class cleanly.
   */
  public void close()
  {
    System.out.println("Closing db connection");
    try
    {
      if (resultSet != null)
      {
        resultSet.close();
      }
      if (statement != null)
      {
        statement.close();
      }
      if (connection != null)
      {
        connection.close();
      }
    }
    catch (Exception ex)
    {
      System.out.println("Can't Close db Connection exception caught");
    }
  }


  /**
   * Provides a method to get the Object data into a String form so it can be put
   * into the DB. It returns the specified Object in the form of a String.
   *
   * @param column  Description of Parameter
   * @param value   Description of Parameter
   * @return        string the String representation of the data Object you supplied
   */
  public String dbRepresentation(int column, Object value)
  {
    int type;

    if (value == null)
    {
      return "null";
    }

    try
    {
      type = metaData.getColumnType(column + 1);
    }
    catch (SQLException e)
    {
      return value.toString();
    }

    switch (type)
    {
      case Types.INTEGER:
      case Types.DOUBLE:
      case Types.FLOAT:
        return value.toString();
      case Types.BIT:
        return ((Boolean) value).booleanValue() ? "1" : "0";
      case Types.DATE:
        return value.toString(); // This will need some conversion.
      default:
        return "\"" + value.toString() + "\"";
    }

  }


  /**
   * Get method for this field
   *
   * @return   The Rows value
   */
  protected Vector getRows()
  {
    return rows;
  }


  /**
   * This method is here to ensure that we exit cleanly from the JDBC connections
   * that might have been made. It simply calls the close() method.
   *
   * @exception Throwable  Description of Exception
   */
  protected void finalize() throws Throwable
  {
    close();
    //   super.finalize();
  }


  /**
   * Get method for this field
   *
   * @return   Description of the Returned Value
   */
  private String getdbVendor()
  {
    return dbVendor;
  }


  /**
   * Get method for this field
   *
   * @return   Description of the Returned Value
   */
  private String getdbName()
  {
    return dbName;
  }


  /**
   * Get method for this field
   *
   * @return   Description of the Returned Value
   */
  private String getdbUsername()
  {
    return dbUsername;
  }


  /**
   * Get method for this field
   *
   * @return   Description of the Returned Value
   */
  private String getdbPassword()
  {
    return dbPassword;
  }


  /**
   * Get method for this field
   *
   * @return   Description of the Returned Value
   */
  private String getdbServer()
  {
    return dbServer;
  }


  /**
   * Get method for this field
   *
   * @return   Description of the Returned Value
   */
  private String getdbPort()
  {
    return dbPort;
  }
}