ZipCodeTranslator FINAL EXAM *PHASE 2*

/*
 * Collin Emond
 * 5-16-16
 * Final Exam
 * CSC-112
 * Prof. Silvestri
 * Phase 2: The Database Lookup
 * Access the silvestri database with queries to return the specified data
 * cremond0001@student.stcc.edu
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javafx.application.Application;
import javafx.geometry.Insets;
import javafx.geometry.Pos;
import javafx.scene.Scene;
import javafx.scene.control.*;
import javafx.scene.layout.*;
import javafx.scene.paint.Color;
import javafx.scene.shape.Line;
import javafx.stage.Stage;

public class ZipCodePhase2 extends Application {
    TextField txtzipcode = new TextField();
    TextField txtcitymiddle = new TextField();
    TextField txtcitytop = new TextField();
    TextField txtstatetop = new TextField();
    TextField txtstatemiddle = new TextField();
    Button zipcityBtn = new Button("Zip To City");
    Button citytozipBtn = new Button("City to Zip");
    TextField txtzipcodes = new TextField("Zip Code(s)");
    private PreparedStatement preparedStatement;
    Connection conn;

    @Override
    public void start(Stage primaryStage) {

        // Establish connection
        StartDatabase();

        // Create a new pane to hold
        BorderPane pane = new BorderPane();
        pane.setTop(topHalf());
        pane.setCenter(middlePart());
        pane.setBottom(bottomHalf());
        zipcityBtn.setOnAction(e -> checkzipcity());
        citytozipBtn.setOnAction(e -> checkcitytozip());

        // Create a scene
        Scene scene = new Scene(pane, 800, 300);
        primaryStage.setTitle("Zip Code to City/State Translator App");
        primaryStage.setScene(scene);
        primaryStage.show();
        primaryStage.setResizable(false);
    }

    // TextStatus Field
    TextField txtstatus = new TextField();

    // Method that executes Zip to City Button Code
    private void checkzipcity() {
        if (txtzipcode.getText().matches("(\\d{5})")) {
            zipDatabase();
            txtstatus.setText("Valid Zip Code - City and State retrieved from Database!");
        } else {
            txtstatus.setText("Not a Valid Zip Code - No Retrieval from Database!");
            txtcitytop.setText("None");
            txtstatetop.setText("None");
        }
    }

    // Method that executes City to Zip Button Code
    private void checkcitytozip() {
        if (txtcitymiddle.getText().matches("^[a-zA-Z\\s-]+$") && txtstatemiddle.getText().matches("^[A-Za-z]{2}")) {
            cityDatabase();
            txtstatus.setText("Valid City and State - Zip Code retrieved from Database!");
        } else {
            txtstatus.setText("Not a Valid City and State - No Retrieval from Database!");
            txtzipcodes.setText("No zipcodes found");
        }
    }

    // Top Half of Program
    public VBox topHalf() {

        HBox hbox = new HBox();
        hbox.setPadding(new Insets(15, 12, 15, 12));
        hbox.setSpacing(15);
        hbox.setAlignment(Pos.CENTER);

        // VBox to hold HBox and Separator
        VBox vbox = new VBox();
        vbox.setAlignment(Pos.CENTER);

        // Add Line
        Line separator = new Line();
        separator.setStrokeWidth(5);
        separator.setStroke(Color.BLACK);
        separator.setEndX(800.0);
        separator.autosize();

        // Add Labels
        Label lblzipcode = new Label("Zip Code:");
        Label lblcity = new Label("City:");
        Label lblstate = new Label("State:");

        // Add Properties of TextField
        txtzipcode.setPrefColumnCount(5);
        txtzipcode.setPrefWidth(80);
        txtcitytop.setPrefColumnCount(25);
        txtcitytop.setPrefWidth(200);
        txtcitytop.setEditable(false);
        txtcitytop.setMouseTransparent(true);
        txtcitytop.setFocusTraversable(false);
        txtstatetop.setPrefColumnCount(2);
        txtstatetop.setEditable(false);
        txtstatetop.setMouseTransparent(true);
        txtstatetop.setFocusTraversable(false);
        txtstatetop.setPrefWidth(50);

        // Add to hbox
        hbox.getChildren().addAll(lblzipcode, txtzipcode, zipcityBtn, lblcity, txtcitytop, lblstate, txtstatetop);
        vbox.getChildren().addAll(hbox, separator);
        return vbox;
    }

    // Middle of program
    public VBox middlePart() {

        HBox hbox = new HBox();
        hbox.setPadding(new Insets(15, 12, 15, 12));
        hbox.setSpacing(15);
        hbox.setAlignment(Pos.CENTER);

        // VBox to hold HBox and Separator
        VBox vbox = new VBox();
        vbox.setAlignment(Pos.CENTER);

        // Add Line
        Line separator = new Line();
        separator.setStrokeWidth(5);
        separator.setStroke(Color.BLACK);
        separator.setEndX(800.0);
        separator.autosize();

        // Add Labels
        Label lblcity = new Label("City:");
        Label lblstate = new Label("State:");

        // Add Properties of TextField
        txtstatemiddle.setPrefColumnCount(2);
        txtstatemiddle.setPrefWidth(50);
        txtzipcodes.setPrefColumnCount(5);
        txtzipcodes.setEditable(false);
        txtzipcodes.setMouseTransparent(true);
        txtzipcodes.setFocusTraversable(false);
        txtzipcodes.setPrefHeight(100);
        txtzipcodes.setPrefWidth(140);

        // Add to hbox
        hbox.getChildren().addAll(lblcity, txtcitymiddle, lblstate, txtstatemiddle, citytozipBtn, txtzipcodes);
        vbox.getChildren().addAll(hbox, separator);

        return vbox;
    }

    // Bottom of program
    public HBox bottomHalf() {

        HBox hbox = new HBox();
        hbox.setAlignment(Pos.CENTER);

        // Add Label
        Label lblstatus = new Label("Status:");

        // Add Properties of TextField
        txtstatus.setEditable(false);
        txtstatus.setMouseTransparent(true);
        txtstatus.setFocusTraversable(false);
        txtstatus.setPrefWidth(500);

        // Add to hbox
        hbox.getChildren().addAll(lblstatus, txtstatus);

        return hbox;
    }

    // Establish a connection to the silvestri database
    private void StartDatabase() {
        String DATABASE = "silvestri";
        String USERNAME = "readonly";
        String PASSWORD = "readonly";
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://cs.stcc.edu/" + DATABASE + "?user=" + USERNAME + "&password=" + PASSWORD;
        String zip = txtzipcode.getText();
        String state = txtstatemiddle.getText();
        String city = txtcitymiddle.getText();
        try {
            Class.forName(driver);
            System.out.println("Driver loaded successfully");

            Connection connection = DriverManager.getConnection(url);
            System.out.println("Database Connected");
            String zipCode = ("Select city, state FROM Zipcodes WHERE zipcode= " + zip);
            String City = ("Select zipcode from Zipcodes where state ='" + state + "' && city ='" + city + "';");

            preparedStatement = connection.prepareStatement(zipCode);
            preparedStatement = connection.prepareStatement(City);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    // Send ZipCode to Database
    private void zipDatabase() {
        try {
            String zip = txtzipcode.getText();
            String zipCode = ("Select city, state FROM Zipcodes WHERE zipcode= " + zip);
            ResultSet rset = preparedStatement.executeQuery(zipCode);
            if (rset.next()) {
                txtcitytop.setText(rset.getString(1));
                txtstatetop.setText(rset.getString(2));
                txtstatus.setText("City and State were retrieved from Database!");
            } else {
                txtstatus.setText("No data retrieved from Database!");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    // Send City, State to Database
    private void cityDatabase() {
        try {
            String state = txtstatemiddle.getText();
            String city = txtcitymiddle.getText();
            String City = ("Select zipcode from Zipcodes where state ='" + state + "' && city ='" + city + "';");
            ResultSet rset = preparedStatement.executeQuery(City);
            if (rset.next()) {
                String NumberofZips = "";
                if (rset.next()) {
                    NumberofZips += (rset.getString(1));
                }
                txtzipcodes.setText(NumberofZips);
            } else {
                txtstatus.setText("City or State you are looking for cannot be found");
                txtzipcodes.setText("No Zip Code(s)");
            }
        } catch (Exception ex) {
            ex.getMessage();
        }
    }

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