database properties: jdbc.url=jdbc:derby:BigJavaDB;create=true # With other data
ID: 3921532 • Letter: D
Question
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);
}
}
Explanation / Answer
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);
}
}
}
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.