Java JDBC API Analysis

A1

For this assignment, you will analyze code that uses the JDBC API to access a database, retrieve data, and compose output based on that data. You will then comment the code to reflect the purpose and expected results of the code.

Download the linked TXT file, and read through the Java code carefully.

/**
* ********************************************************************
* Program: SQLcode
* Purpose: To analyze and document JDBC API calls.
* Programmer: TYPE YOUR NAME HERE
* Class: PRG/421r13, Java Programming II
* Instructor:
* Creation Date: TYPE TODAY'S DATE HERE
*
* Comments: The purpose of the JDBC API calls in this program
* is to retrieve data from a relational database.
* To complete this assignment, analyze the code and replace
* the numbered comments as instructed below.
**********************************************************************
*/
package sqlcodeexample;

// 1. THE REASON FOR IMPORTING THE FOLLOWING LIBRARIES IS...
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQLCodeExample {

public static void main(String[] args) {
try {
String host = "jdbc:mysql://localhost/STUDENT";
String uName = "bsmith";
String uPass = "roxie";

// 2. THE PURPOSE (RESULT) OF THE FOLLOWING API CALL IS...
Connection conn = DriverManager.getConnection(host, uName, uPass);

// 3. THE PURPOSE (RESULT) OF THE FOLLOWING API CALLS IS...
Statement stmt = conn.createStatement();

String sql = "select Stu_id, Stu_Name from Stu_Class_1";
ResultSet rs = stmt.executeQuery(sql);

System.out.println("Displaying student information: ");

// 4. THE RESULT OF THE FOLLOWING API CALLS IS...
while (rs.next()) {
System.out.println("Student id " + rs.getString("Stu_id"));
System.out.println(" is associated with student name " + rs.getString("Stu_Name"));
}
}
// 5. THE PURPOSE OF THE FOLLOWING CATCH BLOCK IS...
catch (SQLException err) {
System.out.println(err.getMessage());
}
}
}

A2

For this assignment, you will create Java code that accesses a relational database, requests data, and then analyzes and displays a portion of that data.

Imagine a MySQL relational database schema named COMPANY_DB containing two tables, employee_table and payroll_table, such that the records in each of the tables is as follows:

employee_table:

payroll_table:

The credentials you will need to access the database which holds both of the tables are as follows:

• Host string = localhost:3306
• Username = student
• Password = prg421

Copy and paste the linked Java "starter" code into the NetBeans editor and save as a JAVA file.

Add Java statements to the file to accomplish the following:

• Establish a connection to the database
• Query the database, joining the two tables on the Emp_id field
• Display your name and today's date on the console along with the following returned database results:
  • employee identification number
  • first and last name
  • state
  • payroll schedule
  • 401k plan
• Close the database connection

Identify and correct any compile-time errors that exist in the starter code.

/**
* ********************************************************************
* Program: PRG282Week5CodeAssignment_startercode.java
* Purpose: Starter code to access two tables via JDBC.
* Programmer: YOUR NAME GOES HERE
* Class: PRG/421r13, Java Programming II
* Instructor: YOUR INSTRUCTOR'S NAME GOES HERE
* Creation Date: TODAY'S DATE GOES HERE
*
* CommentS: The purpose of this code is to access a relational
* database (MySQL), query two joined tables, and
* process the results of the query. For this assignment,
* no heavy-duty processing is required; we will simply
* examine the returned data and display a selected
* portion of it on the console.
**********************************************************************
*/
package SQLcommand;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class SQLstarter {

public static void main(String[] args) {
try { // start of the try/catch block of code

String host = "jdbc:mysql://localhost:3306/COMPANY_DB"; // This value is provided in the instructions for this assignment.
String uName = "student"; // This value is provided in the instructions for this assignment.
String uPass = "prg421"; // This value is provided in the instructions for this assignment.

Connection conn = DriverManager.getConnection(host, uName, uPass);
Statement stmt = conn.createStatement();

// Select values from the tables
System.out.println("YOUR NAME");
System.out.println(new Date());
System.out.println();

System.out.println("Emp_id, FName, LName, State, Paysch, 401k"); // display values from the tables


} catch (SQLException err) {
System.out.println(err.getMessage());
}
}
}