Question

Using JAVA

1. Resource:

   • "Starter Code to Access Tables via JDBC" text file ******** PASTED BELOW ****************

   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 that 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:

Emp id	FName	LNname	Addr	City	State	Zip
100	Jack	Smith	123 North	Topeka	KS	66603
101	Joe	Apple	4 Street	Denver	CO	80202
111	Nancy	Good	45 SW	Hartford	CT	06103
121	Tom	Whatever	89 NE	Dover	DE	19901
122	Jim	Thompson	789 W 95	Albany	NY	12207
123	Tommy	Boyson	154 Bolt	Boston	MA	02201
125	John	Jones	47 West	Lincoln	NE	68502

·        payroll_table:

Emp id	Paysch	401k	Spouse
100	BiWk	yes	yes
101	BiWk	yes	yes
111	Monthly	no	no
121	Wkly	pending	yes
122	Wkly	yes	no
123	Monthly	pending	no
125	Monthly	no	yes

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.

Note: Because you will not be connecting to an actual database, some compiler errors will remain.

After you finish, rename your JAVA file with a .txt extension using the following naming convention:

• PRG421_Week5CodingAssignment_LastnameFirstname.txt.

Submit your TXT file.

*********************************************** CODE PROVIDED *******************************************************

/**********************************************************************
*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.SQLException;

public class SQLstarter {
public static void main (String[ ] args) {

try {         // start of the try/catch block of code
String host = " "; // This value is provided in the instructions for this assignment.   
String uName = " "; // This value is provided in the instructions for this assignment.   
String uPass = " "; // This value is provided in the instructions for this assignment.
  
Connection conn = DriverManager.getConnection(host, uName, uPass);
Statement stmt = con.createStatement();
  
// Select values from the tables
String sql = "select Emp id, FName, LName, Addr, City, State, Zip from Employee_table, Union, select Paysch, 401k, Spouse from Payroll-table;";
ResultSet rs = stsmt.executeQuery (sql); // cursor pointing to its current row of data
  
System.out.println ("Displaying student information: "); // display values from the tables
  
String Emp id = rset.getString("Emp id");
String FName = rset.getString("FName ");
String LName = rset.getString("LName ");
String Addr = rset.getString("Addr ");
String Zip = rset.getString("Zip");
String Paysch = rset.getString("Paysch ");
String 401k = rset.getString("401k");
String Spouse = rset.getString("Spouse”);

// System.out.println (Stu_id + ", " +Stu_Name );
System.out.println (Emp id + ", " +FName + ”, “ +LName + “, ” +Add + ”, “ +Zip + ”, “ +Paysch +”,
“ +401k + ”, “+Spouse);   

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

}

}

}

Answer 1

/********************************SQLstarter.java***************************************/

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SQLstarter {
   public static void main(String[] args) {

       try {
           Class.forName("com.mysql.jdbc.Driver");
           String host = "jdbc:mysql://localhost:3306/company_db";
           String username = "root";
           String password = "";
           Connection conn = DriverManager.getConnection(host, username, password);
           Statement stmt = conn.createStatement();

           // Select values from the tables
           String sql = "select employee_table.Emp_id, FName, LNName, State, Paysch, 401k from employee_table JOIN payroll_table ON employee_table.Emp_id = payroll_table.Emp_id;";
           ResultSet rs = stmt.executeQuery(sql); // cursor pointing to its current row of data

           System.out.println("Displaying student information: "); // display values from the tables

           while (rs.next()) {
               int Emp_id = rs.getInt("Emp_id");
               String FName = rs.getString("FName");
               String LName = rs.getString("LNName");
               String state = rs.getString("State");
               String Paysch = rs.getString("Paysch");
               String k401 = rs.getString("401k");

               // System.out.println (Stu_id + ", " +Stu_Name );
               System.out.println(Emp_id + ", " + FName + ", " + LName + ", " + Paysch + ", " + k401);
           }

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

       }

   }

}

/******************output*******************************/

Displaying student information:
100, Jack, Smith, BiWk, yes
101, Joe, Apple, BiWk, yes
102, Nancy, Good, Monthly, no

Please let me know if you have any doubt or modify the answer, Thanks :)