// JdbcApplet.java is an Applet implementation of JdbcProgram.java

import java.awt.*;
import java.applet.*;
import java.sql.*;

public class JdbcApplet extends Applet
{
  List gui;
  
  Connection con;
  Statement st;
  ResultSet rs;
  
  public void init()
  {
    // prepare the GUI component
    
    gui = new List();
    setLayout(new BorderLayout());
    add(gui, "Center");

    try {

      System.out.println("loading JDBC driver");
    
      // load the appropriate database driver
    
      Class.forName("com.hp.jdbc.allbase.JdbcDriver");

      // on a PC you might also use another driver
      // Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      // which provides access to the JDBC-ODBC bridge
      // quite handy for local PC prototyping...

      System.out.println("connecting to database");
    
      // establish connection to database on server

      con = DriverManager.getConnection(
        "jdbc:allbase://your3k/musicdbe.grp.acct", "user.acct", "upass,apass"
        // or with JdbcOdbc bridge "jdbc:odbc:YourDSN", "NoUser", "NoPass"
      );

    } catch (ClassNotFoundException e) {
      gui.add("Failed to load JDBC driver");
    } catch (SQLException e) {
      gui.add("Failed to connect to database");
    }
  }

  public void start()
  {
    try {
    
      System.out.println("retrieving data via JDBC");
    
      // create an SQL statement to be executed

      st = con.createStatement();

      // send a query to the database server

      rs = st.executeQuery(
        "select composername,birth,death from music.composers"
      );

      // retrieve the resulting entries for displaying in GUI component

      while (rs.next()) {
        gui.add(
          rs.getString("composername")
          + ", "
          + rs.getString("birth")
          + " - "
          + rs.getString("death")
        );
      }

    } catch (NullPointerException e) {
      gui.add("Failed to create and execute SQL statement");
    } catch (SQLException e) {
      gui.add("Failed to create and execute SQL statement");
    }
  }  

  // public void paint(Graphics g)
  // {
  //   nothing to customize today
  // }  

  public void stop()
  {
    try {
    
      System.out.println("releasing query resources");
    
      // close resources aquired

      rs.close();
      st.close();

    } catch (NullPointerException e) {
      // ignore for now
    } catch (SQLException e) {
      // ignore for now
    }

    // BUG: missing call to gui.removeAll() here

    // this results in SQL query results adding up in the list box
    // every time the applet start/stop cycle is executed, for example
    // by using web browser back/forward buttons to revisit the page

    // here it makes a nice demo that SQL queries *are* repeated ;-)
  }  

  public void destroy()
  {
    try {
    
      System.out.println("disconnecting from database");
    
      // release the database connection

      con.close();

    } catch (NullPointerException e) {
      // ignore for now
    } catch (SQLException e) {
      // ignore for now
    }
  }  

}