1  import java.sql.*;
  2  import javafx.application.Application;
  3  import javafx.collections.FXCollections;
  4  import javafx.geometry.Pos;
  5  import javafx.scene.Scene;
  6  import javafx.scene.control.Button;
  7  import javafx.scene.control.ComboBox;
  8  import javafx.scene.control.Label;
  9  import javafx.scene.control.PasswordField;
 10  import javafx.scene.control.ScrollPane;
 11  import javafx.scene.control.TextArea;
 12  import javafx.scene.control.TextField;
 13  import javafx.scene.layout.BorderPane;
 14  import javafx.scene.layout.GridPane;
 15  import javafx.scene.layout.HBox;
 16  import javafx.scene.layout.VBox;
 17  import javafx.stage.Stage;
 18  
 19  public class SQLClient extends Application {
 20    // Connection to the database
 21    private Connection connection;
 22  
 23    // Statement to execute SQL commands
 24    private Statement statement;
 25  
 26    // Text area to enter SQL commands
 27    private TextArea tasqlCommand = new TextArea();
 28  
 29    // Text area to display results from SQL commands
 30    private TextArea taSQLResult = new TextArea();
 31  
 32    // DBC info for a database connection
 33    private TextField tfUsername = new TextField();
 34    private PasswordField pfPassword = new PasswordField();
 35    private ComboBox<String> cboURL = new ComboBox<>();
 36    private ComboBox<String> cboDriver = new ComboBox<>();
 37  
 38    private Button btExecuteSQL = new Button("Execute SQL Command");
 39    private Button btClearSQLCommand = new Button("Clear");
 40    private Button btConnectDB = new Button("Connect to Database");
 41    private Button btClearSQLResult = new Button("Clear Result");
 42    private Label lblConnectionStatus 
 43      = new Label("No connection now");
 44  
 45    @Override // Override the start method in the Application class
 46    public void start(Stage primaryStage) {
 47      cboURL.getItems().addAll(FXCollections.observableArrayList(
 48        "jdbc:mysql://localhost/javabook",
 49        "jdbc:mysql://liang.armstrong.edu/javabook",
 50        "jdbc:odbc:exampleMDBDataSource",
 51        "jdbc:oracle:thin:@liang.armstrong.edu:1521:orcl"));
 52      cboURL.getSelectionModel().selectFirst();
 53      
 54      cboDriver.getItems().addAll(FXCollections.observableArrayList(
 55        "com.mysql.jdbc.Driver", "sun.jdbc.odbc.dbcOdbcDriver",
 56        "oracle.jdbc.driver.OracleDriver"));
 57      cboDriver.getSelectionModel().selectFirst();
 58      
 59      // Create UI for connecting to the database 
 60      GridPane gridPane = new GridPane();
 61      gridPane.add(cboURL, 1, 0);
 62      gridPane.add(cboDriver, 1, 1);
 63      gridPane.add(tfUsername, 1, 2);
 64      gridPane.add(pfPassword, 1, 3);
 65      gridPane.add(new Label("Database URL"), 0, 0);
 66      gridPane.add(new Label("JDBC Driver"), 0, 1);
 67      gridPane.add(new Label("Username"), 0, 2);
 68      gridPane.add(new Label("Password"), 0, 3);
 69      
 70      HBox hBoxConnection = new HBox();
 71      hBoxConnection.getChildren().addAll(
 72        lblConnectionStatus, btConnectDB);
 73      hBoxConnection.setAlignment(Pos.CENTER_RIGHT);
 74      
 75      VBox vBoxConnection = new VBox(5);
 76      vBoxConnection.getChildren().addAll(
 77        new Label("Enter Database Information"),
 78        gridPane, hBoxConnection);
 79      
 80      gridPane.setStyle("-fx-border-color: black;");
 81      
 82      HBox hBoxSQLCommand = new HBox(5);
 83      hBoxSQLCommand.getChildren().addAll(
 84        btClearSQLCommand, btExecuteSQL);
 85      hBoxSQLCommand.setAlignment(Pos.CENTER_RIGHT);
 86      
 87      BorderPane borderPaneSqlCommand = new BorderPane();
 88      borderPaneSqlCommand.setTop(
 89        new Label("Enter an SQL Command"));
 90      borderPaneSqlCommand.setCenter(
 91        new ScrollPane(tasqlCommand));
 92      borderPaneSqlCommand.setBottom(
 93        hBoxSQLCommand);
 94      
 95      HBox hBoxConnectionCommand = new HBox(10);
 96      hBoxConnectionCommand.getChildren().addAll(
 97        vBoxConnection, borderPaneSqlCommand);
 98  
 99      BorderPane borderPaneExecutionResult = new BorderPane();
100      borderPaneExecutionResult.setTop(
101        new Label("SQL Execution Result"));
102      borderPaneExecutionResult.setCenter(taSQLResult);
103      borderPaneExecutionResult.setBottom(btClearSQLResult);
104      
105      BorderPane borderPane = new BorderPane();
106      borderPane.setTop(hBoxConnectionCommand);
107      borderPane.setCenter(borderPaneExecutionResult);
108      
109      // Create a scene and place it in the stage
110      Scene scene = new Scene(borderPane, 670, 400);
111      primaryStage.setTitle("SQLClient"); // Set the stage title
112      primaryStage.setScene(scene); // Place the scene in the stage
113      primaryStage.show(); // Display the stage    
114  
115      btConnectDB.setOnAction(e -> connectToDB());
116      btExecuteSQL.setOnAction(e -> executeSQL()); 
117      btClearSQLCommand.setOnAction(e -> tasqlCommand.setText(null));
118      btClearSQLResult.setOnAction(e -> taSQLResult.setText(null));
119    }
120  
121    /** Connect to DB */
122    private void connectToDB() {
123      // Get database information from the user input
124      String driver = cboDriver
125          .getSelectionModel().getSelectedItem();
126      String url = cboURL.getSelectionModel().getSelectedItem();
127      String username = tfUsername.getText().trim();
128      String password = pfPassword.getText().trim();
129  
130      // Connection to the database
131      try {
132        Class.forName(driver);
133        connection = DriverManager.getConnection(
134          url, username, password);
135        lblConnectionStatus.setText("Connected to " + url);
136      }
137      catch (java.lang.Exception ex) {
138        ex.printStackTrace();
139      }
140    }
141  
142    /** Execute SQL commands */
143    private void executeSQL() {
144      if (connection == null) {
145        taSQLResult.setText("Please connect to a database first");
146        return;
147      }
148      else {
149        String sqlCommands = tasqlCommand.getText().trim();
150        String[] commands = sqlCommands.replace('\n', ' ').split(";");
151  
152        for (String aCommand: commands) {
153          if (aCommand.trim().toUpperCase().startsWith("SELECT")) {
154            processSQLSelect(aCommand);
155          }
156          else {
157            processSQLNonSelect(aCommand);
158          }
159        }
160      }
161    }
162  
163    /** Execute SQL SELECT commands */
164    private void processSQLSelect(String sqlCommand) {
165      try {
166        // Get a new statement for the current connection
167        statement = connection.createStatement();
168  
169        // Execute a SELECT SQL command
170        ResultSet resultSet = statement.executeQuery(sqlCommand);
171  
172        // Find the number of columns in the result set
173        int columnCount = resultSet.getMetaData().getColumnCount();
174        String row = "";
175  
176        // Display column names
177        for (int i = 1; i <= columnCount; i++) {
178          row += resultSet.getMetaData().getColumnName(i) + "\t";
179        }
180  
181        taSQLResult.appendText(row + '\n');
182  
183        while (resultSet.next()) {
184          // Reset row to empty
185          row = "";
186  
187          for (int i = 1; i <= columnCount; i++) {
188            // A non-String column is converted to a string
189            row += resultSet.getString(i) + "\t"; 
190          }
191  
192          taSQLResult.appendText(row + '\n');
193        }
194      }
195      catch (SQLException ex) {
196        taSQLResult.setText(ex.toString());
197      }
198    }
199  
200    /** Execute SQL DDL, and modification commands */
201    private void processSQLNonSelect(String sqlCommand) {
202      try {
203        // Get a new statement for the current connection
204        statement = connection.createStatement();
205  
206        // Execute a non-SELECT SQL command
207        statement.executeUpdate(sqlCommand);
208  
209        taSQLResult.setText("SQL command executed");
210      }
211      catch (SQLException ex) {
212        taSQLResult.setText(ex.toString());
213      }
214    }
215  
216    /**
217     * The main method is only needed for the IDE with limited
218     * JavaFX support. Not needed for running from the command line.
219     */
220    public static void main(String[] args) {
221      launch(args);
222    }
223  }