package com.cin.test.util;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.cin.dto.UserDTO;
public class UserTable extends Table {
public UserTable(Connection connection) {
super("userrecord", connection);
}
public void init() throws SQLException {
Statement stmt = connection.createStatement();
try{
stmt.execute("insert into userrecord (NAME, ZIP, SSN, AGE, SEX, MARITAL, RACE, TAXSTAT, DETAIL, HOUSEHOLDDETAIL, FATHERORIGIN, MOTHERORIGIN, BIRTHCOUNTRY, CITIZENSHIP) values ('GISH, CHAUNCEY', '12303', 10, 11, ' Male', ' Never married', ' White', ' Nonfiler', ' Grandchild <18 never marr child of subfamily RP', ' Other relative of householder', ' United-States', ' United-States', ' United-States', ' Native- Born in the United States')");
stmt.execute("insert into userrecord (NAME, ZIP, SSN, AGE, SEX, MARITAL, RACE, TAXSTAT, DETAIL, HOUSEHOLDDETAIL, FATHERORIGIN, MOTHERORIGIN, BIRTHCOUNTRY, CITIZENSHIP) values ('KALISCH, DION', '12301', 11, 38, ' Male', ' Never married', ' White', ' Single', ' Nonfamily householder', ' Householder', ' United-States', ' United-States', ' United-States', ' Native- Born in the United States')");
stmt.execute("insert into userrecord (NAME, ZIP, SSN, AGE, SEX, MARITAL, RACE, TAXSTAT, DETAIL, HOUSEHOLDDETAIL, FATHERORIGIN, MOTHERORIGIN, BIRTHCOUNTRY, CITIZENSHIP) values ('WITCHARD, DELORSE', '12302', 12, 38, ' Male', ' Never married', ' White', ' Single', ' Nonfamily householder', ' Householder', ' United-States', ' United-States', ' United-States', ' Native- Born in the United States')");
stmt.execute("insert into userrecord (NAME, ZIP, SSN, AGE, SEX, MARITAL, RACE, TAXSTAT, DETAIL, HOUSEHOLDDETAIL, FATHERORIGIN, MOTHERORIGIN, BIRTHCOUNTRY, CITIZENSHIP) values ('RINGELSPAUGH, JEROLD', '12301', 13, 6, ' Male', ' Never married', ' White', ' Nonfiler', ' Child <18 never marr not in subfamily', ' Child under 18 never married', ' United-States', ' United-States', ' United-States', ' Native- Born in the United States')");
stmt.execute("insert into userrecord (NAME, ZIP, SSN, AGE, SEX, MARITAL, RACE, TAXSTAT, DETAIL, HOUSEHOLDDETAIL, FATHERORIGIN, MOTHERORIGIN, BIRTHCOUNTRY, CITIZENSHIP) values ('FUEHRER, ALYSE', '12301', 14, 30, ' Female', ' Never married', ' White', ' Single', ' Nonfamily householder', ' Householder', ' United-States', ' United-States', ' United-States', ' Native- Born in the United States')");
stmt.execute("insert into userrecord (NAME, ZIP, SSN, AGE, SEX, MARITAL, RACE, TAXSTAT, DETAIL, HOUSEHOLDDETAIL, FATHERORIGIN, MOTHERORIGIN, BIRTHCOUNTRY, CITIZENSHIP) values ('ASKLAND, SANDY', '12301', 15, 31, ' Male', ' Never married', ' White', ' Single', ' Secondary individual', ' Nonrelative of householder', ' United-States', ' United-States', ' Germany', ' Native- Born abroad of American Parent(s)')");
stmt.execute("insert into userrecord (NAME, ZIP, SSN, AGE, SEX, MARITAL, RACE, TAXSTAT, DETAIL, HOUSEHOLDDETAIL, FATHERORIGIN, MOTHERORIGIN, BIRTHCOUNTRY, CITIZENSHIP) values ('OGLE, ANTONIO', '12302', 16, 64, ' Female', ' Married-civilian spouse present', ' White', ' Joint one under 65 & one 65+', ' Spouse of householder', ' Spouse of householder', ' United-States', ' United-States', ' United-States', ' Native- Born in the United States')");
stmt.execute("insert into userrecord (NAME, ZIP, SSN, AGE, SEX, MARITAL, RACE, TAXSTAT, DETAIL, HOUSEHOLDDETAIL, FATHERORIGIN, MOTHERORIGIN, BIRTHCOUNTRY, CITIZENSHIP) values ('VILLAREAL, WALLACE', '12302', 17, 46, ' Male', ' Married-civilian spouse present', ' Amer Indian Aleut or Eskimo', ' Joint both under 65', ' Householder', ' Householder', ' United-States', ' United-States', ' United-States', ' Native- Born in the United States')");
stmt.execute("insert into userrecord (NAME, ZIP, SSN, AGE, SEX, MARITAL, RACE, TAXSTAT, DETAIL, HOUSEHOLDDETAIL, FATHERORIGIN, MOTHERORIGIN, BIRTHCOUNTRY, CITIZENSHIP) values ('SHORES, JAMISON', null, 18, 58, ' Male', ' Married-civilian spouse present', ' White', ' Joint both under 65', ' Householder', ' Householder', ' United-States', ' United-States', ' United-States', ' Native- Born in the United States')");
// user 18 has no data for job, education, ...
}finally{
stmt.close();
}
}
public UserDTO getRow(int ssn) throws SQLException{
Statement stmt = connection.createStatement();
try{
stmt.execute("select NAME, ZIP, SSN, AGE, SEX, MARITAL, RACE, TAXSTAT, DETAIL, HOUSEHOLDDETAIL, FATHERORIGIN, MOTHERORIGIN, BIRTHCOUNTRY, CITIZENSHIP from userrecord where ssn="+ssn);
ResultSet rs = stmt.getResultSet();
if( rs.next() ){
UserDTO user = new UserDTO();
user.setName(rs.getString(1).trim());
user.setZipCode(rs.getString(2));
user.setSsn(rs.getInt(3));
user.setAge(rs.getInt(4));
user.setSex(rs.getString(5));
user.setMarital(rs.getString(6));
user.setRace(rs.getString(7));
user.setTaxStatus(rs.getString(8));
user.setDetail(rs.getString(9));
user.setHouseholdDetail(rs.getString(10));
user.setFatherOrigin(rs.getString(11));
user.setMotherOrigin(rs.getString(12));
user.setBirthCountry(rs.getString(13));
user.setCitizenship(rs.getString(14));
return user;
} else {
return null;
}
}finally{
stmt.close();
}
}
}