For this lab you will continue using your current project folder created for labs 2 & 3 and create 3 newsource files. Files are as follows:

I already have the other files just need help with the following,

File DbConnect: to allow an object to connect / close a database connection.

File Dao: where Dao stands for Data Access Object.This will allow for database connectivity and CRUD (Create Read Update Delete) like operations. Ref: for CRUD->Tutorialspoint.

File LoanProcessing:acts 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 createa folder called libswithin 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 databaselocated here:

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

Codework

Include the following helper code for your 3 files.(note- if some assignment statements in the spec continue to wrap to another line, you may want to adjust your source editor to include the assignment onto one line).

DBConnect.java

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

publicclass DBConnect {

// Code database URL

staticfinal String DB_URL = "jdbc:mysql://www.papademas.net/411labs?autoReconnect=true&useSSL=false";

// Database credentials

staticfinal String USER = "db411", PASS = "411";

public Connection connect() throws SQLException {

return DriverManager.getConnection(DB_URL, USER, PASS);

}

}

Remaining helper code below will not include imports for brevity, so please make sure to add them in(Source > Organize Imports)!

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

// 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.

Lastly code your LoanProcessing class file.

For your LoanProcessing source file make sure to extendBankRecords.

Include the following methods for your file.

Include in main() your readData() method call which will process your BankRecord objects. Theninstantiate a Dao object and trigger your createTable() method,your insertRecords(your BankRecords array object)method and retrieveRecords() method in that order. Of course you can comment out your createTable / insertRecords once youve accomplished that to test how your output statements appear. Logic for that follows next.

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.

Sample starter code for main (fill in code where comments are shown to do so):

BankRecords br = new BankRecords();

br.readData();

Dao dao = new Dao();

dao.createTable();

dao.insertRecords(robjs); // perform inserts

ResultSet rs = dao.retrieveRecords(); // fill result set object

// Create heading for display

// Extract data from result set

while (rs.next()) {

// Retrieve data by column name (i.e., for id,income,pep)

// Display values for id,income,pep

}

rs.close();// closes result set object

Extra Credit options

-Include SQL Prepared statements when inserting records (+5 points)

-Include besides console, output a JTable GUI output display of your recordset

data(+5 points)

For a JTable to trigger code, build a frame with Javax Swing components which will

display your loan data into a JTable.Column headings must be included as well as a

scrollbar for proper appearance of your display within a GUI frame showing your table

component.

Oryou for extra credit you may choose the following option

-Serialize and deserialize BankRecord objects using the java.util.Map class. (+10 points)

Name your serializable file bankrecords.ser. Make your application sleep for 5 seconds

between the processes. Display to the console the time difference between the serialization

and deserialization processes.?

Include a zip file of allyour project source code(new and old), plus jar filein an orderly fashion and a snapshot of your app at runtime which must include a table creation message, an insertion message and the first few rows of your record results showing the Loan Analysis Report data into a doc file into BB for credit.If you have any extra credit,snapshot that as well and label your snapshot(s) accordingly. Sample display output follows