In java I have to do the following: Create a file named Dao.java: Dao.java -Incl
ID: 3702496 • Letter: I
Question
In java I have to do the following:
Create a file named Dao.java:
Dao.java
-Include the following class fields, constructor
//Declare DB objects
DBConnectconn = null;
Statement stmt = null;
// constructor
public Dao() { //create db object instance
conn = new DBConnect();
}
-Include a method to create a database table called createTable. createTable merely
creates a table when the method is called. Include the fields pid, id, income and pep
when building your table setup. A PRIMARY KEY which ensures record uniqueness is included for your build for the pid field which is shown below.
[ Note when creating a table, it is IMPERATIVE to include the following name:
yourFirstinitial_First4LettersOfYourLastName_tab]
// CREATE TABLE METHOD
publicvoid createTable() {
try {
// Open a connection
System.out.println("Connecting to a selected database to create Table...");
System.out.println("Connected database successfully...");
// Execute create query
System.out.println("Creating table in given database...");
stmt = conn.connect().createStatement();
String sql = "CREATE TABLE yourTableName_tab " +
"(pid INTEGER not NULL, " +
" id VARCHAR(10), " +
" income numeric(8,2), " +
" pep VARCHAR(3), " +
" PRIMARY KEY ( pid ))";
stmt.executeUpdate(sql);
System.out.println("Created table in given database...");
conn.connect().close();//close db connection
} catch (SQLException se) {
// Handle errors for JDBC
se.printStackTrace();
}
}
Notice carefully that comments are put in code as well the display of relevant information to the console. For future methods continue this way (note-see snapshot at the last page).
-Include a method to insert records called insertRecords().
// INSERT INTO METHOD
publicvoid insertRecords(BankRecords[] robjs) {
try {
// Execute a query
System.out.println("Inserting records into the table...");
stmt = conn.connect().createStatement();
// Include all object data to the database table
for (inti = 0; i<robjs.length; ++i) {
// finish string assignment to insert all object data
// (pid, id, income, pep) intoyour database table
String sql = "";
stmt.executeUpdate(sql);
}
conn.connect().close();
} catch (SQLException se) {
se.printStackTrace();
}
}
Finish coding the above sql string where commented with an insert statement (example insert statement follows):
sql = "INSERT INTO yourTableName_tab(field 1,field 2, field n) " +
"VALUES (' "+value 1+" ', ' "+value 2+" ', ' "+value n+"' )";
Note for brevity purposes, future starter code will EXCLUDE try / catch blocks. Add in your own try / catch blocks were applicable.
-Include a method to retrieve records for display called retrieveRecords().
public ResultSet retrieveRecords() {
ResultSet rs = null;
stmt = conn.connect().createStatement();
String sql = "SELECT * from yourTableName_tab";
rs = stmt.executeQuery(sql);
conn.connect().close();
returnrs;
}
Methods breakdown
insertRecords(BankRecords [] arrayName) will allow for thearray of BankRecord objects, to be passed to your method which will allow for the insertionof allthe id,income and pepdata from your BankRecords array (or whatever you named it)into your database table when called.
retrieveRecords() will return a ResultSet object used for creating output. The result set contains record data including yourid, income and peptable fields.
*Code tweak: Make sure to 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 yourTableName_tab order by pep desc";
As a quick note: make sure to alwayscloseoutof 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.
Again, include messages to the console when your methods trigger. Ex. Table created, Inserting records into database, etc.
A super great resource to assist you with all your JDBC-CRUD operations for your methods can be found at this site: http://www.tutorialspoint.com/jdbc/, as well as the Chapter 23 PowerPoint from Gaddis. Remember though to phrasecoding the best you can using your own object names, method naming and variable names, including coding syntax and even comments, if referencing any material from tutorialspoint so your lab work is unique.
The following code is what I added to the code above which looks like this:
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Dao {
//Declare DB objects
DBConnect conn = null;
Statement stmt = null;
// constructor
public Dao() { //create db object instance
conn = new DBConnect();
}
// CREATE TABLE METHOD
public void createTable() {
try {
// Open a connection
System.out.println("Connecting to a selected database to create Table...");
System.out.println("Connected database successfully...");
// Execute create query
System.out.println("Creating table in given database...");
stmt = conn.connect().createStatement();
String sql = "CREATE TABLE First4LettersOfYourLastName_tab" +
"(pid INTEGER not NULL, " +
" id VARCHAR(10), " +
" income numeric(8,2), " +
" pep VARCHAR(3), " +
" PRIMARY KEY ( pid ))";
stmt.executeUpdate(sql);
System.out.println("Created table in given database...");
conn.connect().close();//close db connection
} catch (SQLException se) {
// Handle errors for JDBC
se.printStackTrace();
}
}
// INSERT INTO METHOD
public void insertRecords(BankRecords[] robjs) {
try {
// Execute a query
System.out.println("Inserting records into the table...");
stmt = conn.connect().createStatement();
// Include all object data to the database table
for (int i = 0; i<robjs.length; ++i) {
String sql = "";
sql = "INSERT INTO First4LettersOfYourLastName_tab(pid,id,income,pep) " +
"VALUES (' "+ robjs[i].getPid() +" ', ' "+ robjs[i].getId() + "', ' "+ robjs[i].getIncome() +" ', ' "+robjs[i].getPep()+"' )";
//(pid, id, income, pep) into your database table
stmt.executeUpdate(sql);
}
conn.connect().close();
} catch (SQLException se) {
se.printStackTrace();
}
}
public ResultSet retrieveRecords() {
ResultSet rs = null;
try {
stmt = conn.connect().createStatement();
String sql =
"select id,income, pep from First4LettersOfYourLastName_tab order by pep desc";
rs = stmt.executeQuery(sql);
conn.connect().close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
}
My question is that if I did everything correctly like added what I had to do from the question to the code I added on to. Or am I missing any requirements from what is being asked to do? Thank you.
The three other files from the project:
Bank Records:
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 inforec[];
List<List<String>> array = new ArrayList<List<String>>();
//declaring variables
String id;
int age;
String gender;
double income;
String married;
int child;
String auto;
String save_act;
String current_act;
String mortage;
String savings;
String pep;
String region;
//Reads Data
void readData()
{
String Line = "";
try(BufferedReader br = new BufferedReader (new FileReader("D:/bank-Detail.csv")))
{
int i = 0;
while((Line = br.readLine()) != null)
{
array.add(Arrays.asList(Line.split(",")));
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
processData();
}
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 getgender() {
return gender;
}
public void setgender(String gender) {
this.gender = gender;
}
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 getChild() {
return child;
}
public void setChild(int child) {
this.child = child;
}
public String getauto() {
return auto;
}
public void setauto(String auto) {
this.auto = auto;
}
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 getPep() {
return pep;
}
public void setPep(String pep) {
this.pep = pep;
}
public String getMortage() {
return mortage;
}
public void setMortage(String mortage) {
this.mortage = mortage;
}
public String getsavings() {
return savings;
}
public void setsavings(String savings) {
this.savings = savings;
}
// Process Data
void processData()
{
int i = 0;
inforec = new BankRecords[array.size()];
for(List<String> row: array)
{
inforec[i] = new BankRecords();
inforec[i].setId(row.get(0));
inforec[i].setAge(Integer.parseInt(row.get(1)));
inforec[i].setgender(row.get(4));
inforec[i].setregion(row.get(5));
inforec[i].setIncome(Double.parseDouble(row.get(6)));
inforec[i].setMarried(row.get(6));
inforec[i].setChild(Integer.parseInt(row.get(6)));
inforec[i].setauto(row.get(9));
inforec[i].setSave_act(row.get(9));
inforec[i].setCurrent_act(row.get(9));
inforec[i].setMortage(row.get(12));
inforec[i].setsavings(row.get(12));
inforec[i].setPep(row.get(11));
i++;
}
//Data would be printed
}
void printData()
{
System.out.println("ID Age Sex Region Income Mortgage savings" );
for (int i=0; i<=24; i++)
{
String Final = String.format( "%s %s %s %-10s %8.2f %s",inforec[i].getId(),inforec[i].getAge(),inforec[i].getgender(),
inforec[i].getregion(),inforec[i].getIncome(), inforec[i].getMortage());
System.out.println(Final);
}
}
public static void main(String[] args) throws IOException
{
BankRecords sc = new BankRecords();
sc.readData();
}
}
Records:
import java.io.FileWriter;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import javax.swing.JOptionPane;
public class Records extends BankRecords{
private static FileWriter fw = null;
public Records()
{
try
{
fw = new FileWriter("bankrecords.txt");
}
catch(IOException e)
{
e.printStackTrace();
}
}
//male Comparator
private static void maleComparator()
{
Arrays.sort(inforec, new MaleComparator());
int MaleInner = 0, MaleSuburbun = 0, MaleTown = 0, MaleRural = 0;
for(int i=0; i < inforec.length; i++)
{
if(inforec[i].getgender().equals("MALE") && inforec[i].getregion().equals("INNER_CITY")&& inforec[i].getauto().equals("YES") && inforec[i].getChild() == 1)
MaleInner++;
}
System.out.println("Males with car and 1 children in INNER_CITY: "+ MaleInner);
for(int i=0; i < inforec.length; i++)
{
if(inforec[i].getgender().equals("MALE") && inforec[i].getregion().equals("TOWN")&& inforec[i].getauto().equals("YES") && inforec[i].getChild() == 1)
MaleTown++;
}
System.out.println("Males with car and 1 childern in TOWN: " + MaleTown);
for(int i=0; i < inforec.length; i++)
{
if(inforec[i].getgender().equals("MALE") && inforec[i].getregion().equals("RURAL")&& inforec[i].getauto().equals("YES") && inforec[i].getChild() == 1)
MaleRural++;
}
System.out.println("Males with car and 1 children in RURAL: " + MaleRural);
for(int i =0; i < inforec.length; i++)
{
if(inforec[i].getgender().equals("MALE") && inforec[i].getregion().equals("SUBURBAN")&& inforec[i].getauto().equals("YES") && inforec[i].getChild() == 1)
MaleSuburbun++;
}
System.out.println("Males with car and 1 children in Suburban: " + MaleSuburbun);
try
{
fw.write(" Males with car and 1 children in INNER_CITY: "+ MaleInner + " " + "Males with car and 1 childern in TOWN: " + MaleTown + " " + "Males with car and 1 children in RURAL: " + MaleRural + " Males with car and 1 children in Suburban: " + MaleSuburbun + " " );
}
catch (IOException e)
{
e.printStackTrace();
}
}
//Female with mortage and savings
private static void femaleComparator()
{
Arrays.sort(inforec, new FemaleComparator());
int female = 0;
for(int j=0; j < inforec.length; j++)
{
if(inforec[j].getgender().equals("FEMALE") && inforec[j].getMortage().equals("YES") && inforec[j].getsavings().equals("YES"))
female++;
}
System.out.println(" Females with Mortage: " + female);
System.out.println(" Females with Savings: " + female);
try
{
System.out.println("Information:");
fw.write(" Females with mortgage : " + female);
fw.write("Female Savings" + female);
}
catch (IOException e)
{
e.printStackTrace();
}
}
// The Avergae Income for Comparator
public static void AvgIncomeComparator()
{
Arrays.sort(inforec, new IncomeComparator());
double incomeave = 0, sum = 0;
for(int j =0; j < inforec.length; j++)
{
sum += inforec[j].getIncome();
}
incomeave = sum / inforec.length; //average of income sum
System.out.println(" Average Income is : " + incomeave);
try
{
fw.write("Average Income is : " + incomeave + " ");
}
catch(IOException e)
{
e.printStackTrace();
}
}
public static void main(String[] args) throws IOException
{
Records obj = new Records();
obj.readData();
femaleComparator();
maleComparator();
AvgIncomeComparator();
try{
fw.close();
}
catch(IOException e){
e.printStackTrace();
}
}
}
Client:
public abstract class Client {
abstract void readData();
abstract void processData();
abstract void printData();
}
Explanation / Answer
Your code looks almost correct.
Some changes are done .
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Dao {
// Declare DB objects
DBConnect conn = null;
Statement stmt = null;
// constructor
public Dao() { // create db object instance
conn = new DBConnect();
}
// CREATE TABLE METHOD
public void createTable() {
try {
// Open a connection
System.out.println("Connecting to a selected database to create Table...");
System.out.println("Connected database successfully...");
// Execute create query
System.out.println("Creating table in given database...");
stmt = conn.connect().createStatement();
String sql = "CREATE TABLE First4LettersOfYourLastName_tab" +
"(pid INTEGER not NULL, " +
" id VARCHAR(10), " +
" income numeric(8,2), " +
" pep VARCHAR(3), " +
" PRIMARY KEY ( pid ))";
stmt.executeUpdate(sql);
System.out.println("Created table in given database...");
} catch (SQLException se) {
// Handle errors for JDBC
se.printStackTrace();
} finally {
conn.connect().close();// close db connection
stmt.close();
}
}
// INSERT INTO METHOD
public void insertRecords(BankRecords[] robjs) {
try {
// Execute a query
System.out.println("Inserting records into the table...");
stmt = conn.connect().createStatement();
// Include all object data to the database table
for (int i = 0; i < robjs.length; ++i) {
String sql = "";
sql = "INSERT INTO First4LettersOfYourLastName_tab(pid,id,income,pep) " +
"VALUES (' " + robjs[i].getPid() + " ', ' " + robjs[i].getId() + "', ' " + robjs[i].getIncome()
+ " ', ' " + robjs[i].getPep() + "' )";
// (pid, id, income, pep) into your database table
stmt.executeUpdate(sql);
}
// commit the transaction here
conn.connect().commit();
} catch (SQLException se) {
se.printStackTrace();
} finally {
conn.connect().close();// close db connection
stmt.close();
}
}
public ResultSet retrieveRecords() {
ResultSet rs = null;
try {
stmt = conn.connect().createStatement();
String sql =
"select id,income, pep from First4LettersOfYourLastName_tab order by pep desc";
rs = stmt.executeQuery(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
conn.connect().close();
}
return rs;
}
}
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.