Daniel J. /CSC-112

/* Daniel Jorge
 * 5/16/2016
 * Intermediate Java/ CSC-112
 * Final part 2, Zip-City gui with database connection
 * Gui program that queries a database for zipcodes of cities/towns
 * dani1996dj@gmail.com
 */
package application;

import java.sql.*;

public class DBConnection {

    // necissary fields for a read only connection to the database
    private static final String DATABASE = "silvestri";
    private static final String USERNAME = "readonly";
    private static final String PASSWORD = "readonly";

    static String driver = "com.mysql.jdbc.Driver";
    static String url = "jdbc:mysql://cs.stcc.edu/" + DATABASE + "?user=" + USERNAME + "&password=" + PASSWORD;

    Connection conn = null;
    Statement statement;

    // Connect to the database
    public void connect() throws Exception {

        // creates a connection to the database
        Class.forName(driver).newInstance();

        conn = DriverManager.getConnection(url);

    }

    // Queries the database for the city and state of the zipcode provided by
    // the user
    public String[] getCity_State(int zip) throws SQLException {

        statement = conn.createStatement();
        ResultSet res = statement.executeQuery("SELECT city,state FROM Zipcodes WHERE zipcode =" + zip);

        String[] strs = new String[2];

        while (res.next()) {
            strs[0] = res.getString(1);
            strs[1] = res.getString(2);
        }
        return strs;
    }

    // Queries the database for the zip code(s) within he city and state
    // provided by the user
    public String getZips(String city, String state) throws SQLException {

        statement = conn.createStatement();
        ResultSet res = statement
                .executeQuery("SELECT zipcode FROM Zipcodes WHERE city = '" + city + "' AND state = '" + state + "'");

        String zipcode = "";

        while (res.next()) {
            zipcode += res.getString("zipcode") + "\n";
        }

        return zipcode;
    }

    // closes the connection to the database, executed at the end of a query
    public void close() throws SQLException {
        conn.close();
    }

}