Basic Sales and Inventory System

A console-based canteen sales and inventory system written in plain Java with JDBC. Built as a study project to practice layered separation (DAO / DTO / config), interface-driven design, and direct JDBC work without a framework.

No Spring, no ORM, no build tool — deliberately. Everything is hand-wired so the data access flow stays visible end to end.

██╗  ██╗ █████╗ ██╗███╗   ██╗ █████╗ ███╗   ██╗
██║ ██╔╝██╔══██╗██║████╗  ██║██╔══██╗████╗  ██║
█████╔╝ ███████║██║██╔██╗ ██║███████║██╔██╗ ██║
██╔══██╗██╔══██║██║██║╚██╗██║██╔══██║██║╚██╗██║
██║  ██║██║  ██║██║██║ ╚████║██║  ██║██║ ╚████║
╚═╝  ╚═╝╚═╝  ╚═╝╚═╝╚═╝  ╚═══╝╚═╝  ╚═╝╚═╝  ╚═══╝

========= Sales and Inventory Management System =========

---

Features

Authentication

• Username/password login against the users table
• 3 login attempts before the system exits
• Inactive accounts are blocked at login
• Self-service password change

Inventory

• Add new food items (name, price, quantity)
• Adding an existing item name tops up its stock instead of creating a duplicate
• Sell items with a stock-guarded update — the sale fails cleanly if stock is insufficient
• Live inventory table printed above every menu, with column totals

Reporting

• Sales report — items with sold > 0, including revenue per item and grand total
• Unsold report — items still holding stock (quantity > 0)
• Combined report view

User management (ADMIN role only)

• Add users with a role
• Deactivate users
• Change user roles
• Admins cannot deactivate or demote their own account

---

Main menu

  ┌───────────────────────────┐
  │         MAIN MENU         │
  ├───────────────────────────┤
  │ 1. Add Item               │
  │ 2. Sell Item              │
  │ 3. View Inventory         │
  │ 4. Sales Inventory        │
  │ 5. View Unsold            │
  │ 6. Show All Reports       │
  │ 7. Change Password        │
  │ 8. User Management        │
  │ 0. Exit                   │
  └───────────────────────────┘

---

Tech stack

Component	Choice
Language	Java 21 (source/target 21, see .settings/org.eclipse.jdt.core.prefs)
Data access	JDBC with PreparedStatement
Driver	MySQL Connector/J 8.0.33 (bundled in lib/)
Database	MySQL 8 (utf8mb4, InnoDB)
IDE	Eclipse (.project / .classpath committed)
Build	Plain javac — no Maven or Gradle
UI	Terminal, Scanner input, printf table rendering

---

Project structure

src/app/
├── Main.java                            Entry point, login loop, menus, report rendering
├── config/
│   └── DatabaseConnection.java          Static JDBC connection holder
├── data/
│   ├── FoodDTO.java                     Food item + derived totals
│   └── UserDTO.java                     User account
└── dao/
    ├── FoodDAO.java                     Inventory contract
    ├── UserDAO.java                     User contract
    └── implementation/
        ├── FoodDAOImpl.java             JDBC implementation
        └── UserDAOImpl.java             JDBC implementation

database/
├── cateen database SQL script.txt       Schema + seed users
└── How to run.txt                       Original Windows build/run commands

lib/
└── mysql-connector-j-8.0.33.jar         JDBC driver

The DAO interfaces are the seam. Main depends only on FoodDAO and UserDAO, so the JDBC implementations can be swapped without touching the UI layer.

---

Setup

1. Database

The app expects MySQL on port 3307 (not the default 3306). Run the script in database/:

CREATE DATABASE canteen_db;
USE canteen_db;
-- then the rest of database/cateen database SQL script.txt

Two tables are created:

foods — id, name (unique), price DECIMAL(10,2), quantity, sold, timestamps users — id, username (unique), password, role, active, timestamps

2. Connection settings

Edit src/app/config/DatabaseConnection.java:

private static final String url = "jdbc:mysql://localhost:3307/canteen_db";
private static final String user = "root";
private static final String pw   = "root";

Change the port to 3306 if that's what your MySQL is on. If you hit an authentication or timezone error, use the commented alternate URL in the same file, which adds allowPublicKeyRetrieval=true&serverTimezone=UTC&useSSL=false.

3. Build and run

Windows

cd BasicSalesAndInventorySystem
javac -cp "bin;lib\mysql-connector-j-8.0.33.jar" -d bin src\app\config\*.java src\app\dao\*.java src\app\dao\implementation\*.java src\app\data\*.java src\app\Main.java
java -cp "bin;lib\mysql-connector-j-8.0.33.jar" app.Main

macOS / Linux — same thing, but the classpath separator is : instead of ;:

cd BasicSalesAndInventorySystem
javac -cp "bin:lib/mysql-connector-j-8.0.33.jar" -d bin src/app/config/*.java src/app/dao/*.java src/app/dao/implementation/*.java src/app/data/*.java src/app/Main.java
java -cp "bin:lib/mysql-connector-j-8.0.33.jar" app.Main

Eclipse — import as an existing project, then fix the classpath. The committed .classpath points at an absolute path from the original machine:

C:/Users/ADMIN/eclipse-workspace-2024/CRUDAttendanceMonitoringSystem/lib/mysql-connector-j-8.0.33.jar

That path won't exist on your machine, and it refers to a different project entirely. Remove that entry and add lib/mysql-connector-j-8.0.33.jar from this repo instead (Build Path → Add JARs).

---

Seeded accounts

Username	Password	Role
admin	admin123	ADMIN
supervisor	super123	ADMIN
mark	mark123	USER
mary	mary123	USER
cashier1	cash123	USER

Demo credentials for local use only.

---

Design notes

Stock-guarded sale. The sell operation is a single conditional UPDATE rather than a read-then-write:

UPDATE foods SET quantity = quantity - ?, sold = sold + ?
WHERE name = ? AND quantity >= ?

The quantity >= ? predicate lives in the WHERE clause, so the database enforces the stock check atomically. A zero-row result means insufficient stock, and the code treats that as a failed sale. This avoids the classic read-check-write race where two concurrent sales both read the same stock level and both pass the check.

Derived values stay out of the database. FoodDTO.getTotalPrice() and getTotalSoldPrice() are computed on read rather than stored, so there is no stale total to keep in sync.

Role check on every privileged action. manageUsers() re-reads the current user's role from the database before showing the admin menu, rather than trusting a role cached at login.

---

Known limitations

This is study code, and it has real gaps. Documenting them here rather than pretending they aren't there:

Security

• Passwords stored and compared in plaintext — UserDAOImpl.java:27 inserts the raw password, and UserDAOImpl.java:93 compares with String.equals. Production code needs BCrypt/Argon2 hashing with a per-user salt, and a constant-time comparison.
• Database credentials hardcoded in source — DatabaseConnection.java:11-15. Should come from environment variables or an external properties file that is gitignored.
• No account lockout — the 3-attempt limit (Main.java:19) only ends the current process. Restarting the app resets it. Real lockout needs a persisted failed-attempt counter.

Correctness

• NullPointerException on unknown username — Main.java:48 calls user.isActive() without checking whether findByUsername returned null. Typing a username that doesn't exist crashes the app before the password prompt.
• Shared static connection is not thread-safe — DatabaseConnection.java:18 caches one Connection in a static field, but every DAO method wraps it in try-with-resources and closes it. The pattern works single-threaded because getConnection() reopens on a closed connection, but it defeats the caching it was written for and would break under concurrency. A connection pool (HikariCP) is the right answer.
• Transaction handling is fragile — FoodDAOImpl.java:137 sets autoCommit(false) on the shared connection and restores it in finally, on a connection that is then closed by try-with-resources. It works, but transaction scope should live in a service layer, not inside a DAO method.
• Silent input truncation — Main.java:276 cuts food names to 12 characters to protect the table layout, without telling the user. Presentation concerns leaking into data.
• Unhandled InputMismatchException — addItem() and sellFood() use scanner.nextInt() / nextDouble() directly. Non-numeric input throws instead of reprompting, unlike the menu loop which handles NumberFormatException properly.
• FoodDAO.delete(int) is implemented but unreachable — no menu option calls it.

Repository hygiene

• Compiled .class files under bin/ are committed and will drift from src/.
• The JDBC jar is committed rather than resolved by a build tool.
• clearScreen() (Main.java:208) only works on Windows — on Linux and macOS the screen never clears and output stacks up.

If this were rebuilt for real use, the shape would be: Maven or Gradle for dependency management, HikariCP for pooling, a service layer owning transaction boundaries, hashed passwords, externalized config, and JUnit coverage on the DAO layer against Testcontainers.

---

License

No license specified. Study/portfolio project.