1  import javafx.application.Application;
  2  import javafx.scene.Scene;
  3  import javafx.scene.control.Button;
  4  import javafx.scene.control.Label;
  5  import javafx.scene.control.TextField;
  6  import javafx.scene.layout.HBox;
  7  import javafx.scene.layout.VBox;
  8  import javafx.stage.Stage;
  9  import java.sql.*;
 10  
 11  public class FindGradeUsingPreparedStatement extends Application {
 12    // PreparedStatement for executing queries
 13    private PreparedStatement preparedStatement;
 14    private TextField tfSSN = new TextField();
 15    private TextField tfCourseId = new TextField();
 16    private Label lblStatus = new Label();
 17    
 18    @Override // Override the start method in the Application class
 19    public void start(Stage primaryStage) {
 20      // Initialize database connection and create a Statement object
 21      initializeDB();
 22  
 23      Button btShowGrade = new Button("Show Grade");
 24      HBox hBox = new HBox(5);
 25      hBox.getChildren().addAll(new Label("SSN"), tfSSN, 
 26        new Label("Course ID"), tfCourseId, (btShowGrade));
 27  
 28      VBox vBox = new VBox(10);
 29      vBox.getChildren().addAll(hBox, lblStatus);
 30      
 31      tfSSN.setPrefColumnCount(6);
 32      tfCourseId.setPrefColumnCount(6);
 33      btShowGrade.setOnAction(e -> showGrade());
 34      
 35      // Create a scene and place it in the stage
 36      Scene scene = new Scene(vBox, 420, 80);
 37      primaryStage.setTitle("FindGrade"); // Set the stage title
 38      primaryStage.setScene(scene); // Place the scene in the stage
 39      primaryStage.show(); // Display the stage   
 40    }
 41  
 42    private void initializeDB() {
 43      try {
 44        // Load the JDBC driver
 45        Class.forName("com.mysql.jdbc.Driver");
 46  //      Class.forName("oracle.jdbc.driver.OracleDriver");
 47        System.out.println("Driver loaded");
 48  
 49        // Establish a connection
 50        Connection connection = DriverManager.getConnection
 51          ("jdbc:mysql://localhost/javabook", "scott", "tiger");
 52  //    ("jdbc:oracle:thin:@liang.armstrong.edu:1521:orcl",
 53  //     "scott", "tiger");
 54        System.out.println("Database connected");
 55  
 56        String queryString = "select firstName, mi, " +
 57          "lastName, title, grade from Student, Enrollment, Course " +
 58          "where Student.ssn = ? and Enrollment.courseId = ? " +
 59          "and Enrollment.courseId = Course.courseId";
 60  
 61        // Create a statement
 62        preparedStatement = connection.prepareStatement(queryString);
 63      }
 64      catch (Exception ex) {
 65        ex.printStackTrace();
 66      }
 67    }
 68  
 69    private void showGrade() {
 70      String ssn = tfSSN.getText();
 71      String courseId = tfCourseId.getText();
 72      try {
 73        preparedStatement.setString(1, ssn);
 74        preparedStatement.setString(2, courseId);
 75        ResultSet rset = preparedStatement.executeQuery();
 76  
 77        if (rset.next()) {
 78          String lastName = rset.getString(1);
 79          String mi = rset.getString(2);
 80          String firstName = rset.getString(3);
 81          String title = rset.getString(4);
 82          String grade = rset.getString(5);
 83  
 84          // Display result in a label
 85          lblStatus.setText(firstName + " " + mi +
 86            " " + lastName + "'s grade on course " + title + " is " +
 87            grade);
 88        } else {
 89          lblStatus.setText("Not found");
 90        }
 91      }
 92      catch (SQLException ex) {
 93        ex.printStackTrace();
 94      }
 95    }
 96  
 97    /**
 98     * The main method is only needed for the IDE with limited
 99     * JavaFX support. Not needed for running from the command line.
100     */
101    public static void main(String[] args) {
102      launch(args);
103    }
104  }