Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Making a Java text area to allow simultaneous SQL commands to be put into the table view in a single run For my program, I

Making a Java text area to allow simultaneous SQL commands to be put into the table view in a single run

For my program, I need to revise my code to allow the program to read two simultaneous SQL commands that are inputted by the user. For example, the user could say this in the text area:

SELECT * FROM staff;

INSERT INTO staff ('id', 'lastname', 'firstname') VALUES ('567', 'Doe', 'Jane'); --And the program would need to bring up the selected table as well as insert the new information in one run. However, for the life of me, I cannot figure out how to make my program do this. Can you help me understand how to do this? Thanks!

package dbrevise;

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javafx.application.Application; import javafx.beans.property.SimpleStringProperty; import javafx.beans.value.ObservableValue; import javafx.collections.FXCollections; import javafx.collections.ObservableList; 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.TableColumn; import javafx.scene.control.TableView; 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.util.Callback;

/** * * @author T */ public class DBRevise 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; //Sets up the table and data associated with it private ObservableListdata; //Sets up the text areas for the SQL command and result private final TextArea taSQLCommand = new TextArea(); private TableView tvSQLResult = new TableView(); //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("Connect to Database"); private final Button btClearSQLResult = new Button("Clear Result"); //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) { cbURL.getItems().addAll(FXCollections.observableArrayList ("jdbc:mysql://localhost/test")); cbURL.getSelectionModel().selectFirst(); cbDriver.getItems().addAll(FXCollections.observableArrayList ("com.mysql.jdbc.Driver")); cbDriver.getSelectionModel().selectFirst(); 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("Enter an 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(tvSQLResult); //Sets the location to the center borderPane2.setBottom(btClearSQLResult); 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, 755, 550); //Creates the dimensions primaryStage.setTitle("SQL Client"); //Sets the title of pane primaryStage.setScene(scene); ///Sets the scene primaryStage.show(); btConnect.setOnAction(e -> connect()); btExecuteSQLCommand.setOnAction(e -> executeSQLCommand()); btClearSQLCommand.setOnAction(e -> taSQLCommand.setText(null)); btClearSQLResult.setOnAction(e -> tvSQLResult.setItems(null)); } public void connect() { 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(); } } private void executeSQLCommand() { String sqlCommand = taSQLCommand.getText().trim(); String[] command = sqlCommand.replace(' ', ' ').split(";"); for (String instruction: command) { if (instruction.trim().toUpperCase().startsWith("SELECT")) { processSQLSelect(instruction); if (instruction.trim().toUpperCase().startsWith("INSERT")) { processSQLInsert(instruction); } } } } private void processSQLSelect(String sqlCommand) { try { statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sqlCommand); fillTableView(resultSet, tvSQLResult); } catch (SQLException ex) { ex.printStackTrace(); } } private void processSQLNonSelect(String sqlCommand) { try { statement = connection.createStatement(); statement.executeUpdate(sqlCommand); } catch (SQLException ex) { ex.printStackTrace(); } } private void processSQLInsert (String sqlInsertCommand) { try { statement = connection.createStatement(); statement.executeUpdate(sqlInsertCommand); fillTableView(resultSet, tvSQLResult); } catch (SQLException ex) { ex.printStackTrace(); } } private void processNonSQLInsert (String sqlInsertCommand) { try { statement = connection.createStatement(); statement.executeUpdate(sqlInsertCommand); } catch (SQLException ex) { ex.printStackTrace(); } } //Populate Table View private void fillTableView(ResultSet rs, TableView tableView) { //empty the table view from the content of the previous statement tableView.getColumns().clear();

ObservableList data = FXCollections.observableArrayList(); try {

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

final int j = i; TableColumn col = new TableColumn(rs.getMetaData().getColumnName(i + 1));

col.setCellValueFactory(new Callback String>, ObservableValue>() { public ObservableValue call(TableColumn.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); } while (rs.next()) { //Iterates Row ObservableList row = FXCollections.observableArrayList(); for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) { //Iterates Column row.add(rs.getString(i)); }

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

} //Adds data 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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Professional Microsoft SQL Server 2012 Administration

Authors: Adam Jorgensen, Steven Wort

1st Edition

1118106881, 9781118106884

More Books

Students also viewed these Databases questions

Question

Is coal in short supply?

Answered: 1 week ago

Question

b. Did you suppress any of your anger? Explain.

Answered: 1 week ago