//DBExample2.java
// Written by Chuck Cusack, Feb 2002.
// A simple applet that connects to a MySQL database.
//

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

public class DBExample2 extends JApplet implements ActionListener {
    Connection con;
    Statement stmt;
    JTextArea Query=new JTextArea(12,60);

    JLabel LowScoreL=new JLabel("Score at least");
    JTextField LowScoreF=new JTextField();
    Box LSBox=Box.createHorizontalBox();

    JLabel HighScoreL=new JLabel("Score at most");
    JTextField HighScoreF=new JTextField();
    Box HSBox=Box.createHorizontalBox();

    JLabel BowlerNameL=new JLabel("Name contains");
    JTextField BowlerNameF=new JTextField();
    Box BNBox=Box.createHorizontalBox();
  
    JButton doIt=new JButton("Search");

    JLabel Rows=new JLabel("Rows:           ");

    BowlingModel BM=new BowlingModel();
    JTable theResults=new JTable(BM);
    JScrollPane TablePane=new JScrollPane(theResults);

    //----------------------------------------------------------------------
    // Initialize the applet
    //
    public void init() {
    // Draw the stuff on the applet.

    Query.setBorder(new TitledBorder("The SQL Query"));
    LSBox.add(LowScoreL);
    LSBox.add(Box.createHorizontalStrut(20));
    LSBox.add(LowScoreF);

    HSBox.add(HighScoreL);
    HSBox.add(Box.createHorizontalStrut(20));
    HSBox.add(HighScoreF);

    BNBox.add(BowlerNameL);
    BNBox.add(Box.createHorizontalStrut(15));
    BNBox.add(BowlerNameF);

    Box SearchBox=Box.createVerticalBox();
    SearchBox.add(LSBox);
    SearchBox.add(HSBox);
    SearchBox.add(BNBox);

    Box BottomBox=Box.createHorizontalBox();
    BottomBox.add(Box.createHorizontalStrut(50));
    BottomBox.add(SearchBox);
    BottomBox.add(Box.createHorizontalStrut(100));
    BottomBox.add(doIt);
    BottomBox.add(Box.createHorizontalStrut(50));
    BottomBox.add(Rows);
    BottomBox.add(Box.createHorizontalStrut(40));

    this.getContentPane().setLayout(new BorderLayout());
    this.getContentPane().add(BottomBox,BorderLayout.NORTH);
    this.getContentPane().add(Query,BorderLayout.SOUTH);
    this.getContentPane().add(TablePane,BorderLayout.CENTER);
    this.validate(); 
    
    doIt.addActionListener(this);

    }
    public void start() {
        connectDB();
    }
    public void stop() {
        disconnectDB();
    }
    //----------------------------------------------------------------------
    // Make a connection to the database
    //
    public void connectDB() {
        //------------------------------------------------------------------
        // Strings to represent the names of drivers, the URL, etc.
        //
        String sqlDriver="org.gjt.mm.mysql.Driver"; // The MySQL driver
        String url = "jdbc:mysql://csce/cusack2"; // The URL of the database
        String userName="cusack2RO";              // The user name
        String password="egbdf5s";                // The password

        //------------------------------------------------------------------
        // Load the driver for our database--MySQL in this case.
        //
        try { 
            Class.forName(sqlDriver);
        } catch(java.lang.ClassNotFoundException e) {
            System.err.print("ClassNotFoundException: ");
            System.err.println(e.getMessage());
        }

        //------------------------------------------------------------------
        // Establish a connection to the database.
        //
        try {
            con = DriverManager.getConnection(url,userName,password);
        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }

    }
    public void disconnectDB() {
        try {
            con.close();
        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
    }
    
    public void PerformSearch(String Low,String High,String Name) {
        String query=
            "SELECT CONCAT(BowlerFirstName,' ',BowlerLastName) as Name,\n"+
            "RawScore, Match_Games.GameNumber, "+
            "Tournaments.TourneyDate,Tournaments.TourneyLocation \n"+
            "from "+
            "(((Bowlers INNER JOIN Bowler_Scores \n"+
            "ON Bowlers.BowlerID=Bowler_Scores.BowlerID) \n"+
            "INNER JOIN Match_Games \n"+
            "ON Bowler_Scores.MatchID=Match_Games.MatchID "+
            "AND Bowler_Scores.GameNumber=Match_Games.GameNumber) \n"+
            "INNER JOIN Tourney_Matches \n"+
            "ON Match_Games.MatchID=Tourney_Matches.MatchID) \n"+
            "INNER JOIN Tournaments \n"+
            "ON Tourney_Matches.TourneyID=Tournaments.TourneyID \n";
        if(Low.length()==0) 
             Low="0";
        if(High.length()==0) 
             High="300";
        query=query+" WHERE RawScore >= "+Low+" AND RawScore <= "+High+"\n";
        if(Name.length()!=0) {
           query=query+" AND ( BowlerLastName like '%"+Name+"%'";
           query=query+" OR BowlerFirstName like '%"+Name+"%' )";
        }
        Query.setText(query);
        try {
            //----------------------------------------------------------------
            // Create a statement that will result in a scrollable result
            // set that is NOT updatable.
            //
            if(stmt!=null) 
              stmt.close();
            stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                       ResultSet.CONCUR_READ_ONLY);

            //----------------------------------------------------------------
            // Execute the query and return the result set.
            //
	    ResultSet rs = stmt.executeQuery(query);

            // Update the table model with the new result set.
            BM.setResultSet(rs);
            Rows.setText("Rows: "+BM.getRowCount());

        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }

    }

    public void actionPerformed(ActionEvent e) {
          PerformSearch(LowScoreF.getText(),
                        HighScoreF.getText(),
                        BowlerNameF.getText());
    }
}