Access MySQL database with JavaFX

1. Create a database table

CREATE TABLE Course (
CourseID char (5),
Subjectid char (4) NOT NULL,
Coursenumber Integer,
Title varchar (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. Create a JavaFX project

Package application;

Importjava.sql.Connection;ImportJava.sql.DriverManager;ImportJava.sql.ResultSet;Importjava.sql.SQLException;Importjava.sql.Statement;Importjavafx.application.Application;Importjavafx.event.ActionEvent;ImportJavafx.event.EventHandler;ImportJavafx.scene.Scene;ImportJavafx.scene.control.Button;ImportJavafx.scene.control.Label;ImportJavafx.scene.control.TextField;ImportJavafx.scene.layout.HBox;ImportJavafx.scene.layout.VBox;ImportJavafx.stage.Stage; Public classMainextendsApplication {PrivateStatement stmt; PrivateTextField TFSSN =NewTextField ();PrivateTextField Tfcourseid =NewTextField ();PrivateLabel lblstatus =NewLabel ();//Store Query Results@Override Public voidStart (Stage primarystage) {Try{initializedb (); Button Bsshowgrade=NewButton ("Show Grade"); HBox HBox =NewHBox (5); Hbox.getchildren (). AddAll (NewLabel ("SSN"), TFSSN,NewLabel ("Course Id"), Tfcourseid, Bsshowgrade); VBox VBox=NewVBox (10);Vbox.getchildren (). AddAll (HBox, lblstatus); Tfssn.setprefcolumncount (6); Tfcourseid.setprefcolumncount (6); Bsshowgrade.setonaction (NewEventhandler<actionevent>() {@Override Public voidhandle (ActionEvent arg0) {//TODO auto-generated Method StubString ssn = Tfssn.gettext ();//get an input ssnString 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 = ' one ' and enrollment.ssn = STUDENT.SS N; ResultSet RSet= Stmt.executequery (queryString);//querying the database and returning query results

if(Rset.next ()) {//Show Query ResultsString 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=NewScene (VBox, 420, 80); Primarystage.settitle ("Findgrade");            Primarystage.setscene (Scene);        Primarystage.show (); } Catch(Exception e) {e.printstacktrace (); }    }     Public Static voidMain (string[] args) {launch (args); }    Private voidInitializedb () {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 (); }    }    }

Run Result: Enter SSN and CourseID to display the results of the query.

Another 1: Install JavaFX in eclipse

Click Help->install New Software in Eclipse and set the following in the popup window:

Click Add after the work with box, and in the popup window, set the following: Name:ex (FX) lipse,location:http://download.eclipse.org/efxclipse/updates-released /2.3.0/site/, click OK. After setting up the above, you will find two plugins check boxes: E (FX) Clipse–install installation and E (FX) Clipse–single components, select both checkboxes to complete the installation.

Another 2: Start the program has been error:not unique Table/alias: ' student ', online query is because the same name as the MySQL keyword, but there is no name of the field, and finally found that the SQL statement when the line is missing a space, Therefore, be aware of the whitespace in which the SQL statement wraps.

3. Using PreparedStatement to create parameters and SQL statements

 PackageApplication;ImportJava.sql.*;Importjavafx.application.Application;Importjavafx.event.ActionEvent;ImportJavafx.event.EventHandler;ImportJavafx.scene.Scene;ImportJavafx.scene.control.Button;ImportJavafx.scene.control.Label;ImportJavafx.scene.control.TextField;ImportJavafx.scene.layout.HBox;ImportJavafx.scene.layout.VBox;ImportJavafx.stage.Stage; Public classFindgradeusingpreparedstatementextendsapplication{PrivatePreparedStatement PreparedStatement; PrivateStatement stmt; PrivateTextField TFSSN =NewTextField ();PrivateTextField Tfcourseid =NewTextField ();PrivateLabel lblstatus =NewLabel ();//Store Query Results@Override Public voidStart (Stage primarystage) {Try{Connection conn=Initializedb (); Button Bsshowgrade=NewButton ("Show Grade"); HBox HBox =NewHBox (5); Hbox.getchildren (). AddAll (NewLabel ("SSN"), TFSSN,NewLabel ("Course Id"), Tfcourseid, Bsshowgrade); VBox VBox=NewVBox (10);Vbox.getchildren (). AddAll (HBox, lblstatus); Tfssn.setprefcolumncount (6); Tfcourseid.setprefcolumncount (6); Bsshowgrade.setonaction (NewEventhandler<actionevent>() {@Override Public voidhandle (ActionEvent arg0) {//TODO auto-generated Method StubString ssn = Tfssn.gettext (); String CourseID =Tfcourseid.gettext (); Try {String queryString= "Select FirstName, MI, lastName, title, grade from Student, enrollment, Course" + "whe Re student.ssn =? and Enrollment.courseid "+" =? and Enrollment.courseid = Course.courseid "+" and enrollment.ssn = STUDENT.SSN ";//question mark as a placeholder for a parameter PreparedStatement=conn.preparestatement (queryString);preparedstatement.setstring (1, SSN);//Set the parameter value at the placeholder preparedstatement.setstring (2, CourseID); ResultSet RSet=Preparedstatement.executequery ();//Execute Query statement

if(Rset.next ()) {//Show Query ResultsString 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=NewScene (VBox, 420, 80); Primarystage.settitle ("Findgrade");            Primarystage.setscene (Scene);        Primarystage.show (); } Catch(Exception e) {e.printstacktrace (); }    }     Public Static voidMain (string[] args) {//TODO auto-generated Method Stublaunch (args); }    PrivateConnection 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 (); }        returnConn; }}

Reference: Java language Programming Advanced Edition

Access MySQL database with JavaFX