Write the code to complete the methods in OrderJDBC.java (look for TODO items).
ID: 3604921 • Letter: W
Question
Write the code to complete the methods in OrderJDBC.java (look for TODO items).
List all customers in database (listAllCustomers) 3 marks List all orders for a customer (listCustomerOrders) 3 marks List all lineitems for an order (listLineItemsForOrder) 2 marks Computer order total (computeOrderTotal) 2 marks Add a customer (addCustomer) 2 marks Delete a customer (deleteCustomer) (Make sure to also delete Orders and OrderedProducts for the customer deleted). 2 marks Update a customer (updateCustomer) 2 marks New order (newOrder) 2 marks New order item (newLineItem) 2 marks Update order total (updateOrderTotal) 2 marks Query1: Return the list of products that have not been in any order. Hint: Left join can be used instead of a subquery. 3 marks Query2: 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. 3 marks Query3: 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. 3 marks Query4: Return the employees who have had at least 2 distinct orders where some product on the order had quantity >= 5. 4 marksExplanation / Answer
import java.sql.*;
public class JdbcDb {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/Datab";
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
System.out.println("Inserting records into the table...");
stmt = conn.createStatement();
//1
String sql = "Select * from customer";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
System.out.print("ID: " + id);
System.out.println("name" + name);
}
rs.close();
}
sql="Select orderId,custname from customer,orders where order.custId=customer.custId";
catch(SQLException se){
se.printStackTrace();
}
catch(Exception e){
e.printStackTrace();
}
finally{
try{
if(stmt!=null)
conn.close();
}catch(SQLException se){
}
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
}
}
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.