Homework Soursedatabase properties jdbcurljdbcderbyBigJavaDBcreatetrue Wit
database properties:
jdbc.url=jdbc:derby:BigJavaDB;create=true
# With other databases, you may need to add entries such as these
# jdbc.username=admin
# jdbc.password=secret
# jdbc.driver=org.apache.derby.jdbc.EmbeddedDriver
InventoryDB:
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
/**
* An inventory consisting of multiple products.
* Uses a JDBC database interface to manage the data.
*/
public class InventoryDB
{
/**
* Create or re-create the ProductsDB table in the database with
* some default data.
* @param conn - Database connection
* @throws SQLException - on any database error
*/
public void initialize() throws SQLException
{
try (Connection conn = SimpleDataSource.getConnection())
{
try (Statement stat = conn.createStatement())
{
try
{
// This will fail if the table doesn\'t exist. That is OK.
stat.execute(\"DROP TABLE ProductsDB\");
}
catch (SQLException e)
{
System.out.println(\"Notice: Exception during DROP TABLE Products: \" + e.getMessage() + \" (This is expected when the database is empty)\");
}
// If an execption occurs after this point, allow it to be thrown.
// ProductsDB table: Product_Code, Description, Quantity, Price
stat.execute(\"CREATE TABLE ProductsDB (Product_Code VARCHAR(7), Description VARCHAR(40), Quantity INT, Price DECIMAL(10,2))\");
// Add default list of products using parallel arrays.
String productCodes[] = {\"116-064\", \"257-535\", \"643-119\", \"011-025\"};
String descriptions[] = {\"Toaster\", \"Hair dryer\", \"Car vacuum\", \"Gallon 2% Milk\"};
int quantities[] = {50, 75, 43, 111};
double prices[] = {24.95, 29.95, 19.99, 2.95};
for (int i = 0; i < productCodes.length; i++)
{
ProductDB a = new ProductDB(productCodes[i]);
a.addProduct(descriptions[i], quantities[i], prices[i]);
System.out.printf(\"Notice: inserted product %s %s %d %.2f\ \",
productCodes[i], descriptions[i], quantities[i], prices[i]);
}
}
}
}
/**
* Obtain an array list of all the Products in the Inventory.
* @return arraylist of ProductDB
* @throws SQLException - on any database error
*/
public ArrayList<ProductDB> getAllProducts() throws SQLException
{
ArrayList<ProductDB> products = new ArrayList<ProductDB>();
try (Connection conn = SimpleDataSource.getConnection())
{
try (Statement stat = conn.createStatement())
{
// ProductsDB table: AccountNumber, Balance
ResultSet result = stat.executeQuery(\"SELECT Product_Code FROM Products\");
while (result.next())
{
ProductDB a = new ProductDB(result.getString(1));
products.add(a);
}
}
}
return products;
}
/**
* Finds a product with a given code or null if not found.
* @param productCode the number to find
* @return the product with the given code
* @throws SQLException - on any database error
*/
public ProductDB find(String productCode) throws SQLException
{
try (Connection conn = SimpleDataSource.getConnection())
{
// Does the product exist?
try (PreparedStatement stat = conn.prepareStatement(\"SELECT COUNT(*) FROM ProductsDB WHERE Product_Code = ?\"))
{
stat.setString(1, productCode);
ResultSet result = stat.executeQuery();
// There must be one row returned.
result.next();
if (result.getInt(1) == 0)
{
return null;
}
// Product exists: return it.
ProductDB a = new ProductDB(productCode);
return a;
}
}
}
/**
* Gets the sum of the products in this inventory.
* @return the sum of the balances
* @throws SQLException - on any database error
*/
public double getTotalValue() throws SQLException
{
double total = 0;
ArrayList<ProductDB> products = getAllProducts();
for (ProductDB a : products)
{
total = total + a.getTotalValue();
}
return total;
}
/**
* Return a string that describes all the products in the inventory.
*/
public String toString()
{
StringBuffer sb = new StringBuffer();
ArrayList<ProductDB> products;
try
{
products = getAllProducts();
for (ProductDB a : products)
{
sb.append(a.toString());
}
}
catch (SQLException e)
{
sb.append(\"SQLException occurred: \" + e.getMessage());
}
return sb.toString();
}
}
InventoryMgr:
import java.io.IOException;
import java.sql.SQLException;
import java.util.Scanner;
public class InventoryMgr
{
public static void main(String[] args) throws ClassNotFoundException, IOException {
SimpleDataSource.init(\"database.properties\");
Scanner in = new Scanner(System.in);
InventoryDB myInventory = new InventoryDB();
boolean done = false;
while (!done)
{
try
{
System.out.println(\"I) Initialize database A)dd Product P)urchase Products S)ell Products C)heck Product Q)uit\");
String input = in.nextLine().toUpperCase();
if (input.equals(\"I\"))
{
System.out.println(\"Enter \'YES\' if you wish to reinitialize the inventory: \");
String answer = in.nextLine();
if (answer.equalsIgnoreCase(\"YES\"))
myInventory.initialize();
else
System.out.println(\"OK, existing data preserved\");
}
else if (input.equals(\"A\"))
{
String productCode = promptForWord(in, \"Enter new product code: \");
if (myInventory.find(productCode) != null)
{
System.out.printf(\"Error: product code %d already exists.\ \", productCode);
}
else
{
String desc = promptForWord(in, \"Enter new product description: \");
int qty = promptForInt(in, \"Enter new product quantity: \");
double price = promptForDouble(in, \"Enter new product price: \");
ProductDB a = new ProductDB(productCode);
a.addProduct(desc, qty, price);
}
}
else if (input.equals(\"P\"))
{
String productCode = promptForWord(in, \"Enter product code for purchase: \");
ProductDB a = myInventory.find(productCode);
if (a == null)
{
System.out.printf(\"Error: product code %s does not exist.\ \", productCode);
}
else
{
System.out.printf(\"Product %s: %s\ \", productCode, a.toString());
int qty = promptForInt(in, \"Enter number of products purchased: \");
a.purchased(qty);
System.out.printf(\"Product %s now has quantity %d.\ \", productCode, a.getQuantity());
}
}
else if (input.equals(\"S\"))
{
String productCode = promptForWord(in, \"Enter product code for sale: \");
ProductDB a = myInventory.find(productCode);
if (a == null)
{
System.out.printf(\"Error: product %s does not exist.\ \", productCode);
}
else
{
System.out.printf(\"Product %s: %s\ \", productCode, a.toString());
int quantitySold = promptForInt(in, \"Enter number of products sold: \");
if (a.getQuantity() < quantitySold)
{
System.out.printf(\"Error: Product %s quantity %d is less than requested quantity %d\ \",
productCode, a.getQuantity(), quantitySold);
}
else
{
a.sold(quantitySold);
System.out.printf(\"Product %s now has quantity %d.\ \", productCode, a.getQuantity());
}
}
}
else if (input.equals(\"C\"))
{
String productCode = promptForWord(in, \"Enter product code to check: \");
ProductDB a = myInventory.find(productCode);
if (a == null)
{
System.out.printf(\"Error: product %s does not exist.\ \", productCode);
}
else
{
System.out.printf(\"Product: %s %s\ \", productCode, a.toString());
}
}
else if (input.equals(\"Q\"))
{
done = true;
}
}
catch (SQLException e)
{
System.out.printf(\"Database exception: %s\ \", e.getMessage());
e.printStackTrace();
}
}
}
/**
* Ask the user for an integer input. Repeat until successful.
* @param in Scanner for reading input
* @param prompt String to show to user
* @return value entered by user
*/
public static int promptForInt(Scanner in, String prompt)
{
int result = 0;
boolean done = false;
while (!done)
{
System.out.print(prompt);
String inputStr = in.nextLine().trim();
try
{
result = Integer.parseInt(inputStr);
done = true;
}
catch (NumberFormatException e)
{
System.out.printf(\"Error: \'%s\' was not recognized as an integer. Please try again.\ \", inputStr);
}
}
return result;
}
/**
* Ask the user for a double precision number. Repeat until successful.
* @param in Scanner for reading input
* @param prompt String to show to user
* @return value entered by user
*/
public static double promptForDouble(Scanner in, String prompt)
{
double result = 0;
boolean done = false;
while (!done)
{
System.out.print(prompt);
String inputStr = in.nextLine().trim();
try
{
result = Double.parseDouble(inputStr);
done = true;
}
catch (NumberFormatException e)
{
System.out.printf(\"Error: \'%s\' was not recognized as a double. Please try again.\ \", inputStr);
}
}
return result;
}
/**
* Ask the user for a single word as a string. Repeat until successful.
* @param in Scanner for reading input
* @param prompt String to show to user
* @return value entered by user
*/
public static String promptForWord(Scanner in, String prompt)
{
System.out.print(prompt);
return in.nextLine().trim();
}
}
ProductDB:
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
A product has a code, description, price, and quantity in stock.
This implementation uses a database table to contain its data.
*/
public class ProductDB
{
private String productCode;
/**
* Constructs a product object for operations on the ProductsDB table.
* @param aProductCode the product code
*/
public ProductDB(String aProductCode)
{
productCode = aProductCode;
}
/**
* Add the data for a product to the database.
* @param description - describes product
* @param quantity - count in inventory
* @throws SQLException - on any database error
*/
public void addProduct(String desc, int qty, double price) throws SQLException
{
try (Connection conn = SimpleDataSource.getConnection())
{
try (PreparedStatement stat = conn.prepareStatement(\"INSERT INTO ProductsDB (Product_Code, Description, Quantity, Price) VALUES (?, ?, ?, ?)\"))
{
stat.setString(1, productCode);
stat.setString(2, desc);
stat.setInt(3, qty);
stat.setDouble(4, price);
stat.execute();
}
}
}
/**
* Increases the quantity of product when we\'ve
* purchased products to replenish our supply.
* @param number the count of products purchased.
* @throws SQLException - on any database error
*/
public void purchased(int qtyPurchased)
throws SQLException
{
// TODO: Update the ProductsDB table\'s quantity for this
// object\'s product code.
}
/**
* Decrease the quantity of product when we\'ve
* sold product to a customer.
* @param qtySold - Number of product sold
* @throws SQLException - on any database error
*/
public void sold(int qtySold)
throws SQLException
{
// TODO: Update the ProductsDB table\'s quantity for this
// object\'s product code.
}
/**
* Gets the description for this product.
* @return the product description
*/
public String getDescription()
throws SQLException
{
// TODO: Query the ProductsDB table for the description
// for this object\'s product code.
return \"\"; // Replace this with the actual description from the ProductsDB table
}
/**
* Gets the quantity of this product.
* @return the current quantity
* @throws SQLException - on any database error
*/
public int getQuantity()
throws SQLException
{
// Query the ProductsDB table for the quantity
// of this object\'s product code.
try (Connection conn = SimpleDataSource.getConnection())
{
try (PreparedStatement stat =
conn.prepareStatement(\"SELECT Quantity FROM ProductsDB WHERE Product_Code = ?\"))
{
// Set the value for the first \'?\' in the prepared statement.
stat.setString(1, productCode);
// Run the query.
ResultSet result = stat.executeQuery();
// There should be only one row in the result set. Advance to
// the first row and get the computed total value of this product.
result.next();
// The computed value is in the first column of this first row.
return result.getInt(1);
}
}
}
/**
* Gets the price of this product.
* @return the current price
* @throws SQLException - on any database error
*/
public double getPrice()
throws SQLException
{
// Query the ProductsDB table for the price
// of this object\'s product code.
try (Connection conn = SimpleDataSource.getConnection())
{
try (PreparedStatement stat = conn.prepareStatement(\"SELECT Price FROM ProductsDB WHERE Product_Code = ?\"))
{
// Set the value for the first \'?\' in the prepared statement.
stat.setString(1, productCode);
ResultSet result = stat.executeQuery();
// There should be only one row in the result set. Advance to
// the first row and get the computed total value of this product.
result.next();
// The computed value is in the first column of this first row.
return result.getDouble(1);
}
}
}
/**
* Gets the code for this product.
* @return the product code
*/
public String getCode()
{
// We keep the product code as the key in the object.
return productCode;
}
/**
* Get the total value in inventory of this product
* (quantity times price).
* return value
* @throws SQLException - on any database error
*/
public double getTotalValue()
throws SQLException
{
// Query the ProductsDB table for the quantity and price
// of this object\'s product code.
try (Connection conn = SimpleDataSource.getConnection())
{
try (PreparedStatement stat = conn.prepareStatement(\"SELECT Quantity * Price FROM ProductsDB WHERE Product_Code = ?\"))
{
stat.setString(1, productCode);
ResultSet result = stat.executeQuery();
// There should be only one row in the result set. Advance to
// the first row and get the computed total value of this product.
result.next();
// The computed value is in the first column of this first row.
return result.getDouble(1);
}
}
}
/**
* Return a string describing this product.
*/
public String toString()
{
String result;
try
{
// Query the ProductsDB table for the description, quantity, and price
// of this object\'s product code.
try (Connection conn = SimpleDataSource.getConnection())
{
try (PreparedStatement stat = conn.prepareStatement(\"SELECT Description, Quantity, Price FROM ProductsDB WHERE Product_Code = ?\"))
{
stat.setString(1, productCode);
ResultSet rs = stat.executeQuery();
// There should be only one row in the result set. Advance to
// the first row and get the computed total value of this product.
rs.next();
result = String.format(\"Product: %s %s %d %.2f\",
productCode, rs.getString(1), rs.getInt(2), rs.getDouble(3));
}
}
}
catch (SQLException e)
{
result = \"SQLException while getting product info: \" + e.getMessage();
}
return result;
}
}
SimpleDataSource:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Properties;
/**
A simple data source for getting database connections.
*/
public class SimpleDataSource
{
private static String url;
private static String username;
private static String password;
/**
Initializes the data source.
@param fileName the name of the property file that
contains the database driver, URL, username, and password
*/
public static void init(String fileName)
throws IOException, ClassNotFoundException
{
Properties props = new Properties();
FileInputStream in = new FileInputStream(fileName);
props.load(in);
String driver = props.getProperty(\"jdbc.driver\");
url = props.getProperty(\"jdbc.url\");
username = props.getProperty(\"jdbc.username\");
if (username == null) username = \"\";
password = props.getProperty(\"jdbc.password\");
if (password == null) password = \"\";
if (driver != null)
Class.forName(driver);
}
/**
Gets a connection to the database.
@return the database connection
*/
public static Connection getConnection() throws SQLException
{
return DriverManager.getConnection(url, username, password);
}
}
Solution
Please find the three functions with explanation below
/**
* Increases the quantity of product when we\'ve
* purchased products to replenish our supply.
* @param number the count of products purchased.
* @throws SQLException - on any database error
*/
public void purchased(int qtyPurchased)
throws SQLException
{
// TODO: Update the ProductsDB table\'s quantity for this
// object\'s product code.
//Getting the current quantity count from Db and then incrementing it with the purchased value
int Quantity= getQuantity()+qtyPurchased;
try (Connection conn = SimpleDataSource.getConnection())
{
try (PreparedStatement stat =
conn.prepareStatement(\"UPDATE ProductsDB SET Quantity=\"+Quantity +\" WHERE Product_Code = ?\"))
{
// Set the value for the first \'?\' in the prepared statement.
stat.setString(1, productCode);
// Run the query.
stat.executeUpdate();
}
}
}
/**
* Decrease the quantity of product when we\'ve
* sold product to a customer.
* @param qtySold - Number of product sold
* @throws SQLException - on any database error
*/
public void sold(int qtySold)
throws SQLException
{
// TODO: Update the ProductsDB table\'s quantity for this
// object\'s product code.
//Getting the current quantity count from Db and then subtracting the sold value from it
int Quantity= getQuantity()-qtySold;
try (Connection conn = SimpleDataSource.getConnection())
{
try (PreparedStatement stat =
conn.prepareStatement(\"UPDATE ProductsDB SET Quantity=\"+Quantity +\" WHERE Product_Code = ?\"))
{
// Set the value for the first \'?\' in the prepared statement.
stat.setString(1, productCode);
// Run the query.
stat.executeUpdate();
}
}
}
/**
* Gets the description for this product.
* @return the product description
*/
public String getDescription()
throws SQLException
{
// TODO: Query the ProductsDB table for the description
// for this object\'s product code.
try (Connection conn = SimpleDataSource.getConnection())
{
try (PreparedStatement stat =
conn.prepareStatement(\"SELECT Description FROM ProductsDB WHERE Product_Code = ?\"))
{
// Set the value for the first \'?\' in the prepared statement.
stat.setString(1, productCode);
// Run the query.
ResultSet result = stat.executeQuery();
// There should be only one row in the result set. Advance to
// the first row and get the description of this product.
result.next();
// The description is in the first column of this first row.
return result.getString(1);
}
}
}
