Revising a JavaFX SQLClient and making it work with TableView and a method called populateTableView.

For my program, I need to rewrite the listing 35.1 from my textbook, Introduction to Java 11th Ed. and display the query result in a TableView. In my solution, my professor wants me to use a method called populateTableView, which is causing me issues when I try to implement it into my code.

My Code:

package ass7.dbrevisesqlclient;

import java.sql.*; import javafx.application.Application; import javafx.collections.FXCollections; import javafx.geometry.Pos; import javafx.scene.Scene; import javafx.scene.control.Button; import javafx.scene.control.ComboBox; import javafx.scene.control.Label; import javafx.scene.control.PasswordField; import javafx.scene.control.ScrollPane; import javafx.scene.control.TextArea; import javafx.scene.control.TextField; import javafx.scene.control.TableView; import javafx.scene.control.TableColumn; import javafx.beans.property.SimpleStringProperty; import javafx.beans.value.ObservableValue; import javafx.collections.ObservableList; import javafx.scene.control.TableColumn.CellDataFeatures; import javafx.util.Callback; import javafx.scene.layout.BorderPane; import javafx.scene.layout.GridPane; import javafx.scene.layout.HBox; import javafx.scene.layout.VBox; import javafx.stage.Stage;

/** * * @author T */ public class Ass7DBReviseSQLClient extends Application { private Connection connection; //Sets up the connection to connect with the database private Statement statement; //Sets up the statement to execute SQL commands private ResultSet resultSet; private TableView tableView; //Sets up the table and data associated with it private ObservableListinformation; //Sets up the text areas for the SQL command and result private final TextArea taSQLCommand = new TextArea(); private final TextArea taSQLResult = new TextArea(); //Sets the necessary information for the database private final TextField tfUsername = new TextField(); private final PasswordField pfPassword = new PasswordField(); private final ComboBox cbURL = new ComboBox<>(); private final ComboBox cbDriver = new ComboBox<>(); //Creates the nuttons for the program private final Button btExecuteSQLCommand = new Button("Execute SQL Command"); private final Button btClearSQLCommand = new Button("Clear"); private final Button btConnect = new Button("Connection to Database"); //Creates the label status private final Label lblStatus = new Label("No Connection Currently "); public static void main(String[] args) { launch(args); } @Override public void start(Stage primaryStage) { initializeDB(); GridPane gridPane = new GridPane(); gridPane.add(cbURL, 1, 0); gridPane.add(cbDriver, 1, 1); gridPane.add(tfUsername, 1, 2); gridPane.add(pfPassword, 1, 3); gridPane.add(new Label("JDBC Driver: "), 0, 0); //Creates label JDBC Drivers gridPane.add(new Label("Database URL: "), 0, 1); //Creates the label Database URL gridPane.add(new Label("Username: "), 0, 2); //Creates the label Username gridPane.add(new Label("Password: "), 0, 3); //Creates the label Password HBox hBox1 = new HBox(); //HBox for the connection hBox1.getChildren().addAll(lblStatus, btConnect); //Adds the lblstatus and the connect button hBox1.setAlignment(Pos.CENTER_RIGHT); //Sets the alignment of hBox1 to the center right VBox vBox1 = new VBox(6); //VBox for the connection vBox1.getChildren().addAll(new Label ("Enter the Database Information: "), gridPane, hBox1); //Creates a new label and adds the gridPane as well as hBox1 gridPane.setStyle("-fx-border-color:black;"); //Sets a black border around the gridpane HBox hBox2 = new HBox(6); //HBox for the SQL command hBox2.getChildren().addAll(btClearSQLCommand, btExecuteSQLCommand); hBox2.setAlignment(Pos.CENTER_RIGHT); //Sets the alignment to center right BorderPane borderPane1 = new BorderPane(); //SQL Command borderpane borderPane1.setTop(new Label("Please Enter a SQL Command: ")); //Creates new label borderPane1.setCenter(new ScrollPane(taSQLCommand)); //Creates the scrollpane borderPane1.setBottom(hBox2); //Sets hBox2 to the bottom of the borderpane HBox hBox3 = new HBox(12); //HBox for connection command hBox3.getChildren().addAll(vBox1, borderPane1); //Gets vBox1 and borderPane1 BorderPane borderPane2 = new BorderPane(); //BorderPane for execution result borderPane2.setTop(new Label("SQL Execution Result")); //Creates new label borderPane2.setCenter(taSQLResult); //Sets the location to the center BorderPane borderPane3 = new BorderPane(); //Regular BorderPane borderPane3.setTop(hBox3); //Sets the top value for the BorderPane borderPane3.setCenter(borderPane2); //Sets the center value Scene scene = new Scene(borderPane3, 950, 550); //Creates the dimensions primaryStage.setTitle("SQL Client"); //Sets the title of pane primaryStage.setScene(scene); ///Sets the scene primaryStage.show(); btConnect.setOnAction(e -> initializeDB()); btExecuteSQLCommand.setOnAction(e -> executeSQLCommand()); btClearSQLCommand.setOnAction(e -> taSQLCommand.setText(null)); if (borderPane3 == null) { //NOT WORKING borderPane3.setCenter(new Label("No columns in the table")); } else { borderPane3.setCenter(borderPane2); } } public void initializeDB() { try { cbURL.getItems().addAll(FXCollections.observableArrayList ("jdbc:mysql://localhost/test")); cbURL.getSelectionModel().selectFirst(); cbDriver.getItems().addAll(FXCollections.observableArrayList( "com.mysql.jdbc.Driver")); cbDriver.getSelectionModel().selectFirst(); String driver = cbDriver.getSelectionModel().getSelectedItem(); String url = cbURL.getSelectionModel().getSelectedItem(); String username = tfUsername.getText().trim(); String password = pfPassword.getText().trim(); try { Class.forName(driver); connection = DriverManager.getConnection(url, username, password); lblStatus.setText("Connected to " + url); } catch (java.lang.Exception ex) { ex.printStackTrace(); } } catch (Exception ex){ ex.printStackTrace(); } } private void populateTableView(String instruction) { //ResultSet resultSet, TableView tableView tableView.getColumns().clear(); //Empties the columns ObservableListinformation = FXCollections.observableArrayList(); try { statement = connection.createStatement(); tableView = new TableView(); for (int i = 0; i < resultSet.getMetaData().getColumnCount(); i++) { final int j = i; TableColumn tableColumn = new TableColumn( resultSet.getMetaData().getColumnName(i + 1)); tableColumn.setCellValueFactory( new Callback, ObservableValue>() { @Override public ObservableValuecall(CellDataFeatures String> param) { return new SimpleStringProperty(param.getValue().get(j).toString()); } }); tableView.getColumns().addAll(tableColumn); System.out.println("Column["+i+"]"); } while (resultSet.next()) { ObservableListrow = FXCollections.observableArrayList(); for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) { row.add(resultSet.getString(i)); } System.out.println("Row [1] added " + row); information.add(row); } tableView.setItems(information); } catch (Exception e) { e.printStackTrace(); System.out.println("There is an Error in Building Data"); } } private void executeSQLCommand() { if (connection == null) { taSQLResult.setText("Please Connect to a Database First"); } else { String sqlCommand = taSQLCommand.getText().trim(); String[] command = sqlCommand.replace(' ', ' ').split(";"); for (String instruction: command) { if (instruction.trim().toUpperCase().startsWith("SELECT")) { processSQLSelect(instruction); } } } } private void processSQLSelect(String sqlCommand) { try { statement = connection.createStatement(); statement.executeUpdate(sqlCommand); taSQLResult.setText("SQL Command Has Been Executed"); } catch (SQLException ex) { taSQLResult.setText(ex.toString()); } } }

Original Code SQLClient Code:

import java.sql.*; import javafx.application.Application; import javafx.collections.FXCollections; import javafx.geometry.Pos; import javafx.scene.Scene; import javafx.scene.control.Button; import javafx.scene.control.ComboBox; import javafx.scene.control.Label; import javafx.scene.control.PasswordField; import javafx.scene.control.ScrollPane; import javafx.scene.control.TextArea; import javafx.scene.control.TextField; import javafx.scene.layout.BorderPane; import javafx.scene.layout.GridPane; import javafx.scene.layout.HBox; import javafx.scene.layout.VBox; import javafx.stage.Stage;

public class SQLClient extends Application { // Connection to the database private Connection connection;

// Statement to execute SQL commands private Statement statement;

// Text area to enter SQL commands private TextArea tasqlCommand = new TextArea();

// Text area to display results from SQL commands private TextArea taSQLResult = new TextArea();

// DBC info for a database connection private TextField tfUsername = new TextField(); private PasswordField pfPassword = new PasswordField(); private ComboBox cboURL = new ComboBox<>(); private ComboBox cboDriver = new ComboBox<>();

private Button btExecuteSQL = new Button("Execute SQL Command"); private Button btClearSQLCommand = new Button("Clear"); private Button btConnectDB = new Button("Connect to Database"); private Button btClearSQLResult = new Button("Clear Result"); private Label lblConnectionStatus = new Label("No connection now");

@Override // Override the start method in the Application class public void start(Stage primaryStage) { cboURL.getItems().addAll(FXCollections.observableArrayList( "jdbc:mysql://localhost/javabook", "jdbc:mysql://liang.armstrong.edu/javabook", "jdbc:odbc:exampleMDBDataSource", "jdbc:oracle:thin:@liang.armstrong.edu:1521:orcl")); cboURL.getSelectionModel().selectFirst(); cboDriver.getItems().addAll(FXCollections.observableArrayList( "com.mysql.jdbc.Driver", "sun.jdbc.odbc.dbcOdbcDriver", "oracle.jdbc.driver.OracleDriver")); cboDriver.getSelectionModel().selectFirst(); // Create UI for connecting to the database GridPane gridPane = new GridPane(); gridPane.add(cboURL, 1, 0); gridPane.add(cboDriver, 1, 1); gridPane.add(tfUsername, 1, 2); gridPane.add(pfPassword, 1, 3); gridPane.add(new Label("JDBC Driver"), 0, 0); gridPane.add(new Label("Database URL"), 0, 1); gridPane.add(new Label("Username"), 0, 2); gridPane.add(new Label("Password"), 0, 3); HBox hBoxConnection = new HBox(); hBoxConnection.getChildren().addAll( lblConnectionStatus, btConnectDB); hBoxConnection.setAlignment(Pos.CENTER_RIGHT); VBox vBoxConnection = new VBox(5); vBoxConnection.getChildren().addAll( new Label("Enter Database Information"), gridPane, hBoxConnection); gridPane.setStyle("-fx-border-color: black;"); HBox hBoxSQLCommand = new HBox(5); hBoxSQLCommand.getChildren().addAll( btClearSQLCommand, btExecuteSQL); hBoxSQLCommand.setAlignment(Pos.CENTER_RIGHT); BorderPane borderPaneSqlCommand = new BorderPane(); borderPaneSqlCommand.setTop( new Label("Enter an SQL Command")); borderPaneSqlCommand.setCenter( new ScrollPane(tasqlCommand)); borderPaneSqlCommand.setBottom( hBoxSQLCommand); HBox hBoxConnectionCommand = new HBox(10); hBoxConnectionCommand.getChildren().addAll( vBoxConnection, borderPaneSqlCommand);

BorderPane borderPaneExecutionResult = new BorderPane(); borderPaneExecutionResult.setTop( new Label("SQL Execution Result")); borderPaneExecutionResult.setCenter(taSQLResult); borderPaneExecutionResult.setBottom(btClearSQLResult); BorderPane borderPane = new BorderPane(); borderPane.setTop(hBoxConnectionCommand); borderPane.setCenter(borderPaneExecutionResult); // Create a scene and place it in the stage Scene scene = new Scene(borderPane, 670, 400); primaryStage.setTitle("SQLClient"); // Set the stage title primaryStage.setScene(scene); // Place the scene in the stage primaryStage.show(); // Display the stage

btConnectDB.setOnAction(e -> connectToDB()); btExecuteSQL.setOnAction(e -> executeSQL()); btClearSQLCommand.setOnAction(e -> tasqlCommand.setText(null)); btClearSQLResult.setOnAction(e -> taSQLResult.setText(null)); }

private void connectToDB() { // Get database information from the user input String driver = cboDriver .getSelectionModel().getSelectedItem(); String url = cboURL.getSelectionModel().getSelectedItem(); String username = tfUsername.getText().trim(); String password = pfPassword.getText().trim();

// Connection to the database try { Class.forName(driver); connection = DriverManager.getConnection( url, username, password); lblConnectionStatus.setText("Connected to " + url); } catch (java.lang.Exception ex) { ex.printStackTrace(); } }

private void executeSQL() { if (connection == null) { taSQLResult.setText("Please connect to a database first"); return; } else { String sqlCommands = tasqlCommand.getText().trim(); String[] commands = sqlCommands.replace(' ', ' ').split(";");

for (String aCommand: commands) { if (aCommand.trim().toUpperCase().startsWith("SELECT")) { processSQLSelect(aCommand); } else { processSQLNonSelect(aCommand); } } } }

private void processSQLSelect(String sqlCommand) { try { // Get a new statement for the current connection statement = connection.createStatement();

// Execute a SELECT SQL command ResultSet resultSet = statement.executeQuery(sqlCommand);

// Find the number of columns in the result set int columnCount = resultSet.getMetaData().getColumnCount(); String row = "";

// Display column names for (int i = 1; i <= columnCount; i++) { row += resultSet.getMetaData().getColumnName(i) + "\t"; }

taSQLResult.appendText(row + ' ');

while (resultSet.next()) { // Reset row to empty row = "";

for (int i = 1; i <= columnCount; i++) { // A non-String column is converted to a string row += resultSet.getString(i) + "\t"; }

taSQLResult.appendText(row + ' '); } } catch (SQLException ex) { taSQLResult.setText(ex.toString()); } }

private void processSQLNonSelect(String sqlCommand) { try { statement = connection.createStatement();

statement.executeUpdate(sqlCommand);

taSQLResult.setText("SQL command executed"); } catch (SQLException ex) { taSQLResult.setText(ex.toString()); } }

public static void main(String[] args) { launch(args); } }

My Professor's Hint:

Populate Table View private void populateTableView(ResultSet rs, TableView tableView) {

tableView.getColumns().clear(); //empty the table view from the content of the previous statement

ObservableList data = FXCollections.observableArrayList();

try { /** TABLE COLUMN ADDED DYNAMICALLY * */

for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) { //We are using non property style for making dynamic table

final int j = i;

TableColumn col = new TableColumn(rs.getMetaData().getColumnName(i + 1)); 3

//col.setCellValueFactory(TextFieldTableCell.forTableColumn());

col.setCellValueFactory(new Callback, ObservableValue>() {

public ObservableValue call(CellDataFeatures param) {

if (param == null || param.getValue() == null || param.getValue().get(j) == null) {

return null;

}

return new SimpleStringProperty(param.getValue().get(j).toString());

}

});

tableView.getColumns().addAll(col);

//System.out.println("Column [" + i + "] "); } /** Data added to ObservableList */

while (rs.next()) {

//Iterate Row ObservableList row = FXCollections.observableArrayList();

for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {

//Iterate Column row.add(rs.getString(i));

}

System.out.println("Row [1] added " + row);

data.add(row);

}

//FINALLY ADDED TO TableView tableView.setItems(data);

}

catch (Exception e) { e.printStackTrace();

System.out.println("Error on Building Data");

}

}