A Zip code to City/State Translator Database APP

/*
 * Name: Lu Liu
 * Date: 5/10/2016
 * Course Number: CSC-112
 * Course Name: Intermediate Topics in Java Programming
 * Email: lliu0001@student.stcc.edu
 *
 * Assignment: HW # 18
 * Programe Description:
 * A Zip code to City/State Translator Database APP
 */
import javafx.application.Application;
import javafx.geometry.Insets;
import javafx.geometry.Pos;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.control.Label;
import javafx.scene.control.TextArea;
import javafx.scene.control.TextField;
import javafx.scene.layout.BorderPane;
import javafx.scene.layout.HBox;
import javafx.scene.layout.VBox;
import javafx.scene.paint.Color;
import javafx.scene.shape.Line;
import javafx.stage.Stage;
import java.sql.*;

public class ZipCodeApp extends Application {
    private PreparedStatement preparedStatement;
    private PreparedStatement preparedStatement1;
    private TextField tfZipCode = new TextField();
    private TextField tfCITY = new TextField();
    private TextField tfSTATE = new TextField();
    private TextField tfCity = new TextField();
    private TextField tfState = new TextField();
    TextArea taZipCode = new TextArea();
    TextField tfStatus = new TextField();

    @Override // Override the start method in the Application class
    public void start(Stage primaryStage) {
        // Initialize database connection and create a Statement object
        initializeDB();

        // Create pane to hold the label and text field
        HBox hBox = new HBox(10);

        tfZipCode.setAlignment(Pos.CENTER_LEFT);
        tfCity.setAlignment(Pos.CENTER_LEFT);
        tfState.setAlignment(Pos.CENTER_LEFT);
        Button bnToCity = new Button("Zip to City");
        hBox.setPadding(new Insets(5, 15, 25, 5));
        hBox.getChildren().addAll(new Label("Zip Code:"), tfZipCode, bnToCity, new Label("City:"), tfCity,
                new Label("State:"), tfState);

        HBox hBox2 = new HBox(10);
        hBox2.setPadding(new Insets(5, 15, 25, 5));
        tfCITY.setAlignment(Pos.CENTER_LEFT);
        tfSTATE.setAlignment(Pos.CENTER_LEFT);
        Button bnToZip = new Button("City to Zip");

        taZipCode.setMaxSize(130, 130);
        taZipCode.setText("Zip Codes");
        hBox2.getChildren().addAll(new Label("City:"), tfCITY, new Label("State:"), tfSTATE, bnToZip, taZipCode);
        Line line1 = new Line(10, 10, 750, 10);
        line1.setStrokeWidth(3);
        line1.setStroke(Color.WHITE);
        BorderPane centerPane = new BorderPane();
        centerPane.setTop(line1);
        centerPane.setCenter(hBox2);

        HBox hBox3 = new HBox(10);
        hBox3.setPadding(new Insets(5, 15, 25, 80));
        tfStatus.setMinSize(510, 20);
        tfStatus.setAlignment(Pos.CENTER);
        hBox3.getChildren().addAll(new Label("Status:"), tfStatus);
        Line line2 = new Line(10, 200, 750, 200);
        line2.setStrokeWidth(3);
        line2.setStroke(Color.WHITE);
        BorderPane bottomPane = new BorderPane();
        bottomPane.setCenter(hBox3);
        bottomPane.setTop(line2);

        // Create pane to hold all
        BorderPane mainPane = new BorderPane();
        mainPane.setPadding(new Insets(25, 5, 5, 5));
        mainPane.setStyle("-fx-background-color: #D3D3D3;");
        mainPane.setTop(hBox);
        mainPane.setCenter(centerPane);
        mainPane.setBottom(bottomPane);

        // catch the invalid input
        bnToCity.setOnAction(e -> {
            try {
                int zipCode = Integer.parseInt(tfZipCode.getText());
                if (tfZipCode.getText().length() == 5) {
                    tfStatus.setText("Valid Zip Code");
                    showAddress();
                } else {
                    tfStatus.setText("The ZIP Code must be 5 digits integer");
                }
            } catch (Exception ex) {
                tfStatus.setText("The ZIP Code must be 5 digits integer");
            }
        });

        bnToZip.setOnAction(e -> showZipCodes());

        // Create a scene and place it in the stage
        Scene scene = new Scene(mainPane, 760, 300);
        primaryStage.setTitle("Zip Code Translation System"); // Set the stage
        primaryStage.setScene(scene); // Place the scene in the stage
        primaryStage.show(); // Display the stage

    }

    // Method initializeDB
    private void initializeDB() {
        final String DATABASE = "silvestri";
        final String USERNAME = "readonly";
        final String PASSWORD = "readonly";
        String url = "jdbc:mysql://cs.stcc.edu/" + DATABASE + "?user=" + USERNAME + "&password=" + PASSWORD;
        try {
            // Load the JDBC driver
            Class.forName("com.mysql.jdbc.Driver");

            // Establish a connection
            Connection connection = DriverManager.getConnection(url);

            // Create a statement
            String queryString = "select state, city from Zipcodes " + "where zipCode  = ? ";
            String queryString1 = "select zipcode from Zipcodes " + "where city = ?" + " and state = ?";
            preparedStatement = connection.prepareStatement(queryString);
            preparedStatement1 = connection.prepareStatement(queryString1);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    // Method showAddress
    private void showAddress() {
        String ZipCode = tfZipCode.getText();
        // String City = tfCITY.getText();
        // String State = tfSTATE.getText();
        try {
            preparedStatement.setString(1, ZipCode);
            // preparedStatement.setString(2, City);
            // preparedStatement.setString(3, State);
            ResultSet rset = preparedStatement.executeQuery();
            if (rset.next()) {
                String city = rset.getString(2);
                String state = rset.getString(1);
                // Display result
                tfCity.setText(city);
                tfState.setText(state);
            } else {
                tfStatus.setText("Not found");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    // Method showZipCodes
    private void showZipCodes() {
        String City = tfCITY.getText();
        String State = tfSTATE.getText();
        try {
            preparedStatement1.setString(1, City);
            preparedStatement1.setString(2, State);
            ResultSet rset = preparedStatement1.executeQuery();

            if (rset.next()) {
                String zipCode = rset.getString("zipcode");
                taZipCode.setText(zipCode + "" + "\n");
                while (rset.next()) {
                    zipCode = rset.getString("zipcode");
                    taZipCode.setText(taZipCode.getText() + zipCode + "" + "\n");
                }
            } else {
                tfStatus.setText("Not found");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * The main method is only needed for the IDE with limited JavaFX support.
     * Not needed for running from the command line.
     */
    public static void main(String[] args) {
        launch(args);
    }
}