本文主要向大家介绍了MySQL数据库之利用JavaFX访问MySQL数据库 ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
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() {
@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() {
@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;
}
}
以上就介绍了MySQL的相关知识,希望对MySQL有兴趣的朋友有所帮助。了解更多内容,请关注职坐标数据库MySQL频道!