DerbyCustomerDAO.java

package durbodax.daos;

import java.sql.*;

import tada.TaDaMethod;
import durbodax.customers.*;

/**
 * DerbyCustomerDAO Derby specific implemenation to retrieve customer data from ipums db
 * @author gbeckenbaugh
 */
public class DerbyCustomerDAO implements CustomerDAO {

    private Connection _conn = null;
    final static String queryStatement =
        "SELECT MAIN.ID,YEARR,GQ,GQTYPE,FARM,OWNERSHP,VALUE,RENT,FTOTINC,NFAMS,NCOUPLES,NMOTHERS," + //12 fields
        "NFATHERS,MOMLOC,STEPMOM,MOMRULE,POPLOC,STEPPOP,POPRULE,SPLOC,SPRULE,FAMSIZE,NCHILD,NCHLT5," + //24 fields
        "FAMUNIT,ELDCH,YNGCH,NSIBS,RELATE,AGE,SEX,RACE,MARST,CHBORN,BPL,SCHOOL,EDUCREC,SCHLTYPE,EMPSTAT," + //39 fields
        "LABFORCE,OCC1950,OCCSCORE,SEI,IND1950,CLASSWKR,WKSWORK2,HRSWORK2,YRLASTWK,WORKEDYR,INCTOT," + //50 fields
        "INCWAGE,INCBUS,INCFARM,INCSS,INCWELFR,INCOTHER,POVERTY,MIGRATE5,MIGPLAC5,MOVEDIN,VETSTAT,TRANWORK," + //62 fields
        "BPL.DESCRIPTION,CHBORN.DESCRIPTION,OCC1950.DESCRIPTION,MIGPLAC5.DESCRIPTION " + //66 fields
        "FROM MAIN " +
        "INNER JOIN BPL ON MAIN.BPL=BPL.ID " +
        "INNER JOIN CHBORN ON MAIN.CHBORN=CHBORN.ID " +
        "INNER JOIN OCC1950 ON MAIN.OCC1950=OCC1950.ID " +
        "INNER JOIN MIGPLAC5 ON MAIN.MIGPLAC5=MIGPLAC5.ID ";

    /**
     * fillCustomer populates the Customer Object from the Resultset
     * @param rsIn ResultSet
     * @return Customer
     */
    private Customer fillCustomer (ResultSet rsIn){

        Customer c = new Customer();

        try{
        	System.out.println("flow:410");
                   
            c.setAge(rsIn.getInt("AGE"));                        //AGE
            c.setAgeOfEldestChild(rsIn.getInt("ELDCH"));           //ELDCH
            c.setAgeOfYoungestChild(rsIn.getInt("YNGCH"));         //YNGCH
            c.setBirthplace(rsIn.getString(63));              //BPL.DESCRIPTION
            c.setChildBorn(rsIn.getString(64));               //CHBORN.DESCRIPTION
            c.setEducationLevel(rsIn.getInt("EDUCREC"));             //EDUCREC
            c.setEmploymentStatus(rsIn.getInt("EMPSTAT"));           //EMPSTAT
            c.setFamilyMembership(rsIn.getInt("FAMUNIT"));           //FAMUNIT
            c.setFarmIncome(rsIn.getInt("INCFARM"));                 //INCFARM
            c.setFarmStatus(rsIn.getInt("FARM"));                  //FARM
            c.setFathersLocationInHouse(rsIn.getInt("POPLOC"));     //POPLOC
            c.setGender(rsIn.getInt("SEX"));                     //SEX
            c.setId(rsIn.getInt(1));                          //MAIN.ID
            c.setIndustry1950Basis(rsIn.getInt("IND1950"));          //IND1950
            c.setLaborForce(rsIn.getInt("LABFORCE"));                 //LABFORCE
            c.setLastYearWorked(rsIn.getInt("YRLASTWK"));             //YRLASTWK
            c.setLinkToFather(rsIn.getInt("POPRULE"));               //POPRULE
            c.setLinkToMother(rsIn.getInt("MOMRULE"));               //MOMRULE
            c.setLinkToSpouse(rsIn.getInt("SPRULE"));               //SPRULE
            c.setMaritalStatus(rsIn.getInt("MARST"));              //MARST
            c.setMothersLocationInHouse(rsIn.getInt("MOMLOC"));     //MOMLOC
            c.setMovedInCode(rsIn.getInt("MOVEDIN"));                //MOVEDIN
            c.setNonFarmBusinessIncome(rsIn.getInt("INCBUS"));      //INCBUS
            c.setNumChildenUnderFiveInHouse(rsIn.getInt("NCHLT5")); //NCHLT5
            c.setNumChildrenInHouse(rsIn.getInt("NCHILD"));         //NCHILD
            c.setNumCouplesInHouse(rsIn.getInt("NCOUPLES"));          //NCOUPLES
            c.setNumFamiliesInHouse(rsIn.getInt("NFAMS"));         //NFAMS
            c.setNumFamilyMembersInHouse(rsIn.getInt("FAMSIZE"));    //FAMSIZE
            c.setNumFathersInHouse(rsIn.getInt("NFATHERS"));          //NFATHERS
            c.setNumMothersInHouse(rsIn.getInt("NMOTHERS"));          //NMOTHERS
            c.setNumSiblingsInHouse(rsIn.getInt("NSIBS"));         //NSIBS
            c.setOccupation(rsIn.getString(65));              //OCC1950.DESCRIPTION
            c.setOccupation1950Basis(rsIn.getInt("OCC1950"));        //OCC1950
            c.setOccupationIncomeScore(rsIn.getInt("OCCSCORE"));      //OCCSCORE
            c.setOtherIncome(rsIn.getInt("INCOTHER"));                //INCOTHER
            c.setOwnership(rsIn.getInt("OWNERSHP"));                   //OWNERSHP
            c.setPovertyStatus(rsIn.getInt("POVERTY"));              //POVERTY
            c.setProbableStepFather(rsIn.getInt("STEPPOP"));         //STEPPOP
            c.setProbableStepMother(rsIn.getInt("STEPMOM"));         //STEPMOM
            c.setRace(rsIn.getInt("RACE"));                       //RACE
            c.setRelationshipWHouseholder(rsIn.getInt("RELATE"));   //RELATE
            c.setRent(rsIn.getInt("RENT"));                        //RENT
            c.setResidence5YearsAgo(rsIn.getString(66));      //MIGPLAC5.DESCRIPTION
            c.setResidentialStatus(rsIn.getInt("MIGRATE5"));          //MIGRATE5
            c.setSchoolStatus(rsIn.getInt("SCHOOL"));               //SCHOOL
            c.setSchoolType(rsIn.getInt("SCHLTYPE"));                 //SCHLTYPE
            c.setSocialSecurityIncome(rsIn.getInt("INCSS"));       //INCSS
            c.setSocioEconomicIndex(rsIn.getInt("SEI"));         //SEI
            c.setSpouseLocationInHouse(rsIn.getInt("SPLOC"));      //SPLOC
            c.setTotalFamilyIncome(rsIn.getInt("FTOTINC"));           //FTOTINC
            c.setTotalPersonalIncome(rsIn.getInt("INCTOT"));        //INCTOT
            c.setValue(rsIn.getInt("VALUE"));                       //VALUE
            c.setVeteranStatus(rsIn.getInt("VETSTAT"));              //VETSTAT
            c.setWageAndSalaryIncome(rsIn.getInt("INCWAGE"));        //INCWAGE
            c.setWeeksWorkedPerYear(rsIn.getInt("WKSWORK2"));         //WKSWORK2
            c.setWelfareIncome(rsIn.getInt("INCWELFR"));              //INCWELFR
            c.setWorkClass(rsIn.getInt("CLASSWKR"));                  //CLASSWKR
            c.setWorkHours(rsIn.getInt("HRSWORK2"));                  //HRSWORK2
        }
        catch(SQLException e){
            System.out.println("fillCustomer Failed: " + e.toString());
        }

        return c;
    }
    /**
     * selectCustomer retrieves a customer from the main table
     * @param idIn integer
     * @return Customer
     */
    public Customer selectCustomer(int idIn){

        ResultSet rs;
        Statement s;
        Connection conn = DerbyDAOFactory.getConnection();
        Customer c = new Customer();
        String selectQuery = queryStatement + "WHERE MAIN.ID = " + Integer.toString(idIn);

        try{
        	System.out.println("flow:411");
            s = conn.createStatement();
            rs = s.executeQuery(selectQuery);
            boolean foundRec = rs.next();
            if (foundRec){
            	System.out.println("flow:412");
                c = fillCustomer(rs);
            } else {
            	System.out.println("flow:413");
                rs.close();
                s.close();
                return null;

            }

            rs.close();
            s.close();
        }
        catch(SQLException e){
        	System.out.println("flow:414");
            System.out.println("Select Customer Failed: " + e.toString());
        }
        return c;
    }


    /**
     * Persists customer data to the main table
     * @param idIn integer
     * @return Customer
     */
    public int saveCustomer(Customer customer){

        if(customer.getId() == -1) {
        	System.out.println("flow:415");
           return insertCustomer(customer);

        } else {
        	System.out.println("flow:416");
           return updateCustomer(customer);

        }
    }


    private int insertCustomer(Customer customer){

        Statement insert;

        Connection conn = DerbyDAOFactory.getConnection();
        int index;
        int birthplaceId;
        int residenceId;

        //get next sequential index number
        index = getIndex(conn, "MAIN");
        System.out.println("flow:417");
        if(index != -1) {
        	System.out.println("flow:418");
            birthplaceId = getBirthPlaceId(conn, customer);
            residenceId = getResidenceId(conn, customer);

            String insertSQL = "INSERT INTO MAIN " +
                "(ID,YEARR,GQ,GQTYPE,FARM,OWNERSHP," + //6
                "VALUE,RENT,FTOTINC,NFAMS,NCOUPLES," + //11
                "NMOTHERS,NFATHERS,MOMLOC,STEPMOM,MOMRULE," + //16
                "POPLOC,STEPPOP,POPRULE,SPLOC,SPRULE," + //21
                "FAMSIZE,NCHILD,NCHLT5,FAMUNIT,ELDCH," + //26
                "YNGCH,NSIBS,RELATE,AGE,SEX," + //31
                "RACE,MARST,CHBORN,BPL,SCHOOL," + //36
                "EDUCREC,SCHLTYPE,EMPSTAT,LABFORCE,OCC1950," + //41
                "OCCSCORE,SEI,IND1950,CLASSWKR,WKSWORK2," + //46
                "HRSWORK2,YRLASTWK,INCTOT,INCWAGE," + //50
                "INCBUS,INCFARM,INCSS,INCWELFR,INCOTHER," + //55
                "POVERTY,MIGRATE5,MIGPLAC5,MOVEDIN,VETSTAT," + //60
                "TRANWORK)" + //61
                "VALUES" +
                "(" + Integer.toString(index) + ",99,1,0," +  //4
                Integer.toString(customer.getFarmStatus()) + "," +
                Integer.toString(customer.getOwnership()) + "," + //6

                Integer.toString(customer.getValue()) +  "," +
                Integer.toString(customer.getRent()) + "," +
                Integer.toString(customer.getTotalFamilyIncome()) + "," +
                Integer.toString(customer.getNumFamiliesInHouse()) + "," +
                Integer.toString(customer.getNumCouplesInHouse()) + "," + //11

                Integer.toString(customer.getNumMothersInHouse()) + "," +
                Integer.toString(customer.getNumFathersInHouse()) + "," +
                Integer.toString(customer.getMothersLocationInHouse()) +
                ",0," + Integer.toString(customer.getLinkToMother()) + "," + //16

                Integer.toString(customer.getFathersLocationInHouse()) + ",0," +
                Integer.toString(customer.getLinkToFather()) + "," +
                Integer.toString(customer.getSpouseLocationInHouse()) + "," +
                Integer.toString(customer.getLinkToSpouse()) + "," + //21

                Integer.toString(customer.getNumFamilyMembersInHouse()) + "," +
                Integer.toString(customer.getNumChildrenInHouse()) + "," +
                Integer.toString(customer.getNumChildenUnderFiveInHouse()) + "," +
                Integer.toString(customer.getFamilyMembership()) + "," +
                Integer.toString(customer.getAgeOfEldestChild()) + "," + //26

                Integer.toString(customer.getAgeOfYoungestChild()) + "," +
                Integer.toString(customer.getNumSiblingsInHouse()) + "," +
                Integer.toString(customer.getRelationshipWHouseholder()) + "," +
                Integer.toString(customer.getAge()) + "," +
                Integer.toString(customer.getGender()) + "," + //31

                Integer.toString(customer.getRace()) + "," +
                Integer.toString(customer.getMaritalStatus()) + ",0," +
                Integer.toString(birthplaceId) + "," +
                Integer.toString(customer.getSchoolStatus()) + "," + //36

                Integer.toString(customer.getEducationLevel()) + "," +
                Integer.toString(customer.getSchoolType()) + "," +
                Integer.toString(customer.getEmploymentStatus()) + "," +
                Integer.toString(customer.getLaborForce()) + ",0," + //41

                Integer.toString(customer.getOccupationIncomeScore()) + "," +
                Integer.toString(customer.getSocioEconomicIndex()) + ",999," +
                Integer.toString(customer.getWorkClass()) + "," +
                Integer.toString(customer.getWeeksWorkedPerYear()) + "," + //46

                Integer.toString(customer.getWorkHours()) + "," +
                Integer.toString(customer.getLastYearWorked()) + "," +
                Integer.toString(customer.getTotalPersonalIncome()) + "," +
                Integer.toString(customer.getWageAndSalaryIncome()) + "," +
                Integer.toString(customer.getNonFarmBusinessIncome()) + "," +
                Integer.toString(customer.getFarmIncome()) + "," + //52

                Integer.toString(customer.getSocialSecurityIncome()) + "," +
                Integer.toString(customer.getWelfareIncome()) + "," +
                Integer.toString(customer.getOtherIncome())+ "," + //55

                Integer.toString(customer.getPovertyStatus()) + "," +
                Integer.toString(customer.getResidentialStatus()) + "," +
                Integer.toString(residenceId) + "," +
                Integer.toString(customer.getMovedInCode()) + "," +
                Integer.toString(customer.getVeteranStatus()) + ",0)"; //61
            
            try {
            	System.out.println("flow:419");
                insert = conn.createStatement();
                insert.execute(insertSQL);

                insert.close();
        }
        catch(SQLException e){
        	System.out.println("flow:420");
            System.out.println("Insert Customer Failed: " + e.toString());
        }

        }

        return index;


    }

    private int updateCustomer(Customer customer){

         Statement update;
         System.out.println("flow:421");
        Connection conn = DerbyDAOFactory.getConnection();
        int index;
        int birthplaceId;
        int residenceId;

        //get next sequential index number
        index = customer.getId();

        if(index != -1) {

            birthplaceId = getBirthPlaceId(conn, customer);
            residenceId = getResidenceId(conn, customer);

                
            String updateSQL = "UPDATE MAIN " +
                "SET FARM = " + Integer.toString(customer.getFarmStatus()) + "," +
                "OWNERSHP =" + Integer.toString(customer.getOwnership()) + "," +
                
                "VALUE =" + Integer.toString(customer.getValue()) +  "," +
                "rent =" + Integer.toString(customer.getRent()) + "," +
                "FTOTINC =" + Integer.toString(customer.getTotalFamilyIncome()) + "," +
                "NFAMS =" + Integer.toString(customer.getNumFamiliesInHouse()) + "," +
                "NCOUPLES =" + Integer.toString(customer.getNumCouplesInHouse()) + "," +

                "NMOTHERS =" + Integer.toString(customer.getNumMothersInHouse()) + "," +
                "NFATHERS =" + Integer.toString(customer.getNumFathersInHouse()) + "," +
                "MOMLOC =" + Integer.toString(customer.getMothersLocationInHouse()) + "," +
                "MOMRULE =" + Integer.toString(customer.getLinkToMother()) + "," +

                "POPLOC =" + Integer.toString(customer.getFathersLocationInHouse()) + "," +
                "POPRULE =" + Integer.toString(customer.getLinkToFather()) + "," +
                "SPLOC =" + Integer.toString(customer.getSpouseLocationInHouse()) + "," +
                "SPRULE =" + Integer.toString(customer.getLinkToSpouse()) + "," +

                "FAMSIZE =" + Integer.toString(customer.getNumFamilyMembersInHouse()) + "," +
                "NCHILD =" + Integer.toString(customer.getNumChildrenInHouse()) + "," +
                "NCHLT5 =" + Integer.toString(customer.getNumChildenUnderFiveInHouse()) + "," +
                "FAMUNIT =" + Integer.toString(customer.getFamilyMembership()) + "," +
                "ELDCH =" + Integer.toString(customer.getAgeOfEldestChild()) + "," +

                "YNGCH =" + Integer.toString(customer.getAgeOfYoungestChild()) + "," +
                "NSIBS =" + Integer.toString(customer.getNumSiblingsInHouse()) + "," +
                "RELATE =" + Integer.toString(customer.getRelationshipWHouseholder()) + "," +
                "AGE =" + Integer.toString(customer.getAge()) + "," +
                "SEX =" + Integer.toString(customer.getGender()) + "," +

                "RACE =" + Integer.toString(customer.getRace()) + "," +
                "MARST =" + Integer.toString(customer.getMaritalStatus()) + "," +
                "BPL =" + Integer.toString(birthplaceId) + "," +
                "SCHOOL =" + Integer.toString(customer.getSchoolStatus()) + "," +

                "EDUCREC =" + Integer.toString(customer.getEducationLevel()) + "," +
                "SCHLTYPE =" + Integer.toString(customer.getSchoolType()) + "," +
                "EMPSTAT =" + Integer.toString(customer.getEmploymentStatus()) + "," +
                "LABFORCE =" + Integer.toString(customer.getLaborForce()) + "," +

                "OCCSCORE =" + Integer.toString(customer.getOccupationIncomeScore()) + "," +
                "SEI =" + Integer.toString(customer.getSocioEconomicIndex()) + "," +
                "CLASSWKR =" + Integer.toString(customer.getWorkClass()) + "," +
                "WKSWORK2 =" + Integer.toString(customer.getWeeksWorkedPerYear()) + "," +

                "HRSWORK2 =" + Integer.toString(customer.getWorkHours()) + "," +
                "YRLASTWK =" + Integer.toString(customer.getLastYearWorked()) + "," +
                "INCTOT =" + Integer.toString(customer.getTotalPersonalIncome()) + "," +
                "INCWAGE =" + Integer.toString(customer.getWageAndSalaryIncome()) + "," +
                "INCBUS =" + Integer.toString(customer.getNonFarmBusinessIncome()) + "," +
                "INCFARM =" + Integer.toString(customer.getFarmIncome()) + "," +

                "INCSS =" + Integer.toString(customer.getSocialSecurityIncome()) + "," +
                "INCWELFR =" + Integer.toString(customer.getWelfareIncome()) + "," +
                "INCOTHER =" + Integer.toString(customer.getOtherIncome())+ "," +

                "POVERTY =" + Integer.toString(customer.getPovertyStatus()) + "," +
                "MIGRATE5 =" + Integer.toString(customer.getResidentialStatus()) + "," +
                "MIGPLAC5 =" + Integer.toString(residenceId) + "," +
                "MOVEDIN =" + Integer.toString(customer.getMovedInCode()) + "," +
                "VETSTAT =" + Integer.toString(customer.getVeteranStatus()) +
                "WHERE ID =" + Integer.toString(index);
            
            try {
            	System.out.println("flow:422");
                update = conn.createStatement();
                update.execute(updateSQL);

                update.close();
        }
        catch(SQLException e){
        	System.out.println("flow:423");
        	System.out.println("flow:");
            System.out.println("Update Customer Failed: " + e.toString());
        }

        }

        return index;

    }


    private int getIndex(Connection conn, String table) {

        ResultSet result;
        Statement getIndex;

        int index = -1;

        String selectSQL = "select COUNT(*) from " + table;

        try {
        	System.out.println("flow:424");

            getIndex = conn.createStatement();
            result = getIndex.executeQuery(selectSQL);

            if(result.next()) {
            	System.out.println("flow:425");
                index = result.getInt(1)+1;
                //System.out.println("New index for table " + table + " is " + Integer.toString(index));
            }

            result.close();
            getIndex.close();

        }
        catch(SQLException e){
        	System.out.println("flow:426");
        	System.out.println("Unable to retrieve next sequential index value: " + e.toString());
        }

        return index;

    }


    @TaDaMethod(variablesToTrack = "birthplaceId", correspondingDatabaseAttribute = "BPL.ID")
    private int getBirthPlaceId(Connection conn, Customer customer) {

        ResultSet result;
        Statement findBirthplace;

        int birthplaceId = -1;

        try {
        	System.out.println("flow:427");

                //see if birthplace exists, if not add it
                String birthplaceLookupSQL = "select * from BPL WHERE UPPER(DESCRIPTION) ='" + customer.getBirthplace().trim().toUpperCase() + "'";

                findBirthplace = conn.createStatement();
                result = findBirthplace.executeQuery(birthplaceLookupSQL);

                if(result.next()) {
                	System.out.println("flow:428");
                    //birthplace found
                    birthplaceId = result.getInt("ID");

                } else {
                	System.out.println("flow:429");
                    //need to add it to table
                    birthplaceId = getNewBirthplaceId(conn,customer);

                }

                result.close();
                findBirthplace.close();

            } catch(SQLException e){
            	System.out.println("flow:430");
                System.out.println("Error during birthplace lookup: " + e.toString());
                
             } catch(Exception e) {
            	 System.out.println("flow:431");
                 System.out.println(e.getMessage());
                 System.out.println(e.getStackTrace());

             }

        return birthplaceId;
    }


    @TaDaMethod(variablesToTrack = "residenceId", correspondingDatabaseAttribute = "MIGPLAC5.ID")
    private int getResidenceId(Connection conn, Customer customer) {

        ResultSet result;
        Statement findResidence;

        int residenceId = -1;
        System.out.println("flow:432");
        try {
        	System.out.println("flow:433");
            String residenceLookupSQL = "select * from MIGPLAC5 WHERE UPPER(DESCRIPTION) ='" + customer.getResidence5YearsAgo().trim().toUpperCase() + "'";

                findResidence = conn.createStatement();
                result = findResidence.executeQuery(residenceLookupSQL);

                if(result.next()) {
                	System.out.println("flow:434");
                    //birthplace found
                    residenceId = result.getInt("ID");

                } else {
                	System.out.println("flow:435");

                    residenceId = getNewResidenceId(conn,customer);

                }

                result.close();
                findResidence.close();

            } catch(SQLException e){
            	System.out.println("flow:436");
                System.out.println("Error during residence lookup: " + e.toString());
                
             } catch(Exception e) {
            	 System.out.println("flow:437");

                 System.out.println(e.getMessage());
                 System.out.println(e.getStackTrace());

             }

         return residenceId;

    }

    private int getNewResidenceId(Connection conn, Customer customer) {

        Statement insertResidence;

        int index = 999 + getIndex(conn, "MIGPLAC5");

        String residenceInsertSQL = "INSERT INTO MIGPLAC5 (ID,DESCRIPTION) VALUES (" + Integer.toString(index) + ",'" + customer.getResidence5YearsAgo() + "')";
        System.out.println("flow:438");
            try {
            	System.out.println("flow:439");
                insertResidence = conn.createStatement();
                insertResidence.execute(residenceInsertSQL);

                insertResidence.close();

            } catch(SQLException e){
            	System.out.println("flow:440");
                System.out.println("Error during residence lookup: " + e.toString());
             }

         return index;

    }

    private int getNewBirthplaceId(Connection conn, Customer customer) {

        Statement insertBirthplace;

        int index = 999 + getIndex(conn, "BPL");
        System.out.println("flow:441");
        String birthplaceInsertSQL = "INSERT INTO BPL (ID,DESCRIPTION) VALUES (" + Integer.toString(index) + ",'" + customer.getBirthplace() + "')";

            try {
            	System.out.println("flow:442");

                insertBirthplace = conn.createStatement();
                insertBirthplace.execute(birthplaceInsertSQL);

                insertBirthplace.close();

            } catch(SQLException e){
            	System.out.println("flow:443");
                System.out.println("Error during residence lookup: " + e.toString());
             }

         return index;

    }

}