Java Database

In the Faculty Table SQL data, each row has Faculty members of ssn, firstName, lastName and phone.

111221110 Patty Smith 9129215555

111221111 George Franklin 9129212525

111221112 Jean Yao 9129215556

111221113 Frank Goldman 9129215557

111221114 Steve Templeton 9129215558

111221115 Alex Bedat 9129215559

111221116 Judy Woo 9129215560

111221117 Joe Chang 9129215561

111221118 Francis Chin 9129215562

111221119 Ray Smith 9129215563

In Java class, I made GUI that have two menus: File and Edit.

In the Edit menu, when the user clicks "Search", an Input Dialog pops up, asking a Faculty ssn number. use the ssn the user input (end with %) do a select on the Faculty table in the javabook database to retrieve ssn, first name, last name and phone for all matching ssns. Then put that data into the TextArea. Lastly, set the right hand Label to "Number of faculty = n" where n is the number of lines of data.

Below is my code.

import java.util.Optional; import javafx.geometry.Insets; import javafx.application.Application; import javafx.event.ActionEvent; import javafx.event.EventHandler; import javafx.scene.Scene; import javafx.scene.control.Alert; import javafx.scene.control.Alert.AlertType; import javafx.scene.control.ButtonType; import javafx.scene.control.Label; import javafx.scene.control.Menu; import javafx.scene.control.MenuBar; import javafx.scene.control.MenuItem; import javafx.scene.control.TextArea; import javafx.scene.control.TextInputDialog; import javafx.scene.layout.Background; import javafx.scene.layout.BorderPane; import javafx.scene.layout.VBox; import javafx.stage.Stage; import java.sql.*; import javafx.beans.property.SimpleStringProperty;

public class DisplayFaculty extends Application { private int numFaculty = 0; private String databaseName = null; @Override public void start(Stage primaryStage) { Scene scene = new Scene(new VBox(), 400, 260); BorderPane pane = new BorderPane(); BorderPane pane2 = new BorderPane(); pane.setPadding(new Insets(10, 10, 10, 10)); pane2.setPadding(new Insets(20, 10, 10, 10)); Label cLabel = new Label("Not Connected"); Label nsLabel = new Label("Number of Faculty = " + numFaculty); TextArea tArea = new TextArea(); pane2.setLeft(cLabel); pane2.setRight(nsLabel); pane.setTop(tArea); pane.setBottom(pane2); MenuBar menuBar = new MenuBar(); Menu menuFile = new Menu("File"); MenuItem connect = new MenuItem("Connect"); connect.setOnAction((ActionEvent t) -> { try { initializeDB(cLabel); } catch (Exception ex){ ex.printStackTrace(); } }); MenuItem close = new MenuItem("Close"); close.setOnAction((ActionEvent t) -> { try { userConfirmsClose(cLabel); } catch (Exception ex) { ex.printStackTrace(); } }); MenuItem exit = new MenuItem("Exit"); exit.setOnAction(e -> { System.exit(0); }); menuFile.getItems().addAll(connect, close, exit); Menu menuEdit = new Menu("Edit"); MenuItem search = new MenuItem("Search"); search.setOnAction((ActionEvent t) -> { try{ searchDB(tArea); } catch(Exception ex){ ex.printStackTrace(); } }); MenuItem clear = new MenuItem("Clear"); clear.setOnAction((ActionEvent t) -> { tArea.clear(); numFaculty = 0; nsLabel.setText("Number of Faculty = " + numFaculty); }); menuEdit.getItems().addAll(search, clear); menuBar.getMenus().addAll(menuFile, menuEdit); ((VBox) scene.getRoot()).getChildren().addAll(menuBar, pane); primaryStage.setTitle("Display Faculty"); primaryStage.setScene(scene); primaryStage.show(); } public void initializeDB(Label label) throws SQLException, ClassNotFoundException { TextInputDialog dialog = new TextInputDialog(); dialog.setTitle("Text Input Dialog"); dialog.setHeaderText("Database Input Dialog"); dialog.setContentText("Please enter the database name:"); Optional result = dialog.showAndWait(); try { databaseName = result.get(); Class.forName("com.mysql.jdbc.Driver"); Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/" +result.get()+ "?autoReconnect=true&useSSL=false", "scott", "tiger"); label.setText("Connected to " + databaseName); } catch (Exception ex) { Alert alert = new Alert(Alert.AlertType.ERROR); alert.setTitle("Error Dialog"); alert.setContentText("Database not found"); databaseName = null; alert.showAndWait(); ex.printStackTrace(); } } public static void main(String[] args) { launch(args); } public void userConfirmsClose(Label label){ try { Class.forName("com.mysql.jdbc.Driver"); Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/" +databaseName+ "?autoReconnect=true&useSSL=false", "scott", "tiger"); label.setText("Not connected"); databaseName = null; System.out.print("Connection close"); } catch (Exception ex) { Alert alert = new Alert(Alert.AlertType.ERROR); alert.setTitle("Error"); alert.setContentText("You are not connected"); alert.showAndWait(); ex.printStackTrace(); } } public void searchDB(TextArea tArea) throws SQLException, ClassNotFoundException { if (databaseName != null) { TextInputDialog dialog = new TextInputDialog(); dialog.setTitle("Faculty"); dialog.setHeaderText("Faculty Members"); dialog.setContentText("Please enter Faculty ssn number follwing by %"); Optional search_id = dialog.showAndWait(); try { Class.forName("com.mysql.jdbc.Driver"); Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/" +databaseName+ "?autoReconnect=true&useSSL=false", "scott", "tiger"); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("select * from Faculty where ssn like '" + search_id.get() + "';"); while (resultSet.next()) tArea.setText(resultSet.getString(1) + "\t" + resultSet.getString(2) + "\t" + resultSet.getString(4) + "\t" + resultSet.getString(5)); }catch(Exception ex){ ex.printStackTrace(); } } }

Here is the problem, only the last row of the data prints in the TextArea.

How to print all of the rows of data into TextArea? and How to set the label to "Number of faculty = n" where n is the number of lines of data.