|
|
DBExample2
DBExample2.java
//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());
}
}
|