Q3 Verification Script - New

package edu.stonybrook.pnarendra.todbsassignment;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class SalaryStdDev {

    public static void main(String[] args) {

        String url = "jdbc:db2://localhost:50000/";
        try {
            url = url + "SAMPLE";
            String username = "db2inst1";
            String password = "password";
            Connection con;
            Statement stmt;
            ResultSet rs;
            Connection con2;
            Statement stmt2;
            ResultSet rs2;
            Connection con3;
            Statement stmt3;
            ResultSet rs3;
            Connection con4;
            Statement stmt4;
            ResultSet rs4;
            Class.forName("com.ibm.db2.jcc.DB2Driver");
            con = DriverManager.getConnection(url, username, password);
            stmt = con.createStatement();
            con2 = DriverManager.getConnection(url, username, password);
            stmt2 = con.createStatement();
            con3 = DriverManager.getConnection(url, username, password);
            stmt3 = con.createStatement();
            con4 = DriverManager.getConnection(url, username, password);
            stmt4 = con.createStatement();
            List<String> vv = new ArrayList<String>();
            int c = 0;
            rs = stmt.executeQuery("SELECT DISTINCT SUBSTR(ZIPCODE,1,5) FROM CSE532.FACILITY");
            while(rs.next()) {
                vv.add(rs.getString(1));
            }
            for(String zip : vv) {
                //Check if ZIP exists in USZIP Table
                rs2 = stmt2.executeQuery("SELECT COUNT(*) FROM CSE532.USZIP WHERE SUBSTR(CSE532.USZIP.GEOID10,1,5) = '"+zip+"'");
                rs2.next();
                if(rs2.getInt(1) == 0) {
                    System.out.println("ZIP - " + zip+" - No Shape Info. Ignored");
                }else {
                    //Get neighbor zips + itself
                    boolean hasER = false;
                    rs3 = stmt3.executeQuery("SELECT DISTINCT(GEOID10) FROM CSE532.USZIP WHERE db2gse.st_intersects(SHAPE, (SELECT SHAPE FROM CSE532.USZIP WHERE substr(GEOID10,1,5) = '"+ zip +"')) = 1"); //AND SUBSTR(GEOID10,1,5) IN  (SELECT DISTINCT SUBSTR(ZIPCODE,1,5) FROM CSE532.FACILITY)
                    while(rs3.next()) {
                        rs4 = stmt4.executeQuery("SELECT COUNT(*) FROM CSE532.FACILITY INNER JOIN CSE532.FACILITYCERTIFICATION ON CSE532.FACILITY.FACILITYID = CSE532.FACILITYCERTIFICATION.FACILITYID WHERE CSE532.FACILITYCERTIFICATION.ATTRIBUTEVALUE = 'Emergency Department' AND SUBSTR(CSE532.FACILITY.ZIPCODE,1,5) = '"+rs3.getString(1)+"'");
                        rs4.next();
                        if(rs4.getInt(1) != 0) {
                            System.out.println("ZIP - "+ zip+", Neighbor - "+rs3.getString(1)+"- Found an ER. ZIP No longer considered ");
                            hasER = true;
                            break;
                        }
                    }
                    if(hasER == false) {
                        System.out.println("VERIFIED - "+zip);
                        c++;
                    }
                }

            }
            System.out.println(c);


        } catch (ClassNotFoundException e) {
            System.out.println("Driver not found!");
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("Unable to establish connection to - " + url + " with given credentials - ");
            e.printStackTrace();
        }

    }

}