利用JavaFX訪問MySQL資料庫

標籤：work   and   rest   clip   pack   connect   art   count   關鍵字   

1. 建立資料庫表

create table Course(
courseId char(5),
subjectId char(4) not null,
courseNumber integer,
title varchar(50) not null,
numOfCredits integer,
primary key (courseId)
);

create table Student(
ssn char(9),
firstName varchar(25),
mi char(1),
lastName varchar(25),
birthDate date,
street varchar(25),
phone char(11),
zipCode char(5),
deptId char(4),
primary key (ssn)
);

create table Enrollment(
ssn char(9),
courseId char(15),
dateRegistered date,
grade char(1),
primary key (ssn, courseId),
foreign key (ssn) references Student(ssn),
foreign key (courseId) references Course(courseId)
);

2. 建立JavaFX項目

package application;

import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import javafx.application.Application;import javafx.event.ActionEvent;import javafx.event.EventHandler;import javafx.scene.Scene;import javafx.scene.control.Button;import javafx.scene.control.Label;import javafx.scene.control.TextField;import javafx.scene.layout.HBox;import javafx.scene.layout.VBox;import javafx.stage.Stage;public class Main extends Application {    private Statement stmt;    private TextField tfSSN = new TextField();     private TextField tfCourseId = new TextField();    private Label lblStatus = new Label();// 存放查詢結果    @Override    public void start(Stage primaryStage) {        try {            initializeDB();            Button bsShowGrade = new Button("Show Grade");            HBox hBox = new HBox(5);            hBox.getChildren().addAll(new Label("SSN"), tfSSN, new Label("Course Id"), tfCourseId, bsShowGrade);            VBox vBox = new VBox(10);            vBox.getChildren().addAll(hBox, lblStatus);            tfSSN.setPrefColumnCount(6);            tfCourseId.setPrefColumnCount(6);            bsShowGrade.setOnAction(new EventHandler<ActionEvent>() {                @Override                public void handle(ActionEvent arg0) {                    // TODO Auto-generated method stub                    String ssn = tfSSN.getText();// 擷取輸入的SSN                    String courseId = tfCourseId.getText();                    try {                        String queryString ="select firstName, mi, lastName, title, grade from Student, Enrollment, Course" +                                   " where Student.ssn = ‘"+ssn+"‘ and Enrollment.courseId "                                           + "= ‘"+courseId+"‘ and Enrollment.courseId = Course.courseId"                                           + " and Enrollment.ssn = Student.ssn";                        String queryStrin = "select firstName, mi, lastName, grade from student, Enrollment where Student.ssn = ‘11‘ and Enrollment.ssn = Student.ssn";                        ResultSet rSet = stmt.executeQuery(queryString);// 查詢資料庫，並返回查詢結果

　　　　　　　　　　　　　　　　if (rSet.next()) {// 顯示查詢結果                            String firstName = rSet.getString(1);                            String mi = rSet.getString(2);                            String lastName = rSet.getString(3);                            String title = rSet.getString(4);                            String grade = rSet.getString(5);                            lblStatus.setText(firstName + " " + mi + " " + lastName + " " + title + " " + grade);                        } else {                            lblStatus.setText("Not found");                        }                    } catch (SQLException ex) {                        ex.printStackTrace();                    }                }            });            Scene scene = new Scene(vBox, 420, 80);            primaryStage.setTitle("FindGrade");            primaryStage.setScene(scene);            primaryStage.show();        } catch (Exception e) {            e.printStackTrace();        }    }    public static void main(String[] args) {        launch(args);    }    private void initializeDB() {        try {            Class.forName("com.mysql.jdbc.Driver");            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/dbname", "user", "password");            stmt = conn.createStatement();        } catch (Exception ex) {            ex.printStackTrace();        }    }    }

運行結果：輸入SSN和courseID，顯示查詢結果，。

另1：在eclipse中安裝JavaFX

點擊eclipse中的Help->Install New Software，在彈出的視窗中設定如下：

點擊Work with框後的Add，在彈出的視窗中，設定如下：Name: ex(fx)lipse，Location：http://download.eclipse.org/efxclipse/updates-released/2.3.0/site/ ，點擊OK。上述設定好後，會發現兩個外掛程式的複選框：e(fx)clipse – install 安裝和e(fx)clipse – single components，選中這兩個複選框，完成安裝。

另2：開始程式一直報錯：Not unique table/alias: ‘student‘，網上查詢是因為跟MySQL關鍵字重名，但並沒有重名的欄位，最後發現是SQL語句在換行時少了一個空格，因此要注意SQL語句在換行時的空格情況。

3. 利用PreparedStatement建立參數和的SQL語句

 

package application;import java.sql.*;import javafx.application.Application;import javafx.event.ActionEvent;import javafx.event.EventHandler;import javafx.scene.Scene;import javafx.scene.control.Button;import javafx.scene.control.Label;import javafx.scene.control.TextField;import javafx.scene.layout.HBox;import javafx.scene.layout.VBox;import javafx.stage.Stage;public class FindGradeUsingPreparedStatement extends Application{        private PreparedStatement preparedStatement;    private Statement stmt;    private TextField tfSSN = new TextField();     private TextField tfCourseId = new TextField();    private Label lblStatus = new Label();// 存放查詢結果    @Override    public void start(Stage primaryStage) {        try {            Connection conn = initializeDB();            Button bsShowGrade = new Button("Show Grade");            HBox hBox = new HBox(5);            hBox.getChildren().addAll(new Label("SSN"), tfSSN, new Label("Course Id"), tfCourseId, bsShowGrade);            VBox vBox = new VBox(10);            vBox.getChildren().addAll(hBox, lblStatus);            tfSSN.setPrefColumnCount(6);            tfCourseId.setPrefColumnCount(6);            bsShowGrade.setOnAction(new EventHandler<ActionEvent>() {                @Override                public void handle(ActionEvent arg0) {                    // TODO Auto-generated method stub                    String ssn = tfSSN.getText();                    String courseId = tfCourseId.getText();                    try {                        String queryString ="select firstName, mi, lastName, title, grade from Student, Enrollment, Course" +                                   " where Student.ssn = ? and Enrollment.courseId "                                           + "= ? and Enrollment.courseId = Course.courseId"                                           + " and Enrollment.ssn = Student.ssn";//問號作為參數的預留位置                        preparedStatement = conn.prepareStatement(queryString);                                                preparedStatement.setString(1, ssn);//設定預留位置處的參數值                        preparedStatement.setString(2, courseId);                        ResultSet rSet = preparedStatement.executeQuery();//執行查詢語句

　　　　　　　　　　　　　　　　if (rSet.next()) {// 顯示查詢結果                            String firstName = rSet.getString(1);                            String mi = rSet.getString(2);                            String lastName = rSet.getString(3);                            String title = rSet.getString(4);                            String grade = rSet.getString(5);                            lblStatus.setText(firstName + " " + mi + " " + lastName + "‘s grade on course " + title + " is " + grade);                        } else {                            lblStatus.setText("Not found");                        }                    } catch (SQLException ex) {                        ex.printStackTrace();                    }                }            });            Scene scene = new Scene(vBox, 420, 80);            primaryStage.setTitle("FindGrade");            primaryStage.setScene(scene);            primaryStage.show();        } catch (Exception e) {            e.printStackTrace();        }    }    public static void main(String[] args) {        // TODO Auto-generated method stub        launch(args);    }    private Connection initializeDB() {        Connection conn = null;        try {            Class.forName("com.mysql.jdbc.Driver");            conn = DriverManager.getConnection("jdbc:mysql://localhost/dbname", "user", "password");            System.out.println("Database connected");                                } catch (Exception ex) {            ex.printStackTrace();        }        return conn;    }}

 參考資料：Java語言程式設計 進階版

利用JavaFX訪問MySQL資料庫