Question

Need help with the following: [i'm using SQL Server 2008]

Create a function that accepts a zipcode string (5 characters) as input and returns 1 if thw zipcode is in the zipcode table and 0 if it is not . also create a test file that demonstrates using the function.

Answer 1

I am assuming that the table that contains zipcode information is "location_info" table. Please replace the username, password and databaseName in the defined static variables.

public class TestSQL {
  
    public static void main(String args[]) throws SQLException {
        LocationInfo tsql = new LocationInfo();
        tsql.doesZipCodeExists("12345");
    }
}

class LocationInfo {
  
    public final static String databaseName = "databaseName";
    public final static String userName = "user";
    public final static String password = "pass";
  

    public int doesZipCodeExists(String zipCode) throws SQLException {
        Statement statement = connectToDB(databaseName, userName, pass);
        ResultSet result = statement.executeQuery("SELECT count(*) as occurences FROM location_info_table WHERE zip_code = '"+zipCode+"';");

        int isZipCodeAvailable = 0;
        if (result.next()) {
            int count = result.getInt("occurencces");
            if (count >= 1) {
                isZipCodeAvailable = 1;
            }
        }
        result.close();
        statement.close();
        return isZipCodeAvailable;
    }
  
  
    public Statement connectToDB(String database, String user, String password) {
        try {

      String url = "jdbc:sqlserver://localhost:1443;databaseName="+database;
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
      Connection con = DriverManager.getConnection(url, user, password);

            return con.createStatement();
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println(e);
        }
        return null;
    }
}