/**
 * Title:        <p>
 * Description:  <p>
 * Copyright:    Copyright (c) <p>
 * Company:      <p>
 * @author
 * @version 1.0
 */
package telephonebook;

// TelephoneBook.java
// Inserting into, updating and searching through a database
import java.sql.*;
import java.awt.*;
import java.awt.event.*;


public class TelephoneBook extends Frame implements ActionListener {

   private TextArea output, outputStatus;
   private String url;
   private Connection connection;
   Button badd,bfind,bdelete,bupdate,bdisplay;
   TextField tffname,tflname,tftelno;
   Statement statement;
   ResultSet resultSet;


   public TelephoneBook()
   {
      super( "Phone Book Database Application" );



      // Start screen layout
      setLayout(new FlowLayout());

      tflname = new TextField(50);
      tffname = new TextField(50);
      tftelno = new TextField(50);
      output = new TextArea( 20, 60);
      outputStatus = new TextArea( 5, 60);
      badd =    new Button("ADD      ");
      bdelete = new Button("DELETE   ");
      bupdate = new Button("UPDATE   ");
      bfind =   new Button("FIND     ");
      bdisplay =   new Button("DISPLAY  ");
      add(new Label("Last Name: "));
      add(tflname);
      add(new Label("First Name: "));
      add(tffname);
      add(new Label("Tel Number: "));
      add(tftelno);
      add(badd);
      add(bupdate);
      add(bdelete);
      add(bfind);
      add(bdisplay);
      add(output);
      add(outputStatus);
      show();

      badd.addActionListener(this);
      bdisplay.addActionListener(this);
      bupdate.addActionListener(this);
      bfind.addActionListener(this);
      bdelete.addActionListener(this);


      // Set up database connection
     try {
         url = "jdbc:odbc:Telephone";

         Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
         connection = DriverManager.getConnection( url );
         outputStatus.append( "Connection successful\n" );
      }
      catch ( ClassNotFoundException cnfex ) {
         // process ClassNotFoundExceptions here
         cnfex.printStackTrace();
         outputStatus.append( "Connection unsuccessful\n" +
                        cnfex.toString() );
      }
      catch ( SQLException sqlex ) {
         // process SQLExceptions here
         sqlex.printStackTrace();
        outputStatus.append( "Connection unsuccessful\n" +
                        sqlex.toString() );
      }
      catch ( Exception ex ) {
         // process remaining Exceptions here
         ex.printStackTrace();
        outputStatus.append( ex.toString() );
      }

      setSize( 500, 600 );
      show();
      displayData();
   }
   public void actionPerformed(ActionEvent e)
   {
   Button b = (Button)e.getSource();
   if (b.equals(badd))
     processAdd();
   if (b.equals(bupdate))
     processUpdate();
    if (b.equals(bfind))
     processFind();
   if (b.equals(bdisplay))
     displayData();
   if (b.equals(bdelete))
     processDelete();
   }

   public void processAdd( )
   {
      try {
          statement = connection.createStatement();

         if ( !tflname.getText().equals( "" ) &&
              !tffname.getText().equals( "" ) ) {
            String query = "INSERT INTO Phone(" +
               "firstname, lastname, telephonenumber" + ") VALUES ('" +
               tflname.getText() + "', '" +
               tffname.getText() + "', '" +
               tftelno.getText() +  "')";
            outputStatus.append( "\nSending query: " +
                           connection.nativeSQL( query )
                           + "\n" );
            int result = statement.executeUpdate( query );

            if ( result == 1 )
              {
               outputStatus.append( "\nInsertion successful\n" );
               }
            else {
                outputStatus.append( "\nInsertion failed\n" );

            }
         }
         else
            outputStatus.append( "\nEnter at least first and " +
                           "last name then press ADD\n" );

         statement.close();
      }
      catch ( SQLException sqlex ) {
         sqlex.printStackTrace();
         output.append( sqlex.toString() );
      }
   }


   public void processUpdate( )
   {
     try {
       if ( !tflname.getText().equals( "" ) &&
              !tffname.getText().equals( "" ) &&
              !tftelno.getText().equals( "" ))
          {
            String query = "UPDATE Phone SET " +
                   "firstname='" + tffname.getText() +
                   "', lastname='" + tflname.getText() +
                   "', telephonenumber='" + tftelno.getText() + "' WHERE lastname= '" + tflname.getText()+"'";
                   outputStatus.append( "\nSending query: " +
                   connection.nativeSQL( query ) + "\n" );

            int result = statement.executeUpdate( query );

            if ( result == 1 )
               outputStatus.append( "\nUpdate successful\n" );
            else {
               outputStatus.append( "\nUpdate failed\n" );
               tffname.setText( "" );
               tflname.setText( "" );
               tftelno.setText( "" );
                  }
           }
        else
           outputStatus.append( "\nYou must enter all fields and "  +
                                " You may only update an"  +
                                " existing record. Use FIND to locate" +
                                " the record, then modify the information" +
                                " and press UPDATE\n");
        }
      catch ( SQLException sqlex ) {
         sqlex.printStackTrace();
         output.append( sqlex.toString() );
      }
   }

   public void processDelete( )
   {
      try {
          statement = connection.createStatement();

         if ( !tflname.getText().equals( "" ) &&
              !tffname.getText().equals( "" ) ) {
            String query = "DELETE * FROM Phone" +
                           " WHERE lastname= '" + tflname.getText()+"'";
            outputStatus.append( "\nSending query: " +
                           connection.nativeSQL( query )
                           + "\n" );
            int result = statement.executeUpdate( query );

            if ( result == 1 )
              {
               outputStatus.append( "\nDeletion successful\n" );
               }
            else {
                outputStatus.append( "\nDeletion failed\n" );
                }
         }
         else
            outputStatus.append( "\nEnter the first and " +
                           "last name then press DELETE\n" );
      }
      catch ( SQLException sqlex ) {
         sqlex.printStackTrace();
         output.append( sqlex.toString() );
      }
   }


    public void processFind( )
   {
     try {


        if ( !tflname.getText().equals( "" ))
            {
            statement = connection.createStatement();
            String query = "SELECT * FROM Phone" +
                           " WHERE lastname= '" + tflname.getText()+"'";
                   outputStatus.append( "\nSending query: " +
                   connection.nativeSQL( query ) + "\n" );

            resultSet = statement.executeQuery( query );

            displayFind(resultSet);
            outputStatus.append( "\nQuery successful\n" );

         }
         else
            outputStatus.append( "\nEnter last name and then press FIND\n" );
      }
      catch ( SQLException sqlex ) {
         sqlex.printStackTrace();
         output.append( sqlex.toString() );
      }
   }

  private void displayData()
   {
      try {
         String query = "SELECT * FROM Phone";

         statement = connection.createStatement();
         resultSet = statement.executeQuery( query );
         displayResultSet( resultSet );

      }
      catch ( SQLException sqlex ) {
         sqlex.printStackTrace();
      }
   }

   private void displayResultSet( ResultSet rs )
      throws SQLException
   {


      // position to first record
      boolean moreRecords = rs.next();

      // If there are no records, display a message
      if ( ! moreRecords ) {
         outputStatus.append(" ");
         outputStatus.append( "Telephone list contains no records" );
         return;
      }

      try {
         output.setText(" ");
         // get column heads
         ResultSetMetaData rsmd = rs.getMetaData();

         for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
            output.append(rsmd.getColumnName( i ) +  '\t');

            output.append("\n\n");

         // get row data
         do {
           output.append( getNextRow( rs, rsmd ) +  '\n');

         } while ( rs.next() );

         // display table with ResultSet contents
         output.toString();
         tflname.setText( "" );
         tffname.setText( "" );
         tftelno.setText( "" );
      }
      catch ( SQLException sqlex ) {
         sqlex.printStackTrace();
      }
   }
   private void displayFind(ResultSet rs)
   {
      try {
        rs.next();
         tflname.setText( rs.getString(1));
         tffname.setText(rs.getString(2));
         tftelno.setText( rs.getString(3));
          }
      catch ( SQLException sqlex ) {
         sqlex.printStackTrace();
         outputStatus.append(sqlex.toString());
      }
   }


   private String getNextRow( ResultSet rs,
                              ResultSetMetaData rsmd )
       throws SQLException
   {
      String currentRow = "";

      for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
         switch( rsmd.getColumnType( i ) ) {
            case Types.VARCHAR:
                  currentRow = currentRow + rs.getString( i )+ '\t';
               break;
            case Types.INTEGER:
                  currentRow = currentRow + new Long( rs.getLong(i))+'\t';
               break;
            default:
               System.out.println( "Type was: " +
                  rsmd.getColumnTypeName( i ) );
         }

    return currentRow;
   }




   public static void main( String args[] )
   {
   TelephoneBook app = new TelephoneBook();

      app.addWindowListener(
         new WindowAdapter() {
            public void windowClosing( WindowEvent e )
            {
               System.exit( 0 );
            }
         }
      );
   }
}