Showing posts with label inventory system in java. Show all posts
Showing posts with label inventory system in java. Show all posts

Sunday, April 1, 2018

Jana Happy Belly Inventory System in Java JDBC and MySQL

A very simple program that I wrote that will help small and big companies to manage their inventory system I wrote this code using Java JDBC and MySQL. I hope you will find my work useful.

I am currently accepting programming and web development work kindly contact me in the following email address for further details. Thank you.

My email address are the following jakerpomperada@gmail.com and jakerpomperada@yahoo.com.

My mobile number here in the Philippines is 09173084360.

My telephone number at home here in Bacolod City, Negros Occidental Philippines is  +63 (034) 4335675.











Directory Structure in NetBeans IDE 8.2

Sample Program Output


Program Listing

InventoryMenu.java


package InventoryJDBC;


/**
 * NetBeans IDE 8.2
 * @author Mr. Jake R. Pomperada
 * April 1, 2018  Sunday
 * Bacolod City, Negros Occidental
 */

import java.sql.SQLException;
import java.util.Scanner;

public class InventoryMenu {
    public static void main(String[] args) throws SQLException {
        DisplayMenu();
    }

    public static void DisplayMenu() throws SQLException {
        Scanner userInput = new Scanner(System.in);
        String READ_MENU;

        System.out.println("*************************************************************");
        System.out.println("| Jana Happy Belly Inventory System in Java JDBC and MySQL  |");
System.out.println("|       Written By: Mr. Jake R.Pomperada, MAED-IT           |");
        System.out.println("*************************************************************");
        System.out.println("|                                                           |");
        System.out.println("|        1. ADD ITEMS IN THE DATABASE                       |");
        System.out.println("|        2. UPDATE ITEMS IN THE DATABASE                    |");
        System.out.println("|        3. VIEW ITEMS IN THE DATABASE                      |");
        System.out.println("|        4. DELETE ITEMS IN THE DATABASE                    |");
        System.out.println("|        5. QUIT DATABASE APPLICATION                       |");
        System.out.println("*************************************************************");

        System.out.print("Select Your Choice :=> ");

        READ_MENU = userInput.next();

        switch (READ_MENU) {
            case "1":
               Add add = new Add();
              
                break;
            case "2":
                Edit edit = new Edit();
                break;
            case "3":
                View view = new View();
             
                break;
            case "4":
                Delete delete = new Delete();
                break;
            case "5":
                System.exit(0);
                break;
            default:
                System.out.println("Invalid selection");
                break; 
        }
    }
}


Add.java



package InventoryJDBC;


/**
 * NetBeans IDE 8.2
 * @author Mr. Jake R. Pomperada
 * April 1, 2018  Sunday
 * Bacolod City, Negros Occidental
 */

import java.sql.PreparedStatement;
import java.sql.*;
import java.util.Scanner;


public class Add {
     
     static Connection conn;

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String url = "jdbc:mysql://localhost:3306/inventory";
            conn = DriverManager.getConnection(url, "root", "");
        } catch (Exception e) {
            e.printStackTrace();;
        }
    }
    
    static void closeConnection() {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    
     Add() throws SQLException { {
        Scanner userInput = new Scanner(System.in);
       try {
        System.out.println("You selected option 1: Add Item in the Database : ");
        String itemName;
        System.out.print("Enter Item Name : ");
        itemName = userInput.nextLine();

        int startQuantity;
        System.out.print("Enter Start Quantity : ");
        startQuantity = userInput.nextInt();

        int endQuantity;
        System.out.print("Enter Start Quantity : ");
        endQuantity = userInput.nextInt();

        int solveBalance = (startQuantity - endQuantity);
        
        System.out.print("Balance Quantity : " + solveBalance);
        
        String insertrecordSQL = "INSERT INTO items (item,start,end,balance)" +
                              "VALUES (?, ?,?,?)"; 

        PreparedStatement statement = conn.prepareStatement(insertrecordSQL);
        statement.setString(1, itemName);
        statement.setInt(2, startQuantity);
        statement.setInt(3,endQuantity);
        statement.setInt(4,solveBalance);

        int rowsInserted = statement.executeUpdate();
        if (rowsInserted > 0) {
            System.out.println();
            System.out.println("New Record Inserted Successfully!!!");
        }
        
        closeConnection();
        InventoryMenu.DisplayMenu();
      
        }  catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

     
       
      
}
}


Edit.java


package InventoryJDBC;


/**
 * NetBeans IDE 8.2
 * @author Mr. Jake R. Pomperada
 * April 1, 2018  Sunday
 * Bacolod City, Negros Occidental
 */

import java.sql.PreparedStatement;
import java.sql.*;
import java.util.Scanner;
import java.sql.SQLException;


public class Edit {
     
     static Connection conn;

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String url = "jdbc:mysql://localhost:3306/inventory";
            conn = DriverManager.getConnection(url, "root", "");
        } catch (Exception e) {
            e.printStackTrace();;
        }
    }
    
    static void closeConnection() {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    
     Edit() throws SQLException { 
        Scanner userInput = new Scanner(System.in);
       try {
        System.out.println("You selected option 2: Update Item in the Database : ");
        
        String itemId;
        
          
        System.out.print("Enter Item ID to Update : ");
        itemId= userInput.nextLine();
          
        
         DisplayRecord(itemId);
        
         
       
        String itemName;
        System.out.print("Enter Item Name : ");
        itemName = userInput.nextLine();
        userInput.nextLine();

        int startQuantity;
        System.out.print("Enter Start Quantity : ");
        startQuantity = userInput.nextInt();

        int endQuantity;
        System.out.print("Enter Start Quantity : ");
        endQuantity = userInput.nextInt();

        int solveBalance = (startQuantity - endQuantity);
        
        System.out.print("Balance Quantity : " + solveBalance);  
        
       
        String updateRecordSQL = "UPDATE items SET item = ?, start=?, end=?, balance=?  WHERE id = ?"; 

        PreparedStatement statement = conn.prepareStatement(updateRecordSQL);
    
        statement.setString(1,itemName);    
        statement.setInt(2, startQuantity);
        statement.setInt(3,endQuantity);
        statement.setInt(4,solveBalance);
        statement.setString(5,itemId);

      
        int rowsUpdated = statement.executeUpdate();
        if (rowsUpdated > 0) {
            System.out.println();
            System.out.println("Record Updated Successfully!!!"); 
            System.out.println("\n");
            
        }
        
         
         System.out.println("\n");
         InventoryMenu.DisplayMenu();
          closeConnection();
        }  catch (SQLException ex) {
            ex.printStackTrace();
        }
    
     }
  
public void DisplayRecord(String itemID) throws SQLException {
        try {
          
            String sql_stmt = "SELECT id,item,start,end,balance FROM items WHERE id = " + itemID;
             String selectrecordSQL = "SELECT * FROM items"; 
         
     PreparedStatement statement = conn.prepareStatement(selectrecordSQL);
     
     ResultSet resultSet = statement.executeQuery(selectrecordSQL);
        

            if (resultSet.next()) {

                ResultSetMetaData metaData = resultSet.getMetaData();
                int numberOfColumns = metaData.getColumnCount();
                System.out.print("\n\n");
                System.out.print("DATABASE RECORD LISTING");
                System.out.print("\n\n");

                for (int i = 1; i <= numberOfColumns; i++) {
                    System.out.printf("%-21s", metaData.getColumnName(i));
                }
                System.out.println();

                do {
                    for (int i = 1; i <= numberOfColumns; i++) {
                        System.out.printf("%-21s", resultSet.getObject(i));
                    }
                    System.out.println();
                } while (resultSet.next());

                System.out.println();

            } else {
                System.out.println("No database records found.");
            }

            
        } catch (SQLException ex) {
            System.out.println("The following error has occurred: " + ex.getMessage());
        }     
       
 } 

}


Delete.java


package InventoryJDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Scanner;

/**
 * NetBeans IDE 8.2
 * @author Mr. Jake R. Pomperada
 * April 1, 2018  Sunday
 * Bacolod City, Negros Occidental
 */

public class Delete {
     static Connection conn;

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String url = "jdbc:mysql://localhost:3306/inventory";
            conn = DriverManager.getConnection(url, "root", "");
        } catch (Exception e) {
            e.printStackTrace();;
        }
    }
    
    static void closeConnection() {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    
     Delete () throws SQLException { 
        Scanner userInput = new Scanner(System.in);
       try {
        System.out.println("You selected option 2: Update Item in the Database : ");
        
        String itemId;
              
       
        System.out.print("Enter Item ID to be deleted : ");
        itemId = userInput.nextLine();

        
         DisplayRecord(itemId);
        
         
       
       String confirm_delete;
        System.out.print("Enter Y to confirm deletion : ");
        confirm_delete = userInput.next();

        if ("Y".equals(confirm_delete) || "y".equals(confirm_delete)) { 
        
       
        String deleterecordSQL = "DELETE FROM items WHERE id=?"; 

     PreparedStatement statement = conn.prepareStatement(deleterecordSQL);
        statement.setString(1,itemId);
         
         int rowsDeleted = statement.executeUpdate();
        if (rowsDeleted > 0) {
            System.out.println();
            System.out.println("Record Deleted Successfully in the Database!!!");
            System.out.println();
        }
      
        }
        
         
         System.out.println("\n");
         InventoryMenu.DisplayMenu();
          closeConnection();
        }  catch (SQLException ex) {
            ex.printStackTrace();
        }
    
     }
  
public void DisplayRecord(String itemID) throws SQLException {
        try {
          
            String sql_stmt = "SELECT id,item,start,end,balance FROM items WHERE id = " + itemID;
             String selectrecordSQL = "SELECT * FROM items"; 
         
     PreparedStatement statement = conn.prepareStatement(selectrecordSQL);
     
     ResultSet resultSet = statement.executeQuery(selectrecordSQL);
        

            if (resultSet.next()) {

                ResultSetMetaData metaData = resultSet.getMetaData();
                int numberOfColumns = metaData.getColumnCount();
                System.out.print("\n\n");
                System.out.print("DATABASE RECORD LISTING");
                System.out.print("\n\n");

                for (int i = 1; i <= numberOfColumns; i++) {
                    System.out.printf("%-21s", metaData.getColumnName(i));
                }
                System.out.println();

                do {
                    for (int i = 1; i <= numberOfColumns; i++) {
                        System.out.printf("%-21s", resultSet.getObject(i));
                    }
                    System.out.println();
                } while (resultSet.next());

                System.out.println();

            } else {
                System.out.println("No database records found.");
            }

            
        } catch (SQLException ex) {
            System.out.println("The following error has occurred: " + ex.getMessage());
        }     
       
 } 
}


View.java

package InventoryJDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;


/**
 * NetBeans IDE 8.2
 * @author Mr. Jake R. Pomperada
 * April 1, 2018  Sunday
 * Bacolod City, Negros Occidental
 */

public class View {
     static Connection conn;

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String url = "jdbc:mysql://localhost:3306/inventory";
            conn = DriverManager.getConnection(url, "root", "");
        } catch (Exception e) {
            e.printStackTrace();;
        }
    }
    
    static void closeConnection() {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    
     View () throws SQLException { 
        
       
       try {
         System.out.println("You selected option 2: View Database Record");
                
         DisplayRecords();
             
         System.out.println("\n");
         InventoryMenu.DisplayMenu();
          closeConnection();
        }  catch (SQLException ex) {
            ex.printStackTrace();
        }
    
     }
  
public void DisplayRecords() throws SQLException {
        try {
          
            String sql_stmt = "SELECT id,item,start,end,balance FROM items ";
             String selectrecordSQL = "SELECT * FROM items"; 
         
     PreparedStatement statement = conn.prepareStatement(selectrecordSQL);
     
     ResultSet resultSet = statement.executeQuery(selectrecordSQL);
        

            if (resultSet.next()) {

                ResultSetMetaData metaData = resultSet.getMetaData();
                int numberOfColumns = metaData.getColumnCount();
                System.out.print("\n\n");
                System.out.print("DATABASE RECORD LISTING");
                System.out.print("\n\n");

                for (int i = 1; i <= numberOfColumns; i++) {
                    System.out.printf("%-21s", metaData.getColumnName(i));
                }
                System.out.println();

                do {
                    for (int i = 1; i <= numberOfColumns; i++) {
                        System.out.printf("%-21s", resultSet.getObject(i));
                    }
                    System.out.println();
                } while (resultSet.next());

                System.out.println();

            } else {
                System.out.println("No database records found.");
            }

            
        } catch (SQLException ex) {
            System.out.println("The following error has occurred: " + ex.getMessage());
        }     
       
 } 
}


items.sql

-- phpMyAdmin SQL Dump
-- version 4.7.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Apr 01, 2018 at 03:36 AM
-- Server version: 10.1.28-MariaDB
-- PHP Version: 7.1.11

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `inventory`
--

-- --------------------------------------------------------

--
-- Table structure for table `items`
--

CREATE TABLE `items` (
  `id` int(11) NOT NULL,
  `item` varchar(200) NOT NULL,
  `start` int(10) NOT NULL,
  `end` int(10) NOT NULL,
  `balance` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `items`
--

INSERT INTO `items` (`id`, `item`, `start`, `end`, `balance`) VALUES
(1, 'Hotdog ', 35, 20, 15),
(3, 'Milo', 120, 50, 70),
(4, 'Beer', 500, 200, 300),
(5, 'Bread', 750, 150, 600),
(6, 'Meat', 800, 400, 400);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `items`
--
ALTER TABLE `items`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `items`
--
ALTER TABLE `items`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;