Java 3 - 2025

Week 8

2026

Teach categories as "Doll,Teddy Bear" instead of "2,3"
Teach an object with multiple categories.

Product Categories

Your project must contain at least one public-facing object. In my example, I have a public- and admin- view for products.
Create a new database table for categories. Unsigned means negative numbers are not allowed.
Insert some records.

CREATE TABLE product_category (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

INSERT INTO product_category (name) VALUES
    ('Bean Bag Toy'),
    ('Teddy Bear'),
    ('Doll');

Update your existing table to include a foreign key constraint on the category id.
Update each record to reference one of the categories.

ALTER TABLE products ADD category_id INT UNSIGNED NOT NULL DEFAULT 1;
ALTER TABLE products ADD FOREIGN KEY (category_id) REFERENCES product_category(id);

Insert more Products

Insert more records to have more than 20 records in the Products table.
Make sure your database is set up as shown on this slide.

-- Bean Bag Toys (category_id = 1)
INSERT INTO Products (prod_id, vend_id, prod_name, prod_price, prod_desc, category_id)
VALUES
('BBT001', 'FRB01', 'Comfy Bean Bag Toy', 25.99, 'A soft and comfortable bean bag toy for kids.', 1),
('BBT002', 'FRB01', 'Fun Bean Bag Toy', 19.99, 'A playful and colorful bean bag toy for young children.', 1),
('BBT003', 'FRB01', 'Bean Bag Buddy', 29.99, 'A friendly and plush bean bag toy with a cute face.', 1),
('BBT004', 'FRB01', 'Squishy Bean Bag Toy', 24.99, 'A squishy and soft bean bag toy that hugs back.', 1),
('BBT005', 'FRB01', 'Plush Bean Bag Toy', 34.99, 'A large, plush bean bag toy perfect for cuddling.', 1);

-- Teddy Bears (category_id = 2)
INSERT INTO Products (prod_id, vend_id, prod_name, prod_price, prod_desc, category_id)
VALUES
('TBE001', 'BRS01', 'Classic Teddy Bear', 40.00, 'A traditional teddy bear with soft fur and a red bow.', 2),
('TBE002', 'BRS01', 'Plush Teddy Bear', 45.50, 'A cuddly teddy bear with extra plush fur for comfort.', 2),
('TBE003', 'BRE02', 'Teddy Bear with Heart', 50.00, 'A teddy bear holding a heart-shaped pillow.', 2),
('TBE004', 'BRE02', 'Teddy Bear Family', 80.00, 'A set of three teddy bears of different sizes for family playtime.', 2),
('TBE005', 'BRS01', 'Sleepy Teddy Bear', 37.99, 'A sleepy teddy bear with a soft nightcap for bedtime comfort.', 2);

-- Dolls (category_id = 3)
INSERT INTO Products (prod_id, vend_id, prod_name, prod_price, prod_desc, category_id)
VALUES
('DOL001', 'DLL01', 'Classic Doll', 22.99, 'A timeless doll with long hair and a beautiful dress.', 3),
('DOL002', 'DLL01', 'Baby Doll', 19.99, 'A soft baby doll with a bottle and pacifier.', 3),
('DOL003', 'DLL01', 'Fashion Doll', 29.99, 'A stylish fashion doll with interchangeable outfits.', 3),
('DOL004', 'DLL01', 'Princess Doll', 35.50, 'A princess doll with a sparkling gown and tiara.', 3),
('DOL005', 'DLL01', 'Talking Doll', 44.99, 'An interactive doll that talks and sings songs.', 3);

Join Queries

Run this query to view the products and their categories.

SELECT p.prod_id, p.prod_name, p.prod_price, p.prod_desc, c.id AS category_id, c.name AS category_name
FROM products AS p JOIN product_category AS c
WHERE p.category_id = c.id;

Run this query to view the products and limit which categories display. The %s are wildcard characters

SELECT p.prod_id, p.prod_name, p.prod_price, p.prod_desc, c.id AS category_id, c.name AS category_name
FROM products AS p JOIN product_category AS c
WHERE p.category_id = c.id
AND '1,2' LIKE CONCAT('%', p.category_id , '%');

Run this query to paginate the results.
- LIMIT 5 means show 5
- OFFSET 0 means start at index 0
- LIMIT 5 OFFSET 5 would show products 6-10

SELECT p.prod_id, p.prod_name, p.prod_price, p.prod_desc, c.id AS category_id, c.name AS category_name
FROM products AS p JOIN product_category AS c
WHERE p.category_id = c.id
LIMIT 5 OFFSET 0;

New Stored Procedure

Drop and recreate the sp_get_all_products() stored procedure.
The new stored procedure joins two tables together and also includes limit and offset which will be used for pagination.
The p_category_id is a VARCHAR because I will allow multiple categories to be selected in the form of "1,2", "2,3", or "1,3"
The AND clause is used to ignore the category filter if the parameter is an empty string.

DROP PROCEDURE sp_get_all_products;

CREATE PROCEDURE sp_get_all_products(
  IN p_limit int,
  IN p_offset int,
  IN p_category_id VARCHAR(255)
)
BEGIN
  SELECT p.prod_id, p.prod_name, p.prod_price, p.prod_desc, c.id AS category_id, c.name AS category_name
  FROM products AS p JOIN product_category AS c
  WHERE p.category_id = c.id
  AND (
	  CASE 
	  WHEN p_category_id <> ''
      THEN p_category_id LIKE CONCAT('%', p.category_id , '%')
      ELSE TRUE
	  END
  )
  LIMIT p_limit OFFSET p_offset;
END;

Product POJO

Update the Product POJO to include a categoryId and categoryName instance variable.
Update the parameterized constructor.
Create getter and setter methods.

public class Product {
	// code omitted
    private int categoryId;
    private String categoryName;
    
    // This constructor is for products on the shop page
    public Product(String id, String name, double price, String description, int categoryId, String categoryName) {
        // code omitted
        this.categoryId = categoryId;
        this.categoryName = categoryName;
    }
    
    // code omitted
    
    public int getCategoryId() {
        return categoryId;
    }

    public void setCategoryId(int categoryId) {
        this.categoryId = categoryId;
    }

    public String getCategoryName() {
        return categoryName;
    }

    public void setCategoryName(String categoryName) {
        this.categoryName = categoryName;
    }
    
}

ProductDAO

Update the ProductDAO.getProducts method to include the new stored procedure parameters and data returned.

// This method get products for the Shop page.
public static List<Product> getProducts(int limit, int offset, String categories) {
    List<Product> products = new ArrayList<>();
    try(Connection connection = getConnection()) {
        CallableStatement statement = connection.prepareCall("{CALL sp_get_all_products(?,?,?)}");
        statement.setInt(1, limit);
        statement.setInt(2, offset);
        statement.setString(3, categories);
        ResultSet rs = statement.executeQuery();
        while(rs.next()) {
            String id = rs.getString("prod_id");
            String name = rs.getString("prod_name");
            double price = rs.getDouble("prod_price");
            String description = rs.getString("prod_desc");
            int categoryId = rs.getInt("category_id");
            String categoryName = rs.getString("category_name");
            products.add(new Product(id, name, price, description, categoryId, categoryName));
        }
    } catch(SQLException e) {
        throw new RuntimeException("Database error - " + e.getMessage());
    }
    return products;
}

Shop Servlet

Update the doGet method of the Shop servlet to define the limit, offset, and categories parameters.
- We will later use req.getParameter to get this data.

@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
    int limit = 5;
    int offset = 0;
    String categories = "1,2,3";
    List<Product> products = ProductDAO.getProducts(limit, offset, categories);
    req.setAttribute("products", products);
    req.getRequestDispatcher("WEB-INF/ecommerce/shop.jsp").forward(req, resp);
}

ProductDAO

Update the main method to test the getProducts method.

public static void main(String[] args) {
    getProducts(5, 0, "1,2,3").forEach(System.out::println);
}

Category Count

Run this query to view all product categories with a count of the number of products in each category.

SELECT c.id, c.name, COUNT(p.category_id) AS num_products
FROM products AS p JOIN product_category AS c
ON p.category_id = c.id
GROUP BY c.id, c.name
ORDER BY c.name;

Create a stored procedure for that previous query.

CREATE PROCEDURE sp_get_product_categories()
BEGIN
    SELECT c.id, c.name, COUNT(p.category_id) AS num_products
    FROM products AS p JOIN product_category AS c
    ON p.category_id = c.id
    GROUP BY c.id, c.name
    ORDER BY c.name;
END;

ProductCategory POJO

Create a new POJO in the models package called "ProductCategory".
Add the following instance variables.
Generate constructors, getters, setters, and toString.

public class ProductCategory {
    private int id;
    private String name;
    private int numProducts;
}

ProductDAO

Create a new method in the ProductDAO class called getAllCategories.
Call the new stored procedure and return a List<ProductCategory> object.

public static List<ProductCategory> getAllCategories() {
    List<ProductCategory> categories = new ArrayList<>();
    try (Connection connection = getConnection();
         CallableStatement statement = connection.prepareCall("{CALL sp_get_product_categories()}");
         ResultSet resultSet = statement.executeQuery();
    ) {
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int numProducts = resultSet.getInt("num_products");
            categories.add(new ProductCategory(id, name, numProducts));
        }
    } catch(SQLException e) {
        System.out.println(e.getMessage());
    }
    return categories;
}

Shop Servlet

Update the doGet method of the Shop servlet to get the product categories.

List<ProductCategory> productCategories = ProductDAO.getAllCategories();
req.setAttribute("productCategories", productCategories);

ProductDAO

Update the main method to test the getAllCategories method.

public static void main(String[] args) {
    getAllCategories().forEach(System.out::println);
}

shop-sidebar.jspf

Create a shop-sidebar.jspf file. Add this code to create a list of categories that have multiple selections, and a list of product limits that can have one selection.
Similar to the edit-profile page, this sidebar will hide on mobile and tablet view.

<!-- Right sidebar START -->
<div class="col-lg-3">
    <!-- Responsive offcanvas body START -->
    <div class="offcanvas-lg offcanvas-end" tabindex="-1" id="offcanvasSidebar">
        <div class="offcanvas-header bg-light">
            <h5 class="offcanvas-title" id="offcanvasNavbarLabel">Advanced Filter</h5>
            <button  type="button" class="btn-close" data-bs-dismiss="offcanvas" data-bs-target="#offcanvasSidebar" aria-label="Close"></button>
        </div>
        <div class="offcanvas-body p-3">
            <form>
                <!-- Category START -->
                <div class="card card-body shadow p-4 mb-4">
                    <!-- Title -->
                    <h4 class="mb-2">Category</h4>
  
                    <!-- Checkbox -->
                    <div class="form-check">
                      <input class="form-check-input" type="checkbox" value="" id="filter-1">
                      <label class="form-check-label" for="filter-1">Option 1</label>
                    </div>
                    <!-- Checkbox -->
                    <div class="form-check">
                      <input class="form-check-input" type="checkbox" value="" id="filter-2">
                      <label class="form-check-label" for="filter-2">Option 2</label>
                    </div>
                    <!-- Checkbox -->
                    <div class="form-check">
                      <input class="form-check-input" type="checkbox" value="" id="filter-3">
                      <label class="form-check-label" for="filter-3">Option 3</label>
                    </div>
                    <!-- Checkbox -->
                    <div class="form-check">
                      <input class="form-check-input" type="checkbox" value="" id="filter-4">
                      <label class="form-check-label" for="filter-4">Option 4</label>
                    </div>
                </div>
                <!-- Category END -->

                <!-- Limit START -->
                <div class="card card-body shadow p-4">
                    <!-- Title -->
                    <h4 class="mb-2">Show</h4>
                    <div class="btn-group" role="group" aria-label="Show radio toggle button group">
                        <input type="radio" class="btn-check" id="show-5" autocomplete="off">
                        <label class="btn btn-outline-success" for="show-5">5</label>

                        <input type="radio" class="btn-check" id="show-10" autocomplete="off">
                        <label class="btn btn-outline-success" for="show-10">10</label>

                        <input type="radio" class="btn-check" id="show-20" autocomplete="off">
                        <label class="btn btn-outline-success" for="show-20">20</label>
                    </div>
                </div><!-- Limit END -->
              	<!-- Submit Button -->
        		<div class="d-grid text-center m-4">
            		<button type="submit" class="btn btn-primary">Filter Results</button>
        		</div>
            </form><!-- Form End -->
        </div><!-- offcanvas-body End -->
    </div><!-- Responsive offcanvas body END -->
</div><!-- Right sidebar END -->

shop.jsp

Update the shop.jsp view to display the products in a 3 column layout on desktop view, with the sidebar on the right.
A blue toggle button will display on tablet and mobile view to open the sidebar.


    <div class="container py-4">
        <div class="col d-flex justify-content-between align-items-center">
            <h2 class="mb-4">Shop the best selection of toys around!</h2>
            <!-- Responsive toggler START -->
            <button class="btn btn-primary d-lg-none" type="button" data-bs-toggle="offcanvas" data-bs-target="#offcanvasSidebar" aria-controls="offcanvasSidebar">
                <i class="bi bi-list fs-4"></i>
            </button>
            <!-- Responsive toggler END -->
        </div>
        <div class="row">
            <!-- Main content START -->
            <div class="col-lg-9">
                <div class="row g-4">
                    <c:forEach items="${products}" var="product">
                    <%-- 12 means full-width, 6 means half-width, 4 means one-third width, 3 means one-forth width   --%>
                    <div class="col-xs-12 col-sm-6 col-md-4">
                        <div class="card shadow-sm">
                            <div class="card-header py-2">
                                <h4 class="my-0 text-center">${product.name}</h4>
                            </div>
                            <div class="card-body">
                                <p class="card-text">${product.description}</p>
                                <div class="d-flex justify-content-between align-items-center">
                                    <small class="fw-bold"><fmt:formatNumber value="${product.price}" type="currency" /></small>
                                    <a href="${appURL}/add-to-cart?prod_id=${product.id}" class="btn btn-secondary">Add to Cart</a>
                                </div>
                            </div>
                        </div>
                    </div>
                    </c:forEach>
                </div>
            </div><!-- Main content END -->
            <%@include file="shop-sidebar.jspf"%>
        </div><!-- Row END -->
    </div><!-- Container END -->

shop-sidebar checkboxes

Edit the form tag to make GET requests to the Shop servlet.
<form method="GET" action="${appURL}/shop">
- Up until now, we have only used the POST method with forms.
Keep one of the category checkboxes. Add a c:forEach tag around the div.
<c:forEach items="${productCategories}" var="category">...</c:forEach>
Display the category name and number of products in the label.
<label class="form-check-label" for="filter-1">${category.name} (${category.numProducts})</label>
Set the category id as the input value.
value="${category.id}"
Add a name attribute.
name="categories"

shop-sidebar radio buttons

Set a value attribute for each.
value="5" value="10" value="20"
Add a name attribute.
name="limit"
Add a checked attribute to the second radio input.
View the live shop page.

Shop Servlet doGet

In the Shop servlet's doGet method, get all of the possible query string parameters.
The limit value needs to be converted into an int. The default value will be the same as the pre-checked option.
Because there may be more than one category, those values must be retrieved as an array. Then the values of the array must be converted to a comma-separated String.
Set both values as request attributes

String limitValue = req.getParameter("limit");
int limit = 10;
try {
    limit = Integer.parseInt(limitValue);
} catch(NumberFormatException e) {

}
int offset = 0; // Will be implemented in a future lesson on pagination
String[] categoryValues = req.getParameterValues("categories");
String categories = "";
if(categoryValues != null && categoryValues.length > 0) {
    categories = String.join(",", categoryValues);
}
req.setAttribute("categories", categories);
req.setAttribute("limit", limit);

Re-check Checked Options

Add a c:if statement to the Category checkbox input to select the last selected items.
<c:if test="${fn:contains(categories, category.id)}">checked</c:if>
- The fn statement is available because of the third taglib directive in the top.jspf file.
Add c:if statements to each radio button input to select the last selected item.

<c:if test="${limit eq 5}">checked</c:if>

<c:if test="${limit eq 10}">checked</c:if>

<c:if test="${limit eq 20}">checked</c:if>
Run and test the program.

shop.jsp categories

Inside each card-body, display the category name as a Bootstrap Badge.
Add a hyperlink to make the text show the items in the category clicked.
<p class="badge rounded-pill text-bg-secondary"><a class="text-white" href="${appURL}/shop?categories=${product.categoryId}">${product.categoryName}</a></p>