//DBExample1.java
// Written by Chuck Cusack, Feb 2002.
// Revised Spring 2004
// A simple applet that connects to a MySQL database.
//
// As with much code submitted by my students, this code
// is "self-documenting"
//

import java.sql.*;
import java.awt.*;
import javax.swing.*;

public class DBExample1 extends JApplet {
    public void init() {
        String sqlDriver="org.gjt.mm.mysql.Driver";
        String url = "jdbc:mysql://csce/cusack2";
        String userName="cusack2RO";
        String password="egbdf5s";
        String query="SELECT RoomNumber, "
                + "CONCAT(MarkerBrand,' ',MarkerSize,' ',ColorName,' Marker') "
                + "AS theMarker FROM "
                + "Colors, MarkerTypes,RoomMarkers, ClassRooms "
                + "WHERE Colors.ColorID=MarkerTypes.ColorID "
                + "AND MarkerTypes.MarkerID=RoomMarkers.MarkerID "
                + "AND ClassRooms.RoomID=RoomMarkers.RoomID "
                + "AND RoomNumber='108' ";

        String theResults="";

        Connection con;
        Statement stmt;

        try {
            Class.forName(sqlDriver);

        } catch(java.lang.ClassNotFoundException e) {
            System.err.print("ClassNotFoundException: ");
            System.err.println(e.getMessage());
        }

        try {
            con = DriverManager.getConnection(url,userName,password);
           //con = DriverManager.getConnection(
            //        url+"?user=" + userName +"&password=" + password);
            stmt = con.createStatement();
	    ResultSet rs = stmt.executeQuery(query);
	    while (rs.next()) {
                  //--------------------------------------------------
                  // Get the information about the current student, 
                  // based on the column names.
                  //
                  theResults+=rs.getString("RoomNumber")+" "
                         +rs.getString("theMarker")+"\n";
                  //--------------------------------------------------
                  // Alternatively, use the column numbers:
                  //
                  //theResults=rs.getString(1)+" "
                  //       +rs.getString(2)+"\n"
            }
            stmt.close();
            con.close();
        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            theResults="This applet needs to be on the same machine\n"
                        +" as the MySQL database is on, and apparently\n"
                        +" it is not.  If it was, you would have seen\n"
                        +" a list of addresses here.";
        }

    // Draw the stuff on the applet.
    JLabel title=new JLabel("Markers in Room 108");
    JTextArea addresses=new JTextArea(theResults);

    this.getContentPane().setLayout(new BorderLayout());
    this.getContentPane().add(title,BorderLayout.NORTH);
    this.getContentPane().add(addresses,BorderLayout.CENTER);
    this.validate(); 
    }
}