Question
JavaFX databasing Programming with SQL. For my assignment, I need to rewrite a SQLClient Code (I will post it below) and display the query result
JavaFX databasing Programming with SQL.
For my assignment, I need to rewrite a SQLClient Code (I will post it below) and display the query result with TableView that allows users to bring up a database in the TableView. Also, in my solution, my professor wants me to use this populateTableView (I will insert the hint below). I cannot use JTable (or any other of J functions or Swing imports).
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); } }
processTableView hint Code:
package ass7dbrevisesqlclient;
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; 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;
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");
}
}
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started