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