NCP 524 (Software Engineering Laboratory)
EXPERIMENT #6
DATABASES
NAME: Garcia, John Russell G. INSTRUCTOR: Dr. Alexis John Rubio
SECTION: 2 CP DATE: 3/20/19 GRADE:
Machine Problem #1:
Write an application that connects to the CoffeeDB database, and allows the user to insert
a new row into the Customer table.
Code:
package machine.problem.pkg1; name = s.nextLine();
System.out.print("Enter customer's address:
import java.sql.Connection; ");
import java.sql.DriverManager; add = s.nextLine();
import java.sql.Statement; System.out.print("Enter customer's city: ");
import java.util.Scanner; city = s.nextLine();
System.out.print("Enter customer's state: ");
/** state = s.nextLine();
* System.out.print("Enter customer's zip: ");
* @author John Russell Garcia zip = s.nextLine();
*/ System.out.print("Enter customer’s
public class MachineProblem1 { customer number: ");
custnum = s.nextLine();
/**
* @param args the command line arguments stmt.execute("INSERT INTO Customer
*/ (customernumber,name,address,city,state,zip
public static void main(String[] args) { ) VALUES
String name,add,city,state,zip,custnum; ('"+custnum+"','"+name+"','"+add+"','"+city
Scanner s = new Scanner(System.in); +"','"+state+"','"+zip+"')");
final String DB_URL = System.out.println("Row added successfully
"jdbc:derby://localhost:1527/CoffeeDB"; ");
try
{ conn.close();
}
Connection conn = catch (Exception ex)
DriverManager.getConnection(DB_URL, {
"root", "root"); System.out.println("ERROR: " +
Statement stmt = conn.createStatement(); ex.getMessage());
}
System.out.println("Database connected"); }
System.out.println();
}
System.out.print("Enter customer's name: ");
1|Page
NCP 524 (Software Engineering Laboratory)
Screenshot:
Machine Problem #2:
Write an application that connects to the CoffeeDB database, and allows the user to select
a customer, then change any of that customer’s information. (You should not attempt to change
the customer number, because it is referenced by the UnpaidOrder table.)
Code:
package machine.problem.pkg2; final String DB_URL =
"jdbc:derby://localhost:1527/CoffeeDB";
import java.sql.Connection; try
import java.sql.DriverManager; {
import java.sql.Statement;
import java.util.Scanner; Connection conn =
DriverManager.getConnection(DB_URL,
/** "root", "root");
* Statement stmt = conn.createStatement();
* @author John Russell Garcia
*/ System.out.println("Database connected");
public class MachineProblem2 { System.out.println();
/** System.out.print("Select a customer number:
* @param args the command line arguments ");
*/ choice = s.nextLine();
public static void main(String[] args) {
String System.out.print("Select an info you want to
choice,change,name,add,city,state,zip; change: ");
Scanner s = new Scanner(System.in); change = s.nextLine();
change = change.toLowerCase();
2|Page
NCP 524 (Software Engineering Laboratory)
if (change.equals("name")) }
{ }
System.out.print("Change name to: "); else if (change.equals("state"))
name = s.nextLine(); {
try System.out.print("Change state to: ");
{ state = s.nextLine();
stmt.execute("UPDATE Customers " + try
"SET name = '"+name+"' " + {
"WHERE customernumber = stmt.execute("UPDATE Customer " +
'"+choice+"';"); "SET state = '"+state+"' " +
} "WHERE customernumber = '"+choice+"'");
catch(Exception ex) }
{ catch(Exception ex)
{
} System.out.println(ex);
} }
else if (change.equals("address")) }
{ else if (change.equals("zip"))
System.out.print("Change address to: "); {
add = s.nextLine(); System.out.print("Change zip to: ");
try zip = s.nextLine();
{ try
stmt.execute("UPDATE Customer " + {
"SET address = '"+add+"' " + stmt.execute("UPDATE Customer " +
"WHERE customernumber = "SET zip = '"+zip+"' " +
'"+choice+"';"); "WHERE customernumber =
} '"+choice+"';");
catch(Exception ex) }
{ catch(Exception ex)
{
}
} }
else if (change.equals("city")) }
{
System.out.print("Change city to: "); System.out.println("Info successfully
city = s.nextLine(); changed");
try conn.close();
{ }
stmt.execute("UPDATE Customer " + catch (Exception ex)
"SET city = '"+city+"' " + {
"WHERE customernumber = System.out.println("ERROR: " +
'"+choice+"';"); ex.getMessage());
} }
catch(Exception ex) }
{
3|Page
NCP 524 (Software Engineering Laboratory)
Screenshot:
Machine Problem #3:
Write an application that connects to the CoffeeDB database, then calculates and displays
the total amount owed in unpaid orders. This will be the sum of each row’s Cost column.
Code:
package machine.problem.pkg3; try
{
import java.sql.Connection;
import java.sql.DriverManager; Connection conn =
import java.sql.ResultSet; DriverManager.getConnection(DB_URL,
import java.sql.Statement; "root", "root");
Statement stmt = conn.createStatement();
/** String query = "select sum(cost) from
* unpaidorder";
* @author John Russell Garcia ResultSet rs = stmt.executeQuery(query);
*/ rs.next();
public class MachineProblem3 { double unpaid = rs.getDouble(1);
stmt.close();
/** conn.close();
* @param args the command line arguments System.out.println("Total amount owed in
*/ unpaid orders: "+ unpaid);
public static void main(String[] args) { }
final String DB_URL = catch (Exception ex)
"jdbc:derby://localhost:1527/CoffeeDB"; {
4|Page
NCP 524 (Software Engineering Laboratory)
System.out.println("ERROR: " + }
ex.getMessage());
} }
Screenshot:
Machine Problem #4:
Write an application that connects to the CoffeeDB database and displays a JList
component. The JList component should display a list of customers with unpaid orders. When the
user clicks on a customer, the application should display a summary of all the unpaid orders for
that customer.
Machine Problem #5:
Write an application that creates a database named Personnel. The database should have a
table named Employee, with columns for employee ID, name, position, and hourly pay rate. The
employee ID should be the primary key. Insert at least five sample rows of data into the Employee
table.
Code:
package machine.problem.pkg5; Connection conn =
DriverManager.getConnection(DB_URL,
import java.sql.Connection; "root", "root");
import java.sql.DriverManager; Statement stmt = conn.createStatement();
import java.sql.SQLException; stmt.execute("CREATE TABLE Employee
import java.sql.Statement; (employee_id CHAR(10) NOT NULL
PRIMARY KEY, name CHAR(25),position
/** CHAR(25),hourly_payrate DOUBLE )");
* stmt.execute("INSERT INTO Employee
* @author John Russell Garcia VALUES stmt.execute("INSERT INTO
*/ Employee VALUES
public class MachineProblem5 { ('1','Mark Jayson
Warnakulahewa','host',52550),('2','Roshan','h
/** ost',36767),('3','Daniel
* @param args the command line arguments Ombao','subhost',3000),('4','Nyopart','subhos
*/ t',76000),('5','Johnny
public static void main(String[] args) { Speaks','subhost',2000),('6','tito','host',2000")
final String DB_URL = ;
"jdbc:derby://localhost:1527/Personnel"; System.out.println("Employee table
try created.");
{ conn.close();
5|Page
NCP 524 (Software Engineering Laboratory)
} }
catch (Exception ex) }
{
System.out.println("ERROR: " + }
ex.getMessage());
Screenshot:
Machine Problem #6:
Write a GUI application that allows the user to add new employees to the Personnel
database you created in Machine Problem #5.
Code:
private void VALUES
addActionPerformed(java.awt.event.Action ('"+employee_id+"','"+name+"','"+position+
Event evt) { "',"+hourly_rate+") ");
final String DB_URL = JOptionPane.showMessageDialog(null,
"jdbc:derby://localhost:1527/Personnel"; "Successfully added");
String conn.close();
employee_id,name,position,hourly_rate; }
try catch (Exception ex)
{ {
Connection conn = System.out.println("ERROR: " +
DriverManager.getConnection(DB_URL, ex.getMessage());
"root", "root"); }
Statement stmt = conn.createStatement(); }
employee_id = jTextField1.getText();
name = jTextField2.getText(); private void
position = jTextField3.getText(); cancelActionPerformed(java.awt.event.Acti
hourly_rate = jTextField4.getText(); onEvent evt) {
stmt.execute("INSERT INTO Employee System.exit(0);
(employee_id,name,position,hourly_payrate) }
6|Page
NCP 524 (Software Engineering Laboratory)
Screenshot:
Machine Problem #7:
Write a GUI application that allows the user to look up an employee in the Personnel
database you created in Machine Problem #5. The user should be able to change any of the
employee’s information except employee ID, which is the primary key.
Code:
private void ResultSet rs = stmt.executeQuery(query);
changeActionPerformed(java.awt.event.Acti if (rs.next())
onEvent evt) { {
final String DB_URL = employee_id = jTextField1.getText();
"jdbc:derby://localhost:1527/Personnel"; name = jTextField2.getText();
String position = jTextField3.getText();
employee_id,name,position,hourly_rate; hourly_rate = jTextField4.getText();
try stmt.execute("UPDATE employee SET
{ name = '"+name+"',position
Connection conn = ='"+position+"',hourly_payrate="+hourly_ra
DriverManager.getConnection(DB_URL, te+" WHERE employee_id ='"+id+"' ");
"root", "root"); JOptionPane.showMessageDialog(null,
Statement stmt = conn.createStatement(); "Successfully edited");
String query = "Select * from employee conn.close();
where employee_id='"+id+"'"; }
7|Page
NCP 524 (Software Engineering Laboratory)
id = num.getText();
} String query = "Select * from employee
catch (Exception ex) where employee_id='"+id+"'";
{ ResultSet rs = stmt.executeQuery(query);
System.out.println("ERROR: " + if (rs.next())
ex.getMessage()); {
} name1 = rs.getString("name");
} pos = rs.getString("position");
hourly_payrate =
private void rs.getString("hourly_payrate");
cancelActionPerformed(java.awt.event.Acti JOptionPane.showMessageDialog(null,
onEvent evt) { "Name: " +name1+ "\nPosition: " + pos +
System.exit(0); "\nHourly Payrate: "+hourly_payrate);
} jTextField1.setText(id);
String id,name1,pos,hourly_payrate; jTextField2.setText(name1);
private void jTextField3.setText(pos);
searchActionPerformed(java.awt.event.Acti jTextField4.setText(hourly_payrate);
onEvent evt) {
}
final String DB_URL = }
"jdbc:derby://localhost:1527/Personnel"; catch (Exception ex)
try {
{ System.out.println("ERROR: " +
Connection conn = ex.getMessage());
DriverManager.getConnection(DB_URL, }
"root", "root"); }
Statement stmt = conn.createStatement();
Screenshot:
8|Page
NCP 524 (Software Engineering Laboratory)
9|Page
