1. Open the Product class and add a new instance variable named note. Then, add get and set methods for this instance variable.

2. Open the ProductDB class and add code to the getProductFromRow method that sets the note in the Product object.

3. Update the add method so that it adds the note to the database.

4. Update the update method so that it updates the note in the database. Hint: Dont forget to set the parameter number for the product ID.

5. Open the Main method and add code to the displayAllProducts method that displays the notes field of the Product class.

6. Add code to the addProduct method of the Main Class to add a note to the product.

7. Add code to the updateProduct method of the Main Class to update the note for the product.

8. Run the application and to make sure the new code works correctly. Create a new product that has a note. Update the note for an existing product. Display all of the products and make sure you can see the notes. And so on.

9. Note: SQL syntax errors are common. Use a breakpoint to exam the string variable that holds the SQL statement. You can copy the SQL statement into the Workbench to test the statement directly on the database. Look for missing spaces or commas in the wrong place.

//--------------------------------------------------product.java

package murach.business;

import java.text.NumberFormat;

public class Product { private long id; private String code; private String description; private double price;

public long getId() { return id; }

public void setId(long id) { this.id = id; }

public String getCode() { return code; }

public void setCode(String code) { this.code = code; }

public String getDescription() { return description; }

public void setDescription(String name) { this.description = name; }

public double getPrice() { return price; }

public void setPrice(double price) { this.price = price; } public String getPriceFormatted() { NumberFormat currencyFormatter = NumberFormat.getCurrencyInstance(); return currencyFormatter.format(getPrice()); }

}

//------------------------------------------------------------------productDB.java

package murach.db;

import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List;

import murach.business.Product;

public class ProductDB {

private static Product getProductFromRow(ResultSet rs) throws SQLException {

int productID = rs.getInt(1); String code = rs.getString(2); String description = rs.getString(3); double price = rs.getDouble(4); String note = rs.getString(5);

Product p = new Product(); p.setId(productID); p.setCode(code); p.setDescription(description); p.setPrice(price); return p; }

public static List getAll() throws DBException { String sql = "SELECT * FROM Product ORDER BY ProductID"; List products = new ArrayList<>(); Connection connection = DBUtil.getConnection(); try (PreparedStatement ps = connection.prepareStatement(sql); ResultSet rs = ps.executeQuery()) { while (rs.next()) { Product p = getProductFromRow(rs); products.add(p); } rs.close(); return products; } catch (SQLException e) { throw new DBException(e); } }

public static Product get(String productCode) throws DBException { String sql = "SELECT * FROM Product WHERE Code = ?"; Connection connection = DBUtil.getConnection(); try (PreparedStatement ps = connection.prepareStatement(sql)) { ps.setString(1, productCode); ResultSet rs = ps.executeQuery(); if (rs.next()) { Product p = getProductFromRow(rs); rs.close(); return p; } else { rs.close(); return null; } } catch (SQLException e) { throw new DBException(e); } }

public static void add(Product product) throws DBException { String sql = "INSERT INTO Product (Code, Description, ListPrice) " + "VALUES (?, ?, ?)"; Connection connection = DBUtil.getConnection(); try (PreparedStatement ps = connection.prepareStatement(sql)) { ps.setString(1, product.getCode()); ps.setString(2, product.getDescription()); ps.setDouble(3, product.getPrice()); ps.executeUpdate(); } catch (SQLException e) { throw new DBException(e); } }

public static void update(Product product) throws DBException { String sql = "UPDATE Product SET " + "Code = ?, " + "Description = ?, " + "ListPrice = ?" + "WHERE ProductID = ?"; Connection connection = DBUtil.getConnection(); try (PreparedStatement ps = connection.prepareStatement(sql)) { ps.setString(1, product.getCode()); ps.setString(2, product.getDescription()); ps.setDouble(3, product.getPrice()); ps.setLong(4, product.getId()); ps.executeUpdate(); } catch (SQLException e) { throw new DBException(e); } }

public static void delete(Product product) throws DBException { String sql = "DELETE FROM Product " + "WHERE ProductID = ?"; Connection connection = DBUtil.getConnection(); try (PreparedStatement ps = connection.prepareStatement(sql)) { ps.setLong(1, product.getId()); ps.executeUpdate(); } catch (SQLException e) { throw new DBException(e); } } }

//---------------------------------------------------------main.java

package murach.ui;

import java.util.List; import murach.business.Product; import murach.db.DBException; import murach.db.DBUtil; import murach.db.ProductDB;

public class Main {

// declare a class variable public static void main(String args[]) { // display a welcome message Console.displayNewLine(); Console.display("Welcome to the Product Manager ");

// display the command menu displayMenu();

// perform 1 or more actions String action = ""; while (!action.equalsIgnoreCase("exit")) { // get the input from the user action = Console.getString("Enter a command: "); Console.displayNewLine();

if (action.equalsIgnoreCase("list")) { displayAllProducts(); } else if (action.equalsIgnoreCase("add")) { addProduct(); } else if (action.equalsIgnoreCase("update")) { updateProduct(); } else if (action.equalsIgnoreCase("del") || action.equalsIgnoreCase("delete")) { deleteProduct(); } else if (action.equalsIgnoreCase("help") || action.equalsIgnoreCase("menu")) { displayMenu(); } else if (action.equalsIgnoreCase("exit")) { Console.display("Bye. "); } else { Console.display("Error! Not a valid command. "); } } }

public static void displayMenu() { Console.display("COMMAND MENU"); Console.display("list - List all products"); Console.display("add - Add a product"); Console.display("update - Update a product"); Console.display("del - Delete a product"); Console.display("help - Show this menu"); Console.display("exit - Exit this application "); }

public static void displayAllProducts() { Console.display("PRODUCT LIST");

List products = null; try { products = ProductDB.getAll(); } catch (DBException e) { Console.display(e + " "); } if (products == null) { Console.display("Error! Unable to get products. "); } else { Product p; StringBuilder sb = new StringBuilder(); for (Product product : products) { p = product; sb.append(StringUtil.padWithSpaces( p.getCode(), 12)); sb.append(StringUtil.padWithSpaces( p.getDescription(), 34)); sb.append(p.getPriceFormatted()); sb.append(" "); } Console.display(sb.toString()); } }

public static void addProduct() { String code = Console.getString("Enter product code: "); String description = Console.getString("Enter product name: "); double price = Console.getDouble("Enter price: ");

Product product = new Product(); product.setCode(code); product.setDescription(description); product.setPrice(price); try { ProductDB.add(product); Console.display(product.getDescription() + " was added to the database. "); } catch (DBException e) { Console.display("Error! Unable to add product."); Console.display(e + " "); } }

public static void updateProduct() { String code = Console.getString("Enter product code to update: "); Product product; try { product = ProductDB.get(code); if (product == null) { throw new Exception("Product not found."); } } catch (Exception e) { Console.display("Error! Unable to update product."); Console.display(e + " "); return; } String description = Console.getString("Enter product name: "); double price = Console.getDouble("Enter price: ");

product.setDescription(description); product.setPrice(price); try { ProductDB.update(product); } catch (DBException e) { Console.display("Error! Unable to update product."); Console.display(e + " "); } Console.display(product.getDescription() + " was updated in the database. "); } public static void deleteProduct() { String code = Console.getString("Enter product code to delete: ");

Product product; try { product = ProductDB.get(code); if (product == null) { throw new Exception("Product not found."); } } catch (Exception e) { Console.display("Error! Unable to delete product."); Console.display(e + " "); return; } try { ProductDB.delete(product); } catch (DBException e) { Console.display("Error! Unable to delete product."); Console.display(e + " "); } Console.display(product.getDescription() + " was deleted from the database. "); } public static void exit() { try { DBUtil.closeConnection(); } catch (DBException e) { Console.display("Error! Unable to close connection."); Console.display(e + " "); } System.out.println("Bye. "); System.exit(0); } }