The bank now needs your help in deciphering whom from its records should be exlusive to premium loans versus those offered micro or less premium loans.

Use a database to store then present Loan analysis information from your data BankRecords objects you worked on in prior labs.

For this lab you will continue using your current project folder created for labs 2 & 3 and create a new file called Dao where Dao stands for Data Access Object. This will allow for CRUD database connectivity and operations. Create also a file called LoanProcessing as well which will act as a driver file (i.e., includes main function) to call your database CRUD methods and create some resulting output.

To start working with a database you will need a JDBC driver to allow for any connectivity within your app.

To include a driver for your MySQL database, you need to create a folder called libs within your project. The MySQL driver for JDBC connections packages in the form of a jar file which you can download here:

You will find a JDBC driver for connecting to a MySQL database located here:

https://dev.mysql.com/downloads/connector/j/

Depending on your OS you will need to download a windows zip file or a tar file if you are a Mac/Linux user. Unzip or untar the DOWNLOADED file and locate the needed .jar file and copy it into into your libs folder.

Make sure to include the jar file in your Build Path by right clicking on your project folder and then choose Build Path > Configure Build Path....

Once the dialog box opens make sure to click on the Libraries tab then click Add JARs

Drill down to the libs folder within your project and then choose the jar file to add.

Click OK to commit.

Start coding your Dao class file. Include the following methods for your file.

-A method to create a database table called createTable. createTable merely creates a table when called. Include an id field, an income field and a pep field when building your table setup.

[ Note when creating a table it is IMPERATIVE to include the following name:

yourFirstinitial_First4LettersOfYourLastName_tab ]

-A method to insert records called insertRecords().

-A method to retrieve records for display called retrieveRecords().

In order to communicate or connect with your database (which will be located on the papaserver) for CRUD operations, youll need to include certain credentials. Use the data below for establishing a JDBC connection to the papaserver.

url = "jdbc:mysql://www.papademas.net:3306/411labs?autoReconnect=true&useSSL=false";

username = "db411";

password = "411";

Methods breakdown

insertRecords(BankRecords [] arrayName) will allow for the array of BankRecord objects, to be passed to your method which will allow for the insertion of all the id, income and pep data from your BankRecords array (or whatever you named it) into your database table when called. Make sure then to extend your Dao class to include your BankRecords class to allow data to be obtained.

retrieveRecords() will return a ResultSet object used for creating output. Have the result set contain the record data consisting of the id, income and pep fields. Sort the pep field in descending order to allow for premium loan candidates to appear first in the record set for reporting purposes (i.e., those with data values of YES). The resultset query string to build can be something like:

String sql =

"select id,income, pep from your_table_name order by pep desc";

Make sure to always close out of your connections and any statements when through with any processing!

Make sure to include error trapping using SQLException handling for all your database operations and connection logic.

Also include messages to the console when your methods trigger. Ex. Table created, Inserting records into database, etc.

Lastly code your LoanProcessing class file.

For your LoanProcessing source file extend BankRecords.

Include the following methods for your file.

Include in main() your readData() method call which will process your BankRecord objects. Then instantiate a Dao object and trigger your createTable() method, your insertRecords(your BankRecords array object) method and retrieveRecords() method in that order.

Once you have retrieved a recordset, print out all the records from the recordset to the console in a nice columnar format included with heading names for id, income and pep. Doesnt hurt to have a title too, like Loan Analysis Report.

Extra options

-Include SQL Prepared statements when inserting records

Client,java

public abstract class Client { abstract void readData(); abstract void processData(); abstract void printData(); }

BankRecords.java

import java.io.BufferedReader; import java.io.FileNotFoundException; import java.io.FileReader; import java.io.IOException; import java.util.ArrayList; import java.util.Arrays; import java.util.List;

public class BankRecords extends Client{

static BankRecords rObjs[]; List> array = new ArrayList>();

//declaring variables String id; int age; String sex; String region; double income; String married; int children; String car; String save_act; String current_act; String mortage; String pep;

//This method reads all the data from the given excel file and check any errors

void readData() { String Line = "";

try(BufferedReader br = new BufferedReader (new FileReader("src/bank-Detail.csv"))) { int i = 0; while((Line = br.readLine()) != null) //till end of the file { array.add(Arrays.asList(Line.split(","))); //System.out.println(array.get(i++)); } } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); }

processData(); //calling a method }

//getters and setters for all the variables public String getId() { return id; }

public void setId(String id) { this.id = id; }

public int getAge() { return age; }

public void setAge(int age) { this.age = age; }

public String getSex() { return sex; }

public void setSex(String sex) { this.sex = sex; }

public String getRegion() { return region; }

public void setRegion(String region) { this.region = region; }

public double getIncome() { return income; }

public void setIncome(double income) { this.income = income; }

public String getMarried() { return married; }

public void setMarried(String married) { this.married = married; }

public int getChildren() { return children; }

public void setChildren(int children) { this.children = children; }

public String getCar() { return car; }

public void setCar(String car) { this.car = car; }

public String getSave_act() { return save_act; }

public void setSave_act(String save_act) { this.save_act = save_act; }

public String getCurrent_act() { return current_act; }

public void setCurrent_act(String current_act) { this.current_act = current_act; }

public String getMortage() { return mortage; }

public void setMortage(String mortage) { this.mortage = mortage; }

public String getPep() { return pep; }

public void setPep(String pep) { this.pep = pep; }

//This method processes all the read data and store each piece into string spaced by ','

void processData() { int i = 0;

rObjs = new BankRecords[array.size()]; for(List row: array) { rObjs[i] = new BankRecords();

rObjs[i].setId(row.get(0)); //storing ID rObjs[i].setAge(Integer.parseInt(row.get(1))); //storing Age rObjs[i].setSex(row.get(2)); //storing Sex rObjs[i].setRegion(row.get(3)); //storing Region rObjs[i].setIncome(Double.parseDouble(row.get(4))); //storing Income rObjs[i].setMarried(row.get(5)); //storing Married rObjs[i].setChildren(Integer.parseInt(row.get(6))); //storing Children rObjs[i].setCar(row.get(7)); //storing Car rObjs[i].setSave_act(row.get(8)); //storing save_act rObjs[i].setCurrent_act(row.get(9)); //storing current_act rObjs[i].setMortage(row.get(10)); //storing Mortgage rObjs[i].setPep(row.get(11)); //storing pep i++; } //printData(); }

//printData() method prints all the data in a format

void printData() { System.out.println("ID\t\tAge\t\tSex\t\tRegion\t\tIncome\t\tMortgage" ); for (int i=0; i<=24; i++) { String Final = String.format( "%s\t\t%s\t\t%s\t\t%-10s\t%8.2f\t%s",rObjs[i].getId(),rObjs[i].getAge(),rObjs[i].getSex(), rObjs[i].getRegion(),rObjs[i].getIncome(), rObjs[i].getMortage()); System.out.println(Final); } }

public static void main(String[] args) throws IOException { BankRecords sc = new BankRecords(); sc.readData(); sc.processData(); sc.printData(); } }

Records.java

import java.io.FileWriter;

import java.io.IOException;

import java.text.SimpleDateFormat;

import java.util.Arrays;

import java.util.Calendar;

import java.text.DecimalFormat;

import javax.swing.JOptionPane;

public class Records extends BankRecords{

private static FileWriter fw = null;

public Records()

{

try

{

fw = new FileWriter("src/bankrecords.txt"); //creating new text file

}

catch(IOException e)

{

e.printStackTrace();

}

}

//This method sorts Sex and Mortgage and then count the number of females with Mortgage

private static void femaleComparator()

{

Arrays.sort(rObjs, new FemaleComparator()); //sorting defined objects

int FemaleCount = 0;

for(int i=0; i < rObjs.length; i++)

{

if(rObjs[i].getSex().equals("FEMALE") && rObjs[i].getMortage().equals("YES") && rObjs[i].getSave_act().equals("YES"))

FemaleCount++;

}

System.out.println("Number of females with mortage and a saving account: " + FemaleCount);

try

{

fw.write("Number of females with mortgage and a savings account: " + FemaleCount);

fw.write(System.getProperty( "line.separator" ));

}

catch (IOException e)

{

e.printStackTrace();

}

}

//maleComparator method count the number of males with car and 1 children per location

private static void maleComparator()

{

Arrays.sort(rObjs, new MaleComparator()); //sorting defined objects

int MaleCountInner = 0, MaleCountTown = 0, MaleCountRural = 0, MaleCountSuburban = 0; //initializing to 0

for(int i=0; i < rObjs.length; i++)

{

if(rObjs[i].getSex().equals("MALE") && rObjs[i].getRegion().equals("INNER_CITY")&& rObjs[i].getCar().equals("YES") && rObjs[i].getChildren() == 1)

MaleCountInner++;

}

System.out.println("Number of Males with car and 1 children in INNER_CITY: "+ MaleCountInner);

for(int i=0; i < rObjs.length; i++)

{

if(rObjs[i].getSex().equals("MALE") && rObjs[i].getRegion().equals("TOWN")&& rObjs[i].getCar().equals("YES") && rObjs[i].getChildren() == 1)

MaleCountTown++;

}

System.out.println("Number of Males with car and 1 childern in TOWN: " + MaleCountTown);

for(int i=0; i < rObjs.length; i++)

{

if(rObjs[i].getSex().equals("MALE") && rObjs[i].getRegion().equals("RURAL")&& rObjs[i].getCar().equals("YES") && rObjs[i].getChildren() == 1)

MaleCountRural++;

}

System.out.println("Number of Males with car and 1 children in RURAL: " + MaleCountRural);

for(int i =0; i < rObjs.length; i++)

{

if(rObjs[i].getSex().equals("MALE") && rObjs[i].getRegion().equals("SUBURBAN")&& rObjs[i].getCar().equals("YES") && rObjs[i].getChildren() == 1)

MaleCountSuburban++;

}

System.out.println("Number of Males with car and 1 children in Suburban: " + MaleCountSuburban);

try

{

fw.write("Number of Males with car and 1 children in INNER_CITY: "+ MaleCountInner);

fw.write(System.getProperty( "line.separator" ));

fw.write("Number of Males with car and 1 childern in TOWN: " + MaleCountTown);

fw.write(System.getProperty( "line.separator" ));

fw.write( "Number of Males with car and 1 children in RURAL: " + MaleCountRural);

fw.write(System.getProperty( "line.separator" ));

fw.write("Number of Males with car and 1 children in Suburban: " + MaleCountSuburban);

fw.write(System.getProperty( "line.separator" ));

}

catch (IOException e)

{

e.printStackTrace();

}

}

//This methods counts maximum and minimum age per location

private static void regionAgeComparator()

{

Arrays.sort(rObjs, new RegionAgeComparator());

int InnerCityMaxAge=0, TownMaxAge=0, RuralMaxAge=0, SuburbunMaxAge =0; //initializing to 0

for(int i = 0; i < rObjs.length; i++)

{

if(rObjs[i].getRegion().equals("INNER_CITY") && rObjs[i].getAge() > InnerCityMaxAge)

InnerCityMaxAge = rObjs[i].getAge();

}

for(int i=0; i < rObjs.length; i++)

{

if(rObjs[i].getRegion().equals("TOWN") && rObjs[i].getAge() > TownMaxAge)

TownMaxAge = rObjs[i].getAge();

}

for (int i=0; i

{

if(rObjs[i].getRegion().equals("RURAL") && rObjs[i].getAge() > RuralMaxAge)

RuralMaxAge = rObjs[i].getAge();

}

for(int i=0; i

{

if(rObjs[i].getRegion().equals("SUBURBAN") && rObjs[i].getAge() > SuburbunMaxAge)

SuburbunMaxAge = rObjs[i].getAge();

}

//System.out.println("Max Age per region: INNER_CITY : " + InnerCityMaxAge + " Town : " + TownMaxAge + " Rural : " + RuralMaxAge + " Suburban : " + SuburbunMaxAge);

Arrays.sort(rObjs, new RegionAgeComparator());

//setting min age to max age as a reference to use

int InnerCityMinAge=InnerCityMaxAge, TownMinAge=TownMaxAge, RuralMinAge=RuralMaxAge, SuburbunMinAge =SuburbunMaxAge;

for(int i = 0; i < rObjs.length; i++)

{

if(rObjs[i].getRegion().equals("INNER_CITY") && rObjs[i].getAge() < InnerCityMinAge)

InnerCityMinAge = rObjs[i].getAge();

}

for(int i=0; i < rObjs.length; i++)

{

if(rObjs[i].getRegion().equals("TOWN") && rObjs[i].getAge() < TownMinAge)

TownMinAge = rObjs[i].getAge();

}

for (int i=0; i

{

if(rObjs[i].getRegion().equals("RURAL") && rObjs[i].getAge() < RuralMinAge)

RuralMinAge = rObjs[i].getAge();

}

for(int i=0; i

{

if(rObjs[i].getRegion().equals("SUBURBAN") && rObjs[i].getAge() < SuburbunMinAge)

SuburbunMinAge = rObjs[i].getAge();

}

//System.out.println(" Min Age per region: INNER_CITY : " + InnerCityMinAge + " Town : " + TownMinAge + " Rural : " + RuralMinAge + " Suburban : " + SuburbunMinAge);

//printing all the data

System.out.println("Max and Min ages per location :");

System.out.println(" \t INNER_CITY \t TOWN \t RURAL \t SUBURBUN");

System.out.println("Max:\t " + InnerCityMaxAge + "\t\t " + TownMaxAge + "\t " + RuralMaxAge + "\t " + SuburbunMaxAge);

System.out.println("Min:\t " + InnerCityMinAge + "\t\t " + TownMinAge + "\t " + RuralMinAge + "\t " + SuburbunMinAge);

try

{

fw.write("Max Age per region: INNER_CITY : " + InnerCityMaxAge + " Town : " + TownMaxAge + " Rural : " + RuralMaxAge + " Suburban : " + SuburbunMaxAge);

fw.write(System.getProperty( "line.separator" ));

fw.write("Min Age per region: INNER_CITY : " + InnerCityMinAge + " Town : " + TownMinAge + " Rural : " + RuralMinAge + " Suburban : " + SuburbunMinAge);

fw.write(System.getProperty( "line.separator" ));

}

catch (IOException e)

{

e.printStackTrace();

}

}

//This method sum the income of all people and then take average of it

public static void AvgIncomeComparator()

{

Arrays.sort(rObjs, new IncomeComparator());

DecimalFormat formatter = new DecimalFormat("#0.00");

double FAvgIncome = 0, MAvgIncome = 0, Fsum = 0, Msum = 0, fCt = 0, mCt = 0;

for(int i =0; i < rObjs.length; i++)

{

if (rObjs[i].getSex().equals("FEMALE"))

{

Fsum += rObjs[i].getIncome();

++fCt;

}

else

{

Msum += rObjs[i].getIncome();

++mCt;

}

FAvgIncome = Fsum/(fCt);

MAvgIncome = Msum/(mCt);

}

System.out.println("Female average income: " + formatter.format(FAvgIncome));

System.out.println("Male average income: " + formatter.format(MAvgIncome));

try

{

fw.write("Female Avg income is : " + formatter.format(FAvgIncome));

fw.write(System.getProperty( "line.separator" ));

fw.write("Male Avg income is : " + formatter.format(MAvgIncome));

fw.write(System.getProperty( "line.separator" ));

}

catch(IOException e)

{

e.printStackTrace();

}

}

public static void main(String[] args) throws IOException

{

Records obj = new Records();

obj.readData();

femaleComparator();

maleComparator();

regionAgeComparator();

AvgIncomeComparator();

String timeStamp = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(Calendar.getInstance().getTime());

System.out.println("Current Date = " + timeStamp);

fw.write("Current Date=" + timeStamp);

try{

fw.close();

}

catch(IOException e){

e.printStackTrace();

}

}

}

MaleComparator.java

import java.util.Comparator;

public class MaleComparator implements Comparator {

@Override public int compare(BankRecords o1, BankRecords o2) { // TODO Auto-generated method stub int Result = o1.getSex().compareTo(o2.getSex()); if(Result != 0) { return Result; } return o1.getCar().compareTo(o2.getCar()); } }

FemaleComparator.java

import java.util.Comparator;

public class FemaleComparator implements Comparator {

@Override public int compare(BankRecords o1, BankRecords o2) { // TODO Auto-generated method stub int Result = o1.getSex().compareTo(o2.getSex());

if (Result != 0) { return Result; } return o1.getMortage().compareTo(o2.getMortage()); }

}

RegionAgeComparator.java

import java.util.Comparator;

public class RegionAgeComparator implements Comparator {

@Override public int compare(BankRecords o1, BankRecords o2) { // TODO Auto-generated method stub int Result = o1.getRegion().compareTo(o1.getRegion());

return Result; }

}

IncomeComparator.java

import java.util.Comparator;

public class IncomeComparator implements Comparator{

@Override public int compare(BankRecords o1, BankRecords o2) { // TODO Auto-generated method stub int Result = String.valueOf(o1.getIncome()).compareTo(String.valueOf(o2.getIncome())); return Result; } }