Java 2

Week 9

Topics Covered this week

Operations with relational databases

6 Step Process

STEP 1: Create/Update stored procedure
STEP 2: Create/Update a POJO
STEP 3: Create/Update a DAO
STEP 4: Create/Update a Servlet
STEP 5: Create/Update a JSP
REPEAT

Stored Procedure

Create a stored procedure to get product data with vendor information.

CREATE PROCEDURE sp_get_all_products_admin()
BEGIN
    SELECT prod_id, prod_name, prod_price, prod_desc, products.vend_id, vend_name
    FROM products JOIN vendors
    ON products.vend_id = vendors.vend_id;
END;

POJO

Add two new attributes to the Product class.
Add a new constructor and getter/setter methods.

public class Product {
	// Code omitted
    private String vendorId;
    private String vendorName;
    
    // Code omitted
    
    // This constructor is for the admin view of products
    public Product(String id, String name, double price, String description, String vendorId, String vendorName) {
        this.id = id;
        this.name = name;
        this.price = price;
        this.description = description;
        this.vendorId = vendorId;
        this.vendorName = vendorName;
    }

    // code omitted

    public String getVendorId() {
        return vendorId;
    }

    public void setVendorId(String vendorId) {
        this.vendorId = vendorId;
    }

    public String getVendorName() {
        return vendorName;
    }

    public void setVendorName(String vendorName) {
        this.vendorName = vendorName;
    }

}

DAO

Add a ProductDAO method to get a different list of products.

public class ProductDAO{

    // code omitted

    // This method get products for the Admin page.
    public static List<Product> getProductsAdmin() {
        List<Product> products = new ArrayList<>();
        try(Connection connection = getConnection()) {
            CallableStatement statement = connection.prepareCall("{CALL sp_get_all_products_admin()}");
            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");
                String vendorId = rs.getString("vend_id");
                String vendorName = rs.getString("vend_name");
                products.add(new Product(id, name, price, description, vendorId, vendorName));
            }
        } catch(SQLException e) {
            throw new RuntimeException("Database error - " + e.getMessage());
        }
        return products;
    }
}

Servlet

Create a new servlet called "ProductsAdmin" with the URL value of "/products".
Create a doGet method. Call the ProductDAO.getProductsAdmin() method to get the List<Product>.
Set that object as a request attribute.
Forward to a JSP.

import edu.kirkwood.ecommerce.model.Product;
import edu.kirkwood.ecommerce.model.ProductDAO;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

import java.io.IOException;
import java.util.List;

@WebServlet(value="/products")
public class ProductAdmin extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        List<Product> products = ProductDAO.getProductsAdmin();
        req.setAttribute("products", products);
        req.getRequestDispatcher("WEB-INF/ecommerce/admin-product.jsp").forward(req, resp);
    }
}

JSP

This HTML uses the dashboard table example and various table classes to display the output.

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="jakarta.tags.core" %>
<%@ taglib prefix="fmt" uri="jakarta.tags.fmt" %>
<fmt:setLocale value="en-US" />
<!doctype html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Product Admin</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet"
          integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">
</head>
<body>
<%@ include file="../../main-nav.jsp" %>
<div class="container py-4">
    <h2>Products</h2>
    <div class="table-responsive small">
        <table class="table table-striped table-sm">
            <thead>
            <tr>
                <th scope="col"></th>
                <th scope="col">Name</th>
                <th scope="col">Description</th>
                <th scope="col">Price</th>
                <th scope="col">Vendor</th>
            </tr>
            </thead>
            <tbody>
            <c:forEach items="${products}" var="product">
            <tr>
                <td>
                    <a href="update-product?id=${product.id}" class="btn btn-primary" style="--bs-btn-padding-y: .25rem; --bs-btn-padding-x: .5rem; --bs-btn-font-size: .75rem;">Update</a>
                    <a href="delete-product?id=${product.id}" class="btn btn-danger" style="--bs-btn-padding-y: .25rem; --bs-btn-padding-x: .5rem; --bs-btn-font-size: .75rem;">Delete</a>
                </td>
                <td class="align-middle">${product.name}</td>
                <td class="align-middle">${product.description}</td>
                <td class="text-end align-middle"><fmt:formatNumber value="${product.price}" type="currency" /></td>
                <td class="align-middle"><a href="vendors?id=${product.vendorId}">${product.vendorName}</a></td>
            </tr>
            </c:forEach>
            </tbody>
        </table>
    </div>
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"
        integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz"
        crossorigin="anonymous"></script>
</body>
</html>

Stored Procedure

Create a stored procedure to get order data with customer information.

CREATE PROCEDURE sp_get_all_orders_admin()
BEGIN
    SELECT order_num, order_date, orders.cust_id, cust_name
    FROM orders JOIN customers
    ON orders.cust_id = customers.cust_id;
END;

POJO

Create an Order Class

import java.time.Instant;

public class Order {
    private int orderNum;
    private Instant orderDate;
    private String customerId;
    private String customerName;

    public Order() {
    }

    public Order(int orderNum, Instant orderDate, String customerId, String customerName) {
        this.orderNum = orderNum;
        this.orderDate = orderDate;
        this.customerId = customerId;
        this.customerName = customerName;
    }

    public int getOrderNum() {
        return orderNum;
    }

    public void setOrderNum(int orderNum) {
        this.orderNum = orderNum;
    }

    public Instant getOrderDate() {
        return orderDate;
    }

    public void setOrderDate(Instant orderDate) {
        this.orderDate = orderDate;
    }

    public String getCustomerId() {
        return customerId;
    }

    public void setCustomerId(String customerId) {
        this.customerId = customerId;
    }

    public String getCustomerName() {
        return customerName;
    }

    public void setCustomerName(String customerName) {
        this.customerName = customerName;
    }

    @Override
    public String toString() {
        return "Order{" +
                "orderNum=" + orderNum +
                ", orderDate=" + orderDate +
                ", customerId='" + customerId + '\'' +
                ", customerName='" + customerName + '\'' +
                '}';
    }
}

OrderDAO

Create an OrderDAO Class with a method to get a List<Order>

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;

import static edu.kirkwood.shared.DB_Connect.getConnection;

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

    // This method get products for the Shop page.
    public static List<Order> getOrders() {
        List<Order> orders = new ArrayList<>();
        try(Connection connection = getConnection()) {
            CallableStatement statement = connection.prepareCall("{CALL sp_get_all_orders_admin()}");
            ResultSet rs = statement.executeQuery();
            while(rs.next()) {
                int id = rs.getInt("order_num");
                Instant orderDate = rs.getTimestamp("order_date").toInstant();
                String customerID = rs.getString("cust_id");
                String customerName = rs.getString("cust_name");
                orders.add(new Order(id, orderDate, customerID, customerName));
            }
        } catch(SQLException e) {
            throw new RuntimeException("Database error - " + e.getMessage());
        }
        return orders;
    }
}