Java 2

Week 8

Topics Covered this week

Midterm Exam
Operations with relational databases
ER Diagrams

Database Setup

The teacher emailed you a Database URL, username, password, and schema.
Download DataGrip using the JetBrains Toolbox App.
Create a new project called "java2ee-demo"
Click the + icon. Select Data Source, then MySQL.
Download the MySQL driver.
Fill out the required information.
Click Test Connection.
Click OK.
Toggle open the database and select your database schema.
Right-click the database schema, choose Navigation, Jump to Query Console, then Open Default Query Console.

Database Scripts

Download and extract the MySQL scripts from the "Teach Yourself SQL in 10 minutes" book website.
Run the code from the create and populate text files.

CREATE TABLE Customers
(
  cust_id      varchar(10)  NOT NULL PRIMARY KEY,
  cust_name    varchar(50)  NOT NULL ,
  cust_address varchar(50)  NULL ,
  cust_city    varchar(50)  NULL ,
  cust_state   varchar(5)   NULL ,
  cust_zip     varchar(10)  NULL ,
  cust_country varchar(50)  NULL ,
  cust_contact varchar(50)  NULL ,
  cust_email   varchar(255) NULL 
);


CREATE TABLE OrderItems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    varchar(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL,
  primary key (order_num, order_item)
);


CREATE TABLE Orders
(
  order_num  int      NOT NULL PRIMARY KEY,
  order_date datetime NOT NULL ,
  cust_id    varchar(10) NOT NULL 
);

CREATE TABLE Products
(
  prod_id    varchar(10)      NOT NULL PRIMARY KEY,
  vend_id    varchar(10)      NOT NULL ,
  prod_name  varchar(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL 
);

CREATE TABLE Vendors
(
  vend_id      varchar(10) NOT NULL PRIMARY KEY,
  vend_name    varchar(50) NOT NULL ,
  vend_address varchar(50) NULL ,
  vend_city    varchar(50) NULL ,
  vend_state   varchar(5)  NULL ,
  vend_zip     varchar(10) NULL ,
  vend_country varchar(50) NULL 
);

ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num);
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id);
ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);
ALTER TABLE Products ADD CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);

-- ------------------------
-- Populate Customers table
-- ------------------------
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000002', 'Kids Place', '333 South Lake Drive', 'Columbus', 'OH', '43333', 'USA', 'Michelle Green');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000003', 'Fun4All', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'jjones@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000004', 'Fun4All', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Denise L. Stephens', 'dstephens@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000005', 'The Toy Store', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'Kim Howard');

-- ----------------------
-- Populate Vendors table
-- ----------------------
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRS01','Bears R Us','123 Main Street','Bear Town','MI','44444', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRE02','Bear Emporium','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('DLL01','Doll House Inc.','555 High Street','Dollsville','CA','99999', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FRB01','Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FNG01','Fun and Games','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('JTS01','Jouets et ours','1 Rue Amusement','Paris', NULL,'45678', 'France');

-- -----------------------
-- Populate Products table
-- -----------------------
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR01', 'BRS01', '8 inch teddy bear', 5.99, '8 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR02', 'BRS01', '12 inch teddy bear', 8.99, '12 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR03', 'BRS01', '18 inch teddy bear', 11.99, '18 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG01', 'DLL01', 'Fish bean bag toy', 3.49, 'Fish bean bag toy, complete with bean bag worms with which to feed it');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG02', 'DLL01', 'Bird bean bag toy', 3.49, 'Bird bean bag toy, eggs are not included');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG03', 'DLL01', 'Rabbit bean bag toy', 3.49, 'Rabbit bean bag toy, comes with bean bag carrots');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RGAN01', 'DLL01', 'Raggedy Ann', 4.99, '18 inch Raggedy Ann doll');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL01', 'FNG01', 'King doll', 9.49, '12 inch king doll with royal garments and crown');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL02', 'FNG01', 'Queen doll', 9.49, '12 inch queen doll with royal garments and crown');

-- ---------------------
-- Populate Orders table
-- ---------------------
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20005, '2020-05-01', '1000000001');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20006, '2020-01-12', '1000000003');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20007, '2020-01-30', '1000000004');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20008, '2020-02-03', '1000000005');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20009, '2020-02-08', '1000000001');

-- -------------------------
-- Populate OrderItems table
-- -------------------------
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'BR01', 100, 5.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'BR03', 100, 10.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'BR01', 20, 5.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 2, 'BR02', 10, 8.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 3, 'BR03', 10, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'BR03', 50, 11.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 2, 'BNBG01', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 3, 'BNBG02', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 4, 'BNBG03', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 5, 'RGAN01', 50, 4.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'RGAN01', 5, 4.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 2, 'BR03', 5, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 3, 'BNBG01', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 4, 'BNBG02', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 5, 'BNBG03', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'BNBG01', 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'BNBG02', 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'BNBG03', 250, 2.49);

Entity Relationship Diagram

Open the sample ER Diagram using https://app.diagrams.net

Stored Procedures

Stored procedures are a way to add regular programming logic to your SQL database.
This lets you put in business rules and data safeguards that are not available from the standard integrity constraints.
This also allows you to encapsulate typical uses so that each developer will not have to recreate the logic each time it is needed.

Get All Products

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

CREATE PROCEDURE sp_get_all_products()
BEGIN
    SELECT prod_id, prod_name, prod_price, prod_desc FROM products;
END;

Dotenv

Add the Dotenv Java dependency to your pom.xml file.
Add .env to your .gitignore file
Create a .env file in your src/main/resources folder.
Include these variables, replacing your values with the placeholders

DB_DRIVER=com.mysql.cj.jdbc.Driver
DB_URL=jdbc:mysql://<DB-SERVER-NAME>.mysql.database.azure.com:3306/<DB-SCHEMA>?serverTimezone=UTC&useSSL=true&requireSSL=false
DB_USER=<DB-USER-NAME>
DB_PASSWORD=<DB-PASSWORD>

Add the MySQL Connector/J dependency to your pom.xml file.

MySQL

DB_Connect Class

Create a DB_Connect class in the shared package that includes a getConnection method.
Create a main method to test the connection.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DB_Connect {
    public static Connection getConnection() {
        Dotenv dotenv = Dotenv.load();
        String driver = dotenv.get("DB_DRIVER");
        String connectionString = dotenv.get("DB_URL");
        String user = dotenv.get("DB_USER");
        String password = dotenv.get("DB_PASSWORD");
        try {
            Class.forName(driver);
        } catch(ClassNotFoundException e) {
            throw new RuntimeException("Could not load JDBC driver");
        }
        try {
            Connection connection = DriverManager.getConnection(connectionString, user, password);
            return connection;
        } catch(SQLException e) {
            throw new RuntimeException("Could not connect to database - " + e.getMessage());
        }
    }

    public static void main(String[] args) {
        getConnection();
    }
}

Product Class

Create a new "ecommerce.model" package. This package should be a sibling to the "java2eedemo" package.
Create a new Product class in a with attributes, constructors, getters, setter, and toString methods.

public class Product {
    private String id;
    private String name;
    private double price;
    private String description;
    
    public Product() {}

    public Product(String id, String name, double price, String description) {
        this.id = id;
        this.name = name;
        this.price = price;
        this.description = description;
    }

    public String getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public String getDescription() {
        return description;
    }

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

    @Override
    public String toString() {
        return "Product{" +
                "id='" + id + '\'' +
                ", name='" + name + '\'' +
                ", price=" + price +
                ", description='" + description + '\'' +
                '}';
    }
}

ProductDAO Class

Create a ProductDAO class in the "ecommerce.model" package with a getAll() method.
Write a main method to test the results.

import edu.kirkwood.shared.DB_Connect;

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

public class ProductDAO extends DB_Connect {

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

    public static List<Product> getAll() {
        List<Product> products = new ArrayList<>();
        try(Connection connection = getConnection()) {
            CallableStatement statement = connection.prepareCall("{CALL sp_get_all_products()}");
            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");
                products.add(new Product(id, name, price, description));
            }
        } catch(SQLException e) {
            throw new RuntimeException("Database error - " + e.getMessage());
        }
        return products;
    }
}

main-nav.jsp

Create a main-nav.jsp file that contains HTML to create a top navbar that can be shared on all other JSPs.

<nav class="navbar navbar-expand-lg navbar-dark bg-dark">
    <div class="container-fluid">
        <a class="navbar-brand" href="index.jsp">Java 2 Web Apps</a>
        <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarNavDarkDropdown" aria-controls="navbarNavDarkDropdown" aria-expanded="false" aria-label="Toggle navigation">
            <span class="navbar-toggler-icon"></span>
        </button>
        <div class="collapse navbar-collapse" id="navbarNavDarkDropdown">
            <ul class="navbar-nav">
                <li class="nav-item dropdown">
                    <button class="btn btn-dark dropdown-toggle" data-bs-toggle="dropdown" aria-expanded="false">
                        Weeks 5-7
                    </button>
                    <ul class="dropdown-menu dropdown-menu-dark">
                        <li><a class="dropdown-item" href="hello-servlet">Hello Servlet</a></li>
                        <li><a class="dropdown-item" href="my-calculator">My Calculator</a></li>
                        <li><a class="dropdown-item" href="temp-converter">Temperature Converter</a></li>
                        <li><a class="dropdown-item" href="fraction">Fraction Calculator</a></li>
                        <li><a class="dropdown-item" href="volume">Volume Calculator</a></li>
                        <li><a class="dropdown-item" href="#">Midterm Converter</a></li>
                    </ul>
                </li>
                <li class="nav-item dropdown">
                    <button class="btn btn-dark dropdown-toggle" data-bs-toggle="dropdown" aria-expanded="false">
                        Weeks 8-9
                    </button>
                    <ul class="dropdown-menu dropdown-menu-dark">
                        <li><a class="dropdown-item" href="shop">Shop</a></li>
                        <li><a class="dropdown-item" href="products">Products - Admin</a></li>
                        <li><a class="dropdown-item" href="orders">Orders - Admin</a></li>
                        <li><a class="dropdown-item" href="vendors">Vendors - Admin</a></li>
                        <li><a class="dropdown-item" href="customers">Customers - Admin</a></li>
                    </ul>
                </li>
            </ul>
        </div>
    </div>
</nav>

index.jsp

Update the index.jsp table of contents to include links to the servlets that we will create this week.

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<!doctype html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Table of Contents</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"%>

<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>

ShopServlet

Create a new java package called "ecommerce/controller".
Create a new servlet called "Shop" with the URL value of "/shop".
Create a doGet method. Call the ProductDAO.getAll() method to get the List<Product>.
Set that object as a request attribute.
From now on, please organize JSPs in a "WEB-INF" project folder.

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="/shop")
public class ShopServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        List<Product> products = ProductDAO.getAll();
        req.setAttribute("products", products);
        req.getRequestDispatcher("WEB-INF/ecommerce/shop.jsp").forward(req, resp);
    }
}

JSTL

Add the Jakarta Standard Tag Library API dependency to your pom.xml file.
Add the Jakarta Standard Tag Library Implementation dependency to your pom.xml file.

Core Tag Library

Add jakarta.tags.core taglib directive to the top of your JSP.
<%@ taglib prefix="c" uri="jakarta.tags.core" %>
This will give you access to JSTL core tags, like <c:forEach>.
- There are two required attributes, items and var.
  - items uses a value of ${attribute} to get the request attribute. The attribute must be an Iterable object, like List.
  - var will be a string representing an individual item inside the List.

Format Tag Library

Add jakarta.tags.core taglib directive to the top of your JSP.
<%@ taglib prefix="fmt" uri="jakarta.tags.fmt" %>
This will give you access to JSTL formatting tags, like <fmt:setLocale> and <fmt:formatNumber>
- We will use setLocale later when translating our applications to different languages.
- formatNumber has one required attribute, value. The other attributes are optional.
  - The value attributes uses ${attribute} to get the object's attribute value.

shop.jsp

This uses Bootstrap containers, spacing, grid system, gutters, cards, text alignment, flex, and buttons to for the HTML and CSS.
The object attributes are obtained using ${var.attribute}. The attribute must have a getter method in the POJO.

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="jakarta.tags.core" %>
<%@ taglib prefix="fmt" uri="jakarta.tags.fmt" %>
<%-- en-US, fr-FR, ja-JP --%>
<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>Shop</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 my-4">
    <div class="row g-4">
        <c:forEach items="${products}" var="product">
            <div class="col-sm-12 col-md-6 col-lg-4 col-xl-3">
                <div class="card">
                    <div class="card-header py-3">
                        <h4 class="my-0 text-center">${product.prod_name}</h4>
                    </div>
                    <div class="card-body">
                        <p class="card-text">${product.prod_desc}</p>
                        <div class="d-flex justify-content-between align-items-center">
                            <small class="text-body-secondary"><fmt:formatNumber value="${product.prod_price}" type="currency" /></small>
                            <a href="add-to-cart?id=${product.prod_id}" class="btn btn-sm btn-outline-primary">Add to cart</a>
                        </div>
                    </div>
                </div>
            </div>
        </c:forEach>

    </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>

Add User

Run the following SQL statements to create a stored procedure to add user records. In IntelliJ, you may have to remove DELIMITER $$ and $$ DELIMITER.

DELIMITER $$
CREATE PROCEDURE sp_add_user(
    IN p_email VARCHAR(255),
    IN p_password VARCHAR(255)
)
BEGIN
    -- Create new User record
    INSERT INTO user (email, password)
    VALUES (p_email,p_password);
    -- Get the user's id
    SET @last_user_id = LAST_INSERT_ID();
    -- Generate a random 6 digit code
    SET @code=LPAD(FLOOR(RAND() * 999999.99), 6, '0');
    -- Create new 2 Factor Authentication Code
    INSERT INTO 2fa_code (user_id, code, method) VALUES (@last_user_id, @code, 'email');
END$$
DELIMITER ;

Get All Users

Add a stored procedure to get all of the users.

DELIMITER $$
CREATE PROCEDURE sp_get_all_users()
BEGIN
    SELECT id, first_name, last_name, email, phone, password, language, status, privileges, created_at, last_logged_in, updated_at
    FROM user;
END$$
DELIMITER ;

Get User By Email

Run the following SQL statement to create a stored procedure to view an individual user.

DELIMITER $$
CREATE PROCEDURE sp_get_user(IN p_email VARCHAR(255))
BEGIN
    SELECT id, first_name, last_name, email, phone, password, language, status, privileges, created_at, last_logged_in, updated_at
    FROM user
    WHERE email = p_email;
END$$
DELIMITER ;

Get User By ID

Run the following SQL statement to create a stored procedure to view an individual user.

DELIMITER $$
CREATE PROCEDURE sp_get_user_by_id(IN p_id int)
BEGIN
    SELECT id, first_name, last_name, email, phone, password, language, status, privileges, created_at, last_logged_in, updated_at
    FROM user
    WHERE id = p_id;
END$$
DELIMITER ;

Update User

Run the following SQL statement to create a stored procedure to update an individual user.

DELIMITER $$
CREATE PROCEDURE sp_update_user(
    IN p_id int,
    IN p_first_name VARCHAR(255),
    IN p_last_name VARCHAR(255),
    IN p_email VARCHAR(255),
    IN p_phone VARCHAR(255),
    IN p_language VARCHAR(255),
    IN p_status VARCHAR(255),
    IN p_privileges VARCHAR(255),
    IN p_last_logged_in DATETIME
)
BEGIN
    UPDATE user
    SET first_name =  p_first_name,
        last_name =  p_last_name,
        email =  p_email,
        phone =  p_phone,
        language =  p_language,
        status = p_status,
        privileges = p_privileges,
        last_logged_in = p_last_logged_in
    WHERE id = p_id;
END$$
DELIMITER ;

Delete User

DELIMITER $$
CREATE PROCEDURE sp_delete_user(
    IN p_id int
)
BEGIN
    DELETE FROM user WHERE id = p_id;
END$$
DELIMITER ;

Java 2 Week 8

By Marc Hauschildt

Java 2 Week 8

Marc Hauschildt

Web Technologies and Computer Software Development Instructor at Kirkwood Community College in Cedar Rapids, IA.

kirkwood.edu/businessit

Java 2

Topics Covered this week

Database Setup

Database Scripts

Entity Relationship Diagram

Stored Procedures

Get All Products

Dotenv

MySQL

DB_Connect Class

Product Class

ProductDAO Class

main-nav.jsp

index.jsp

ShopServlet

JSTL

Core Tag Library

Format Tag Library

shop.jsp

Add User

Get All Users

Get User By Email

Get User By ID

Update User

Delete User

Java 2 Week 8

More from Marc Hauschildt