EditedSQLClient

/*
What's in the file:
'5', 'Fred', 'Arcen', 'A', 'address1', Southbend', 'IN', '7659999999', 'email1@iu.edu'
'6', 'Thomas', 'Arthur', 'B', 'address2', Richmond', 'IN', '7659999999', 'email2@iu.edu'
'9', 'Suzette', 'Austin', 'D', 'address3', Bloomington', 'IN', '7659999999', 'email3@iu.edu'

*/



package editedsqlclient;

import java.io.File;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
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.SplitPane;
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;

/**
 *
 * @author T
 */
public class EditedSQLClient extends Application {
    private Connection connection;
    private Statement statement;


    private TextField tfFilename = new TextField();
    private TextArea taFile = new TextArea();

    private TextField tfUsername = new TextField();
    private PasswordField pfPassword = new PasswordField();
    private ComboBox<String> cbURL = new ComboBox<>();
    private ComboBox<String> cbDriver = new ComboBox<>();

    private Button btViewFile = new Button("View File");
    private Button btView = new Button("View");
    private Button btCopy = new Button("Copy");


    private Label lblStatus = new Label();

    @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 hBoxConnection = new HBox(10); //HBox for the connection
            hBoxConnection.getChildren().addAll(lblStatus, btView, btCopy); //Adds the lblstatus and the connect button
            hBoxConnection.setAlignment(Pos.CENTER_RIGHT); //Sets the alignment of hBox1 to the center right


        VBox vBoxConnection = new VBox(6); //VBox for the connection
            vBoxConnection.getChildren().addAll(new Label
             ("Target Database Table"),
             gridPane, hBoxConnection); //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

       BorderPane bpFileName = new BorderPane();
            bpFileName.setLeft(new Label("File Name"));
            bpFileName.setCenter(tfFilename);
            bpFileName.setRight(btViewFile);

        BorderPane bpFileContent = new BorderPane();
            bpFileContent.setTop(bpFileName);
            bpFileContent.setCenter(taFile);

        BorderPane bpFileSource = new BorderPane();
            bpFileSource.setTop(new Label("Source Text File"));
            bpFileSource.setCenter(bpFileContent);

        SplitPane splitPane = new SplitPane();
            splitPane.getItems().addAll(bpFileSource, vBoxConnection);

        Scene  scene = new Scene(splitPane, 950, 350);
        primaryStage.setTitle("CopyFileToStaff");
        primaryStage.setScene(scene);
        primaryStage.show();

        //btConnect.setOnAction(e -> connect());
        btViewFile.setOnAction(e -> showFile());
        btView.setOnAction(e -> executeView());
        btCopy.setOnAction(e -> {
           try {
               copyFile();
            }

           catch (Exception ex) {
               lblStatus.setText(ex.toString());
           }
        });
    }

    private void showFile() {
        Scanner input = null;
        try {
            input = new Scanner(new File(tfFilename.getText().trim()));

            while (input.hasNext()) {
                taFile.appendText(input.nextLine() + '\n');
            }
        }
            catch (FileNotFoundException ex) {
                    System.out.println("File not found: " + tfFilename.getText());
            }
            finally {
                if (input != null) input.close();
            }
    }

    private void copyFile() throws Exception {
        Class.forName(cbDriver.getSelectionModel().getSelectedItem().trim());
        System.out.println("Driver loaded");

        Connection connection = DriverManager.getConnection(
            cbURL.getSelectionModel().getSelectedItem().trim(),
                tfUsername.getText().trim(),
                String.valueOf(pfPassword.getText()).trim());
        System.out.println("Database connected");

        taFile.setText("");
        lblStatus.setText("Batch updates completed");
    }

    private void executeView() {
        processViewCommand();
    }

    private void processViewCommand() {
        try {
            //try {
            /*Class.forName(cbDriver.getSelectionModel().getSelectedItem().trim());
            Connection connection =
                DriverManager.getConnection(cbURL.getSelectionModel().getSelectedItem().trim());
                    //tfUsername.getText().trim();
                    //String.valueOf(pfPassword.getText()).trim();
            */
            String query = "SELECT * FROM staff";
            PreparedStatement ps = connection.prepareStatement(query);

            ResultSet resultSet = ps.executeQuery();

            while (resultSet.next()) {
                String columnData = resultSet.getString("id");
                taFile.appendText(columnData + '\n');
            }

            /*catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        */
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

     public static void main(String[] args) throws SQLException {
    launch(args);
    }
}

    //Old code; didn't work but saved it in case I needed it
    /*    String query = "SELECT * from staff";
        String viewCommand = taFile.getText().trim();


    try {


      /// Get a new statement for the current connection
        statement = connection.createStatement();

        // Execute SQL command
        //query = "SELECT * from staff";
        ResultSet resultSet = statement.executeQuery(query);

        // 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";
        }

        taFile.appendText(row + '\n');

        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";
        }

           taFile.appendText(row + '\n');
        }
        }
        catch (SQLException ex) {
           taFile.setText(ex.toString());
        }
    }\
    */