Customer (CustomerId, CustomerName) Employee (EmployeeId, EmployeeName, Salary, SupervisorId) Product(ProductId, ProductName, ListPrice) Orders (OrderId, OrderDate, CustomerId, EmployeeId,...
Customer (CustomerId, CustomerName) Employee (EmployeeId, EmployeeName, Salary, SupervisorId) Product(ProductId, ProductName, ListPrice) Orders (OrderId, OrderDate, CustomerId, EmployeeId, Total) OrderedProduct (OrderId, ProductId, Quantity, Price)
Write the code to complete the methods in OrderJDBC.java (look for TODO items). <---**IN BOLD** throughout code.
/*
OrderJDBC.java - A JDBC program for accessing and updating an order database on MySQL.
*/
import java.io.File;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
/**
* An application for querying and updating an order database.
*/
public class OrderJDBC
{
/**
* Connection to database
*/
private Connection con;
/**
* Main method is only used for convenience. Use JUnit test file to verify your answer.
*
* @param args
* none expected
* @throws SQLException
* if a database error occurs
*/
public static void main(String[] args) throws SQLException
{
OrderJDBC q = new OrderJDBC();
q.connect();
q.init();
// Application operations
System.out.println(q.listAllCustomers());
q.listCustomerOrders("00001");
q.listLineItemsForOrder("01000");
q.computeOrderTotal("01000");
q.addCustomer("11111", "Fred Smith");
q.updateCustomer("11111", "Freddy Smithers");
q.newOrder("22222", "11111", "2015-10-31", "E0001");
q.newLineItem("22222", "P0005", 5, "3.10");
q.newLineItem("22222", "P0007", 5, "2.25");
q.newLineItem("22222", "P0008", 5, "2.50");
q.deleteCustomer("11111");
// Queries
// Re-initialize all data
q.init();
System.out.println(OrderJDBC.resultSetToString(q.query1(), 100));
System.out.println(OrderJDBC.resultSetToString(q.query2(), 100));
System.out.println(OrderJDBC.resultSetToString(q.query3(), 100));
System.out.println(OrderJDBC.resultSetToString(q.query4(), 100));
q.close();
}
/**
* Makes a connection to the database and returns connection to caller.
*
* @return
* connection
* @throws SQLException
* if an error occurs
*/
public Connection connect() throws SQLException
{
String uid = "";
String url = "jdbc:mysql://********/db_"+uid;
String pw = "";
System.out.println("Connecting to database.");
// Note: Must assign connection to instance variable as well as returning it back to the caller
con = DriverManager.getConnection(url, uid, pw);
return con;
}
/**
* Closes connection to database.
*/
public void close()
{
System.out.println("Closing database connection.");
try
{
if (con != null)
con.close();
}
catch (SQLException e)
{
System.out.println(e);
}
}
/**
* Creates the database and initializes the data.
*/
public void init()
{
String fileName = "data/order.ddl";
try
{
// Create statement
Statement stmt = con.createStatement();
Scanner scanner = new Scanner(new File(fileName));
// Read commands separated by ;
scanner.useDelimiter(";");
while (scanner.hasNext())
{
String command = scanner.next();
if (command.trim().equals(""))
continue;
// System.out.println(command); // Uncomment if want to see commands executed
stmt.execute(command);
}
scanner.close();
}
catch (Exception e)
{
System.out.println(e);
}
}
/**
* Returns a String with all the customers in the order database.
* Format:
* CustomerId, CustomerName
* 00000, A. Anderson
*
* @return
* String containing customers
*/
public String listAllCustomers() throws SQLException
{
System.out.println("Executing list all customers.");
StringBuilder output = new StringBuilder();
// TODO: Traverse ResultSet and use StringBuilder.append() to add columns/rows to output string
return output.toString();
}
/**
* Returns a String with all the orders for a given customer id.
*
* Note: May need to use getDate(). You should not retrieve all values as Strings.
*
* Format:
* OrderId, OrderDate, CustomerId, EmployeeId, Total
* 01001, 2002-11-08, 00001, E0000, 1610.59
*
* @return
* String containing orders
*/
public String listCustomerOrders(String customerId) throws SQLException
{
// TODO: Similar to listAllCustomers(), execute query and store results in a StringBuilder, then output as a String
return "";
}
/**
* Returns a ResultSet with all line items for a given order id.
* You must use a PreparedStatement.
*
* @return
* ResultSet containing line items
*/
public ResultSet listLineItemsForOrder(String orderId) throws SQLException
{
// TODO: Use a PreparedStatement for this query. Return the ResultSet.
return null;
}
/**
* Returns a ResultSet with a row containing the computed order total from the lineitems (named as orderTotal) for a given order id.
* You must use a PreparedStatement.
* Note: Do NOT just return the Orders.Total value.
*
* @return
* ResultSet containing order total
*/
public ResultSet computeOrderTotal(String orderId) throws SQLException
{
// TODO: Use a PreparedStatement for this query. Return the ResultSet.
return null;
}
/**
* Inserts a customer into the databases.
* You must use a PreparedStatement.
*/
public void addCustomer(String customerId, String customerName) throws SQLException
{
// TODO: Use a PreparedStatement for this INSERT.
}
/**
* Deletes a customer from the databases.
* You must use a PreparedStatement.
* @throws SQLException
*/
public void deleteCustomer(String customerId) throws SQLException
{
// TODO: Use a PreparedStatement for this DELETE.
}
/**
* Updates a customer in the databases.
* You must use a PreparedStatement.
* @throws SQLException
*/
public void updateCustomer(String customerId, String customerName) throws SQLException
{
// TODO: Use a PreparedStatement for this UPDATE.
}
/**
* Creates an order in the database.
* You must use a PreparedStatement.
*
* @throws SQLException
*/
public void newOrder(String orderId, String customerId, String orderDate, String employeeId) throws SQLException
{
// TODO: Use a PreparedStatement for this INSERT.
}
/**
* Creates a lineitem in the database.
* You must use a PreparedStatement.
*
* @throws SQLException
*/
public void newLineItem(String orderId, String productId, int quantity, String price) throws SQLException
{
// TODO: Use a PreparedStatement for this INSERT.
}
/**
* Updates an order total in the database.
* You must use a PreparedStatement.
*
* @throws SQLException
*/
public void updateOrderTotal(String orderId, BigDecimal total) throws SQLException
{
// TODO: Use a PreparedStatement for this UPDATE.
}
/**
* Return the list of products that have not been in any order. Hint: Left join can be used instead of a subquery.
*
* @return
* ResultSet
* @throws SQLException
* if an error occurs
*/
public ResultSet query1() throws SQLException
{
System.out.println("\nExecuting query #1.");
// TODO: Execute the SQL query and return a ResultSet.
return null;
}
/**
* Return the order ids and total amount where the order total does not equal the sum of quantity*price for all ordered products in the order.
*
* @return
* ResultSet
* @throws SQLException
* if an error occurs
*/
public ResultSet query2() throws SQLException
{
System.out.println("\nExecuting query #2.");
// TODO: Execute the SQL query and return a ResultSet.
return null;
}
/**
* Return for each customer their id, name and average total order amount for orders starting on January 1, 2015 (inclusive). Only show customers that have placed at least 2 orders.
* Format:
* CustomerId, CustomerName, avgTotal
* 00001, B. Brown, 489.952000
*
* @return
* ResultSet
* @throws SQLException
* if an error occurs
*/
public ResultSet query3() throws SQLException
{
System.out.println("\nExecuting query #3.");
// TODO: Execute the SQL query and return a ResultSet.
return null;
}
/**
* Return the employees who have had at least 2 distinct orders where some product on the order had quantity >= 5.
* Format:
* EmployeeId, EmployeeName, orderCount
*
* @return
* ResultSet
* @throws SQLException
* if an error occurs
*/
public ResultSet query4() throws SQLException
{
System.out.println("\nExecuting query #4.");
// TODO: Execute the SQL query and return a ResultSet.
return null;
}
/*
* Do not change anything below here.
*/
/**
* Converts a ResultSet to a string with a given number of rows displayed.
* Total rows are determined but only the first few are put into a string.
*
* @param rst
* ResultSet
* @param maxrows
* maximum number of rows to display
* @return
* String form of results
* @throws SQLException
* if a database error occurs
*/
public static String resultSetToString(ResultSet rst, int maxrows) throws SQLException
{
if (rst == null)
return "No Resultset.";
StringBuffer buf = new StringBuffer(5000);
int rowCount = 0;
ResultSetMetaData meta = rst.getMetaData();
buf.append("Total columns: " + meta.getColumnCount());
buf.append('\n');
if (meta.getColumnCount() > 0)
buf.append(meta.getColumnName(1));
for (int j = 2; j <= meta.getColumnCount(); j++)
buf.append(", " + meta.getColumnName(j));
buf.append('\n');
while (rst.next())
{
if (rowCount < maxrows)
{
for (int j = 0; j < meta.getColumnCount(); j++)
{
Object obj = rst.getObject(j + 1);
buf.append(obj);
if (j != meta.getColumnCount() - 1)
buf.append(", ");
}
buf.append('\n');
}
rowCount++;
}
buf.append("Total results: " + rowCount);
return buf.toString();
}
}
Homework Answers
// TODO: Traverse
ResultSet and use StringBuilder.append() to add columns/rows to
output string
String sql = "select * from Orders";
PreparedStatement statement = connection.prepareStatement(sql);
//resultset
ResultSet result=stmt.executeQuery();
ResultSetMetaData md=result.getMetaData();
int nCols=md.getColumnCount();
for(int c=1;c<=cCols;c++)
strResul.append(result.getString(c).append(" "));
//or alternate method:Method -2
String sql = "select * from Orders";
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet result = statement.executeQuery();
while(result.next()) {
result.getInt ("OrderId");
result.getDate ("OrderDate");
result.getInt ("CustomerId");
result.getInt ("EmployeeId");
result.getBigDecimal("Total");
}
//TODO: Similar to listAllCustomers(), execute query and store results in a StringBuilder, then output as a String
StringBuilder b=new StringBuilder(some_appropriate_size);
sb.append("select Total");
sb.append("OrderId");
sb.append("from Orders");
sb.append(Lineitems);
return sb.toString();
//public ResultSet listLineItemsForOrder(String orderId) throws SQLException
//{
// TODO: Use a PreparedStatement for this query. Return the ResultSet.
PreparedStatementps=null;
ResultSet rs=null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.
getConnection("jdbc:oracle:thin:@<hostname>:<port num>:<DB name>"
,"user","password");
String query = "select Total from Orders where OrderId=?";
prSt = con.prepareStatement(query);
rs = prSt.executeQuery();
while(rs.next()){
System.out.println(rs.getInt("Total"));
}
rs.close();
rs = prSt.executeQuery();
while(rs.next()){
System.out.println(rs.getInt("Total"));
}catch(Exception e)
{
}
/*public void addCustomer(String customerId, String customerName) throws SQLException
{ */
// TODO: Use a PreparedStatement for this INSERT.
throws SQLException
{
String query = "INSERT INTO CUSTOMER ( " CustomerId,"
+ " CustomerName,") VALUES ( ?, ?)";
try {
// set all the preparedstatement parameters
PreparedStatement st = con.prepareStatement(query);
st.setInt(1, Customer.getCustomerId());
st.setString(2, Customer.CustomerName());
// execute the preparedstatement insert
st.executeUpdate();
st.close();
}
catch (SQLException se)
{
// log exception
throw se;
}
}
// TODO: Use a PreparedStatement for this DELETE.
String sql = "DELETE FROM Customer WHERE CustomerId=?";
PreparedStatement prest = con.prepareStatement(sql);
// TODO: Use a PreparedStatement for this UPDATE.
String sqlUpdate = "UPDATE Customers "
+ "SET CustomerName = ? "
+ "WHERE CustomerId = ?";
PreparedStatement prest = con.prepareStatement(sqlUpdate);
// TODO: Use a PreparedStatement for this INSERT.
String query = " insert into Orders (OrderId, OrderDate, CustomerId, EmployeeId, Total)"
+ " values (?, ?, ?, ?, ?)";
// create the mysql insert preparedstatement
PreparedStatement preparedStmt = con.prepareStatement(query);
preparedStmt.setInt (1, 100);
preparedStmt.setDate (2, "1/1/2019");
preparedStmt.setInt (3, 12);
preparedStmt.setInt(4, 31);
preparedStmt.setBigDecimal (5, 5000.00);
// execute the preparedstatement
preparedStmt.execute();
// TODO: Use a PreparedStatement for this INSERT.
String query = " insert into OrderedProduct (OrderId, ProductId,
Quantity, Price)"
+ " values (?, ?, ?, ?, )";
// create the mysql insert preparedstatement
PreparedStatement preparedStmt = con.prepareStatement(query);
preparedStmt.setInt (1, 100);
preparedStmt.setInt (2, 12);
preparedStmt.setInt(3, 31);
preparedStmt.setBigDecimal (4, 5000.00);
// execute the preparedstatement
preparedStmt.execute();
// TODO: Use a PreparedStatement for this UPDATE.
String sqlUpdate = "UPDATE Orders "
+ "SET Total = ? "
+ "WHERE OrderIId = ?";
// TODO: Execute the SQL query and return a ResultSet.
select Products.ProductId,Products.Productname
,OrderedProducts.ProductId
from Products
left Join productlist
on Products.ProductIdOrderedProducts.ProductId;
ResultSet rs=null;
rs.getInt ("productId");
Add Answer to:
Customer (CustomerId, CustomerName)
Employee (EmployeeId, EmployeeName, Salary, SupervisorId)
Product(ProductId, ProductName, ListPrice)
Orders (OrderId, OrderDate, CustomerId, EmployeeId,...
What happens when you try to compile and run the following code? String query = "INSERT...
What happens when you try to compile and run the following code? String query = "INSERT INTO Invoices (InvoiceDate InvoiceTotal) " + "VALUES ('2/10/01', '443.55')"; Statement statement = connection.createStatement(); statement.executeUpdate(query); a. A SQLException is thrown at runtime because the executeUpdate method can’t accept a String object. b. A SQLException is thrown at runtime because the SQL statement is coded incorrectly. c. An error occurs at compile time because the SQL statement is coded incorrectly. d. This code compiles and...
Written in Java I have an error in the accountFormCheck block can i get help to...
Written in Java I have an error in the accountFormCheck block can i get help to fix it please. Java code is below. I keep getting an exception error when debugging it as well Collector.java package userCreation; import java.io.IOException; import java.net.URL; import java.util.ResourceBundle; import java.util.regex.Matcher; import java.util.regex.Pattern; import javafx.event.ActionEvent; import javafx.fxml.*; import javafx.scene.*; import javafx.scene.control.*; import javafx.scene.text.Text; import javafx.stage.*; public class Controller implements Initializable{ @FXML private PasswordField Password_text; @FXML private PasswordField Confirm_text; @FXML private TextField FirstName_text; @FXML private TextField LastName_text;...
Really need help with this. This is for my Advanced Java Programming Class. If anyone is...
Really need help with this. This is for my Advanced Java Programming Class. If anyone is an expert in Java I would very much appreciate the help. I will provide the code I was told to open. Exercise 13-3 Use JPA to work with the library checkout system In this exercise, you'll convert the application from the previous exercise to use JPA instead of JDBC to access the database. Review the project Start NetBeans and open the project named ch13_ex3_library that's...
For this assignment, you will write a program to work with Huffman encoding. Huffman code is...
For this assignment, you will write a program to work with Huffman encoding. Huffman code is an optimal prefix code, which means no code is the prefix of another code. Most of the code is included. You will need to extend the code to complete three additional methods. In particular, code to actually build the Huffman tree is provided. It uses a data file containing the frequency of occurrence of characters. You will write the following three methods in the...
In java. You are provided an abstract class called MyAbstract. You can only modify the writeFile...
In java. You are provided an abstract class called
MyAbstract. You can only modify the writeFile () class. You are
provided another class called Finally you are provided the file you
must read from in the MyAbstract class. You will need to extend
MyAbstract to MyTestClass.
You should have the following outputs.
The content of myData
employee avg salary
number of employees
File that was created in myFile()
Everything I was provided is listed already.
l. Servers ary 1 35...
Please create a class in Java that completes the following conditions MorseTree.java /* This program will...
Please create a class in Java that completes the
following conditions
MorseTree.java
/* This program will read in letters followed by their morse
code
* and insert them properly in a tree based on the amount of
symbols
* it has and whether they are left or right descendent.
Finally
* it prints a few certain nodes.
*/
import java.io.File;
import java.io.FileNotFoundException;
import java.util.Scanner;
public class MorseTree {
public static void main(String[] args) throws
FileNotFoundException {
//...
This task involves constructing a Java program that supports interaction via a graphical user interface.The object...
This task involves constructing a Java program that supports interaction via a graphical user interface.The object is to implement a Scrabble graphical game. The basis for the game is quite simple. Presented with a 6x6 grid of Scrabble tiles, the challenge is for the player(s) to form as many high scoring words as possible. Words may only be formed from sequences of adjacent tiles. Two tiles are adjacent if their edges or corners meet. A tile may...
could you please help me with this problem, also I need a little text so I...
could you please help me with this problem, also I
need a little text so I can understand how you solved the
problem?
import java.io.File; import java.util.Scanner; /** *
This program lists the files in a directory specified by * the
user. The user is asked to type in a directory name. * If the name
entered by the user is not a directory, a * message is printed and
the program ends. */ public class DirectoryList { public static...
//*Manager.java*// public interface Manager { public void handleCrisis(); } _________________________________________________________ /**Employee.java**/ Public abstract class Employee {...
//*Manager.java*//
public interface Manager {
public void handleCrisis();
}
_________________________________________________________
/**Employee.java**/
Public abstract class Employee {
protected final String name;
protected final int id;
public Employee(String empName, int empId) {
name = empName;
id = empId;
}
public Employee() {
name = generatedNames[lastGeneratedId];
id = lastGeneratedId++;
}
public String getName() {
return name;
}
public int getId() {
return id;
}
//returns true if work was successful. otherwise returns false (crisis)
public abstract boolean work();
public String toString() {
return...
JAVAFX ONLY PROGRAM!!!!! SORTING WITH NESTED CLASSES AND LAMBDA EXPRESSIONS. DIRECTIONS ARE BELOW: DIRECTIONS: The main...
 {</p>
<p>for (int r = 0; r < SUIT_SIZE; r++) {</p>
<p>String path = resourceDir + suits[s] + rank[r] + ".png)
JAVAFX ONLY PROGRAM!!!!!
SORTING WITH NESTED CLASSES AND LAMBDA
EXPRESSIONS.
DIRECTIONS ARE BELOW:
DIRECTIONS:
The main point of the exercise is to demonstrate your
ability to use various types of nested classes. Of course, sorting
is important as well, but you don’t really need to do much more
than create the class that does the comparison. In general, I like
giving you some latitude in how you design and implement your
projects. However, for this assignment, each piece is very...
In java. You are provided an abstract class called
MyAbstract. You can only modify the writeFile () class. You are
provided another class called Finally you are provided the file you
must read from in the MyAbstract class. You will need to extend
MyAbstract to MyTestClass.
You should have the following outputs.
The content of myData
employee avg salary
number of employees
File that was created in myFile()
Everything I was provided is listed already.
l. Servers ary 1 35...
Please create a class in Java that completes the
following conditions
MorseTree.java
/* This program will read in letters followed by their morse
code
* and insert them properly in a tree based on the amount of
symbols
* it has and whether they are left or right descendent.
Finally
* it prints a few certain nodes.
*/
import java.io.File;
import java.io.FileNotFoundException;
import java.util.Scanner;
public class MorseTree {
public static void main(String[] args) throws
FileNotFoundException {
//...
could you please help me with this problem, also I
need a little text so I can understand how you solved the
problem?
import java.io.File; import java.util.Scanner; /** *
This program lists the files in a directory specified by * the
user. The user is asked to type in a directory name. * If the name
entered by the user is not a directory, a * message is printed and
the program ends. */ public class DirectoryList { public static...
//*Manager.java*//
public interface Manager {
public void handleCrisis();
}
_________________________________________________________
/**Employee.java**/
Public abstract class Employee {
protected final String name;
protected final int id;
public Employee(String empName, int empId) {
name = empName;
id = empId;
}
public Employee() {
name = generatedNames[lastGeneratedId];
id = lastGeneratedId++;
}
public String getName() {
return name;
}
public int getId() {
return id;
}
//returns true if work was successful. otherwise returns false (crisis)
public abstract boolean work();
public String toString() {
return...
 {</p>
<p>for (int r = 0; r < SUIT_SIZE; r++) {</p>
<p>String path = resourceDir + suits[s] + rank[r] + ".png)
JAVAFX ONLY PROGRAM!!!!!
SORTING WITH NESTED CLASSES AND LAMBDA
EXPRESSIONS.
DIRECTIONS ARE BELOW:
DIRECTIONS:
The main point of the exercise is to demonstrate your
ability to use various types of nested classes. Of course, sorting
is important as well, but you don’t really need to do much more
than create the class that does the comparison. In general, I like
giving you some latitude in how you design and implement your
projects. However, for this assignment, each piece is very...
Most questions answered within 3 hours.
-
Two identical flutes can play middle C (262 Hz) at 20◦C. How
many beats per second...
asked 2 minutes ago -
Sparky, Co. purchased land as a factory site for $600,000.
Sparky paid $42,000 to tear down...
asked 11 minutes ago -
A Chi-square distribution with 14 degrees of freedom is a
correct model for
Question 8 options:...
asked 41 minutes ago -
In a group of 45 mice, there are 10 that have a certain genetic
character. suppose...
asked 22 minutes ago -
Topic: Hydrogenic Atoms
The wavefunction of one of the d orbitals is proportional to sin
θ...
asked 22 minutes ago -
6. Suppose that the Bank of Canada conducts an open market
purchase of $2000 from a...
asked 37 minutes ago -
A) Suppose U=X∙Y3. Find X* and Y*.
B) Suppose U=X3∙Y. Find X* and Y*.
C) Suppose...
asked 46 minutes ago -
The only quantities of good 1 that Barbara can buy are 1 unit or
zero units....
asked 35 minutes ago -
c. Node Admittance matrix and its use in different calculations
of power transmission system. Display the...
asked 43 minutes ago -
Try to get the code down to less than 40 lines. (PYTHON)
import random
fave_number =...
asked 43 minutes ago -
2.
Regression analysis was applied between sales (in $1,000) and
advertising (in $100), and the following...
asked 51 minutes ago -
I just took a final for chemistry 2. There were alot of
questions on cell potential....
asked 1 hour ago