Question
Revise SQLClient.java) Rewrite Listing 35.1, SQLClient.java to display the query result in a TableView, as shown in Figure 35.9. The query result is displayed in
Revise SQLClient.java) Rewrite Listing 35.1, SQLClient.java to display the query result in a TableView, as shown in Figure 35.9.
The query result is displayed in a TableView.
package sqlclient;
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 om SQL commands fr private TextArea taSQLResult = new TextArea(); // DBC info for a database conn ion ect private TextField tfUsername = new TextField(); private PasswordField pfPassword = new PasswordField(); private 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)); }
/** * Connect to DB */ 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); } } } }
/** * Execute SQL SELECT commands */ 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
taSQLResult.appendText(row + ' ');
while (resultSet.next()) { // Reset row to empty
row = "";
for (int i = 1; i
row += resultSet.getString(i) + "\t"; }
taSQLResult.appendText(row + ' '); } } catch (SQLException ex) { taSQLResult.setText(ex.toString()); } }
private void processSQLNonSelect(String sqlCommand) { try { // Get a new statement for the current connection
statement = connection.createStatement();
// Execute a non-SELECT SQL command statement.executeUpdate(sqlCommand);
taSQLResult.setText("SQL command executed"); } catch (SQLException ex) { taSQLResult.setText(ex.toString()); } }
}
(Revise SQLClient.java) Rewrite Listing 35.1, SQLClient.java, to display the query result in a TableView, as shown in Figure 35.9 xeroise35-05 Database Infom Enter an SOL Command selectfrom Enrollment Connected to ymaslhost/javabookConnedt to Database L Execution Result Gear Execute SOL Command grade 2013-04-18 2013-04-18 2013-04-18 2013-04-18 Cear Result Figure 35.9 The query result is displayed in a TableView 35.5***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