Restaurant Manager Java Project

Overview

This Java console application manages a restaurant's customers, employees, menu items, and orders. It connects to a MySQL database and demonstrates CRUD operations, transactions, views, and stored procedures.

Features include:

• Customer, Employee, and MenuItem CRUD operations (Create, Read, Update, Delete).
• Transaction demo: place orders with multiple items, create bills, and demonstrate COMMIT/ROLLBACK.
• Database view: CustomerOrderSummary to aggregate customer orders.
• Stored procedure: GetBillTotal to calculate order totals.
• Input validation and centralized error handling for MySQL exceptions.

---

Setup

MySQL Version / Connector Version

• MySQL Server: 8.0.34
• MySQL Connector/J: 8.1.0

1. Database: Create a MySQL database restaurant_manager.

2. Tables: Ensure the following tables exist: Customer, Employee, MenuItem, CustomerOrder, OrderItem, Bill, BillLineItem.

3. JDBC Driver: Include MySQL Connector/J in your project classpath.

4. Properties: Configure src/main/resources/app.properties with your database URL, username, and password:

   db.url=jdbc:mysql://localhost:3306/restaurant_manager?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
   db.user=root
   db.password=yourpassword
   

---

Running the Application

1. Compile and run Main.java.
2. The menu displays the following options:

Option	Action
1	View Customers
2	Insert Customer
3	Update Customer
4	Delete Customer
5	View Employees
6	Insert Employee
7	View Menu Items
8	Update Menu Item Price
9	Run Transaction Demo (Place Order with COMMIT/ROLLBACK)
10	Create Database View (CustomerOrderSummary)
11	Create Stored Procedure (GetBillTotal)
0	Exit

• Enter the option number to execute an action.
• All inputs are validated; SQL exceptions are handled with clear messages.

---

Screenshots / Example Output

console menu image

View Customer Image

Insert CustomerImage

Delete Customer Image

Update Customer

View Employees

Update Menu Items

Run Transaction Demo (Place Order with COMMIT/ROLLBACK)

Create View

Create Stored Procedure

Exiting

---

Package Contents

Files included:

• Main.java
• create_and_populate.sql
• app.properties
• README.md
• ai_log.md
• Team-roles.txt
• video_demo.mp4

---

Database View

• CustomerOrderSummary: Combines Customer and CustomerOrder tables for quick reporting. Shows orderID, customerName, totalAmount, and status.
• Idempotent: can run multiple times without errors.

---

Stored Procedure

• GetBillTotal:

  • Input: orderID
  • Output: totalAmount
  • Calculates the sum of quantity * unitPrice from OrderItem for the given order.
  • Idempotent: can run multiple times without error.

Example:

CALL GetBillTotal(2, @total);
SELECT @total;

---

Transaction Demo

Demonstrates atomicity: all operations succeed together or fail together.

1. Create a CustomerOrder.
2. Add multiple OrderItems.
3. Generate a Bill.
4. Add BillLineItems.
5. Choose COMMIT or ROLLBACK.

---

Schema Reference

Customer

Field	Type	Notes
customerID	BIGINT	Primary Key
name	VARCHAR(150)	Customer name
contact	VARCHAR(150)	Email/phone

Employee

Field	Type	Notes
employeeID	BIGINT	Primary Key
restaurantID	BIGINT	FK to restaurant
name	VARCHAR(150)	Employee name
role	VARCHAR(80)	Job role

MenuItem

Field	Type	Notes
menuItemID	BIGINT	Primary Key
restaurantID	BIGINT	FK to restaurant
name	VARCHAR(150)	Item name
description	VARCHAR(255)	Item description
price	DECIMAL(10,2)	Item price

CustomerOrder

Field	Type	Notes
orderID	BIGINT	Primary Key
restaurantID	BIGINT	FK
customerID	BIGINT	FK
employeeID	BIGINT	FK
orderDateTime	DATETIME	Timestamp
status	VARCHAR(20)	OPEN/IN_PROGRESS/COMPLETED
totalAmount	DECIMAL(10,2)	Order total

OrderItem

Field	Type	Notes
orderID	BIGINT	FK to CustomerOrder
menuItemID	BIGINT	FK to MenuItem
quantity	INT	Quantity ordered
unitPrice	DECIMAL(10,2)	Price per unit

Bill

Field	Type	Notes
billID	BIGINT	Primary Key (matches orderID)
orderID	BIGINT	FK
issuedAt	DATETIME	Timestamp
status	VARCHAR(20)	ISSUED/PAID
totalAmount	DECIMAL(10,2)	Total

BillLineItem

Field	Type	Notes
orderID	BIGINT	FK
billID	BIGINT	FK
lineNum	INT	Line number
menuItemID	BIGINT	FK
quantity	INT	Quantity
unitPrice	DECIMAL(10,2)	Price per unit

---

Notes

• All operations are validated to prevent SQL errors and invalid input.
• Transaction demo ensures atomic operations.
• Views and stored procedures are idempotent.
• Suitable for demonstration, learning, and extending with more restaurant features.

---