package JavaDatabaseBible.part2;

import java.awt.event.*;
import java.sql.*; 
import java.util.Vector; 
import java.util.Hashtable;
import sun.jdbc.odbc.JdbcOdbcDriver; 

public class DatabaseUtilities{
  private String dbName  = "SQLServerContacts";
  private String url = "jdbc:odbc:SQLServerContacts";
  private String userName = "dbo";
  private String password = "sa";
  private String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
  
  public DatabaseUtilities(){
  }
  public void setDatabaseName(String dbName){
    this.dbName=dbName;
  }
  public void setDatabaseUrl(String url){
    this.url=url;
  }
  public void setJdbcDriverName(String jdbcDriver){
    this.jdbcDriver=jdbcDriver;
    try{
      Class.forName(jdbcDriver);
    }
    catch(ClassNotFoundException e){
      reportException(e);
    }
  }
  public void setUserName(String userName){
    this.userName=userName;
  }
  public void setPassword(String password){
    this.password=password;
  }
  public boolean connectToDatabase(String dbName){
    boolean connected = false;
    this.dbName=dbName;
    Connection con = null;
    try{
      con = DriverManager.getConnection(url,userName,password);
      if(con!=null)connected=true;
    }
    catch(SQLException e){
      reportException(e);
    }
    finally{
      try{
        con.close();
      }catch(SQLException e){
        reportException(e);
      }
    }
    return connected;
  }
  public Vector getTables(String[] types){
    Vector tableVector = new Vector();
    Connection con = null;
    try{
      con = DriverManager.getConnection(url,userName,password);
      DatabaseMetaData dbmd = con.getMetaData();
      ResultSet rs = dbmd.getTables(null,null,"%",types);
      ResultSetMetaData md = rs.getMetaData();
      int nColumns = md.getColumnCount();
      while(rs.next()){
        tableVector.addElement(rs.getString("TABLE_NAME"));
      }
    }
    catch(SQLException e){
      reportException(e);
    }
    finally{
      try{
        con.close();
      }catch(SQLException e){
        reportException(e);
      }
    }
    return tableVector;
  }
  public Vector getTableTypes(){
    Vector typeVector = new Vector();
    Connection con = null;
    try{
      con = DriverManager.getConnection(url,userName,password);
      DatabaseMetaData dbmd = con.getMetaData();
      ResultSet rs = dbmd.getTableTypes();
      ResultSetMetaData md = rs.getMetaData();
      int nColumns = md.getColumnCount();
      while(rs.next()){
        typeVector.addElement(rs.getString(1));
      }
    }
    catch(SQLException e){
      reportException(e);
    }
    finally{
      try{
        con.close();
      }catch(SQLException e){
        reportException(e);
      }
    }
    return typeVector;
  }
  public Vector getColumns(String tableName){
    Vector columns = new Vector();
    Hashtable columnData;
    Connection con = null;
    try{
      con = DriverManager.getConnection(url,userName,password);
      DatabaseMetaData dbmd = con.getMetaData();
      String catalog = con.getCatalog();
      ResultSet rs = dbmd.getColumns(catalog,"%",tableName,"%");
      ResultSetMetaData md = rs.getMetaData();
      int nColumns = md.getColumnCount();
      String value;
      while(rs.next()){
        columnData = new Hashtable();
        for(int i=1;i<=nColumns;i++){
          value = rs.getString(i);
          if(value==null)value="<NULL>";
          columnData.put(md.getColumnLabel(i),value);
        } 
        columns.addElement(columnData);
      }
    }
    catch(SQLException e){
      reportException(e);
    }
    finally{
      try{
        con.close();
      }catch(SQLException e){
        reportException(e);
      }
    }
    return columns;
  }
  public String[] isolationLevelsSupported(){
    String[] isolationLevels = {null,null,null,null};
    Connection con = null;
    try{
      con = DriverManager.getConnection(url,userName,password);
      DatabaseMetaData dbmd = con.getMetaData();
      if(dbmd.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED))isolationLevels[0]="READ_COMMITTED";
      if(dbmd.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED ))isolationLevels[1]="READ_UNCOMMITTED ";
      if(dbmd.supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ))isolationLevels[2]="REPEATABLE_READ";
      if(dbmd.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE ))isolationLevels[3]="SERIALIZABLE ";
    }
    catch(SQLException e){
      reportException(e);
    }
    finally{
      try{
        con.close();
      }catch(SQLException e){
        reportException(e);
      }
    }
    return isolationLevels;
  }
  public String[] databaseInfo(){
    String[] dbInfo = {null,null,null,null};
    Connection con = null;
    try{
      con = DriverManager.getConnection(url,userName,password);
      DatabaseMetaData dbmd = con.getMetaData();
      dbInfo[0]=dbmd.getDatabaseProductName();
      dbInfo[1]=dbmd.getDatabaseProductVersion();
      dbInfo[2]=dbmd.getDriverName();
      dbInfo[3]=dbmd.getDriverVersion();
    }
    catch(SQLException e){
      reportException(e);
    }
    finally{
      try{
        con.close();
      }catch(SQLException e){
        reportException(e);
      }
    }
    return dbInfo;
  }
  public String[] featuresSupported(){
    String[] features = {null,null,null,null};
    Connection con = null;
    try{
      con = DriverManager.getConnection(url,userName,password);
      DatabaseMetaData dbmd = con.getMetaData();
      if(dbmd.supportsANSI92EntryLevelSQL())features[0]="ANSI92 Entry Level SQL";
      if(dbmd.supportsBatchUpdates())features[1]="Batch Updates";
      if(dbmd.supportsStoredProcedures())features[2]="Stored Procedures";
      if(dbmd.supportsTransactions())features[3]="Transactions";
    }
    catch(SQLException e){
      reportException(e);
    }
    finally{
      try{
        con.close();
      }catch(SQLException e){
        reportException(e);
      }
    }
    return features;
  }
  public void update(String SQLCommand){
    Connection con = null;
    try{
      con = DriverManager.getConnection(url,userName,password);
      Statement stmt = con.createStatement();
      stmt.executeUpdate(SQLCommand);
    }
    catch(SQLException e){
      reportException(e);
    }
    finally{
      try{
        con.close();
      }catch(SQLException e){
        reportException(e);
      }
    }
  }
  public void update(String[] SQLCommand){
    Connection con = null;
    try{
      con = DriverManager.getConnection(url,userName,password);
      Statement stmt = con.createStatement();
      for(int i=0;i<SQLCommand.length;i++){
        stmt.executeUpdate(SQLCommand[i]);
      }
    }
    catch(SQLException e){
      reportException(e);
    }
    finally{
      try{
        con.close();
      }catch(SQLException e){
        reportException(e);
      }
    }
  }
  public String[] getColumnNames(String tableName){
    Vector dataSet = new Vector();
    String[] columnNames = null;
    String SQLCommand = "SELECT * FROM "+tableName+";";

    Connection con = null;
    try{
      con = DriverManager.getConnection(url,userName,password);
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(SQLCommand);
      ResultSetMetaData md = rs.getMetaData();
      
      columnNames = new String[md.getColumnCount()];
      for(int i=0;i<columnNames.length;i++){
        columnNames[i] = md.getColumnLabel(i+1);
      }  
    }
    catch(SQLException e){
      reportException(e);
    }
    finally{
      try{
        con.close();
      }catch(SQLException e){
        reportException(e);
      }
    }
    return columnNames;
  }
  public String[] getColumnNamesUsingQuery(String SQLCommand){
    Vector dataSet = new Vector();
    String[] columnNames = null;

    Connection con = null;
    try{
      con = DriverManager.getConnection(url,userName,password);
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(SQLCommand);
      ResultSetMetaData md = rs.getMetaData();
      
      columnNames = new String[md.getColumnCount()];
      for(int i=0;i<columnNames.length;i++){
        columnNames[i] = md.getColumnLabel(i+1);
      }  
    }
    catch(SQLException e){
      reportException(e);
    }
    finally{
      try{
        con.close();
      }catch(SQLException e){
        reportException(e);
      }
    }
    return columnNames;
  }
  public String[] getDataTypes(String tableName){
    Vector dataSet = new Vector();
    String[] dataTypes = null;
    String SQLCommand = "SELECT * FROM "+tableName+";";
    
    Connection con = null;
    try{
      con = DriverManager.getConnection(url,userName,password);
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(SQLCommand);
      ResultSetMetaData md = rs.getMetaData();
      
      dataTypes = new String[md.getColumnCount()];
      for(int i=0;i<dataTypes.length;i++){
        dataTypes[i] = md.getColumnTypeName(i+1);
      }  
    }
    catch(SQLException e){
      reportException(e);
    }
    finally{
      try{
        con.close();
      }catch(SQLException e){
        reportException(e);
      }
    }
    return dataTypes;
  }
  public Vector executeQuery(String SQLQuery){
    Vector dataSet = new Vector();
    
    Connection con = null;
    try{
      con = DriverManager.getConnection(url,userName,password);
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(SQLQuery);
      ResultSetMetaData md = rs.getMetaData();
      
      int nColumns = md.getColumnCount();
      while(rs.next()){
        Vector rowData = new Vector();
        for(int i=1;i<=nColumns;i++){
          Object o = rs.getObject(i);
          if(o!=null)
            rowData.addElement(o.toString()); 
          else
            rowData.addElement("<NULL>"); 
          
        }  
        dataSet.addElement(rowData);
      }
    }
    catch(SQLException e){
      reportException(e);
    }
    finally{
      try{
        con.close();
      }catch(SQLException e){
        reportException(e);
      }
    }
    return dataSet;
  }
  private void reportException(Exception e){
    System.err.println(e.getMessage());
  }
}