POINT OF SALE SYSTEM IN JAVA AND MYSQL

/* POS_and_Inventory.java
 * Written By Dr. Jake Rodriguez Pomperada, MAED-IT, MT, PhD.-TM
 * Tools : Java SE, MySQL and MySQL Connector for Java, Eclipse IDE
 * Date  : February 4, 2026 Wednesday
 * Location : Bacolod City, Negros Occidental Philippines
 */

package demo;

import java.sql.*;
import java.util.Scanner;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

public class POS_and_Inventory {

    static final String URL = "jdbc:mysql://localhost:3306/pos_db";
    static final String USER = "root";
    static final String PASS = "";
    static final double VAT = 0.12;

    static Scanner sc = new Scanner(System.in);

    public static void main(String[] args) {

        try (Connection con = DriverManager.getConnection(URL, USER, PASS)) {

            while (true) {
                showTitle();
                showMenu();

                System.out.print("Select Option: ");
                int choice = sc.nextInt();

                switch (choice) {
                    case 1 -> addProduct(con);
                    case 2 -> viewProducts(con);
                    case 3 -> updateStock(con);
                    case 4 -> sellProduct(con);
                    case 5 -> {
                        System.out.println("Thank you for using POS System!");
                        System.exit(0);
                    }
                    default -> System.out.println("Invalid choice!");
                }

                System.out.println("\nPress Enter to continue...");
                sc.nextLine();
                sc.nextLine();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // ================= TITLE =================
    static void showTitle() {
        System.out.println("\n===========================================");
        System.out.println("  POINT OF SALE SYSTEM IN JAVA AND MYSQL");
        System.out.println("===========================================");
    }

    // ================= MENU =================
    static void showMenu() {
        System.out.println("1. Add Product");
        System.out.println("2. View Inventory");
        System.out.println("3. Update Stock");
        System.out.println("4. Sell Product (POS)");
        System.out.println("5. Exit");
        System.out.println("-------------------------------------------");
    }

    // ================= ADD PRODUCT =================
    static void addProduct(Connection con) throws Exception {

        sc.nextLine();
        System.out.print("Product Name : ");
        String name = sc.nextLine();

        System.out.print("Price        : ");
        double price = sc.nextDouble();

        System.out.print("Quantity     : ");
        int qty = sc.nextInt();

        PreparedStatement ps = con.prepareStatement(
                "INSERT INTO products(name,price,qty) VALUES(?,?,?)");
        ps.setString(1, name);
        ps.setDouble(2, price);
        ps.setInt(3, qty);
        ps.executeUpdate();

        System.out.println("Product successfully added!");
    }

    // ================= VIEW PRODUCTS =================
    static void viewProducts(Connection con) throws Exception {

        ResultSet rs = con.createStatement()
                .executeQuery("SELECT id,name,price,qty FROM products");

        System.out.println("\nID  NAME        PRICE     QTY");
        System.out.println("----------------------------------");
        while (rs.next()) {
            System.out.printf("%-3d %-10s %-9.2f %-5d%n",
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getDouble("price"),
                    rs.getInt("qty"));
        }
    }

    // ================= SHOW PRODUCTS FOR SELLING =================
    static void showProductsForSelling(Connection con) throws Exception {

        ResultSet rs = con.createStatement()
                .executeQuery("SELECT id,name,price,qty FROM products");

        System.out.println("\nAVAILABLE PRODUCTS");
        System.out.println("ID   NAME       PRICE     STOCK");
        System.out.println("--------------------------------");
        while (rs.next()) {
            System.out.printf("%-4d %-10s %-9.2f %-5d%n",
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getDouble("price"),
                    rs.getInt("qty"));
        }
    }

    // ================= UPDATE STOCK =================
    static void updateStock(Connection con) throws Exception {

        showProductsForSelling(con);

        System.out.print("Product ID : ");
        int id = sc.nextInt();

        PreparedStatement psSelect = con.prepareStatement(
                "SELECT name, qty FROM products WHERE id=?");
        psSelect.setInt(1, id);
        ResultSet rs = psSelect.executeQuery();

        if (!rs.next()) {
            System.out.println("Product not found!");
            return;
        }

        System.out.println("\nSelected Product : " + rs.getString("name"));
        System.out.println("Current Stock    : " + rs.getInt("qty"));

        System.out.print("New Stock : ");
        int newQty = sc.nextInt();

        System.out.print("Confirm update? (Y/N): ");
        char confirm = sc.next().toUpperCase().charAt(0);

        if (confirm != 'Y') {
            System.out.println("Update cancelled.");
            return;
        }

        PreparedStatement psUpdate = con.prepareStatement(
                "UPDATE products SET qty=? WHERE id=?");
        psUpdate.setInt(1, newQty);
        psUpdate.setInt(2, id);
        psUpdate.executeUpdate();

        System.out.println("Stock updated successfully!");
    }

 // ================= POS / SELL PRODUCT =================
    static void sellProduct(Connection con) throws Exception {

        double grandSubtotal = 0;
        int totalItemsPurchased = 0;
        boolean moreItems = true;

        String[] items = new String[50];
        int[] qtys = new int[50];
        double[] totals = new double[50];
        int count = 0;

        LocalDateTime now = LocalDateTime.now();

        DateTimeFormatter dtf =
                DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

        DateTimeFormatter receiptFmt =
                DateTimeFormatter.ofPattern("yyyyMMdd-HHmmss");
        String receiptNo = "OR-" + now.format(receiptFmt);

        while (moreItems) {

            showProductsForSelling(con);

            System.out.print("Product ID : ");
            int id = sc.nextInt();

            PreparedStatement ps = con.prepareStatement(
                    "SELECT * FROM products WHERE id=?");
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();

            if (!rs.next()) {
                System.out.println("Invalid product!");
                continue;
            }

            String productName = rs.getString("name");
            double price = rs.getDouble("price");
            int stock = rs.getInt("qty");

            System.out.println("Selected Product : " + productName);
            System.out.print("Quantity : ");
            int qty = sc.nextInt();

            if (qty <= 0 || qty > stock) {
                System.out.println("Invalid quantity!");
                continue;
            }

            double lineTotal = price * qty;
            grandSubtotal += lineTotal;
            totalItemsPurchased += qty;

            // store item
            items[count] = productName;
            qtys[count] = qty;
            totals[count] = lineTotal;
            count++;

            PreparedStatement upd = con.prepareStatement(
                    "UPDATE products SET qty = qty - ? WHERE id=?");
            upd.setInt(1, qty);
            upd.setInt(2, id);
            upd.executeUpdate();

            double runningVat = grandSubtotal * VAT;
            double runningTotalToPay = grandSubtotal + runningVat;

            // ===== DISPLAY PREVIOUS + NEW PURCHASES =====
            System.out.println("\nCURRENT TRANSACTION ITEMS");
            for (int i = 0; i < count; i++) {
                System.out.printf("%d. %s x%d = Php %.2f%n",
                        (i + 1), items[i], qtys[i], totals[i]);
            }
            System.out.println("--------------------------------");
            System.out.printf(
                    "Running Amount to Pay (with VAT): Php %.2f%n",
                    runningTotalToPay);

            System.out.print("\nAdd another item? (Y/N): ");
            moreItems = sc.next().equalsIgnoreCase("Y");
        }

        double vatAmount = grandSubtotal * VAT;
        double total = grandSubtotal + vatAmount;

        double cash;
        do {
            System.out.printf("\nTOTAL AMOUNT TO PAY : Php %.2f%n", total);
            System.out.print("Cash Tendered : ");
            cash = sc.nextDouble();
        } while (cash < total);

        double change = cash - total;

        // ================= OFFICIAL RECEIPT =================
        System.out.println("\n==========================================================");
        System.out.println("        POINT OF SALE SYSTEM IN JAVA AND MYSQL");
        System.out.println("    Created By Dr. Jake Rodriguez Pomperada, PhD.");
        System.out.println("         THIS SERVES AS THE OFFICIAL RECEIPT");
        System.out.println("==========================================================");
        System.out.println("Receipt No : " + receiptNo);
        System.out.println("Date/Time  : " + now.format(dtf));
        System.out.println("Items Sold : " + totalItemsPurchased);
        System.out.println("------------------------------------");

        for (int i = 0; i < count; i++) {
            System.out.printf("%d. %s x%d = Php %.2f%n",
                    (i + 1), items[i], qtys[i], totals[i]);
        }

        System.out.println("------------------------------------");
        System.out.printf("SUBTOTAL : Php %.2f%n", grandSubtotal);
        System.out.printf("VAT (12%%): Php %.2f%n", vatAmount);
        System.out.printf("TOTAL    : Php %.2f%n", total);
        System.out.printf("CASH     : Php %.2f%n", cash);
        System.out.printf("CHANGE   : Php %.2f%n", change);
        System.out.println("====================================");
    }
}

/*
SQLyog Ultimate v13.1.1 (64 bit)
MySQL - 10.4.32-MariaDB : Database - pos_db
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`pos_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;

USE `pos_db`;

/*Table structure for table `products` */

DROP TABLE IF EXISTS `products`;

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `price` double DEFAULT NULL,
  `qty` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Data for the table `products` */

insert  into `products`(`id`,`name`,`price`,`qty`) values 
(1,'Ballpen',10,57),
(2,'Notebook',35,50),
(3,'Pencil',5,70),
(4,'Eraser',8,50),
(6,'Ink',25.23,30),
(7,'Diskette',5.6,70),
(8,'Sticker Paper',100,38);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;