DerbyMarketingDAO.java
package durbodax.daos;
import durbodax.dtos.*;
import java.util.*;
import java.sql.*;
/**
* DerbyMarketingDAO concrete implementation of the MarketingDAO
* @author gbeckenbaugh
*/
public class DerbyMarketingDAO implements MarketingDAO {
private Connection _conn = null;
/**
* fillMarketingStats populates the MarketingResponseDTO from the ResultSet
* @param rsIn ResultSet
* @return MarketingResponseDTO
*/
private MarketingResponseDTO fillMarketingStats (ResultSet rsIn){
MarketingResponseDTO m = new MarketingResponseDTO();
try{
m.setAge(rsIn.getInt(4));
m.setAgeDesc(rsIn.getString(5));
m.setBirthPlace(rsIn.getString(6));
m.setCount(rsIn.getInt(8));
m.setId(rsIn.getInt(1));
m.setIncome(rsIn.getInt(2));
m.setIncomeDesc(rsIn.getString(3));
m.setMaritalStatus(rsIn.getInt(7));
}
catch(SQLException e){
System.err.println("fillMarketingStats Failed: " + e.toString());
}
return m;
}
/**
* selectMarketingStatsTopBottom retrieves customers from the ipums DB based
* on the parmaeters in MarketingRequestDTO
* @param mReqIn MarketingRequestDTO
* @return ArrayList
*/
public ArrayList<MarketingResponseDTO> selectMarketingStatsTopBottom(MarketingRequestDTO mReqIn){
ArrayList<MarketingResponseDTO> mArray = new ArrayList<MarketingResponseDTO>();
ResultSet rs;
Statement s;
Connection conn = DerbyDAOFactory.getConnection();
MarketingResponseDTO m = new MarketingResponseDTO();
String selectQuery =
"SELECT MAIN.ID, FTOTINC, ' ', AGE, ' ', BPL.DESCRIPTION, MARST, 1 " +
"FROM MAIN " +
"INNER JOIN MARST on MAIN.MARST = MARST.ID " +
"INNER JOIN BPL ON MAIN.BPL = BPL.ID " +
"WHERE AGE BETWEEN " + Integer.toString(mReqIn.getAgeFrom()) + " AND " +
Integer.toString(mReqIn.getAgeTo()) + " AND " +
"FTOTINC BETWEEN " + Integer.toString(mReqIn.getIncomeFrom()) + " AND " +
Integer.toString(mReqIn.getIncomeTo());
if (mReqIn.getBirthPlace() != 0 ){
selectQuery = selectQuery +
" AND MAIN.BPL = " + mReqIn.getBirthPlace();
}
if (mReqIn.getMaritalStatus()!=0 ){
selectQuery = selectQuery +
" AND MARST = " + Integer.toString(mReqIn.getMaritalStatus());
}
if (mReqIn.getTopOrBottom().equalsIgnoreCase("bottom")){
selectQuery = selectQuery + " ORDER BY FTOTINC FETCH FIRST " +
Integer.toString(mReqIn.getGroupSize()) + " ROWS ONLY";
}
else{
selectQuery = selectQuery + " ORDER BY FTOTINC DESC FETCH FIRST " +
Integer.toString(mReqIn.getGroupSize()) + " ROWS ONLY";
}
try{
s = conn.createStatement();
rs = s.executeQuery(selectQuery);
while (rs.next()) {
m = fillMarketingStats(rs);
mArray.add(m);
}
}
catch(SQLException e){
System.err.println("Select Failed: " + e.toString());
}
return mArray;
}
/**
* selectMarketingStatsByGropu retrieves customer counts from the ipums DB
* based on the parmaeters in MarketingRequestDTO
* @param mReqIn MarketingRequestDTO
* @return ArrayList
*/
public ArrayList<MarketingResponseDTO> selectMarketingStatsByGroup(MarketingRequestDTO mReqIn){
ArrayList<MarketingResponseDTO> mArray = new ArrayList<MarketingResponseDTO>();
ResultSet rs;
Statement s;
Connection conn = DerbyDAOFactory.getConnection();
MarketingResponseDTO m = new MarketingResponseDTO();
String incomeDesc = Integer.toString(mReqIn.getIncomeFrom()) + "-" +
Integer.toString(mReqIn.getIncomeTo());
String ageDesc = Integer.toString(mReqIn.getAgeFrom()) + "-" +
Integer.toString(mReqIn.getAgeTo());
String selectQuery =
"SELECT 1, 0, '" + incomeDesc + "',0,'" + ageDesc + "', BPL.DESCRIPTION, MARST, COUNT(*) " +
"FROM MAIN " +
"INNER JOIN MARST on MAIN.MARST = MARST.ID " +
"INNER JOIN BPL ON MAIN.BPL = BPL.ID " +
"WHERE AGE BETWEEN " + Integer.toString(mReqIn.getAgeFrom()) + " AND " +
Integer.toString(mReqIn.getAgeTo()) + " AND " +
"FTOTINC BETWEEN " + Integer.toString(mReqIn.getIncomeFrom()) + " AND " +
Integer.toString(mReqIn.getIncomeTo());
if (mReqIn.getBirthPlace() != 0 ){
selectQuery = selectQuery +
" AND MAIN.BPL = " + mReqIn.getBirthPlace();
}
if (mReqIn.getMaritalStatus()!=0 ){
selectQuery = selectQuery +
" AND MARST = " + Integer.toString(mReqIn.getMaritalStatus());
}
selectQuery = selectQuery +
" GROUP BY MARST, BPL.DESCRIPTION";
if (mReqIn.getTopOrBottom().equalsIgnoreCase("bottom")){
selectQuery = selectQuery + " ORDER BY COUNT(*) FETCH FIRST " +
Integer.toString(mReqIn.getGroupSize()) + " ROWS ONLY";
}
else{
selectQuery = selectQuery + " ORDER BY COUNT(*) DESC FETCH FIRST " +
Integer.toString(mReqIn.getGroupSize()) + " ROWS ONLY";
}
try{
s = conn.createStatement();
rs = s.executeQuery(selectQuery);
while (rs.next()) {
m = fillMarketingStats(rs);
mArray.add(m);
}
}
catch(SQLException e){
System.err.println("Select Failed: " + e.toString());
}
return mArray;
}
}