1  import java.io.File;
  2  import java.io.FileNotFoundException;
  3  import java.io.IOException;
  4  import java.sql.*;
  5  import java.util.Scanner;
  6  import javafx.application.Application;
  7  import javafx.collections.FXCollections;
  8  import javafx.geometry.Pos;
  9  import javafx.scene.Scene;
 10  import javafx.scene.control.Button;
 11  import javafx.scene.control.ComboBox;
 12  import javafx.scene.control.Label;
 13  import javafx.scene.control.PasswordField;
 14  import javafx.scene.control.SplitPane;
 15  import javafx.scene.control.TextArea;
 16  import javafx.scene.control.TextField;
 17  import javafx.scene.layout.BorderPane;
 18  import javafx.scene.layout.GridPane;
 19  import javafx.scene.layout.HBox;
 20  import javafx.scene.layout.VBox;
 21  import javafx.stage.Stage;
 22  
 23  public class CopyFileToTable extends Application {
 24    // Text file info
 25    private TextField tfFilename = new TextField();
 26    private TextArea taFile = new TextArea();
 27  
 28    // JDBC and table info
 29    private ComboBox<String> cboURL = new ComboBox<>();
 30    private ComboBox<String> cboDriver = new ComboBox<>();
 31    private TextField tfUsername = new TextField();
 32    private PasswordField pfPassword = new PasswordField();
 33    private TextField tfTableName = new TextField();
 34  
 35    private Button btViewFile = new Button("View File");
 36    private Button btCopy = new Button("Copy");
 37    private Label lblStatus = new Label();
 38  
 39    @Override // Override the start method in the Application class
 40    public void start(Stage primaryStage) {
 41      cboURL.getItems().addAll(FXCollections.observableArrayList(
 42        "jdbc:mysql://localhost/javabook",
 43        "jdbc:mysql://liang.armstrong.edu/javabook",
 44        "jdbc:odbc:exampleMDBDataSource",
 45        "jdbc:oracle:thin:@liang.armstrong.edu:1521:orcl"));
 46      cboURL.getSelectionModel().selectFirst();
 47      
 48      cboDriver.getItems().addAll(FXCollections.observableArrayList(
 49        "com.mysql.jdbc.Driver", "sun.jdbc.odbc.dbcOdbcDriver",
 50        "oracle.jdbc.driver.OracleDriver"));
 51      cboDriver.getSelectionModel().selectFirst();
 52      
 53      // Create UI for connecting to the database 
 54      GridPane gridPane = new GridPane();
 55      gridPane.add(new Label("JDBC Driver"), 0, 0);
 56      gridPane.add(new Label("Database URL"), 0, 1);
 57      gridPane.add(new Label("Username"), 0, 2);
 58      gridPane.add(new Label("Password"), 0, 3);
 59      gridPane.add(new Label("Table Name"), 0, 4);
 60      gridPane.add(cboURL, 1, 0);
 61      gridPane.add(cboDriver, 1, 1);
 62      gridPane.add(tfUsername, 1, 2);
 63      gridPane.add(pfPassword, 1, 3);
 64      gridPane.add(tfTableName, 1, 4);
 65      
 66      HBox hBoxConnection = new HBox(10);
 67      hBoxConnection.getChildren().addAll(lblStatus, btCopy);
 68      hBoxConnection.setAlignment(Pos.CENTER_RIGHT);
 69  
 70      VBox vBoxConnection = new VBox(5);
 71      vBoxConnection.getChildren().addAll(
 72        new Label("Target Database Table"),
 73        gridPane, hBoxConnection);
 74      
 75      gridPane.setStyle("-fx-border-color: black;");
 76  
 77      BorderPane borderPaneFileName = new BorderPane();
 78      borderPaneFileName.setLeft(new Label("Filename"));
 79      borderPaneFileName.setCenter(tfFilename);
 80      borderPaneFileName.setRight(btViewFile);
 81              
 82      BorderPane borderPaneFileContent = new BorderPane();
 83      borderPaneFileContent.setTop(borderPaneFileName);
 84      borderPaneFileContent.setCenter(taFile);
 85      
 86      BorderPane borderPaneFileSource = new BorderPane();
 87      borderPaneFileSource.setTop(new Label("Source Text File"));
 88      borderPaneFileSource.setCenter(borderPaneFileContent);
 89                  
 90      SplitPane sp = new SplitPane();
 91      sp.getItems().addAll(borderPaneFileSource, vBoxConnection);
 92      
 93      // Create a scene and place it in the stage
 94      Scene scene = new Scene(sp, 680, 230);
 95      primaryStage.setTitle("CopyFileToTable"); // Set the stage title
 96      primaryStage.setScene(scene); // Place the scene in the stage
 97      primaryStage.show(); // Display the stage    
 98  
 99      btViewFile.setOnAction(e -> showFile());
100      btCopy.setOnAction(e -> {
101          try {
102            copyFile();
103          }
104          catch (Exception ex) {
105            lblStatus.setText(ex.toString());
106          }
107      });
108    }
109  
110    /** Display the file in the text area */
111    private void showFile() {
112      Scanner input = null;
113      try {
114        // Use a Scanner to read text from the file
115        input = new Scanner(new File(tfFilename.getText().trim()));
116  
117        // Read a line and append the line to the text area
118        while (input.hasNext()) 
119          taFile.appendText(input.nextLine() + '\n');
120      }
121      catch (FileNotFoundException ex) {
122        System.out.println("File not found: " + tfFilename.getText());
123      }
124      catch (IOException ex) {
125        ex.printStackTrace();
126      }
127      finally {
128        if (input != null) input.close();
129      }
130    }
131  
132    private void copyFile() throws Exception {
133      // Load the JDBC driver
134      Class.forName(cboDriver.getSelectionModel()
135        .getSelectedItem().trim());
136      System.out.println("Driver loaded");
137  
138      // Establish a connection
139      Connection conn = DriverManager.getConnection(
140        cboURL.getSelectionModel().getSelectedItem().trim(),
141        tfUsername.getText().trim(),
142        String.valueOf(pfPassword.getText()).trim());
143      System.out.println("Database connected");
144  
145      // Read each line from the text file and insert it to the table
146      insertRows(conn);
147    }
148  
149    private void insertRows(Connection connection) {
150      // Build the SQL INSERT statement
151      String sqlInsert = "insert into " + tfTableName.getText()
152        + " values (";
153  
154      // Use a Scanner to read text from the file
155      Scanner input = null;
156  
157      // Get file name from the text field
158      String filename = tfFilename.getText().trim();
159  
160      try {
161        // Create a scanner
162        input = new Scanner(new File(filename));
163  
164        // Create a statement
165        Statement statement = connection.createStatement();
166  
167        System.out.println("Driver major version? " +
168          connection.getMetaData().getDriverMajorVersion());
169  
170        // Determine if batchUpdatesSupported is supported 
171        boolean batchUpdatesSupported = false;
172  
173        try {
174          if (connection.getMetaData().supportsBatchUpdates()) {
175            batchUpdatesSupported = true;
176            System.out.println("batch updates supported");
177          }
178          else {
179            System.out.println("The driver " +
180              "does not support batch updates");
181          }
182        }
183        catch (UnsupportedOperationException ex) {
184          System.out.println("The operation is not supported");
185        }
186  
187        // Determine if the driver is capable of batch updates
188        if (batchUpdatesSupported) {
189          // Read a line and add the insert table command to the batch
190          while (input.hasNext()) {
191            statement.addBatch(sqlInsert + input.nextLine() + ")");
192          }
193  
194          statement.executeBatch();
195  
196          lblStatus.setText("Batch updates completed");
197        }
198        else {
199          // Read a line and execute insert table command
200          while (input.hasNext()) {
201            statement.executeUpdate(sqlInsert + input.nextLine() + ")");
202          }
203  
204          lblStatus.setText("Single row update completed");
205        }
206      }
207      catch (SQLException ex) {
208        System.out.println(ex);
209      }
210      catch (FileNotFoundException ex) {
211        System.out.println("File not found: " + filename);
212      }
213      finally {
214        if (input != null) input.close();
215      }
216    }
217  
218    /**
219     * The main method is only needed for the IDE with limited
220     * JavaFX support. Not needed for running from the command line.
221     */
222    public static void main(String[] args) {
223      launch(args);
224    }
225  }